One of the most valuable features when analyzing data in Excel is the ability to filter your data in place. The hours of work that it has saved me made me choose AutoFilter as the Excel tip for my first newsletter almost 6 years ago. But I decided it was worth revisiting since more than half of my current subscribers weren’t around then and a few versions of Excel have gone by since then. I’ll be using Excel 2010 for this example.
In addition to the selection check boxes, there will be a menu option for Text Filter, Number Filter, or Date Filter depending on the format of that column. For text columns you’ll find options such as Equals, Does Not Equal, Begins with, etc. Numeric columns will have options including Equals, Does Not Equal, Greater/Less Than, Between, and even Top 10. Date column options include Equals, Before, After, Between, Tomorrow, Next Week, etc. All three also have a choice for Custom Filter that allows you to enter two and/or filter conditions.
The obvious benefit is that you can dynamically change which records you are looking at without actually deleting, or even rearranging your data. But there are some other very valuable uses for AutoFilter.
First, you can use AutoFilter to locate empty cells. In addition to filtering for any specific entries, you can choose to see blanks. This will let you easily identify areas of missing data in your list.
AutoFilter is also very useful for finding certain types of inconsistent or incorrect data. For example, if you have a column for State that should only include two letter postal abbreviations, scrolling through the AutoFilter options will let you easily spot entries such as N.Y. or Okla. And a date with the year entered as 2105 instead of 2015 will stand out at the bottom of the filter choices. Once you find the errors you can filter on them so they are easily corrected.
If you watch the video you’ll see all of this demonstrated plus a couple of other really useful AutoFilter tricks.