The Data Filter allows you to filter your data so that you only see the rows you want. It has numerous uses and will be one of you most important business tools.
To follow along go to 7.6
If YouTube doesn't work, click here
Setting up the Data Filter
As with all Excel’s tools we HIGHLY recommend that you highlight all the cells that you want to filter on. Once you have it highlighted you can click on the Data tab and on the filter button.
The only difference you will see is the little dropdown boxes that appear at the top of your selection. But you have now switched on a very powerful tool.
Running a filter
When you click on the drop down option you will see the tools available to you. As shown below we can decide what we want to see. In this example we only want to see the items called ‘Cable’ but note that we could have chosen one or more.
When we click OK it hides the other rows and only shows the rows with the word Cable in column A. Two things to note below:
- When the filter is on, the drop down symbol changes to a filter.
- The other rows are hidden, not deleted. Note that we are only seeing row 10.
Clearing the Filter
Once you have the information you want, you may want to see all the rows again. You can do this in 2 ways.
Below we have filtered on column C to see only the zeros. To clear the filter we can either:
- click on the filter dropdown and choose Select All, or
- click the Clear button on the data ribbon next to the Filter button.
The clear button works better when you have put on many filters as it will clear them all at once. The Select All option would require that you go into each filter and Select All
Other tools built into the Data Filter
Going back to the drop down button there are some other options that exist. Excel is clever enough to notice the type of data in a column so it will adapt these options depending on what it sees.
Below, on a column that has text in it you can:
- Sort the column in various ways
- Choose one or more text items
- Search for items
- Choose from a number of Text filter e.g. equals, does not equal, begins with, contains etc.
If you clicked on a dropdown in a column that has mostly numbers, Excel will give you a ‘Number Filters’ options. These will contain some extra options including the useful Top 10, above average and below average.
Multi Level Filtering
We have shown how you add one level of filters. But you can add as many as you want to find the information you are looking for. Watch the video clip to see this, but note below that we have 2 filters setup on this data.
Remember to clear the filters you can either go to each filter and click Select All or click the Clear button in the Ribbon.
Filtering on colours and icons
Like Data Sort, Data Filter is one of the few tools in Excel that notices colours and icons in a cell. If the column you are filtering on has colours or icons you will have an additional filter option available.
Below on column D we have some arrow icons. Note that the filter now shows the ‘Filter by Color’ option and when you click on it, it will show the colours and icons it sees in the column.