See all How-To Articles

How to Change Data Validation in Excel & Google Sheets

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

 

Datavalidationchange intro

 

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.

 

Datavalidationchange ribbon

 

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

 

Datavalidationchange amend allow

 

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

Examples

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

datavalidation change adjust

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

datavalidation change lessthan

Selecting between will retrict the user to the minimum and maximum values allowed.

datavalidation change between

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

Datavalidationchange decimal

 

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.

datavalidation change dates

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.

datavalidation change today

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

Datavalidationchange datetime

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.

 

Datavalidationchange gs amend

 

  1. 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.