In Excel: number your data rows 1, 2, 3 … in a helper column, type 1.5, 2.5, 3.5 … below them, then sort the whole block by that column — one blank row lands under every data row, and you delete the helper column afterwards.
On this page7
Add a helper column beside the data and number the existing rows 1, 2, 3 … down to the last row.
Below the last data row, enter 1.5 and 2.5 and fill down to n.5 — one half-step entry for every blank row you want.
Select the whole block, including the helper column and both sets of numbers.
Open Data ▸ Sort and sort by the helper column, ascending.
Delete the helper column once the blank rows are interleaved correctly.
What this does
Getting a blank row between EVERY existing row is a different task from inserting one row: repeating Insert n times is slow and error-prone at scale, so the reliable method is a helper column and a sort. Number the data rows 1, 2, 3 … in a column beside them, then add half-step numbers — 1.5, 2.5, 3.5 … — below the last row, one per blank row you want. Sorting the combined block ascending by that column interleaves a blank after every numbered row, because Excel is not documented as a stable sort and reusing 1, 2, 3 … a second time leaves the blank/data order undefined. For a handful of rows, Ctrl-click the row headers you want a blank above and right-click ▸ Insert adds them all in one action, and F4 repeats the last insert at the next spot. Before you run it on a workbook other people depend on, try it on a copy or a few rows first. Undo only reaches back through the current session, so a quick trial run is the cheapest way to see exactly what will change before the file is saved and shared. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “insert row in between rows excel”. Start on a copy or a tiny sample, keep the affected rows visible, and compare the result with the outcome you expected before you touch the real workbook. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. The point is a layout choice that keeps the sheet readable and sortable, but the practical win is that someone else can open the file and understand what happened without asking you.
Worked example to check
Worked example for a blank after every record: A2:B4 contains Ada/10, Ben/20, and Cy/30. Put helper keys 1, 2, 3 in C2:C4 and 1.5, 2.5, 3.5 in C5:C7, leaving A5:B7 empty. Sort the entire A2:C7 block by column C, smallest to largest. A2:B7 becomes Ada/10, blank, Ben/20, blank, Cy/30, blank. Check that each name still has its original amount before deleting C. For only one gap between Ada and Ben, insert one whole row above Ben instead of sorting. Check formulas and totals after either method.
In Google Sheets
This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Here is the takeaway for “insert row in between rows excel”: follow the steps at the top, check the result on the sheet itself, and keep undo in mind while you experiment. Done once deliberately, it becomes something you do from memory in Excel rather than something you search for again.
Common mistakes
- Sorting only the helper column and accepting Excel's "continue with the current selection" prompt, which scrambles the data away from its own row.
- Including the header row in the sort range, so it ends up mixed into the data instead of staying on top.
- Reusing 1, 2, 3 … a second time instead of half-steps, which leaves whether the blank lands before or after its data row down to Excel's sort.
- Ctrl-clicking row headers one by one for hundreds of rows — the helper-column sort exists precisely to replace that.
- Forgetting that a blank row inside a range breaks Ctrl+Shift+↓ and splits what Excel treats as one table for sorting and filtering.
Frequently asked questions
How do I put a blank row between every row?
Number the data rows in a helper column, add half-step numbers (1.5, 2.5 …) below them for each blank you want, then sort the whole block ascending by that column and delete the helper column.
Can I do it without a helper column?
Yes, for a small sheet: Ctrl-click the row headers you want a blank above, right-click and choose Insert — Excel adds one blank above each selected row in a single action. Past about 20 rows the sort method is faster and less error-prone.
How do I add a blank row only where a value changes?
Work on a copy and keep the data in its original order. Use =IF(A3<>A2,1,0) in a helper column to mark each change. Starting at the bottom, select the row header for each marked row and insert a whole row above it. Working upward keeps the remaining marked rows in place.
How do I take the blank rows back out again?
Work on a copy. If a key column is filled in every real data row, select only that column within the data range, use Go To Special ▸ Blanks, and delete the selected entire rows. Otherwise, inspect rows and delete only those that are completely empty. Selecting blanks across the whole table can delete real records with missing cells.