Today, in Episode #1264, Bill shows us how to add Subtotals to an Excel Data Set and then copy only the Subtotal rows.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel In Depth, chapter 22-Copying Subtotal Hey welcome back to the MrExcel netcast.
I am Bill Jelen.
Subtotals, one of those really really cool features that could save you a massive amount of time.
Let's say that we have to add subtotals to every customer.
First of all you want to sort the data by customer.
Like A to Z that we all the customers are together, and then back around the data tab on the right hand side, Let me call it subtotal.
We say it each change in, change a 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 all right.
That's cool.
Next thing see over here one two and three, the grouping outline buttons.
Let's click the number two button, now we get just the subtotal eyes.
Even cooler but ahh..
This is where it all breaks down.
I'm going to copy that data CTRL+C to copy.
I'm gonna go to a new workbook.
CTRL+N for new.
Paste CTRL+V and look, they brought all of the rows along instead of just the totals.
Horrible! horrible! horrible!
Alright so, the solution back here is a dialog box.
So I'm gonna bet that you probably haven't seen.
It's not the go-to dialog boxes called Go To Special.
Go to special...
Go to Home>Find & select go to special.
Wait you couldn't see that.
let's take Find & select to add to the quick Access toolbar.
Alright, home>Find & 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.
Go to 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 summer report from that other data set now.
Go to special.
Let's do it again.
Really cool stuff there and Go To Specials sometimes we use visible cells only.
lots of times they use blanks.
sometimes they 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 is a shortcut for it, if you hold down ALT and press Semicolon.
That will do the same thing as Go To Special Visible>Cells Only.
So, for that one great shortcut key, ALT +Semicolon will select the visible cells.
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 Subtotal Hey welcome back to the MrExcel netcast.
I am Bill Jelen.
Subtotals, one of those really really cool features that could save you a massive amount of time.
Let's say that we have to add subtotals to every customer.
First of all you want to sort the data by customer.
Like A to Z that we all the customers are together, and then back around the data tab on the right hand side, Let me call it subtotal.
We say it each change in, change a 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 all right.
That's cool.
Next thing see over here one two and three, the grouping outline buttons.
Let's click the number two button, now we get just the subtotal eyes.
Even cooler but ahh..
This is where it all breaks down.
I'm going to copy that data CTRL+C to copy.
I'm gonna go to a new workbook.
CTRL+N for new.
Paste CTRL+V and look, they brought all of the rows along instead of just the totals.
Horrible! horrible! horrible!
Alright so, the solution back here is a dialog box.
So I'm gonna bet that you probably haven't seen.
It's not the go-to dialog boxes called Go To Special.
Go to special...
Go to Home>Find & select go to special.
Wait you couldn't see that.
let's take Find & select to add to the quick Access toolbar.
Alright, home>Find & 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.
Go to 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 summer report from that other data set now.
Go to special.
Let's do it again.
Really cool stuff there and Go To Specials sometimes we use visible cells only.
lots of times they use blanks.
sometimes they 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 is a shortcut for it, if you hold down ALT and press Semicolon.
That will do the same thing as Go To Special Visible>Cells Only.
So, for that one great shortcut key, ALT +Semicolon will select the visible cells.
Hey,I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
