Use the #2 Group and Outline button to show only the subtotal rows in Excel. It is tough to copy those to a new workbook. Recap:
Add Subtotals
Collapse to #2 View
Copy the subtotals
New workbook & paste. All the detail rows appear
Finding Go To Special via the Go To Dialog
Visible Cells Only
Copy - you can see the difference with the marching ants
New workbook & paste. Only the subtotals, pasted as values
Thanks to Patricia McCarthy for suggesting this tip
Add Subtotals
Collapse to #2 View
Copy the subtotals
New workbook & paste. All the detail rows appear
Finding Go To Special via the Go To Dialog
Visible Cells Only
Copy - you can see the difference with the marching ants
New workbook & paste. Only the subtotals, pasted as values
Thanks to Patricia McCarthy for suggesting this tip
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1997 - Copy Subtotals!
Podcasting this whole book, go ahead, click that "i" on the top-right hand corner there for the playlist!
Now this is my last example about subtotals and, one of my favorite stories, this goes back to 1997 I was, working in my last day job, my manager needed a report, I had a large data set, I added the subtotals I went down to the #2 view, I set the print range, gave him that report, and he was thrilled with that report, he came back, he said "Hey, just send me these rows, right?" And he was standing behind me, when I did this: CTRL+C, CTRL+N, and then at the CTRL+V, it brought all of the detailed rows, and no way to collapse them.
All right?
And secretly unbeknownst to this guy, I'm already running MrExcel.com at night, and he's back there he was one of the most bizarre managers I've ever had, his arms were flailing like so, he said "Of course it didn't work, you didn't use 'visible cells only' " Like "Visible cells only", what are you talking about?
He says "Well first press CTRL+G" Back in the day, this was, you know, back in Excel 97, he had to use either CTRL+G or F5 to display the go-to dialog box and, you may have heard me rant before that I have no use for this dialog box, because, well, there's only two places that you need to go in a spreadsheet - the top cell which is CTRL+Home, or the last cell which is CTRL+Down Arrow, or CTRL+End.
All right, there's plenty of places to get to, the two places I need to go, I don't need a stupid dialog box to get there.
Now hey, in parentheses, the last time I said this in a podcast a thousand episodes ago, someone pointed out this is a great place to get to a hidden column, and yes, you're right, I still have that trick.
So, I rejected the Go To dialog box, I hadn't looked at it for 10 years, and at some point, since I rejected the go-to dialog box in the bottom-left hand corner, Microsoft added a button called Special.
This is classic Microsoft, let's have a really good, really powerful, really useful feature, and put it where no one will ever discover it.
So, useless dialog box , Go to - Special, this is amazing, Go to - Special, it says hey, within the selection we're going to narrow the selection down to, well, maybe just formulas that are errors, maybe just blanks, or in this case, what my manager was talking about, Visible cells only.
All right, now, the good news, I guess, is that it's a little easier to find now, Home, Find & Select, Go To - Special is actually on the menu, and then "Visible cells only", click OK.
See, the little white lines there, that's saying: "Hey, there's things that are hidden that you can't see", I do the CTRL+C, it's really obvious, the marching answer, the dancing answer, whatever you call those little things, are around each individual row, CTRL+N for new, and CTRL+V to paste, and we get just those total rows, and they convert those formulas to values, which is the right thing to do.
I remember, explicitly this day, I turned around to that manager, I said "Where did you learn that trick?" Snd he says "I read it in Excel help." And I'm like "What!" Who takes the time to read Excel help, alright, so, there you go.
Of course in the last couple of days, you've seen me use alternates for this, Alt+; is a great way to go, or, yesterday, I had the "Select visible cells" icon to the quick access toolbar, another great way to go, alright so, some way or another, before you do that copy, you have to, get down to visible cells.
Well, all of the podcasts this month, plus all the podcasts for the rest of the month, and probably two weeks of September, all in this one little book here, so much knowledge compressed in the palm of your hand.
I ask you to go out, and buy the book, 25$ in print, 10$ for an E-book, it's cheap.
Recap of today's episode: First we had the subtotals collapse to #2 view, and then, when you go to copy those subtotals, New workbook & paste, all the detail rows come along, and there's no way to collapse it.
You have to use Go To - Special, back in the day it was Go To, which was either CTRL+G or F5, click the Special button.
Now you can get there easier, just select "Visible cells only".
That Go To - Special dialog box has all kinds of great uses, After you select the visible cells and copy, you can see the marching ants go to a new workbook, and paste, they automatically paste values, which is awesome!
Alright, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Podcasting this whole book, go ahead, click that "i" on the top-right hand corner there for the playlist!
Now this is my last example about subtotals and, one of my favorite stories, this goes back to 1997 I was, working in my last day job, my manager needed a report, I had a large data set, I added the subtotals I went down to the #2 view, I set the print range, gave him that report, and he was thrilled with that report, he came back, he said "Hey, just send me these rows, right?" And he was standing behind me, when I did this: CTRL+C, CTRL+N, and then at the CTRL+V, it brought all of the detailed rows, and no way to collapse them.
All right?
And secretly unbeknownst to this guy, I'm already running MrExcel.com at night, and he's back there he was one of the most bizarre managers I've ever had, his arms were flailing like so, he said "Of course it didn't work, you didn't use 'visible cells only' " Like "Visible cells only", what are you talking about?
He says "Well first press CTRL+G" Back in the day, this was, you know, back in Excel 97, he had to use either CTRL+G or F5 to display the go-to dialog box and, you may have heard me rant before that I have no use for this dialog box, because, well, there's only two places that you need to go in a spreadsheet - the top cell which is CTRL+Home, or the last cell which is CTRL+Down Arrow, or CTRL+End.
All right, there's plenty of places to get to, the two places I need to go, I don't need a stupid dialog box to get there.
Now hey, in parentheses, the last time I said this in a podcast a thousand episodes ago, someone pointed out this is a great place to get to a hidden column, and yes, you're right, I still have that trick.
So, I rejected the Go To dialog box, I hadn't looked at it for 10 years, and at some point, since I rejected the go-to dialog box in the bottom-left hand corner, Microsoft added a button called Special.
This is classic Microsoft, let's have a really good, really powerful, really useful feature, and put it where no one will ever discover it.
So, useless dialog box , Go to - Special, this is amazing, Go to - Special, it says hey, within the selection we're going to narrow the selection down to, well, maybe just formulas that are errors, maybe just blanks, or in this case, what my manager was talking about, Visible cells only.
All right, now, the good news, I guess, is that it's a little easier to find now, Home, Find & Select, Go To - Special is actually on the menu, and then "Visible cells only", click OK.
See, the little white lines there, that's saying: "Hey, there's things that are hidden that you can't see", I do the CTRL+C, it's really obvious, the marching answer, the dancing answer, whatever you call those little things, are around each individual row, CTRL+N for new, and CTRL+V to paste, and we get just those total rows, and they convert those formulas to values, which is the right thing to do.
I remember, explicitly this day, I turned around to that manager, I said "Where did you learn that trick?" Snd he says "I read it in Excel help." And I'm like "What!" Who takes the time to read Excel help, alright, so, there you go.
Of course in the last couple of days, you've seen me use alternates for this, Alt+; is a great way to go, or, yesterday, I had the "Select visible cells" icon to the quick access toolbar, another great way to go, alright so, some way or another, before you do that copy, you have to, get down to visible cells.
Well, all of the podcasts this month, plus all the podcasts for the rest of the month, and probably two weeks of September, all in this one little book here, so much knowledge compressed in the palm of your hand.
I ask you to go out, and buy the book, 25$ in print, 10$ for an E-book, it's cheap.
Recap of today's episode: First we had the subtotals collapse to #2 view, and then, when you go to copy those subtotals, New workbook & paste, all the detail rows come along, and there's no way to collapse it.
You have to use Go To - Special, back in the day it was Go To, which was either CTRL+G or F5, click the Special button.
Now you can get there easier, just select "Visible cells only".
That Go To - Special dialog box has all kinds of great uses, After you select the visible cells and copy, you can see the marching ants go to a new workbook, and paste, they automatically paste values, which is awesome!
Alright, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
