Using Filters
Evan Paulus
Filters are a quick and easy way to run through a large set of data. I often use filters first, before using PivotTables or summary tables with functions, to get a sense of what kind of data I have in my dataset.
To access the Filter option, click on the Data ribbon and select Filter.
If you haven't already highlighted the cells you want to filter, Excel will make a best guess attempt. If you have a blank cell selected that is well outside of any other valued cell, Excel will give you an error. Most of the time, Excel does a good job at guessing what you want to put the filter around.
You'll know you were successful when you have little drop down arrows in each of your header cells.
Excel's filter feature has a few nuances that you will become familiar with if you begin to use it frequently. First, If you add a new column with the filter on on the right side of the current table, it will not be included within your filter. For example, the 'New Column' below was added but you'll notice that it does not provide you with a filter on column E. To fix this, simply turn off the filter and turn it back on.
On the other hand, if you add a new column by inserting one in between two filtered columns, it will automatically filter this new field. And finally, if you add a new row to the bottom of your filtered list, it will automatically add this to the list of filtered items.
Filtering is a nice and intuitive feature. Simply click on the arrows beside each column header and you'll be presented with the Filter options. The choices that are presented are dynamic and depend upon the format of the fields within that column. In the example below, the Date is formatted as a Text field and consequently the Text Filters options are shown.
Filtering is useful for a few reasons. First, it very quickly tells you the different values that exist within the table for each field. Duplicate values, such as 'Tuesday' which is found twice in the original table, is only shown once in the filtered items list. Secondly, it of course can be used to filter the list such that only certain values are displayed. I often use the filter option to quickly search for possible errors in formulas I may have written. If I write a complex formula and copy it down each row in a 10k-row table, there may be one #REF!, #DIV/0!, or #N/A error in the whole list that helps me error-proof my formula.
You can also set a filter on more than one column. If you were to first filter the Date column such that only 'Tuesday' displayed and then selected the Store filter, you will notice that only the options associated with the Date of 'Tuesday' are displayed.
To clear the filter from just one field, you can click that drop down arrow for that field and click 'Clear Filter from...'. To clear all filters, you can use the 'Clear' button on the Data ribbon.
If you're dealing with a very large list, Excel won't try to display all of the possible options. Instead, it will only list the first 10,000 and inform you that not all options are showing. The filters still actually work though, even for numbers not found in the first list of 10,000. You can still use the 'Number Filters' option or type a value in the Search box.
In all, Excel's Filter option is a huge timesaver if you learn these few tricks.