Study smart | Excel superstars
Data Validation
How to ensure accurate and valid entries into cells with Excel’s useful feature
Words Traci Williams, excelace.co.uk
Data Validation is a feature in Excel that enables a user to restrict what can be entered into specific cells, to ensure accuracy of data input. You can set restrictions such as:
- Type of data (e.g. number, date, text)
- Length of entries
- A list of allowed values
- Custom formulae for more complex logic
It also lets you define Input Messages (this is a pop-up message that will appear when the cell is selected, as a guide to users) and Error Alerts (these will appear if invalid data is entered).
Data Validation is the perfect tool to ensure valid and accurate data is entered, preventing the need for data cleansing afterwards.
How can it be used?
Data Validation can be used in a wide variety of ways, including:
- Creating forms or templates for others to complete
- Collecting data that feeds into dashboards that rely on correct input
- Preventing data entry errors, such as typos, wrong dates or unexpected values
Here are some typical scenarios:
- Allow only whole numbers between 0 and 10
- Restrict input to dates within a specific range
- Dropdown list (or pick list) of options
This article will explore the above options.
Example 1: Allow only whole numbers between 0 and 10
In column E, we need to use Data Validation to ensure the user can only enter a Score between 0 and 10, as follows:
Select cells in column E, then:
- Click the Data ribbon
- In Data Tools, click the icon and select Data Validation
- Choose the Settings tab
- Click the dropdown menu and select Whole Number
1) Enter the minimum and maximum numbers (0 and 10), then press OK
Now the cells in column E will only allow whole values between 0-10 to be entered.
If a value outside of these limits is entered, you will see this generic error message (see later how to personalise that message):
The available options are:
Retry – Re-enter another number
Cancel – Remove entry
Help – Takes you to Microsoft’s website for guidance on Data Validation
This means that the user is unable to enter a value that is not between 0 and 10.
Example 2: Restrict to a date range
We need to enter a date in column D, so we’ll use Data Validation to ensure the user is forced to use the correct date format.
Repeat the steps above (Data >> Data Validation >> Settings tab) but instead of choosing Whole Number, select Date. Here you can see the option to enter a start date and finish date:
This will prevent the user from entering a date outside of 2025, and also forces them to use the correct data format.
Top Tip: This function can be used to enforce a consistent date format, even when it’s not necessary to restrict the actual date entered. For example, you might set the start date to 01/01/1900 and the end date to 31/12/3000.
Example 3: Create a pick list
In column C, we need to enter the department but typing them manually is time consuming and prone to human error as the user could include typos, abbreviations or renamed departments.
The safer (and quicker) way to enter the department would be to include a pick list for the user to select from, using Data Validation.
Repeat the steps above (Data >> Data Validation >> Settings tab) but instead of choosing Date, select List, then manually select the range containing the list you want to be able to select from, then click OK:
Each of the selected cells will then include an arrow to the right and, when pressed, the list will appear for the user to select from:
A new exciting feature of Data Validation is that you can start typing the text into the cell and the pick list will narrow down to just those items that begin with what you have typed.
This feature now prevents the user from entering any value that does not appear on the specified list.
Top Tip: You can also use a named range or a dynamic list for the source, especially if your options might change over time.
Input Messages and Error Alerts
Many users skip these tabs, but they are incredibly helpful at communicating the requirements, especially when files are used by others.
Input Message
- Appears when a user selects the cell
- Explains what is expected
- Reduces the chance of confusion
In the third example above, the Input Message would appear as:
This would appear on the spreadsheet as follows:
Error Alert
- Appears after invalid data is entered
- Three styles:
o Stop: User must enter valid data (default setting)
o Warning: Warns user but allows override
o Information: Just informs – the user can override or ignore
Use Stop when accuracy is critical. Use Warning or Information when users may occasionally need to override the rule.
In the third example above, the Error Alert would appear as:
This would appear on the spreadsheet (only when invalid data is entered) as follows:
The intention of the Error Alert is to advise users what the issue is and how it can be corrected. Please ensure they are as helpful as possible in order to be most effective.
Conclusion
Data Validation is a simple but ACE Excel feature. It may not be the flashiest function, but it’s definitely one of the most valuable – especially when you want to protect your spreadsheet from the dreaded human error.
When you give a spreadsheet to someone else – colleague, client or customer – Data Validation is your best insurance policy. It makes sure they can’t break your formulae, throw off your charts or give you a clean-up job before analysis even starts.
By using it:
- You avoid typos
- You keep data consistent
- You stop rogue entries
- You prevent date and number mishaps
- You don’t need to clean the data later
Best of all, it’s super quick to set up and easy to apply.
So next time you build a spreadsheet – especially one that’s shared – take a minute to set up Data Validation. It’s the secret weapon of every Excel ACE.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is the primary purpose of Data Validation in Excel?
2
Which of the following is NOT a built-in type of Error Alert style in Data Validation?
Note: Timelines are a special kind of Slicer meant for date fields. They allow users to filter by periods such as years, quarters, months and days.
3
Which Data Validation option would you use to create a dropdown list of fixed choices such as Sales, Marketing and Finance?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).