I. Intro
You can use data validation to restrict the format of content that can be entered in selected cells of a sheet, ensuring the content entered by different users is accurate and consistent.
Use cases:
• Restrict the cells in a gender column to accept only one predefined option.
• Restrict the cells in an employment date column to accept only dates.
• Restrict the cells in an ID card number column to accept only integers.
• Restrict the cells in an amount column to accept only integers ranging from 1 to 1,000.
II. Steps
Restrict cells to accept only predefined options
1. Select a range of cells, right-click the cells, and select Data validation.
250px|700px|reset
2. On the Settings tab, select Single option or Multiple options from the Criteria drop-down list.
250px|700px|reset
3. Configure the options:
- Custom options: Select Custom for Source and enter the options. Use a line break or a comma (,) to separate the options.
250px|700px|reset
- Reference data from other sheets: Select From a range for Source, click the Select a range icon, and select the cells that you want to reference.
Note: Deleting or modifying the referenced data will change the options used for data validation.
250px|700px|reset
4. Set the background color and drop-down arrow for options:
- Set the background color: Select Option color, click the Edit icon next to it, and set the background color for each option.
250px|700px|reset
- Set the drop-down arrow:
- Select Show arrow for dropdown list to display a drop-down arrow in selected cells, which helps users recognize that predefined options are available for the cell.
250px|700px|reset
5. Click Confirm in the bottom right corner of the Settings tab to save the settings. The following is an example of a range with dropdown arrows.
250px|700px|reset
Restrict cells to accept only numbers
1. Select a range of cells, right-click the cells, and select Data validation.
250px|700px|reset
2. On the Settings tab, select Number from the Criteria drop-down list.
3. Select a filter condition from the drop-down list below. Allow decimals specifies whether decimals can be entered. Users can enter integers in the decimal format, such as 1.0 and 2.0, even if Allow decimals is not selected.
The following filter conditions are available:
• is valid number: Only numbers are allowed.
• between (≥a and ≤b): Only numbers within the specified range are allowed. For example, the value entered must be a number ranging from 1 to 100.
• not between (< a or > b): Only numbers that are not within the specified range are allowed. For example, the value entered must be a number less than 1 or greater than 100.
• = equal to: Only the specified number is allowed. For example, the value entered must be 1.
• ≠ not equal to: Only the specified number is disallowed. For example, the value entered must be a number other than 1.
• < less than: Only numbers less than the specified number are allowed. For example, the value entered must be a number less than 1.
• > greater than: Only numbers greater than the specified number are allowed. For example, the value entered must be a number greater than 1.
• ≤ less than or equal to: Only numbers less than or equal to the specified number are allowed. For example, the value entered must be a number less than or equal to 1.
• ≥ greater than or equal to: Only numbers greater than or equal to the specified number are allowed. For example, the value entered must be a number greater than or equal to 1.
250px|700px|reset
4. Click Confirm in the bottom right corner of the Settings tab to save the settings. The following is an example of cells with data validation for numbers.
250px|700px|reset
Restrict cells to accept only dates
1. Select a range of cells, right-click the cells, and select Data validation.
250px|700px|reset
2. On the Settings tab, select Date from the Criteria drop-down list.
3. Select a filter condition from the drop-down list below.
If you select Show calendar button, a calendar icon will appear when users click the selected cells. Users can then click the calendar icon to open a date picker.
The following filter conditions are available:
• is valid date: Only dates are allowed.
• between (≥a and ≤b): Only dates within the specified range are allowed. For example, the value entered must be a date between October 1, 2021 and October 5, 2021.
• not between (<a or >b): Only dates that are not within the specified range are allowed. For example, the value entered must be a date that is not between October 1, 2021 and October 5, 2021.
• = equal to: Only the specified date is allowed. For example, the value entered must be October 1, 2021.
• ≠ not equal to: Only the specified date is disallowed. For example, the value entered must be a date other than October 1, 2021.
• before: Only dates earlier than the specified date are allowed. For example, the value entered must be a date earlier than October 1, 2021.
• after: Only dates later than the specified date are allowed. For example, the value entered must be a date later than October 1, 2021.
• on or before: Only the specified date and dates earlier than the specified date are allowed. For example, the value entered must be October 1, 2021 or an earlier date.
• on or after: Only the specified date and dates later than the specified date are allowed. For example, the value entered must be October 1, 2021 or a later date.
250px|700px|reset
4. Click Confirm in the bottom right corner of the Settings tab to save the settings. The following is an example of cells with data validation for dates.
250px|700px|reset
Limit the length of input text
1. Select a range of cells, right-click the cells, and select Data validation.
250px|700px|reset
2. On the Settings tab, select Text length from the Criteria drop-down list.
3. Select a text length condition from the drop-down list below. The following text length conditions are available:
• between (≥a and ≤b): The length of the input text must be within the specified range. For example, the input text must be a string that is 1 to 100 characters in length.
• not between (<a or >b): The length of the input text can't be within the specified range. For example, the input text must be a string that is less than 1 character or greater than 100 characters in length.
• = equal to: The length of the input text must be the specified number of characters. For example, the input text must be a string that is 1 character in length.
• ≠ not equal to: The length of the input text can't be the specified number of characters. For example, the input text must be a string that is not 1 character in length.
• < less than: The length of the input text must be less than the specified number of characters. For example, the input text must be a string that is less than 1 character in length.
• > greater than: The length of the input text must be greater than the specified number of characters. For example, the input text must be a string that is greater than 1 character in length.
• ≤ less than or equal to: The length of the input text must be less than or equal to the specified number of characters. For example, the input text must be a string that is less than or equal to 1 character in length.
• ≥ greater than or equal to: The length of the input text must be greater than or equal to the specified number of characters. For example, the input text must be a string that is greater than or equal to 1 character in length.
250px|700px|reset
4. Click Confirm in the bottom right corner of the Settings tab to save the settings. The following is an example of cells with data validation for text.
250px|700px|reset
Restrict cells to accept only checkboxes
1. Select a range of cells, right-click the cells, and select Data validation.
250px|700px|reset
2. On the Settings tab, select Checkbox from the Criteria drop-down list.
3. Click Confirm in the bottom right corner of the Settings tab to save the settings. The following is an example of cells with data validation for checkboxes.
250px|700px|reset
Set the alerting mode and help text
Set the alerting mode
1. Select a range of cells, right-click the cells, and select Data validation. Click Alert and help text.
2. If you select Show alert for When data is invalid, an alert is displayed when the entered content does not meet the criteria.
250px|700px|reset
- If you select Reject input for When data is invalid, an alert is displayed when you try to enter content that does not meet the criteria, and the content cannot be entered. You need to click Cancel and re-enter data in the correct format.
250px|700px|reset
Set the help text
1. Select a range of cells, right-click the cells, and select Data validation. Click Alert and help text.
2. Select Show help text for When cell is selected and enter the help text in the input box.
250px|700px|reset
The following figure shows an example of help text.
250px|700px|reset
Modify or delete data validation settings
Modify data validation settings
Select a range of cells, right-click the cells, and select Data validation. Modify data validation settings.
Select Apply changes to all other cells with the same validation settings to apply the new data validation settings to all cells with the same validation settings in the sheet.
Delete data validation settings
Select a range of cells, right-click the cells, and select Data validation. Click Remove validation in the bottom left corner.
Select Apply changes to all other cells with the same validation settings to remove the data validation settings from all cells with the same validation settings in the sheet.
250px|700px|reset
III. FAQs