Excel in Depth 22 - Copying Subtotal Rows

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 14, 2010.
Add subtotals to an Excel data set and then copy only the subtotal rows
maxresdefault.jpg


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!
 

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top