There is another way to use the AutoSum icon in the standard toolbar. This alternate method will show you the totals of the visible cells, ignoring cells in hidden rows. Episode 350 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel podcast, I'm Bill Jelen.
Continuing the series of podcasts with new and better tricks since I've written the book, one trick that is in the book is the AutoSum icon.
If you select the series of blank cells underneath columns of numeric data, and hit the Σ, the sigma up in the toolbar, Excel will automatically add a SUM function to all of those cells.
Now another tip is the AutoFilter feature.
If you turn on Data, Filter, AutoFilter, you have a drop-down next to every heading, and you can choose to see the records for just a particular customer.
Now, here's the amazing thing, once the data is already filtered, and you're seeing just a subset of the records, if you repeat the AutoSum trick, instead of getting a SUM formula, you will get a subtotal formula.
And the subtotal is set up to only show you the value of the visible rows, so this particular customer buy 2600 units, if I choose another customer and scroll down, and we'll see that that customer purchased 29100 units.
That subtotal function is pretty hard to remember, =SUBTOTAL(9, and then the range, but using the AutoSum icon once your data is already been filtered is a great way to very easily create that formula.
Hey, thanks for stopping by, we'll see you tomorrow for another podcast from MrExcel!
Continuing the series of podcasts with new and better tricks since I've written the book, one trick that is in the book is the AutoSum icon.
If you select the series of blank cells underneath columns of numeric data, and hit the Σ, the sigma up in the toolbar, Excel will automatically add a SUM function to all of those cells.
Now another tip is the AutoFilter feature.
If you turn on Data, Filter, AutoFilter, you have a drop-down next to every heading, and you can choose to see the records for just a particular customer.
Now, here's the amazing thing, once the data is already filtered, and you're seeing just a subset of the records, if you repeat the AutoSum trick, instead of getting a SUM formula, you will get a subtotal formula.
And the subtotal is set up to only show you the value of the visible rows, so this particular customer buy 2600 units, if I choose another customer and scroll down, and we'll see that that customer purchased 29100 units.
That subtotal function is pretty hard to remember, =SUBTOTAL(9, and then the range, but using the AutoSum icon once your data is already been filtered is a great way to very easily create that formula.
Hey, thanks for stopping by, we'll see you tomorrow for another podcast from MrExcel!
