Holly has a data set with Subtotals. She needs to insert a blank row after each subtotal row and add a calculation to those blank rows.
Table of Contents
(0:00) Welcome
(0:15) Go To Special Tricks?
(0:47) Subtotals
(1:10) Helper column on Total rows
(1:50) Move 1's down a row
(2:28) Select just 1's & Insert Rows
(3:00) Select blank cells
(3:32) Entering the formula
(4:30) Favorite Go To Special
Table of Contents
(0:00) Welcome
(0:15) Go To Special Tricks?
(0:47) Subtotals
(1:10) Helper column on Total rows
(1:50) Move 1's down a row
(2:28) Select just 1's & Insert Rows
(3:00) Select blank cells
(3:32) Entering the formula
(4:30) Favorite Go To Special
Transcript of the video:
So we're in Excel, we've added Subtotals, we want a blank row after each subtotal row.
And we want to do a calculation in that row. Another question from Holly.
This is one of the most convoluted set of steps in any of my videos. So this is a great place to pause and screenshot this list of steps right here, because as you go back and try and follow this, you're going to have to go through a lot.
For those of you who are Excel pros and you have a great trick with Go To Special, I'd love to hear about that in the comments below. I'm going to use Go To Special three times here.
I promised Liam Bastick that I'm going to speak at his conference in October, and I'm going to talk for 45 minutes about Go To and Go To Special.
So whatever you have, I'll give you a shout out during the seminar if you give me something I've never heard of before.
All right. So Holly has some data.
She adds Subtotals in hit the subtotal dialogue box here at each change in customer.
Use the sum function on these numeric columns. Click okay.
All right?
And see what that does is it adds ABC Stores' total and AT&T total, and there's a total row.
But Holly needs to insert a blank row below each subtotal and do some sort of a calculation there. All right, so here's the steps.
Number one, click the number #2 button, add a little helper column out here.
I'm going to select from the first subtotal down to the last subtotal, and then I need to select visible cells. Now there's several different ways to do that.
I'm going to do Alt+Semicolon.
Some of you in your Quick Access Toolbar have this, Select Visible Cells.
Or others will do Home, Find & Select, Go To Special and Choose Visible Sells Only.
All three do the exact same thing. I'm going to put a one in all of these cells.
So I type one. Don't press enter.
Press Ctrl+Enter, like that.
And then when we go back to the number three view, you'll see that we've successfully added a one only on the rows that have totals.
But I need to move those ones down exactly one cell.
So go to any of these blank cells above the one and say Insert Sells, Shift Cells Down.
So what I've done now is I've put a one on the first row of each group, except for the first group.
Let's just cruise down here and look at another one.
So here for CUNA Insurance, see the one is on the first row of that group.
All right. Choose the whole column, back into Go To Special.
This time we're going to choose the Constants, but just the Constants that are numbers.
So we uncheck Text, logicals, and errors. Right, and watch this beautiful trick right here.
Home, Insert, Insert Sheet Rows, and we get a total there after ABC Stores' total.
But not just there.
We get one for AT&T total, we get one for all of the customers. There's a blank row exactly below.
All right, we don't need column J anymore. We'll clear that.
Now, that's part one, how to add the blank row in.
Part two is how to put some sort of a calculation in that blank row.
So I'm going to start on the first blank row, and just holding down shift I'll Page Down, Page Down, Page Down.
Boy, it'd be nice if I could CTRL+Shift+Down, but because of those blank rows, I can't.
And I don't think I need that calculation below the grand total.
So I just go from the first blank to the last blank and then my last, Find & Select, Go To Special. This time I'm choosing the blanks.
Now look, in your data set, there's very slim odds that you're going to be on row seven.
For me, I'm on row seven.
You have to see what row is the Active row, and then you have to figure out what calculation you need to do.
Who knows what it is? I don't know.
But build that calculation as if you're pointing to the row directly above you.
So maybe equal I6 divided by G6. Who knows?
Don't press enter. Instead, press CTRL+Enter.
And while those are still selected, do any formatting that you need to do.
All right. So there we go.
We have now put a calculation in. I'm going to cruise down here.
I'll press Page Down a little bit.
There's the next one right below AT&T, right below Bank United, and so on.
I'm not sure exactly why Holly needs this data in column I.
I've seen it before where we put those out in column J.
In fact, I have the video on how to put those out in column J, the column to the right.
But in this particular case, she wanted to insert a row and insert a calculation, so that's how you would do it.
Now again, if you have any awesome favorite Go To Special tricks that you use again and again and again, lay me a note down in the YouTube comments below. I'd love to get your tricks.
I have several. I'm not sure I have 45 minutes worth.
All right. Well, hey.
I want to thank Holly for showing up in my seminar, and I want to thank you for stopping by.
We'll see you next time for another netcast from Mr. Excel.
And we want to do a calculation in that row. Another question from Holly.
This is one of the most convoluted set of steps in any of my videos. So this is a great place to pause and screenshot this list of steps right here, because as you go back and try and follow this, you're going to have to go through a lot.
For those of you who are Excel pros and you have a great trick with Go To Special, I'd love to hear about that in the comments below. I'm going to use Go To Special three times here.
I promised Liam Bastick that I'm going to speak at his conference in October, and I'm going to talk for 45 minutes about Go To and Go To Special.
So whatever you have, I'll give you a shout out during the seminar if you give me something I've never heard of before.
All right. So Holly has some data.
She adds Subtotals in hit the subtotal dialogue box here at each change in customer.
Use the sum function on these numeric columns. Click okay.
All right?
And see what that does is it adds ABC Stores' total and AT&T total, and there's a total row.
But Holly needs to insert a blank row below each subtotal and do some sort of a calculation there. All right, so here's the steps.
Number one, click the number #2 button, add a little helper column out here.
I'm going to select from the first subtotal down to the last subtotal, and then I need to select visible cells. Now there's several different ways to do that.
I'm going to do Alt+Semicolon.
Some of you in your Quick Access Toolbar have this, Select Visible Cells.
Or others will do Home, Find & Select, Go To Special and Choose Visible Sells Only.
All three do the exact same thing. I'm going to put a one in all of these cells.
So I type one. Don't press enter.
Press Ctrl+Enter, like that.
And then when we go back to the number three view, you'll see that we've successfully added a one only on the rows that have totals.
But I need to move those ones down exactly one cell.
So go to any of these blank cells above the one and say Insert Sells, Shift Cells Down.
So what I've done now is I've put a one on the first row of each group, except for the first group.
Let's just cruise down here and look at another one.
So here for CUNA Insurance, see the one is on the first row of that group.
All right. Choose the whole column, back into Go To Special.
This time we're going to choose the Constants, but just the Constants that are numbers.
So we uncheck Text, logicals, and errors. Right, and watch this beautiful trick right here.
Home, Insert, Insert Sheet Rows, and we get a total there after ABC Stores' total.
But not just there.
We get one for AT&T total, we get one for all of the customers. There's a blank row exactly below.
All right, we don't need column J anymore. We'll clear that.
Now, that's part one, how to add the blank row in.
Part two is how to put some sort of a calculation in that blank row.
So I'm going to start on the first blank row, and just holding down shift I'll Page Down, Page Down, Page Down.
Boy, it'd be nice if I could CTRL+Shift+Down, but because of those blank rows, I can't.
And I don't think I need that calculation below the grand total.
So I just go from the first blank to the last blank and then my last, Find & Select, Go To Special. This time I'm choosing the blanks.
Now look, in your data set, there's very slim odds that you're going to be on row seven.
For me, I'm on row seven.
You have to see what row is the Active row, and then you have to figure out what calculation you need to do.
Who knows what it is? I don't know.
But build that calculation as if you're pointing to the row directly above you.
So maybe equal I6 divided by G6. Who knows?
Don't press enter. Instead, press CTRL+Enter.
And while those are still selected, do any formatting that you need to do.
All right. So there we go.
We have now put a calculation in. I'm going to cruise down here.
I'll press Page Down a little bit.
There's the next one right below AT&T, right below Bank United, and so on.
I'm not sure exactly why Holly needs this data in column I.
I've seen it before where we put those out in column J.
In fact, I have the video on how to put those out in column J, the column to the right.
But in this particular case, she wanted to insert a row and insert a calculation, so that's how you would do it.
Now again, if you have any awesome favorite Go To Special tricks that you use again and again and again, lay me a note down in the YouTube comments below. I'd love to get your tricks.
I have several. I'm not sure I have 45 minutes worth.
All right. Well, hey.
I want to thank Holly for showing up in my seminar, and I want to thank you for stopping by.
We'll see you next time for another netcast from Mr. Excel.
