Excel Tip – Find invalid data January 28th, 2013

Vinod Kumar

Recently in our SQL Server Bangalore Usergroup we had an interesting question and I thought to write this blog so that others can also benefit from the same. The question from our member was, they have an Excel sheet which is a dump of data from another system (some Export). His problem involved identifying the invalid records from that dataset of dates which the file contains. Here we are not going to talk about moving the records, but in this post we will try to quickly identify which of these are invalid rows.

One possible Solution

I don’t want to say this is the ONLY option. But this for sure has been the easiest option for me. I would love to hear if you have other methods. We can use macros and other mechanisms too. But that is out of scope of this blog.

I start the task by formatting our column in the format it has been presented to us. For this I have used a quick format option by selecting the “column B” in this example. We can select custom format based on the input we receive.

image

Next we will use the DATA Tab to add some validations. Here I have selected “Data Validations” and given some range based on the values I have got. We can also give values “greater than”, “lesser than” etc. In our example I have given between two date ranges.

image

Now the interesting part comes. Use the drop down of “Data Validation” and select “Circle Invalid Data”.

image

The magic of Excel starts and we can see the invalid rows marked. The sample output looks like this:

image

Hope you enjoyed this exploration. These options have been inside Excel for a long time, we just need to identify them and explore these options to make Excel out tool for productivity. If you want to remove the validation Circles, just select “Data Validation” and “Clear Validation Circles”.

In future blogs, I will share other tips that I come across using Excel and other Office productivity tools. Do feel free to drop-in your comments if you found this interesting.

Tags: , , , , , , , , ,

This entry was posted on Monday, January 28th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


3 Responses to “Excel Tip – Find invalid data”

  1. Jack Owens says:

    Great information

  2. Anup Nayak says:

    Neat

Leave a Reply