Data Validation in Excel

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

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

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

Select the command Data – Data Validation

Selecting Data - Data Validation command

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

Then, you can add an Input Message to act as a reminder of the data validation rule…

Data Validation input message

Then you can add an error alert message in case someone doesn’t enter a number in the allowable range…

Error Alert message

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

If you do enter an invalid number, this is what your Error Alert will look like…

Error Alert in action

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

Then, highlight the cells to paste it to and select the lower section of the Paste menu and click Paste Special

Selecting 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…

Data Validation now copied and pasted

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…

Leave a Reply

Your email address will not be published.