How to Change Data Validation in Excel & Google Sheets

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

 

 

Changing Existing Data Validation

If you already have data validation 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 in to the cells.

Selecting less than or equal too will allow us to adjust the maximum value allowed.

Selecting between will retrict 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.

You can also use the formula =TODAY() 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.

Alternatively, you can link the Start and End 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 start and ending values as required and then click Save.

Note that, in Google sheets, there is only one Number option which allows both Whole Numbers and Decimals.