MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Insert 2 Rows After Each Customer

October 19, 2017 - by Bill Jelen

Insert 2 Rows After Each Customer

Janet's question: She has a large data set. Every time the customer name changes, she wants to insert *two* rows.

Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2158 - Insert 2 Rows After Each Customer
  • Today's question from Janet. I met Janet at Excelapalooza 2017.
  • Janet has a data set that she downloads and every time the customer
  • name changes she wants to insert two rows.
  • Before we get to Janet's question, it is amazing how many
  • times I get asked the question: "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 one row between ABC stores and AT&T I just
  • come here to the first row that's going
  • be shifted down and we'll do Home, Insert, Insert Sheet Rows.
  • Note instead of using the Ribbon, I actually do Alt+I R for Insert Rows.
  • So there are plenty of ways to insert one row. What if I want to insert two rows?
  • What you want to do is select the first place where you want the new row. Then hold
  • down the shift key and press the down arrow to select two rows. Alt+I R will
  • insert two rows and shift them both down.
  • I guess it's kind of counterintuitive
  • because normally if we select this cell
  • it's pushing that one down and if we
  • select two cells you think maybe it
  • would start pushing down below the second row. But that is not how it works.
  • Back to Janet's question. Go out to the right of the data and add a helper column.
  • We have to check to see if one of two things is true.
  • If either: The cell to the left of me is not equal to the cell above it, or if the cell to the left of me is not equal to two rows above it.
  • If either those are true then we
  • want to put in a 1 (It could be any number, 1 is easy). Otherwise
  • we want to put in a letter "A". The
  • important thing here is that one is
  • numeric and one is text.
  • Double click to copy
  • that formula down and what we should see is the
  • first two 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 the Go To Special dialog, we're looking for formulas that
  • result in numbers so it's going to ignore
  • all the A cells and the blanks. It is only going to give me the first two rows of each customer. Then we can go to insert insert
  • sheet rows and it will successfully add
  • two rows after each customer. Once we have that done we'll delete
  • the helper column we are good to go.

Download File

Download the sample file here: Podcast2158.xlsm

Title Photo: MichaelGaida / Pixabay

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.