Filtering Data in Excel

Similar to Sorting, Filtering will get you the data you need out of a big quantity of data. We will be using the Apple Farm again for our example.

Filtering means getting the data you want and display it. And for the data you don't want, you don't display it.

So the main Filtering possibilities are

  • equals to
  • does Not Equal to
  • is great or small than
  • is between two values
  • above average
  • below the average
  • in the top 10 values.

You can have maximum 2 filters conditions....if you want more, look at the bottom of the page for the HOW TO....

APPLE FARM

Our apple farm has grown and we have now 23 trees instead of 10 and so it is more difficult to have a good overview. So we want to sort out our trees according to the total number of apple harvested. You can see the graph is also not so nice anymore....

apple farm table   

So we should now change the bottom lines Total and Grand Total.

Originally the formula was SUM(B5:B27). The problem with the SUM function is that it sums really every row even the ones that are not displayed. Now by changing the formula to

=SUBTOTAL(109,B5:B27)  and     =SUBTOTAL(109,C5:C27) and =SUBTOTAL(109,D5:D27)  for each cell respectively, the total displayed will be the one of the visible cells.

The SUBTOTAL function is discussed here.

Now let's change this into a table. If you don't remember how to do this, go quickly see the following link.

table apples

By pressing on the small inverted triangle on the right of the headers, you will see the following menu.

menu table

We used this already for the sorting, but now we will use the NUMBER FILTERS. By pressing on them the following menu appears.

greater or equal menu

We will now filter the tree that produced between 400 and 600 apples. This is known to be a healthy tree.

filter excel

The new table will look like this:

filtered table

You can see the totals are also correct and are the sums of the filtered items only.

So this is how you filter.

As said before you might want to make more complex filtering, for example trees between 0 and 200 apples as well as trees between 800 and 1500. So this is not possible here except with some complex table manipulation. The best is to use conditional formatting.

 

How to filter using very complex formulas?

If you need very very very complex and multi parameter filtering, you could use the CONDITIONAL FORMATTING first, which will highlight the data you are interested in (in a few different colors for example), and then, using the filtering, you could just do some color filtering.

 

So that's it for filtering data. We filtered it out, isn't it (;-)...

These were only some basics. Now you will learn by doing.