This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I am working with hundreds of rows of contiguous data and I need to separate the rows with a blank row. When I attempt to select ALL the rows as a group, and then a click "Insert Sheet Rows" from the Insert function on the Home tab, Excel inserts new rows directly above my existing data and not between the rows as anticipated.
I have discovered that if I hold down the Ctrl key on the keyboard, and individually select the first (blank) cell of each row that contains data; then go to the “Home” tab, click the drop-down arrow for the “Insert” function and then select “Insert Sheet Rows” from the context menu; I can successfully insert rows between the existing rows of data. As you can imagine, this task is exceedingly tedious and time consuming, because I am forced to click hundreds of times to select each cell/row individually.
How do insert a blank row between each existing row after selecting ALL of the existing rows as a group?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Try the below.
step 1 - select a blank column to be the "key column". (you can either insert one at Column A or use the last empty column in your table)
-
step 2 - use the key column to number your first group of rows between 1 to 99. if you use a forumula, be sure to copy of the formulas and then paste them back into their respective cells as "values".
-
the result above will be that your first group of rows are number between 1 and 100 via the key column. the key column will temporarily be customized row numbers.
-
step 3 - next re-number the rows between 101 and 200. use a formula in their key column to re-number these blank rows like the first group but add a decimal point.
-
for example the row 101 should get the value of 1.1 in the key column. row 102 should get the value of 2.1. row 103 should get the value of 3.1 and so on until row 200. (again copy their formulas and paste them back into their respective cells as values).
-
step 4 - next number rows between 201 to 300. again in their key columns create a formula to assign row 201 with the value of 1.2. row 202 with the value of 2.2 and row 203 with 3.2, etc... ( replace the formulas with values.)
-
step 5 - here is the magic - now sort your table via the key column.
-
lastly, since it is unclear how much data (values, formulas, links, formatting, etc) you are sorting vs how much cpu and memory power you have. so you may end up scrambling your data.
-
as a precaution i highly recommend to (1) use the methodology on a copy of your spreadsheet and (2) be sure you have no programs other than excel running. so temporarily disable your internet and your anti virus program to give your computer maximum power to sort all your data and ensure there are no interruptions while it is in a state of maximum processing.
You can use a macro:
Sub InsertRows()
Dim r As Long
Application.ScreenUpdating = False
For r = Selection.Rows.Count To 2 Step - 1
Selection.Rows(r).Insert Shift:=xlShiftDown
Next r
Application.ScreenUpdating = True
End Sub
it's "elegant logic". let me know how many row's you actually have in order to make the explanation more exacting.
you can use a formula if you don't want use a macro.