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?
![Limiting only numbers](https://daveberesford.co.uk/wp-content/uploads/2019/03/Limiting-numbers-960x596.jpg)
Or you have a list of people and their subscription type and you only want to allow 3 entries – “Gold”, “Silver” or “Bronze”?
![Only allowing three type of text entry to a cell](https://daveberesford.co.uk/wp-content/uploads/2019/03/Limiting-subscription-960x596.jpg)
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…
![Selecting the starting cell for Data Validation](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number1.png)
Select the command Data – Data Validation…
![Selecting Data - Data Validation command](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number2-960x307.png)
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…
![Data Validation Settings](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number3.png)
Then, you can add an Input Message to act as a reminder of the data validation rule…
![Data Validation input message](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number4.png)
Then you can add an error alert message in case someone doesn’t enter a number in the allowable range…
![Error Alert message](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number5.png)
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)…
![Data Validation entry complete](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number6.png)
If you do enter an invalid number, this is what your Error Alert will look like…
![Error Alert in action](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number7.png)
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…
![Clicking Copy on a cell with Data Validation in it](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number8-copy1.png)
Then, highlight the cells to paste it to and select the lower section of the Paste menu and click Paste Special…
![Selecting Paste Special](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number8-copy2.png)
and select Validation…
![](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number8-copy3.png)
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…
![Data Validation now copied and pasted](https://daveberesford.co.uk/wp-content/uploads/2019/03/DataVal-Number8-copy4.png)
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…