How do I insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets?

Lets say in row 1 it says "A" and in row 2 it says "B", "C" in row 3, etc. I would like it to say "B" in row 3, and "C" in row 5, etc. from now on. Inserting a row underneath one by one is simple. I'm asking about inserting empty rows on a larger scale.

  • What do you mean by "simultaneously"? What are you trying to do? What have you already tried that didn't work. As it stands, your question is not very clear.
    – ale
    Commented Apr 23, 2015 at 12:52
  • Since you cross-posted this on Stack Overflow (something's that's frowned upon) but expanded that post, I've added your changes there to this question here so that it's clearer.
    – ale
    Commented Apr 23, 2015 at 19:02

2 Answers 2


I think this requires a script, such as this one:

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var range = sheet.getDataRange();
   var values = range.getValues();
   for (var i = values.length; i>=1; i--) {
     if (values[i-1].join("")) {
       sheet.insertRowsAfter(i, 1);

The command getDataRange() selects the range containing data; but since it's possible you have empty rows between filled ones, there is a separate check for nonemptiness of rows: values[i-1].join("") evaluates to True only if some data is present. The offset i-1 is needed because the index in an array is 0-based, while the row numbers begin with 1. I use i for row number.

The rows are inserted from bottom up (i--), to avoid the situation where newly added rows move down the ones that are still to be processed.

To use this:

  1. Go to Tools->Script Editor.
  2. Copy-paste the script there.
  3. Save, renaming to something descriptive like "Extra lines".
  4. Click "Run" button.
  5. At the first execution (only) you'll be prompted to authorize the script.


  • I would just execute from the script page, by pressing "Run" there. I suppose this is not the kind of operation you do many times on the same spreadsheet.
    – user79865
    Commented Apr 26, 2015 at 23:59
  • The function now runs until it exceeds maximum limit, and when I run it again it just applies itself again to the ones that have already been applied. But essentially it does work. Thanks.
    – Sirudol
    Commented Apr 27, 2015 at 0:41
  • I see: the spreadsheet is so large that the computation times out. When this happens, you can replace "values.length" on line 6 by the number of the last row that was left unprocessed, and run again.
    – user79865
    Commented Apr 27, 2015 at 0:43

If selecting some cells in the spreadsheet:
1. Hold the ctrl key down.
2. Select the cells that you want to empty.
3. Once cells are selected, hit the backspace key.

If selecting all cells in the Spreadsheet:
1. Press Ctrl + a
2. Once all cells are selected, hit the backspace key.

  • I know how to erase cells, I'm wondering how to add empty cells in between. Totally different.
    – Sirudol
    Commented Apr 22, 2015 at 6:09
  • 1
    You should have stated this in your original question. Commented Apr 22, 2015 at 6:23
  • I did. I wrote "under" not "in".
    – Sirudol
    Commented Apr 22, 2015 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.