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 imageAUTHOR

      Robbie C Wilson 

      4 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 imageAUTHOR

      Robbie C Wilson 

      4 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 

      4 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 

      4 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!

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)