Excel Filters are right at home with large tables of data.
You can use them to limit the data displayed in your table to only that which meets one or more criteria you specify.
When your data is filtered Excel hides the rows that do not meet your criteria.
You can apply them to more than one column, with each additional filter being added to the current set to further reduce the data displayed.
For this tutorial I’ve borrowed some data from Phil (my husband).
You see Phil is a keen Eve fan. No, Eve is not another woman, although I do sometimes feel a bit widowed by Eve. Eve is an online sci-fi computer game where you fly fantasy space ships, in a fantasy galaxy, fighting fantasy battles to feed your obsession with geeky stuff.
I won’t bore you with anymore details on how the game works, other than to say there’s a lot of buying a selling of (fantasy) commodities and as a result the game generates large tables of data. Perfect for filters.
Inserting Excel Filters
1. First make sure your data is in a tabular format. That means there are column headings or labels, but no row labels, and preferably no blank columns within your table.
2. On the Data tab in the Ribbon go to the Sort & Filter section and click on the Filter button.
3. Down arrows will appear in each column heading like this:
That’s it. You're ready to go!
How to Use Filters
You can create 3 types of filters:
1. by numeric values - available when the column contains numbers
2. by text that meets a certain criteria - available when the column contains text
3. by cells that meet a format style – available when the column has font or cell formats.
Clicking on any of the filter arrows opens a menu of tools where you can select from the different types relevant to the type of data in the column.
Note: The icon on the filter button changes when you have a filter in place.
Tip: To see what filter is applied hover your mouse over the icon and a screen tip will appear.
1. Sorting by A to Z and vice versa is as it suggests. Just be aware that once you select one of these sort options you cannot undo it.
2. Sort by Colour is great if you’re using Conditional Formatting, or even if you just have different coloured text or cell fill.
3. By colour allows you to only display rows that meet the colour criteria you choose, whether it be the font colour or the cell colour.
4. Or simply tick or un-tick the items you want to filter from the list.
5. Text Filters (see menu below) allow you to specify criteria that matches a text value.
From the menu above I selected Text Filter > Equals and the window below opens to allow you to specify your criteria.
If you select Filters on a number column (see image below) you get slightly different menu options, including Less Than, Greater Than, Top 10, and Averages to name a few.
Dates or Times
When you apply a filter on a column containing dates you are presented with different options in the menu (see image below)
You will notice in the list that the dates are grouped by year, then month and so on.
You can either select specific dates from this list or you can select a time sensitive filter by clicking on Date Filters > and then selecting from the options available.
The time sensitive filters, like Today, Tomorrow, Next Month etc. are dynamic, meaning they will automatically update as time goes on. Each time you open the workbook Excel will reapply the filters based on the current date on your computer.
Tip: if you use a date filter it’s a good idea to format your dates. For example; if you’re filtering by month, format the date column as mmm-yy.
How to Apply a Quick Filter
You can apply a quick filter based on the criteria that matches the active cell (see image below).
For example; my active cell below contains the text ‘Rens’ in a red font with pink fill. When I right click on the cell, and select Filter, Excel brings up a list of options based on the active cell.
How to Clear Filters
Simply click on the filter button and select ‘Clear Filter’.
Or to clear all click on the Data tab of the ribbon and select ‘Clear’ from the Sort & Filter group.
So, you can see from the options available in the windows above that there is a vast range of criteria you can specify. I recommend you have a play around with them. Since most of it is self explanatory I won’t bore you now.
1. Summing filtered data with the SUM, COUNT, AVERAGE, MIN, and MAX formulas (and some others) results in Excel including both the hidden and visible cells in your formula. If you only want to sum the visible cells in a filtered set of data you need to use the SUBTOTAL Formula.
2. When you use the Find tool to search filtered data Excel will only search the data that is visible. To search all of your data you need to clear your filters.
3. Using the sort options in the filter is not reversible.
4. You cannot apply more than one filter to one column, they're mutually exclusive.
5. Make sure each column only contains one type of data. E.g. don’t have dates and text in the same column. The filter can only be applied to one type of data in each column.