Excel: Allow only certain values - how it works
With data validation, you can only allow certain values in cells in Excel. This means that you immediately ensure that incorrect entries cannot be made in the first place. This is particularly useful when more than one person is editing the file.
Allow only certain values in Excel
The following instructions work with all versions from Excel 2007.
- Select a cell or area to be checked as you type.
- In the menu bar or ribbon, navigate to Data> Data Review> Data Review.
- In the next window you define the corresponding criteria for the validity check in the "Settings" tab.
- In the "Allow" field you can choose between "Integer", "Decimal", "List", "Date", "Time", "Text length" and "User-defined". Any value is allowed here as standard.
- Depending on the selection, you have the option under Data to restrict values even more precisely. For example, you can only allow the entry of a date between 01/01/2017 and 31/12/2017.
- If you want to specify fixed values right away, choose "List".
- Enter the desired values separated by a semicolon.
- If the entries already exist in a table, click the button in the "Source" field, navigate to the corresponding sheet in the workbook, mark the relevant area and confirm the selection with "Enter".
- You will then see a drop-down arrow next to the assigned data check field. Now a value can be selected from the list.
- In addition, specify how empty cells should be treated during the check by either activating or deactivating the "Ignore empty cells" check box.
- To delete the data check, select the cell (s) that are no longer to be checked. Navigate to Data> Data Review> Data Review and then click the "Clear All" button.
Enter input and error messages for the data check
You can give users of the file information about the expected format and information about invalid entries. To do this, enter a corresponding text for each validity check.
- Under Data check you will find the "Input message" and "Error message" in addition to the "Settings" tab.
- Check "Show input message when row is selected".
- Enter a title and the explanatory message. As soon as the cursor is in the relevant cell, the user is shown this information as a pop-up.
- Do the same for the error message.
- If an unauthorized value has been entered, this information is displayed. The wrong value is not adopted and the user has to make a correct entry.
- If the correct values have been entered, this message is not displayed accordingly.
If data cannot be overwritten, you can lock and protect cells in Excel. You can read how to do this in the next practical tip.