ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Data Validation in Excel 2007 and Excel 2010

Updated on August 15, 2013

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

Examples of data validation in Excel 2007 and Excel 2010. An Input message appears on the left, data validation circles on the right,  and finally a customisable Information alert is shown on the bottom..
Examples of data validation in Excel 2007 and Excel 2010. An Input message appears on the left, data validation circles on the right, and finally a customisable Information alert is shown on the bottom.. | Source

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:

Configurable Input Message when a cell is selected in Excel 2007 and Excel 2010.
Configurable Input Message when a cell is selected in Excel 2007 and Excel 2010. | Source

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

Configuring the input message for data validation in Excel 2007 and Excel 2010.
Configuring the input message for data validation in Excel 2007 and Excel 2010. | Source
  • 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

Configuring the validation criteria for data validation in Excel 2007 and Excel 2010.
Configuring the validation criteria for data validation in Excel 2007 and Excel 2010. | Source

Now, try if I try to enter 6 into B7, I receive the following when I press Enter:

Default error received when a user types in data outside of the data validation criteria in Excel 2007 and Excel 2010.
Default error received when a user types in data outside of the data validation criteria in Excel 2007 and Excel 2010. | Source

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

Example of using a formula to create data validation criteria in Excel 2007 and Excel 2010.
Example of using a formula to create data validation criteria in Excel 2007 and Excel 2010. | Source
  • 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

Using a formula to function as the data validation criteria in Excel 2007 and Excel 2010
Using a formula to function as the data validation criteria in Excel 2007 and Excel 2010 | Source

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

=IF(G15=SUM(G4:G13),TRUE,FALSE)

The formula for the next question, What is the mode? Is:

=IF(I15=MODE(I4:I13),TRUE,FALSE)

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.

Default error received when a user types in data outside of the data validation criteria in Excel 2007 and Excel 2010.
Default error received when a user types in data outside of the data validation criteria in Excel 2007 and Excel 2010. | Source

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

Example of a customised Information alert configured for data validation in Excel 2007 and Excel 2010.
Example of a customised Information alert configured for data validation in Excel 2007 and Excel 2010. | Source

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.

A configured Information alert displayed when a user enters the incorrect data into a cell in Excel 2007 and Excel 2010.
A configured Information alert displayed when a user enters the incorrect data into a cell in Excel 2007 and Excel 2010. | Source

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.

Example of data validation circles which identify incorrectly entered data in Excel 2007 and Excel 2010.
Example of data validation circles which identify incorrectly entered data in Excel 2007 and Excel 2010. | Source

To clear the circles:

  • Navigate back to the drop down under Data Validation and choose Clear Validation Circles

Conclusion

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

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Hi FlourishAnyway,

      Thanks for your kind comment. I am a real Excel junkie, Excel just has so many cool things that you can do with it. I am so glad that you found my hub useful and also that you found my instructions clear.

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Hi Mel,

      Thanks for your kind comment, I am glad that you found my hub useful. Excel is such a huge application and has so many really useful functions I am still finding things I never knew existed.

    • FlourishAnyway profile image

      FlourishAnyway 3 years ago from USA

      Very nicely written Excel helper. I was an Excel junkie in my previous HR professional role, and your instructions are excellent.

    • Mel Carriere profile image

      Mel Carriere 3 years ago from San Diego California

      I'm saving a copy of your hub because I use Excel for everything and I was unaware of this data validation feature, up until now. Thank you for sharing this great information!

    Click to Rate This Article