Create ad-hoc totals for an Excel data set where you will be filtering the data. Also in this video: Using SUBTOTAL(109,) for summing data that has been manual hidden rows. Using Ctrl+Ditto to copy the exact formula down one row.
Transcript of the video:
The MrExcel podcast is sponsored by "MrExcel XL", a book with 40+ Excel tips, Excel cartoons, ****tails, tweets and jokes.
Learn Excel from MrExcel podcast, episode 1976 - Total the Visible Cells!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Of course, everyone, the very first formula, that most people create, is the AutoSum.
You're down here below a column of numbers, you get the AutoSum button, and it will use the sum function to total the numbers above it, until it encounters either a formula or text.
Right?
Or Alt+= does the exact same thing.
Or, even better, select multiple cells and then hit the AutoSum, and it will put all those formulas, without you having to confirm the formulas.
But here is the cool trick, alright, so let's turn on the AutoFilter.
So we're going to use the Filter by Selection trick, from yesterday's podcast.
The data is filtered.
I'm going to go to the very first blank row beneath the data, and then hit the AutoSum button.
But something very different is happening, you see.
Instead of the SUM function, we get =SUBTOTAL(9 ! And what this is, this is the Total of the visible cells.
Alright so, very cool that that works.
Now here's the thing, I was doing a live power Excel seminar almost ten years ago, in Fort Wayne, IN, for the Institute of Internal Auditors.
And someone showed me this trick, and I really love the trick.
I had never seen the trick before, it was a cool one, I said this is great, I'm going to add this to the seminar.
And then the next year I was back in Fort Wayne, and that guy's manager shows up, right, he hadn't come the first year, because he thought he knew everything about Excel.
But he came back to claim this trick and he says: "Look, I hear that my financial analyst told you this trick, but he didn't tell you the best part, go up above your data and insert two rows up there.
And then once you have the total visible cells at the bottom, cut, CTRL+X, and paste, CTRL+V, all right, what that does is, if you switch to another customer," Alright, so I'll pick a few more customers here.
"And now I have more customers than will fit on the screen!
And I don't have to scroll down to the bottom to see the totals, the totals are always up there at the top!" Alright now, this trick, using the AutoSum below filter data, works when you're hiding the rows with the filter.
What if you're manually hiding the rows?
So here, I selected a few rows, already went to Format, Hide & Unhide, Hide Rows!
So now we have rows hidden.
And I come down to the bottom, and I press the Alt+= or the AutoSum, and it doesn't give me the SUBTOTAL function, it gives me the SUM function.
All right now, at this point, I'm going to do a special thing, I'm going to do CTRL+' (ditto, apostrophe).
It will bring the exact same formula down, without changing the cell reference.
So it's the E2:E8 changed, or did not change, usually, if you would copy and paste to a change to E3:E9.
I'm going to change this to SUBTOTAL(9, and you'll see that it doesn't work, it still gives us all the cells.
1000+400+800 is not 4500, it's including the hidden rows.
But here's what you have to do, get in CTRL+' and change that 9, to a 109, and you will get: The True Total Visible Based on Manually Hidden Rows!
Alright, there you go, so a couple of different tricks, AutoSum, Alt+= just gives us the SUM function.
If you're below the data and you press the AutoSum, you're going to get the SUBTOTAL(9, which works great for filtered, or you can just manually type the SUBTOTAL(109, to get the total of the visible, when you've manually filtered.
Alright, here's a recap of the concepts in today's video: This, by the way, is not even tip #3 in the book, this is Bonus tip #2b right, so between tip 2 and tip 3.
The book covers all of these items.
Well thanks to everyone who suggested this, the people at the Fort Wayne Indiana Chapter of The IIA, is where I first heard it.
It's a great book, you can buy it in print or e-book, click that "i" in the top-right hand corner.
You'd be taken to more information about the book.
OK, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1976 - Total the Visible Cells!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Of course, everyone, the very first formula, that most people create, is the AutoSum.
You're down here below a column of numbers, you get the AutoSum button, and it will use the sum function to total the numbers above it, until it encounters either a formula or text.
Right?
Or Alt+= does the exact same thing.
Or, even better, select multiple cells and then hit the AutoSum, and it will put all those formulas, without you having to confirm the formulas.
But here is the cool trick, alright, so let's turn on the AutoFilter.
So we're going to use the Filter by Selection trick, from yesterday's podcast.
The data is filtered.
I'm going to go to the very first blank row beneath the data, and then hit the AutoSum button.
But something very different is happening, you see.
Instead of the SUM function, we get =SUBTOTAL(9 ! And what this is, this is the Total of the visible cells.
Alright so, very cool that that works.
Now here's the thing, I was doing a live power Excel seminar almost ten years ago, in Fort Wayne, IN, for the Institute of Internal Auditors.
And someone showed me this trick, and I really love the trick.
I had never seen the trick before, it was a cool one, I said this is great, I'm going to add this to the seminar.
And then the next year I was back in Fort Wayne, and that guy's manager shows up, right, he hadn't come the first year, because he thought he knew everything about Excel.
But he came back to claim this trick and he says: "Look, I hear that my financial analyst told you this trick, but he didn't tell you the best part, go up above your data and insert two rows up there.
And then once you have the total visible cells at the bottom, cut, CTRL+X, and paste, CTRL+V, all right, what that does is, if you switch to another customer," Alright, so I'll pick a few more customers here.
"And now I have more customers than will fit on the screen!
And I don't have to scroll down to the bottom to see the totals, the totals are always up there at the top!" Alright now, this trick, using the AutoSum below filter data, works when you're hiding the rows with the filter.
What if you're manually hiding the rows?
So here, I selected a few rows, already went to Format, Hide & Unhide, Hide Rows!
So now we have rows hidden.
And I come down to the bottom, and I press the Alt+= or the AutoSum, and it doesn't give me the SUBTOTAL function, it gives me the SUM function.
All right now, at this point, I'm going to do a special thing, I'm going to do CTRL+' (ditto, apostrophe).
It will bring the exact same formula down, without changing the cell reference.
So it's the E2:E8 changed, or did not change, usually, if you would copy and paste to a change to E3:E9.
I'm going to change this to SUBTOTAL(9, and you'll see that it doesn't work, it still gives us all the cells.
1000+400+800 is not 4500, it's including the hidden rows.
But here's what you have to do, get in CTRL+' and change that 9, to a 109, and you will get: The True Total Visible Based on Manually Hidden Rows!
Alright, there you go, so a couple of different tricks, AutoSum, Alt+= just gives us the SUM function.
If you're below the data and you press the AutoSum, you're going to get the SUBTOTAL(9, which works great for filtered, or you can just manually type the SUBTOTAL(109, to get the total of the visible, when you've manually filtered.
Alright, here's a recap of the concepts in today's video: This, by the way, is not even tip #3 in the book, this is Bonus tip #2b right, so between tip 2 and tip 3.
The book covers all of these items.
Well thanks to everyone who suggested this, the people at the Fort Wayne Indiana Chapter of The IIA, is where I first heard it.
It's a great book, you can buy it in print or e-book, click that "i" in the top-right hand corner.
You'd be taken to more information about the book.
OK, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
