How to Limit / Restrict Cell Values in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to limit cell values in Excel and Google Sheets.
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.
- Select the range where you want to restrict cell values (here, B2:B10), and in the Ribbon, go to Data > Data Validation.
- 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.
As a result, you can now enter only whole numbers between 10 and 20 in cells B2:B10.
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.
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.
- Select the range where you want to restrict cell values (here, B2:B10), and in the Menu, go to Data > Data validation.
- 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.
You can now enter only numbers between 10 and 20 in cells B2:B10.
If you enter any other value outside these limits, you will get an error message.
