See all How-To Articles

How to Limit / Restrict Cell Values in Excel & Google Sheets

This tutorial demonstrates how to limit cell values in Excel and Google Sheets.

 

limit restrict cell values 3

 

Restrict Cell Values

In Excel, you can limit/restrict cell values by setting upper and lower limits using Data Validation. Say you want to allow only numbers between 10 and 20 in range B2:B10.

  1. Select the range where you want to restrict cell values (here, B2:B10), and in the Ribbon, go to Data > Data Validation.

 

limit restrict cell values 1

 

  1. In the Data Validation window, (1) choose Whole number from the Allow drop-down list, (2) enter lower and upper limits in the Minimum and Maximum boxes (10 and 20), and (3) click OK.

 

limit restrict cell values 2

 

As a result, you can now enter only whole numbers between 10 and 20 in cells B2:B10.

 

limit restrict cell values 4

 

If you try to enter a number outside these limits (e.g., 5), Excel won’t let you to do it and will display an alert message.

 

limit restrict cell values 3

 

Note: You can add your own error message. You can also add an input message to provide information on which values are allowed. Another option is to insert data validation using VBA code.

Restrict Cell Values in Google Sheets

Google Sheets also provides a possibility to limit cell values through data validation.

  1. Select the range where you want to restrict cell values (here, B2:B10), and in the Menu, go to Data > Data validation.

 

google sheets limit restrict cell values

 

  1. In the Data validation window, (1) choose Number for Criteria, and (2) enter upper and lower limits. (3) Check Reject input and (4) Show validation help text, and (5) enter an error message (or leave the default). Finally, (6) click Save.

 

google sheets limit restrict cell values 2

 

You can now enter only numbers between 10 and 20 in cells B2:B10.

 

google sheets limit restrict cell values 4

 

If you enter any other value outside these limits, you will get an error message.

 

google sheets limit restrict cell values 3