Error validating the formula for column
Users can either manually type the entries or select them from the dropdown-list that appears when the cell is selected.
If a value that is not in the list is typed then an error dialog is displayed as shown in figure 12 below.
Figure 4: Default Error Dialog Box We can customize this window to display a more meaningful message following these steps: Figure 7: Data Validation Rule (Decimal Number)Now, if the user enters something different than a decimal number within the acceptance criteria in B10 then an error dialog box is displayed as shown in figure 8 below.
This error message is created by following the same steps shown above in figure 5.
Figure 12: Error Alert (List)There are several cool things that can be done with lists in Excel, for example, creating expandable lists, or dependent lists. If you want to restrict the length of a text entry you can do it as well.
For example, in our sample form, user is required to type comments between 20 to 300 characters.
Figure 14: Error Alert (Text Length)On top of all the possible options shown for data validation, you can also use a formula that return a logical value to validate entries.
If user types less than 20 or more than 300 characters in B16 then an error dialog box will be displayed as shown in figure 14 below.For example, let’s say we have a column in our sheet where unique US state names need to be entered.This constraint can be enforced by following the steps given below.You can use the function Figure 15: Data Validation Rule (Text Only)Now the user will be only allowed to type text data in B4.