Kerry asks: after adding automatic subtotals, how can you copy only the subtotal rows? Episode 731 will show you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today, we have a question send by Carrie.
Carrie says, "I know how to use the automatic Subtotals command but then how to copy the subtotals only".
And there's a cool trick for this.
So, let's talk about how to add subtotals.
First of all, if we want to add subtotals by customer, make sure that the data set is already sorted by customers.
So, choose one cell in the customer column and press A to Z and then Data>Subtotals> At Each Change in: >Customer.
And choose the fields that you want to subtotal.
Click OK.
Alright, so it's now gone through and added subtotals throughout the data set.
And if we want to see just the subtotals, we click the number two button up here in the upper left hand corner.
Well, that's good enough, but when you want to copy those subtotals, it becomes a real pain.
Because if we would copy everything Ctrl+C right here and go to a new workbook and paste.
It's going to give us all of the data not just the subtotals.
So, what we have to do is choose the data and then press [ ALT+ ; ] ALT + semicolon selects just the visible cells.
Now, when we copy you see that we're copying just the individual subtotals.
Go to a new workbook and paste [ CTRL+V ].
Great way to copy just the subtotal rows after you've collapsed.
Thanks to Carrie for sending in that question And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Today, we have a question send by Carrie.
Carrie says, "I know how to use the automatic Subtotals command but then how to copy the subtotals only".
And there's a cool trick for this.
So, let's talk about how to add subtotals.
First of all, if we want to add subtotals by customer, make sure that the data set is already sorted by customers.
So, choose one cell in the customer column and press A to Z and then Data>Subtotals> At Each Change in: >Customer.
And choose the fields that you want to subtotal.
Click OK.
Alright, so it's now gone through and added subtotals throughout the data set.
And if we want to see just the subtotals, we click the number two button up here in the upper left hand corner.
Well, that's good enough, but when you want to copy those subtotals, it becomes a real pain.
Because if we would copy everything Ctrl+C right here and go to a new workbook and paste.
It's going to give us all of the data not just the subtotals.
So, what we have to do is choose the data and then press [ ALT+ ; ] ALT + semicolon selects just the visible cells.
Now, when we copy you see that we're copying just the individual subtotals.
Go to a new workbook and paste [ CTRL+V ].
Great way to copy just the subtotal rows after you've collapsed.
Thanks to Carrie for sending in that question And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
