Data Validation: Ensure Your Data is Clean and Your Reports Are Accurate

AUTHOR:  JASON MORRELL 

Jason Morrell

Validating data is important. Whether you do it at the point when data is entered or you want to check a finished workbook, the data validation process is a good investment of your time.

Good data IN means accurate data OUT. In other words, your reports will reflect the true results.

In this post you'll discover what kinds of data can be validated, how to set up and manage the data validation settings and see some examples being constructed.

1. Why does data need to be validated?

Many problems in Excel can be traced back to bad data, which includes numerical data that is out of range, incorrect dates or typos in the text.

Data Validation allows you to 

  • Check data it is entered and if it doesn't meet the conditions that you have set, display a warning.
  • Check existing data and highlight invalid data with thick red ovals.

When you identify invalid data at the front-end and address it, the results and reports produced on the back end are more accurate.

When data validation is applied, it ensures that you are working with ‘clean’ data and to some degree gets you towards the holy grail of creating an IDIOT PROOF spreadsheet! (Famous Last Words)

2. What type of data can be validated?

  • Text may be validated to ensure it is a particular length.
  • Text may be validated to ensure it is a particular length.
  • Numbers, Dates and Times may be validated to ensure they fall in a specified range or are greater than, greater than or equal to , less than or equal to, equal to or not equal to, a specified value.
  • Lists may be specified to restrict the user to a set number of choices. This eliminates data entry errors.
  • Beyond the basics, custom validation formulas can be created to give you even more control.

3. How to apply Data Validation

To create validation rules:

1.  Click the cell or cell range that you wish to validate.

2.  Click the Data Validation icon on the Data ribbon. If your screen displays a large icon, the top half will display the dialog directly, but the lower half will display a sub-menu where you need to click Data Validation to open the dialog.

3.  In the dialog, three tabs are presented. Settings, Input Message and Error Alert tab.

Setting the data validation rules (Settings tab)

4. Set the Validation rules (Settings tab)

Setting the rules (settings tab)

This tab is used to specify what valid data looks like.

1.  Choose an option from the Allow box.

2.  Complete the boxes that are then displayed (each type has different options. Following are some examples.

  • Whole number between 1 and 100.
  • Decimal greater than 2.56.  
  • Date less than or equal to 01/06/2025.  
  • Text Length less than 50 (e.g. for an address line)  
  • List. Show valid options in a drop-down list.  
  • Custom: Create your own validation formulas.

5. User Instructions (Input Message tab)

Setting the data validation rules - tell the user what to do (Input Message tab)

This tab is used to give the user instructions or guidelines about what data to enter in the cell.

1.  Enter a title and an instruction.

2.  The input message is optional. To switch it off, clear the Show input message when cell is selected box.

When the user clicks the cell that contains data validation, a screen tip is displayed as a guide.

Whatever is tyoped in the Input Message tab shows as a screen tip on the worksheetProviding the user with instructions (input message tab)

6. Alert the user of errors (Error Alert tab)

This tab is used to display an alert message indicating that the data is invalid.

1.  Enter a title and an error message to highlight the problem.

2.  Set a Style. The style can be changed to be strict or lenient.    

  • The Stop style does not accept invalid data. You must fix it or cancel.
  • The Warning style is slightly warmer and lets you continue with invalid data if you wish.
  • The Information style is friendly and relaxed. You can click OK to let invalid data go through.

Or untick Show error alert after invalid data is entered to switch off the alert message.

Alerting the user of an error (Error Alert tab)

7. Highlight invalid data on your worksheet

Whether you deliberately let invalid data through the net, or you have just set up validation on an existing worksheet, you can instantly flag all invalid data.

1.  Click the drop-down arrow on the Data Validation icon (on the Data ribbon).

2.  Choose Circle Invalid Data.

3.  To clear the circles, click the Data Validation button and choose Clear Validation Circles

Choose 'Circle invalid data' to highlight any cells that fail the validation rules set for them
This is what circled invalid data looks like. You can't miss it!

8. Highlight all cells with validation rules

To locate cells that have active validation rules:

1.  Press F5 or Ctrl G, then click Special... .

    Or select the Home ribbon | Find & Select | Go To Special

2.  Click the Data Validation option button (bottom-right).

3.  Click All to find every cell with validation or Same to fond those cells with matching validation.

4.  Click Ok

Highlighting validated cells

9. Ex01: Whole number > 100

In the Data Validation dialog, choose Whole Number | Greater than | 100

10. Ex02: Decimal Number <= 123.45

In the Data Validation dialog, choose Decimal | Less than or equal to| 123.45

11. Ex03: Date is in date range

In the Data Validation dialog, choose Date | Between | Start date and End date

12. Ex04: Text length is <= 15 characters

In the Data Validation dialog, choose Text length | Less than or equal to | 15

13. Ex05: Drop-down lists

Drop-down lists are one of the most effective ways to ensure you get valid data. Just pick from the list.

First you must set up your list.

1.  Type or paste the items you wish to present in your drop-down list.

2.  Select the cells and name the cell range (remember, no spaces, but underscores are allowed).

3.  Format the table (Home ribbon | Format as Table). This makes future maintenance easy because when you add, remove or change items in your list, the table expands or shrinks automatically, and the changes are reflected immediately on your drop-down list.

Then create your drop-down list.

1.  Select the cell(s) in which you wish to create a drop-down list.

2.  Click the Data tab.

3.  Click the Data Validation icon on the Data ribbon

4.  In the Data Validation dialog, click the Settings tab.

5.  In the Allow box, choose List.

6.  Position the cursor in the Source box.

7.  Press F3 to display a list of all named cells or cell ranges (if you named the range).

8.  Choose your named range from the list. The Source box will now say, =<YourListName>.

9.  Populate the Input Message and Error Alert tabs as necessary.

10.  Close the dialog box.

Once the setup is complete, always test it, then propagate.

1.  Click the drop-down arrow on the cell to display the drop-down list.

2.  Select an item from the list. You already know it’s valid.

3.  Overtype the contents of the cell with a valid list option. No problems.

4.  Deliberately mis-type something in the cell. That’s when the error message is displayed.

5.  Once you know it works, you can autofill the entire cell down or just the validation component using Paste Special è Validation.

Then’ it’s just a case of maintaining the list. Here’s some pointers.

  • To extend the list of options, simply add new entries onto the end. Because the table is formatted (assumed you followed the suggestions above) the table boundary will extend automatically.
  • Put all your list data into separate columns on one sheet.  
  • Once everything is set up, right-click on the sheet tab and choose 'Hide'. As the creator/designer, you know the sheet is there, but it adds a simple layer of protection by removing it from casual observers and helps avoid accidental (or deliberate) corruption. To display the sheet again, right-click on the sheet tab and choose 'Unhide'.

14. Key Takeaways

  • Data Validation is used to validate data as it is entered or sometimes after it has been entered, to ensure that it meets certain criteria.
  • The Data Validation tool is found on the Data ribbon. It has three tabs.  
  • The Settings tab is where you say what you are checking for. You can check for whole numbers, decimal numbers, dates, time, text length. The usual array of math comparison operators are available including equals, not equal or equal to, greater than, greater than or equal to and between. You can also create a drop-down list which is the easiest, quickest and most efficient way of making sure you get clean data. For more control you can create custom validation formulas which is covered in a separate post.

  • The Input Message tab is a short instruction telling the user what to do.  

  • The Error Alert tab is what pops up when the user enters invalid data.
  • On the Error Alert tab, there are three Stop stylesStop, Warning and Information. Each has a different symbol. The Stop style is the most severe and the Information style is the most lenient.
  • To highlight invalid data, click the lower half of the Data Validation icon on the Data tab and choose Circle Invalid Data. To remove the mark up, choose Clear Validation Circles.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
10 Steps to a Perfect Print Page Setup in Excel With Auto-Repeat Headings and Controlled Page Breaks
Read More
Excel Auditing Checklist: 2 Auto Features and 4 Tools to Quickly Identify and Rectify Errors BEFORE You Publish
Read More
Supercharge Your Cut and Paste: Popular Methods, Shortcuts and 2 Smarter Approaches
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Office Mastery
>