SHARE

Here’s how to actually add multiple rows between cells which contain data, in Google sheets.

Time required: 5-10 minutes depending on sheet size

Difficulty: Easy/Easy-Medium

Works on Google sheets and Excel (But my guide is for sheets) 

Guide summary for advanced users: (No paint diagram version) 

  1. Insert blank column somewhere in your sheet.
  2. Number your new column with odd numbers only, to the bottom of your data
  3. Where your odd numbers end, start adding even numbers. Continue until you get to +1 of your odd numbers above.
  4. Add even numbers below data.
  5. Ensure to repeat each even number for the number of rows you want to insert
  6. Continue this until your last even number
  7. Highlight your data (Just press CTRL + A to highlight all)
  8. Click Data > Click sort range….
  9. Sort by your new column
  10. Right click the header of your temporary column and delete

Disclaimer: This is a function I struggled with. I can’t write visual basic, and upon Googling it all I could find answers for were how to simply add rows between 2 bits of data, not a list.

If that’s what you need to do, see this link from Google, its simple enough, but not what I wanted to achieve.

Disclaimer 2: This method is based solely on guesswork and minimal Google sheets knowledge, but it works. Please feel free to tweet me a better solution and ill include it here 🙂 (@TheSteveMills)

Step one: Choose your data, add a blank column

  1. Insert blank column somewhere in your sheet.

Let’s assume you need to insert 10 rows, between each row. Firstly you will need to insert a blank column so we can number it.

Lets stick it in the middle of the sheet, just to show we can do that, see example:

Step two: Give your new temporary column odd numbers

  1. Number your new column with odd numbers only, to the bottom of your data

I assume my readers find this obvious, but for those new to Google sheets, simply:

Tap in the first few odd numbers > Click the bottom right of the bottom value > Drag down to the end of your data 

Step 3: Add even numbers below your data in your new column

  1. Where your odd numbers end, start adding even numbers. Continue until you get to +1 of your odd numbers above.

(This is the hardest bit)

Here you want to start again at the bottom of your data, and simply tap in the even numbers. You need to repeat each even number for the amount of rows you want to insert between your existing data. So..

  1. Add even numbers below data.
  2. Ensure to repeat each even number for the number of rows you want to insert
  3. Continue this until your last even number

In this case, lets use three as an example:

This step takes the most time. It might take you a few min to tap your even numbers in.

Pro Tip: To get a nicely formatted colourful Google Sheet – Highlight your data, click- Data > Alternating Colours > Choose colour palette.

Not enough people do this. Upgrade your Google Sheets presentation

Step 4: Sort the sheet – and done!

This is the last bit, its easy.

  1. Highlight your data (Just press CTRL + A to highlight all)
  2. Click Data > Click sort range….
  3. Sort by your new column
  4. Right click the header of your temporary column and delete

Now you are finished. The whole process shouldn’t take long. 5-10 mins at max, it took me around 30 mins to do 10,000 rows of data spaced by 10 each, which would have probably taken me hours and hours to do manually.

SHARE
Previous articleBest Mitel IP Phones – Tech-Mag Guides
Next articleHow Your Smartphone Can Keep Kids Safe on Halloween
I'm Steve Mills. Honest SEO. I can show you how to increase your organic traffic. I have worked with multiple digital marketing agencies, hundreds of freelancers, and many company owners, so I know the pitfalls of growing a business online. If you like what I write, that's awesome. If not, let me know.