Mustafa asks a question of how to see the totals from only the visible rows in a filtered data set. There is an easy way to do this, but it is not completely obvious. Episode 667 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question sent in by Mustafa. Mustafa's in Washington.
He had some public transit data and had a great question.
I just have a regular data set here that I'm going to use this on.
You know basically he said. I used the autosum button, I put the totals down at the bottom but then I go through and I'll use the "Autofilter" "Data" "Filter" "Autofilter", and I'll choose a specific row or something like that here I'll just choose a customer and he's frustrated that the totals, first of all, disappear.
And he wants to figure out how you get a running total of just the filtered rows. Well, this is pretty cool.
I'm going to turn off the filter first. "Data" "Filter" turn off the "Autofilter".
Basically here's what we have to do.
Don't add those totals before you apply the filter.
You have to turn on the filter first.
So we'll go up to "Data" "Filter" "Autofilter" and then you have to apply a filter to one column otherwise the trick doesn't work.
So I'll go here and I'll choose one of the customers, will go to the last row that's visible beneath our data and now apply the "Autosum" button.
Normally the autosum button gives us a SUM formula, but here you'll see in the formula bar that is giving us a special formula.
SUBTOTAL formula at the number 9 he is asking for a total.
And the beautiful thing about this is that we get our totals down below the data.
You'll see that Boeing has 71,000.
If I choose a different customer.
Compaq for example, I change to 39,000.
Now to really make this trick work very very well, the thing I don't like about it, is sometimes if we choose a customer, who has many many records, the totals get lost. So what I'd like to do is after I've applied those totals, I'll insert a couple of new rows at the top of the worksheet, up above where the the panes are frozen.
we'll put the word "Total" up here and I'll take those live totals from the bottom and I'm going to not copy them, I'm going to cut them with "Edit" cut or Ctrl x.
Now paste those up in row 1 and now, basically we've allowed the excel autosum to build a great formula for us.
However, this formula now will always show at the top of our worksheet.
The total of all the filtered rows.
Something that's very easy to do but not obvious at all. The fact that you have to apply the filter first in order to make this great trick work.
So I want to thank Mustafa for sending this question in.
And I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Today we have a question sent in by Mustafa. Mustafa's in Washington.
He had some public transit data and had a great question.
I just have a regular data set here that I'm going to use this on.
You know basically he said. I used the autosum button, I put the totals down at the bottom but then I go through and I'll use the "Autofilter" "Data" "Filter" "Autofilter", and I'll choose a specific row or something like that here I'll just choose a customer and he's frustrated that the totals, first of all, disappear.
And he wants to figure out how you get a running total of just the filtered rows. Well, this is pretty cool.
I'm going to turn off the filter first. "Data" "Filter" turn off the "Autofilter".
Basically here's what we have to do.
Don't add those totals before you apply the filter.
You have to turn on the filter first.
So we'll go up to "Data" "Filter" "Autofilter" and then you have to apply a filter to one column otherwise the trick doesn't work.
So I'll go here and I'll choose one of the customers, will go to the last row that's visible beneath our data and now apply the "Autosum" button.
Normally the autosum button gives us a SUM formula, but here you'll see in the formula bar that is giving us a special formula.
SUBTOTAL formula at the number 9 he is asking for a total.
And the beautiful thing about this is that we get our totals down below the data.
You'll see that Boeing has 71,000.
If I choose a different customer.
Compaq for example, I change to 39,000.
Now to really make this trick work very very well, the thing I don't like about it, is sometimes if we choose a customer, who has many many records, the totals get lost. So what I'd like to do is after I've applied those totals, I'll insert a couple of new rows at the top of the worksheet, up above where the the panes are frozen.
we'll put the word "Total" up here and I'll take those live totals from the bottom and I'm going to not copy them, I'm going to cut them with "Edit" cut or Ctrl x.
Now paste those up in row 1 and now, basically we've allowed the excel autosum to build a great formula for us.
However, this formula now will always show at the top of our worksheet.
The total of all the filtered rows.
Something that's very easy to do but not obvious at all. The fact that you have to apply the filter first in order to make this great trick work.
So I want to thank Mustafa for sending this question in.
And I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
