Home Home Why subscribe? Why subscribe? Training Training Purchase Purchase My Account My Account Blog Blog


Quite frequently, you’ll want to put a limit on the data you are looking at.  If, for example, you have sales data for 5 years, you may want to focus on just one or two years.  Excel Filters make it possible to do this without deleting the other data. All Excel filters work the same way on regular Ranges, and then have some different attributes when used in Tables Since Tables were introduced only in Excel 2007, we’ll start by looking at filters on regular Ranges.

Range Filters

There are two tools in Excel for creating semi-automated filters - Auto Filter and Advanced Filter.  You can also mimic Filters with the Outline tools.


Auto-Filter is the most-commonly used Filter, and you may already have used it.  On the Data Tab, it is part of the “Sort and Filter” Group. Note that a Filter can be reapplied.  This is very handy for accountants.  Quite often, with a large data set, I may filter it to check for consistency in a given column.  Let’s say I’m looking at customer records, to ensure that the countries are entered and consistent with their States/Provinces.  I may filter the data set to just show Country “USA”, then go through the data set to make sure no Canadian provinces snuck into the USA by accident.  As I go through the data set, I’ll change any erroneous provinces back to Country “Canada”, and to clean up what I’m looking at, I’ll periodically Reapply the filter.  Before this option arrived, you needed to clear the filter, then reapply it.  A small thing, but when you’re spending 2 or 3 days cleaning up a data set, every keystroke saved counts! To Apply a Filter, select the data to be filtered and click on the Filter Icon.  At least, that’s the theory.  There are a couple of tricks to be aware of when applying a filter. 1. Always start with the cursor in the column headings row, highlighting the ENTIRE row.  The example, above, shows what happens when you only select ONE cell in the data header row - Excel assumes that the row you are in must be a data row (because you only chose one cell) and puts the filters in the row above the headings row.  What we want is the second result, below; you can see that the row is highlighted, and the drop-downs that indicate a Filter are present in the column header row.  And, in case you wondered, no, I’ve never found a way to make those drop-down arrows less obtrusive.  If you have, please let me know! 2. Beware of missing columns when you select.  This is a constant problem for me, because I work with very large spreadsheets (30 - 50 columns is common).  With so many columns, I’m often way off to the right of the screen.  I highlight what I THINK are the right columns, then autofilter, and sometimes I’ve not chosen ALL the columns (say, just G - AB, not A - AB). So long as I catch the error before I filter, I’m ok.  But, if I filter and then sort, I’ll destroy the relationship between the unfiltered columns and the filtered columns.  This is one of many reasons Excel datasets are prone to corruption.  It’s a simple user error, but it can have serious time-wasting impact.  Take that extra second and make sure you have ALL your header columns before you click Filter. 3. Make sure you have no spaces between rows.  If you are working with a range, the filter will take all the rows with at least one data point in them, within the selected columns.  Once it gets down to a completely blank row (at least, as far as the filtered columns are concerned), it stops, and doesn’t add anything below the blank row to the filter set.  Before you filter, then, go to a column that you are certain has no blank rows, do a CTRL + UP (the UP arrow) to find the header row, and then do a CTRL + DOWN (the DOWN arrow) to find the last row.  Click CTRL + DOWN a second time - you should end up at row 1048576.  Reverse back to the header row (CTRL + UP twice). 4. Make sure you have a header row.  After reading the 3 comments, above, you probably realize how important a header row is. 5. Make sure EVERY cell in your header row has a value.  It doesn’t have to be meaningful if you don’t need to understand it - a blank column, just for visual clarity, can have a header value of “asdf” or something else trivial, it just needs a value so that the Filter will accept it.  Header row values do NOT need to be unique.
So, now that your filter is in place, using it is pretty self-explanatory.  But, I’ll explain anyway :) When you click one of the drop-downs in the filter (the little triangles on the header row, one for each heading) you’ll see this context menu. First off, we can sort.  Two options you may have seen before - sort from A- Z (or 0-9) and from Z-A (or 9-0).  You may not have seen “Sort by Color” before, though. Sort by Color is new for Excel 2007.  There is no default sort order, and you can’t make a custom sort order, for sorting by colour. When you choose Sort by Color, you’ll get a small dropdown - choose one of the colours and cells with that colour will be first.  I’m not sure how it figures out what’s first with “Automatic”.  The most useful option with colour sorting is “Custom Sort”.  As you can see, below, this option lets you select the sort order.  This feature is mainly useful if you’ve used Conditional Formatting to colour your data set with some kind of intelligence.  I can’t say I’ve used it often, but if you are limited by organizational directives to a certain number of columns in a spreadsheet, you may find the use of colour provides a few more ways to segment your data.
Ok, now that we’re sorted, let’s get to filtering.  We start the filtering with filter by color, and you’ll get a similar context menu as for Sorting, except this time there’s no custom option.  You can choose ONE colour to filter by.  Automatic seems to be the same as filtering out ALL colours, which can be useful. This feature is primarily useful for focusing on highlighted cells.  Let’s say you’ve just audited a listing of 1,000 receipts, and any that you audited and were OK you highlighted GREEN, and any that had errors you highlighted RED.  You can then Filter by Color to easily count the RED errors, or to put in a note beside, or simply to check that you didn’t make an error in flagging them.  Like many functions in Excel, the true value of this feature lies in working with larger data sets. Text Filters, when working with Text values, provide a set of logical operators.  When you click on one of the options in the Context Menu, say, “Equals...”, it will bring you to another pop-up (the “...” always indicates a further input box is coming). This box is pretty self-explanatory.  In the upper left-hand box, it shows our choice from the Context Menu.  Below it are two radio buttons, And & Or, to allow you to add another criteria (ie Advertising OR Other).  The box on the right contains the available choices.  You can type in something in the box as well - to return both “Advertising” and “Internet Marketing”, which both end with “ing”, you’d type in “*ing”. The “*” (asterisk, or star) is a “wild-card” which takes the place of an unlimited number of unknown characters.  Although “Advertising” and “Internet Marketing” have different numbers of characters prior to their “ing” suffixes, both will satisfy “*ing”.  The displayed drop-down selections are covering another drop-down box (often called a “combo” box) for your second, optional choice. The Filter, once selected, hides all the rows that do NOT meet the criteria.  If we choose, “Other”, we can see that certain rows are no longer showing, but they are still there: Looking just at the affected column, we can see that only one row in our selection fit the criteria.  Let’s take a look at three other, important, points. 1. The cells which do NOT match the criteria are hidden.  This is a special characteristic Excel uses, and some formulas will NOT include hidden cells, but some will.  As of Excel 2007, copying a range with hidden cells will usually exclude  the hidden cells, so that when you paste, you only see the filtered values. In earlier versions of Excel, cells copied from a range with hidden rows/columns would include those hidden cells, so that you had more data showing than you thought you did after you paste. 2. This filter ignored anything below row 17, because row 17 is blank.  As we discussed earlier, Excel will not include fully blank rows which are blank BEFORE the filter is invoked.  Blanks created AFTER the filter is invoked will remain, however, and their data, below, will also remain. 3. Excel provides two ways to see that you have a filter in place.  First off, you can see that the row number on the far left is highlighted blue.  This is somewhat helpful, but that blue is often difficult to discern from black.  Secondly, the drop- down icon has changed to a “filter” icon.  This is especially helpful when you are scanning 40+ columns, trying to find which column has been filtered...
Over to the right, you can see the drop-down for the Date filter.  This shows up, obviously, when you are filtering dates.  Notice the vast increase in options, including some very useful “intelligent” date functions.  However, the Quarter and Year functions are calendar functions, not fiscal functions, so don’t get too excited...
Last, but certainly not least, the actual filters!  These are the little checkboxes - if it’s ticked, you’ll see the data; if it isn’t, you won’t.  As you can see, all the options have been checked on this field; that’s the default.  To select just one or two items, uncheck “Select All” (blanks all the check-boxes) then select the few you really want to see. To see all but a few, just untick those few. Often, to check data integrity, you want to see only (blank).  Now, when you are looking at some fields, depending on how clean the data is, you may have true blanks (nothing in the field) or you may have “”, which will show as an empty space in the select list.  Make sure to tick both.  Another thing to be wary of; when you first create your table, the data presented in the filter list is in alphabetical order.  If you add data (through import, cut & paste, or straight data entry) those new items are added to the bottom of the list, NOT in any given sort order.  Either clear the filter (unselect autofilter) from the data set, then reapply it, or sort the data set for that field; the select list will then show everything in the proper sort order.

Advanced Filter

The Advanced Filter is mentioned here only for completeness.  I never found a real use for it, because it requires the user to create a “criteria range” which holds the filter values you want to use. The Auto-filter allows pretty much the same thing, but with a much easier user-interface. One thing unique about the Advanced filter is the ability to move the filtered data somewhere else.  However, that’s easily done with copy/paste, and doesn’t make up for the cumbersome-ness of the Criteria Range requirement. Up until Excel 2007, Advanced Filter had another primary use - to generate a list of unique items from your data set.  However, in Excel 2007, Microsoft introduced the “Remove Duplicates” function which effectively does the same thing. In short, Advanced Filter is an older tool which has little value except for folks who are used to using it from prior iterations of the program. 


These tools, unlike the Advanced Filter, will get regular use if you prepare summary schedules for others.  They are very intuitive for both the spreadsheet builder and the user. The SUBTOTAL function is somewhat related, and is detailed elsewhere on this site; it creates SUBTOTALS and an outline for a group of segmented data. Group is not technically a “filter”, but it performs a similar task, in that it hides rows or columns.  However, it is usually used for presentation, rather than analysis. Wherever you would normally “hide” a row/column, you can (and should) use Group instead.  Let’s look at the two operations to see the difference.  Here is our presentation.  We want to hide rows 2-9 so you see only the summary rows: Hiding Grouping
To Hide a row, we right-click a selection of rows and then choose “Hide” from the resulting Context Menu.
To Group a row, we select the row(s) to be Grouped, then click “Group” in the Outline Group on the Data Tab.
As you can see, when we hide the rows, the only hint we have that anything has changed is that row numbers 2-8 are missing.  To get them back (assuming we notice the loss) we have to “Unhide” the rows. Grouping, however, provides a mechanism to open/close the Group.  Clicking the “contract” box (the box with a “-”, bottom left corner of the image, above right) (pronounced “con-TRACT”, not “CON-tract”) will “hide” the rows, leaving an “expand” button to indicate the missing rows:
The result is the same as “hiding” the rows, except that we have a very obvious artefact (the “expand” button) to let us know that we have hidden rows 2-8.  We can now hide/unhide rows 2-8 at will (as can any users of the spreadsheet) without needing to hide/unhide/hide again/unhide again/... Most importantly, we can see quickly that rows 2-8 are hidden.  This is invaluable if you are hiding rows deep in a data set where you may not realize rows are hidden.  Having the expand/contract button acts as a beacon to draw your eye to the fact that rows are hidden, so you know there is data hidden that may be affecting, say, totals or other summary calculations.  I have spent many hours (in total) hunting for the reason my columns didn’t foot, only to find that a hidden row or two was messing up my calculations.  I now never “hide” ranges, but always group. Lastly, you’ll notice the “1” and “2” boxes at the top of the left margin.  They also showed up when I “Grouped”.  They act as Master Controls; if you have more than one Group, clicking “1” will CONTRACT all the Groups, and clicking “2” will EXPAND all the Groups.