Janet wants to insert two rows below each customer's records
How to insert multiple rows in one command in Excel
Use an IF OR formula to figure out if this is two rows below. Use "A" or 1
Go To Special to select formulas that result in numeric
Issue one Insert Row command
How to insert multiple rows in one command in Excel
Use an IF OR formula to figure out if this is two rows below. Use "A" or 1
Go To Special to select formulas that result in numeric
Issue one Insert Row command
Transcript of the video:
Learn Excel from MrExcel, Podcast Episode 2158: Insert Two Rows At Each Change.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. Well, today's question is from Janet. I met Janet at Excelapalooza.
Janet has a data set that she downloads and, every time the customer name changes, she wants to insert 2 rows, insert 2 rows. Now, before we get to Janet’s question, it's amazing how many times I get asked the question of how can you insert multiple rows in Excel.
I don't know why this is confusing to people but it's easy to insert multiple rows in Excel.
If I want to insert 1 row between ABC Stores and AT&T, I just come here to the 1st row that's going to be shifted down and we'll do INSERT, INSERT SHEET ROWS. Now, instead of doing that, I actually do ALT+I for insert, R for rows, whichever method you’d like there. So, Alt+I R will insert 1 row, but if I wanted to insert 2 rows, so, what I want to do is here is the first place that I want to shift down and I hold down the SHIFT key and press the DOWN ARROW to select 2 rows.
ALT+I R will insert 2 rows, shift them both down.
Now, I guess it's kind of counterintuitive because, normally, if we select this cell, it's pushing that one down, and if we select 2 cells, you think maybe it would start pushing down below that. I don't know why it's confusing but if you needed to insert 5 rows, ALT+I R, it's always going to insert from the very first cell in the selection. Alright, so we have that rule.
I'm going to come out here and add a HELPER column, and the HELPER column is going to start in the 4th row and it's going to say =IF one of two things is true, right, either of these things are true, so I'm going to use an OR function. If what we have in this cell is not = to the cell above us or if what we have in this cell is not = to 2 cells above us, alright, if either of those are true, then we want to put in a 1 -- and it's not so important what we put in here -- otherwise we want to put in a letter, alright? The important thing here is that one is numeric and one is text. Alright, so, I'm differentiating between numeric and text like that, and we'll double click to copy that down and what we should see is the first 2 rows of each customer have a number whereas everything else has a letter.
Alright, so, we'll choose that whole column, go to HOME, FIND & SELECT, GO TO SPECIAL.
In GO TO SPECIAL, we're looking for FORMULAS that result in NUMBERS. So, it's going to ignore all the As, it's going to ignore the blanks, it's only going to give me the first 2 rows of each customer selected like that, and then we can go to INSERT, INSERT SHEET ROWS, and it will successfully add 2 rows after each customer like that. Now, once we have that done, we'll delete the HELPER out in COLUMN E and we are good to go.
Alright, that's kind of one of the types of tricks that are in this book, 617 Excel Mysteries Solved, Power Excel With MrExcel, the 2017 Edition. Click that i up there in the top right-hand corner for more information on how to buy the book.
Alright, so, wrap up for today: Janet wants to insert 2 rows below each customers records; how to insert multiple rows in one command in Excel. I don't know why that's confusing to people but I get that question a lot. To solve Janet's problem, we use an IF OR formula to figure out if this customer is different than the customer above us or 2 rows above us, and put in either a text A or a number 1, and then go to SPECIAL to select just the 1s in that range, and then one single INSERT SHEET ROWS command will solve the problem.
I want to thank Janet for sending that question in and I want that you for stopping by. We’ll see you next time for another netcast from MrExcel.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. Well, today's question is from Janet. I met Janet at Excelapalooza.
Janet has a data set that she downloads and, every time the customer name changes, she wants to insert 2 rows, insert 2 rows. Now, before we get to Janet’s question, it's amazing how many times I get asked the question of how can you insert multiple rows in Excel.
I don't know why this is confusing to people but it's easy to insert multiple rows in Excel.
If I want to insert 1 row between ABC Stores and AT&T, I just come here to the 1st row that's going to be shifted down and we'll do INSERT, INSERT SHEET ROWS. Now, instead of doing that, I actually do ALT+I for insert, R for rows, whichever method you’d like there. So, Alt+I R will insert 1 row, but if I wanted to insert 2 rows, so, what I want to do is here is the first place that I want to shift down and I hold down the SHIFT key and press the DOWN ARROW to select 2 rows.
ALT+I R will insert 2 rows, shift them both down.
Now, I guess it's kind of counterintuitive because, normally, if we select this cell, it's pushing that one down, and if we select 2 cells, you think maybe it would start pushing down below that. I don't know why it's confusing but if you needed to insert 5 rows, ALT+I R, it's always going to insert from the very first cell in the selection. Alright, so we have that rule.
I'm going to come out here and add a HELPER column, and the HELPER column is going to start in the 4th row and it's going to say =IF one of two things is true, right, either of these things are true, so I'm going to use an OR function. If what we have in this cell is not = to the cell above us or if what we have in this cell is not = to 2 cells above us, alright, if either of those are true, then we want to put in a 1 -- and it's not so important what we put in here -- otherwise we want to put in a letter, alright? The important thing here is that one is numeric and one is text. Alright, so, I'm differentiating between numeric and text like that, and we'll double click to copy that down and what we should see is the first 2 rows of each customer have a number whereas everything else has a letter.
Alright, so, we'll choose that whole column, go to HOME, FIND & SELECT, GO TO SPECIAL.
In GO TO SPECIAL, we're looking for FORMULAS that result in NUMBERS. So, it's going to ignore all the As, it's going to ignore the blanks, it's only going to give me the first 2 rows of each customer selected like that, and then we can go to INSERT, INSERT SHEET ROWS, and it will successfully add 2 rows after each customer like that. Now, once we have that done, we'll delete the HELPER out in COLUMN E and we are good to go.
Alright, that's kind of one of the types of tricks that are in this book, 617 Excel Mysteries Solved, Power Excel With MrExcel, the 2017 Edition. Click that i up there in the top right-hand corner for more information on how to buy the book.
Alright, so, wrap up for today: Janet wants to insert 2 rows below each customers records; how to insert multiple rows in one command in Excel. I don't know why that's confusing to people but I get that question a lot. To solve Janet's problem, we use an IF OR formula to figure out if this customer is different than the customer above us or 2 rows above us, and put in either a text A or a number 1, and then go to SPECIAL to select just the 1s in that range, and then one single INSERT SHEET ROWS command will solve the problem.
I want to thank Janet for sending that question in and I want that you for stopping by. We’ll see you next time for another netcast from MrExcel.
