Add subtotals to an Excel data set and then copy only the subtotal rows
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Excel In Depth chapter 22 - Copying Subtotals!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
SUBTOTAL is one of those really, really cool features that could save you a massive amount of time.
Let's say we have to add subtotals to every customer.
First of all, you want to sort the data by Customer, click A-Z, that way all the customers are together.
And then back here on the Data tab, on the right-hand side is something called Subtotal.
And say At each change in, I'll just change that Customer, we want to use the SUM function, and choose all the numeric fields.
When we click OK, very quickly Excel goes through and adds new lines with the subtotals.
Alright, that's cool!
Next thing, see over here, 1 2 and 3, the grouping outline buttons, let's click the #2 button, now we get just the subtotals on it, even cooler, but!
This is where it all breaks down, I'm going to copy that data, Ctrl+C to copy, I'm going to go to a new workbook, Ctrl+N for New, paste Ctrl+V. And look, they brought all the rows along instead of just the totals.
Oh, horrible, horrible.
Alright, so the solution back here is a dialog box I'm going to bet that you probably haven't seen.
It's not the Go To Dialog Box, it’s called Go To Special.
Go to Home, Find and Select, Go To Special- Wait, you couldn't see that.
Let's take Find and Select, we’ll add it to the Quick Access Toolbar.
Alright, Home, Find and Select, Go To Special, right there, so it's usually out on the right side of the Home tab, I realize we have a small screen here today, and I want to go to the Visible Cells Only.
Right, now when I copy Ctrl+C, look, I'm copying only those individual rows, Ctrl+N for New, Ctrl+V to paste, and I get just those customer totals.
About 25 rows of data.
Great summary report from that other data set.
Now, Go To Special, let's do it again, really cool stuff there on Go To Special.
Sometimes we use Visible Cells Only, lots of times I use Blanks, sometimes I use Formulas, so three things here that are really power Excel tricks that I keep using over and over and over again.
But this particular one, Go To Special, Visible Cells Only, there's a shortcut for it.
If you hold down Alt and press ; , that will do the same thing as Go To Special, Visible Cells Only.
So for that one, great shortcut key, Alt+; will select the visible cells.
Well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Excel In Depth chapter 22 - Copying Subtotals!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
SUBTOTAL is one of those really, really cool features that could save you a massive amount of time.
Let's say we have to add subtotals to every customer.
First of all, you want to sort the data by Customer, click A-Z, that way all the customers are together.
And then back here on the Data tab, on the right-hand side is something called Subtotal.
And say At each change in, I'll just change that Customer, we want to use the SUM function, and choose all the numeric fields.
When we click OK, very quickly Excel goes through and adds new lines with the subtotals.
Alright, that's cool!
Next thing, see over here, 1 2 and 3, the grouping outline buttons, let's click the #2 button, now we get just the subtotals on it, even cooler, but!
This is where it all breaks down, I'm going to copy that data, Ctrl+C to copy, I'm going to go to a new workbook, Ctrl+N for New, paste Ctrl+V. And look, they brought all the rows along instead of just the totals.
Oh, horrible, horrible.
Alright, so the solution back here is a dialog box I'm going to bet that you probably haven't seen.
It's not the Go To Dialog Box, it’s called Go To Special.
Go to Home, Find and Select, Go To Special- Wait, you couldn't see that.
Let's take Find and Select, we’ll add it to the Quick Access Toolbar.
Alright, Home, Find and Select, Go To Special, right there, so it's usually out on the right side of the Home tab, I realize we have a small screen here today, and I want to go to the Visible Cells Only.
Right, now when I copy Ctrl+C, look, I'm copying only those individual rows, Ctrl+N for New, Ctrl+V to paste, and I get just those customer totals.
About 25 rows of data.
Great summary report from that other data set.
Now, Go To Special, let's do it again, really cool stuff there on Go To Special.
Sometimes we use Visible Cells Only, lots of times I use Blanks, sometimes I use Formulas, so three things here that are really power Excel tricks that I keep using over and over and over again.
But this particular one, Go To Special, Visible Cells Only, there's a shortcut for it.
If you hold down Alt and press ; , that will do the same thing as Go To Special, Visible Cells Only.
So for that one, great shortcut key, Alt+; will select the visible cells.
Well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
