How to Change Data Validation in Excel & Google Sheets

This tutorial demonstrates how to change data validation in Excel and Google Sheets.

 

 

Change Existing Data Validation

If you already have data validation rules set up in your worksheet, you can easily amend the validation should your requirements change.

1. Select the range of cells where the data validation rule is set.
2. In the Ribbon, select Data > Data Tools > Data Validation > Data Validation.

 

 

3. Amend the Allow drop-down list under Validation criteria to the type you require.

 

 

There are seven different data types you can select from the Allow box of the Validation criteria:

• Whole number (no decimals allowed)
• Decimal (allows decimal points)
• List (creates a drop-down list)
• Date (only allows valid dates)
• Time (only allows valid times)
• Text length (restricts the user to entering text of a certain length)
• Custom (allows user to enter a custom formula)

Examples

Changing the lower and upper limits lets users adjust the limits of the whole number allowed to be typed into the cells.

 

 

Selecting less than or equal to allows you to adjust the maximum value allowed.

 

 

Selecting between restricts the user to the minimum and maximum values allowed.

 

 

Changing the Allow drop down from Whole Number to Decimal lets users add decimal places for the Hours Worked field from the example above.

 

 

If you have date or time restrictions in place, you can adjust the minimum and maximum values allowed to change the data validation rule(s).

Select between in the Data drop down, and then type in the Start date and End date.

 

 

Use the TODAY Function to indicate today as the Start date and then add the number of days you wish to allow to the formula in the End date.

=TODAY()

The entries below mean that valid dates are within 30 days of the current date.

 

 

Alternatively, you can link the starting and ending dates to dates that are stored in your worksheet.

 

 

To amend this type of validation, you do not necessarily have to change the actual validation rule but, instead, change the values in the cells that are being picked up by the data validation rule such as shown in the graphic above.

Change Data Validation in Google Sheets

You can change Data Validation in Google Sheets in much the same way.

1. Select cells with validation rules, and then in the Menu, select Data > Data validation.

 

 

2. Amend the starting and ending values, and then click Save.

Note that, in Google Sheets, there is only one Number option which allows both whole numbers and decimals.