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.
AutoFilter is a tool that, in essence, lets you hide the rows of a spreadsheet that you don’t want to see. For example, if you have a list of invoices you could use AutoFilter to look at the invoices for just a particular customer, sales rep, or date range. You can even do things like show only invoices over a certain dollar amount. Let’s look at how to use it.
Make sure your cursor (the active cell) is within the list or table of data that you want to filter, then click the Sort & Filter button and choose Filter. You’ll find the button at the right end of the Home ribbon. You’ll see a down arrow button appear at the right edge of the top cell in each column of your table. Click that button in the column you want to filter by, and you’ll get a list of each unique entry in that column. You can clear the checkbox next to the entries you don’t want to see, or clear the All checkbox and then check just the item(s) you want to see. Your list will now be filtered to only show items that match what you have chosen. If you want to, you can also apply additional filters to other columns.
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.
When you are done with your filter, click the down arrow again and choose Clear Filter. Or to turn off AutoFilter completely, go back to the Sort & Filter button and choose Filter.
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.
When explaining how to turn on AutoFilter, I said to make sure your cursor was in the list and then click the Filter button. When you do that, Excel tries to determine which cells are part of the list you want to filter. And usually it guesses right. But if there are any breaks in your data, that is empty rows or columns, Excel may exclude the rows or column after those breaks. For that reason I usually select my entire list/table before turning on AutoFilter. You can do that by highlighting the exact cells that make up your list, or, if there isn’t any non-related data below your list, just use your mouse to select all of the applicable columns.
If you watch the video you’ll see all of this demonstrated plus a couple of other really useful AutoFilter tricks.
