Search For And Highlight Invalid Data In Your Excel Worksheets


If you find that users are consistently entering incorrect data into worksheets then using Data Validation to prevent users entering invalid data and also displaying a message to prevent them is the best way for consistent data quality.

You can read more about data validation right here.
All well and good for current and future data entry, but what about previous data entries?.

Fortunately you do not need to manually go through all of your previous records as we can use formula auditing and data validation together so you can get Excel to check at anytime for data validation conflicts. Neat!

Firstly you will need to apply a set of validation rules to a group of cells and then check for conflicts. Set up Data Validation Rules using:-

  • Data Tab
  • Data Validation
  • Set Validation Rules In Dialog Box
  • Hit Ok

In my example I only want to allow a whole number between 1 and 10 in my range of cells.

To then check for any invalid entries:-

  • Data Tab
  • Data Validation
  • Circle Invalid Data

Any cell entries that don’t fit your validation rules will be circled in red. Easy.

I have only 1 in my data set.

 

When you are ready to hide the circles again then simply do the following….

  • Data Tab
  • Data Validation
  • Clear Validation Circles

This is really a time saver especially if you have inherited a not so great data set. (We have all been there!) Let me know if you find this useful.

Other Excel Tips And Tricks You May Like

1. Perform Inexact Searches In Excel

2. Data Validation Using Data Lists To Control Data Entry

3. Data Validation In Excel Using A Dynamic List

 

Want Even More Excel Tips?

Get 101 Secrets Of A Microsoft Excel Addict….

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts