How to filter data in Excel

Posted on January 20, 2009 at 5:33 am

We have shown you how to summarize data in Excel using aggregation formulas. But what if we are asked to show only a group or subset of data? Indeed it will be troublesome to delete the other rows of data just for this purpose.

We will also show you how to filter data so that you can only see rows based on a filtering rule that you will create. We will start with the simple Excel sheet example below with 3 columns:

You can see that the sample sheet has information about machines: machine type, machine and efficiency. So to simulate a filtering scenario, I will first show the overall unfiltered average of the efficiency column using the AVERAGE formula on column C:

So that data now shows the average of all rows. Now what if we want to get the average of only the rows where MACHINE_TYPE = A; this now requires us to use the filtering capability. To turn on the filtering mode, click Data menu > Filter > AutoFilter:

You will see that all the headers will become a dropdown selection:

To filter by column A (MACHINE TYPE), choose a value on one of the choices on the dropdown. In the example below I chose “A” to show only rows where value=”A”. Once clicked, the other rows will be hidden and you will be left with all rows where value=”A”:

As an indicator that you have filtered the rows, the downward arrow on the columns dropdown changes color from black to blue. You will also see the row numbers skipping values where only the visible rows are shown. Now to add the summary of the filtered rows, you will need to use the formula called SUBTOTAL. The other formulas will not work since they will sum up all of the data including the hidden rows so Excel provided us with SUBTOTAL where you can summarize only visible cells.

Please first look at the syntax of the SUBTOTAL formula coming from the official MS Office Help document:

Based from the syntax above, we need to use this syntax to get the AVERAGE:

SUBTOTAL(1, C:C) where 1 is the corresponding function_num of the average function based on the table above. See the working example of it below:

That’s it on how to filter rows and show their corresponding summarized information. Next time I will show how to pivot data in Excel which also makes data grouping and aggregation easier for us data crunchers!

Ben Carigtan shows you how it’s done.

» Filed Under MS Office Tips

Comments

One Response to “How to filter data in Excel”

MJ said on : January 21st, 2009 at 4:08 am

There are many posts on the web on this topic but this article explains the concept/procedure pretty well.

Thanks