- Types of Data You Enter into Excel
- Entering Different Types of Data into a Cell
- Controlling the Next Cell Selection
- Using Copy, Cut, Paste, Paste Special to Enter Data
- Using Text to Columns to Separate Data in a Single Column
- Inserting Symbols and Equations into a Cell
- Using Web Queries to Get Data onto a Sheet
- Using Series to Quickly Fill a Range
- Editing Data
- Working with Tables
- Fixing Numbers Stored as Text
- Spellchecking Your Sheet
- Finding Data on Your Sheet
- Using Data Validation to Limit Data Entry in a Cell
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using Data Validation to Limit Data Entry in a Cell
Data validation, found under Data, Data Tools, Data Validation, allows you to limit what a user can type in a cell. For example, you can limit users to whole numbers, dates, a list of selections, or a specific range of values. Custom input and error messages can be configured to guide the user entry.
The available validation criteria are as follows:
- Any Value—The default value allowing unrestricted entry.
- Whole Number—Requires a whole number be entered. You can select a comparison value (Between, Not Between, Equal To, and so on) and set the Minimum and Maximum value.
- Decimal—Requires a decimal value be entered. You can select a comparison value (Between, Not Between, Equal To, and so on) and set the Minimum and Maximum value.
- List—Requires user to select from a predefined list, as shown in Figure 3.22. The source can be within the Data Validation dialog box or can be a vertical or horizontal range on any sheet.
Figure 3.22. Provide users with a list of entries to choose from.
- Date—Requires a date be entered. You can select a comparison value (Between, Not Between, Equal To, and so on) and set the Minimum and Maximum value.
- Time—Requires a time be entered. You can select a comparison value (Between, Not Between, Equal To, and so on) and set the Minimum and Maximum value.
- Text Length—Requires a text value be entered. You can select a comparison value (Between, Not Between, Equal To, and so on) and set the Minimum and Maximum number of characters.
- Custom—Uses a formula to calculate TRUE for valid entries or FALSE for invalid entries.
Limiting User Entry to a Selection from a List
Data validation allows you to create a drop-down in a cell, restricting the user to selecting from a predefined list of values, as shown in Figure 3.22. To set up the source range and configure the data validation cell, follow these steps:
- Create a vertical or horizontal list of the values to appear in the drop-down. You can place these values in a sheet different from where the drop-down will actually be placed, then hide the sheet, preventing the user from changing the list.
- Select the cell you want the drop-down to appear in.
- Go to Data, Data Tools, Data Validation. The Data Validation dialog box opens.
- From the Allow field of the Settings tab, select List.
- Place your cursor in the Source field.
- Select the list you created in step 1, as shown in Figure 3.23. If your list is short, instead of the separate list you created in step 1, you can enter the values separated by commas directly in the Source field. For example, you could enter Yes, No in the source field (no quotes, no equal sign).
Figure 3.23. The source for the validation list can be a different sheet. You can then hide the sheet from users.
- If you want to provide the user with an input prompt, go to the Input Message tab and fill in the Title and Input Message fields.
- If you want to provide the user with an error message, go to the Error Alert tab and fill in the Style, Title, and Error Message fields.
- Click OK.
)
){kind=link}
NOTE
The font and font size of the text in the drop-down is controlled by your Windows settings, not Excel.