How to Change Data Validation in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on November 14, 2022

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


Datavalidationchange intro


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.


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:


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


datavalidation change adjust


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


datavalidation change lessthan


Selecting between restricts 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


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.


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


datavalidation change today


Alternatively, you can link the starting and ending 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 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.

AI Formula Generator

Try for Free

See all How-To Articles