Data Validation in Excel 2007 and Excel 2010
Guide to Data Validation to ensure Data Integrity and Data Accuracy in Excel 2007 and Excel 2010
Hi, and welcome to my latest hub on Excel. Today, we are going to look at data validation. Data validation allows you to control what a user types into specific cells. This allows you to control the behaviour of the people who use your spreadsheets and ensures that your workbooks have a high level of data integrity. It is highly customisable and there are a number of different ways to use validation:
- You can specify exactly what users are allowed to put in a cell e.g. whole numbers, dates or text
- A minimum and maximum value that can be entered can be defined. In addition, not between, equal to, not equal to, greater than and less than can also be used
- Formulas can also be used to control a user’s input
- An input message can be defined to guide the user as to what they are allowed to place in the cell
- Errors are also fully configurable with three levels, Stop, Warning and Information
Configuring an Input Message when a cell is selected in Excel 2007 and Excel 2010
Before restricting either one or a range of cells content, users of your spreadsheets must be aware of what is allowed in those cells. We can achieve this with an input message. When a cell is selected, the input message will be automatically displayed so that the user knows what you are expecting:
In the above figure, when a user clicks on B7, the message tells them to insert a number between 1 and 5. To configure the message:
- Choose the cell (or cells) you want the message to be applied to
- Click on the Data Validation button which is in the Data Tools group on the Data tab
- Select the Input Message tab
- Tick the Show input message when cell is selected box
- Enter a Title and Input message of your choosing
- Click OK to finish configuring the message
Creating data validation criteria, for example between two values for a cell or cells, in Excel 2007 and Excel 2010
Once we have configured the message that the user receives when they click on the cell whose contents we want to control, the next step is to create validation criteria. For our first example, we want to ensure users enter a number between 1 and 5. To do this:
- Click on the cells you want to add the criteria to (I used B7 once more)
- Select the Data Validation button
- On the Settings tab, change Allow to Whole Numbers
- Leave Data as between
- For Minimum enter 1 and for Maximum enter 5
- Click OK to complete the configuration
Now, try if I try to enter 6 into B7, I receive the following when I press Enter:
Using a formula to create data validation criteria in Excel 2007 and Excel 2010
As well as using the normal mathematical operators, between, less than greater than etc you can also use formulas to create your data validation criteria.
In this example, suppose I am a maths teacher and I want to write a test using Excel. I have the following questions and I want to restrict students to only being able to enter the correct answer into the cells below each question
- First, I select G15 and click Data Validation
- On the Settings tab, under Allow, this time I choose Custom
- Now a Formula box appears
- I enter the following formula into the box: =IF(G15=SUM(G4:G13),TRUE,FALSE). I will explain exactly why we use this formula in the next section.
- Click on the Input Message tab and configure a message to appear when the cell is selected
- Hit OK to complete the configuration
Note: Excel required that you use a formula that calculates a logical (True / False) value, so you must use an appropriate function such as AND, IS, IF, COUNTIF etc. in your formula.
This is not as complicated as it sounds. Just take your formula:
- In my case I wanted to know the sum of the cells G4 to G13 so I used =SUM(G4:G13)
- Next, you add the target cell which in my case is G15
- So the formula becomes G15=SUM(G4:G13)
Finally, add the IF statement =IF( along with ,TRUE,FALSE) which calculates a logical value, so that
The formula becomes
The formula for the next question, What is the mode? Is:
Configuring an Error, Warning or Information alert for Data Validation in Excel 2007 and Excel 2010
The final part of configuring data validation on a cell or cells in your workbook, is to design an error, warning or information alert that will pop up when a user of your spreadsheet enters the wrong data into a cell you are controlling.
As you can see from the figure below, the default Warning message is not especially user friendly or informative.
To configure an alert that fits with the audience of your workbook and is more useful and informative, first:
- Click on the Data Validation button
- Select the Error Alert tab
- Choose from Stop (Red Cross)
- Warning (Yellow Exclamation Mark)
- Information (I in a blue circle)
- Choose a Title
- Add text to the Error Message
You can see from the figure above, I have created an Information alert for my students if they submit an incorrect answer.
The box that pops up should a student get the answer wrong is shown below.
Using Validation Circles in Excel 2007 and Excel 2010
One very useful feature of data validation is the ability to circle all cells that contain invalid data. This allows you in a single step to see all the data that is outside the criteria of those cells regardless of how you configured the data validation. You can then correct the data to ensure it is correct or return it to the user to update. To use this feature:
- Select the drop down arrow under the Data Validation button and choose Circle Invalid Data
You can see from the figure below that the student has answered both questions wrong and as a result both answers are circled.
To clear the circles:
- Navigate back to the drop down under Data Validation and choose Clear Validation Circles
Data Validation is an excellent way of controlling what users enter into either a cell or cells in an Excel workbook. It can ensure that users only type in exactly what you want them to into the parts of your spreadsheets you want to control. With Data Validation:
- You can control exactly what type of data a user can put in a cell e.g. text, numbers or dates
- A formula can be used to control what can be inputted
- Input messages can be displayed when a user clicks a controlled cell to guide them as to what is expected
- Errors, warning or Information Alerts can be chosen and configured should a user type in the wrong thing
- Finally, validation circles show at a glance the cells that contain data that is outside the validation criteria you defined for the cell
Many thanks for reading; I hope that you found this hub informative and interesting. Please feel free to leave any comments you may have below.
© 2013 Robbie C Wilson