How do you limit entries to spreadsheet cells?
So you have a list of people in your spreadsheet and you want to note their knowledge level on a particular skill. How do you restrict entry to that column to a number between 1 and 5?
Or you have a list of people and their subscription type and you only want to allow 3 entries – “Gold”, “Silver” or “Bronze”?
This is Data Validation, and it’s a really cool thing in Excel.
Limiting entry to numbers
You start off with your spreadsheet and select the first cell that you want to restrict…
Select the command Data – Data Validation…
In the dialog box that appears, select Whole Number from the Allow menu and enter the range of numbers you want to restrict entry to…
Then, you can add an Input Message to act as a reminder of the data validation rule…
Then you can add an error alert message in case someone doesn’t enter a number in the allowable range…
Once you click OK, you see the Data Validation input message and your cell will now only accept numbers in the allowable range (1-5 in this example)…
If you do enter an invalid number, this is what your Error Alert will look like…
How do you copy Data Validation to other cells?
You don’t have to keeping repeating the above process. You just need to click Copy on the cell you have validation in first…
Then, highlight the cells to paste it to and select the lower section of the Paste menu and click Paste Special…
and select Validation…
Once you click OK you have Data Validation now set up in all those cells, making sure you only get the numbers you want to allow…
Have a look at this video to show you the full process…
What about adding a menu of options instead?
This video shows you how you can add Data Validation to restrict cell entry to just any item from a menu you define…