Your manager asks if you can add a blank row after each subtotal. Episode 2433 shows you how to do this in Excel.
The first way, is clearly cheating: Increase the height of each row.
But the second way is the real way, using Ctrl+Enter, Select Visible Cells, Insert Cells Shift Down, and Insert Rows.
The first way, is clearly cheating: Increase the height of each row.
But the second way is the real way, using Ctrl+Enter, Select Visible Cells, Insert Cells Shift Down, and Insert Rows.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2433. Add a blank row after each subtotal.
Oh, what a horrible question.
You have added subtotals and now your manager wants a blank row after each subtotal.
I have a cheat for this and then the real way to do it. Here is the cheat.
Let's just take a look at this row here. This row has a row height of 12.75.
Double that to get 25.5.
Let's make sure that all of our data is top aligned. Select just these rows.
So Ctrl+Shift+Down arrow. Alt semicolon.
And then alt ORE for Format Row Height and we will go with 25.5.
Click OK.
When we print this, it's going to appear that we added the blank row.
If your manager will never open Excel and this is just going to be a PDF, it's perfect. Here's the real way to do it.
Collapse it down to the number two view. Select all of these cells out here to the right.
And Alt-semicolon to select just those cells. Type 1 and control enter.
That puts a one on the subtotal row in each case.
Go to the number 3 view and then here I'm going to shift all of those ones down one row.
So Alt+I E C D for shift cells down.
Enter and that one is now on the first data row for each customer. So we select all of that data.
Control G for Go To. Alt S for Special.
And what we want is just the constant cells. Those are those ones that I just typed.
Click OK and we've now effectively selected that 1, but also that 1 and that 1 and that 1. And now we're home free.
Alt+I for insert R for row and it's done.
It added a blank row after ABC Stores and added a blank row after AT&T and added the blank row after Bank United, all the way down.
We can delete that column. And you're good to go.
Now the next person who gets this file and tries to remove these subtotals is going to hate you.
But really, they should be hating your manager, not you.
You were just doing want your manager wanted.
(When is that guy going to retire?) Check out my book MrExcel 2021, Unmasking Excel. All kinds of great tips and tricks in that book.
If you like these videos, please, down below Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. I will see you next time for another netcast from MrExcel.
Oh, what a horrible question.
You have added subtotals and now your manager wants a blank row after each subtotal.
I have a cheat for this and then the real way to do it. Here is the cheat.
Let's just take a look at this row here. This row has a row height of 12.75.
Double that to get 25.5.
Let's make sure that all of our data is top aligned. Select just these rows.
So Ctrl+Shift+Down arrow. Alt semicolon.
And then alt ORE for Format Row Height and we will go with 25.5.
Click OK.
When we print this, it's going to appear that we added the blank row.
If your manager will never open Excel and this is just going to be a PDF, it's perfect. Here's the real way to do it.
Collapse it down to the number two view. Select all of these cells out here to the right.
And Alt-semicolon to select just those cells. Type 1 and control enter.
That puts a one on the subtotal row in each case.
Go to the number 3 view and then here I'm going to shift all of those ones down one row.
So Alt+I E C D for shift cells down.
Enter and that one is now on the first data row for each customer. So we select all of that data.
Control G for Go To. Alt S for Special.
And what we want is just the constant cells. Those are those ones that I just typed.
Click OK and we've now effectively selected that 1, but also that 1 and that 1 and that 1. And now we're home free.
Alt+I for insert R for row and it's done.
It added a blank row after ABC Stores and added a blank row after AT&T and added the blank row after Bank United, all the way down.
We can delete that column. And you're good to go.
Now the next person who gets this file and tries to remove these subtotals is going to hate you.
But really, they should be hating your manager, not you.
You were just doing want your manager wanted.
(When is that guy going to retire?) Check out my book MrExcel 2021, Unmasking Excel. All kinds of great tips and tricks in that book.
If you like these videos, please, down below Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by. I will see you next time for another netcast from MrExcel.
