Insert a Blank Row in Excel After Every Row of Data

To insert a blank row after every data row in Excel, add a temporary helper column, number your records 1, 3, 5 and so on, add the same number of blank rows with keys 2, 4, 6 and so on, then sort the whole range by the helper column. For a short list, you can instead insert rows one at a time from the bottom upward. Save a copy first, especially if the workbook contains formulas or important formatting.

What You’ll Need

An Excel workbook open in Excel for Microsoft 365 on Windows. The main method also works in recent desktop versions of Excel.
A rectangular data range with a single header row, if your data has headers. The examples below assume headers are in row 1 and records are in rows 2 onward; the process works with other starting rows too.
A spare column beside the data for temporary sort numbers. If there is no spare column, insert one temporarily or use an empty column elsewhere, making sure to include it when you sort.
Enough unused worksheet rows to add one temporary row for each record. For example, 50 records require 50 additional rows at the bottom before sorting.

Step-by-Step

Method 1: Add rows with a helper column

This method is practical for medium or large lists because you set up the row order once and sort the range. The helper numbers make each original record sort immediately before its new blank row. Do not sort just one column: the entire data block and helper column must move together.

Make a working copy. Use File > Save As to create a copy, or duplicate the worksheet before changing it. Sorting rearranges rows, so a saved copy gives you a straightforward way back if the wrong range is selected. If the workbook is shared, make sure you are allowed to make structural changes.
Identify the records to change. Check the first and last record, and decide whether totals, notes, or other special rows belong in the operation. The instructions below apply to ordinary records, not a title row or a total row. Keep the header row in the range to sort, but exclude it from the row numbers you assign.
Choose a temporary helper column. If the data occupies columns A through D, for example, use column E and label its header SortKey. If E already contains information, insert a temporary column beside the range or choose an empty column. Avoid putting the helper numbers inside a field that is part of your data.
Number the existing records with odd values. Type 1 next to the first record and 3 next to the second. Select those two helper cells, then drag the fill handle—the small square at the lower-right corner of the selection—down to the last record. Excel should extend the pattern as 5, 7, 9, and so on. If there is only one record, enter 1 for it. Check the final value before moving on.
Make space for the new rows at the bottom of the range. If there are 50 original records, extend the data block by 50 rows below them. You do not have to insert these as worksheet rows first: you can include 50 unused rows in the range you will sort and put a helper number in each new row. Leave the other cells in these added rows empty. Do not overwrite other information below the list.
Give the added rows even sort numbers. In the helper column beside the first added row, enter 2; beside the next, enter 4. Select both cells and drag the fill handle through the remaining added rows to continue 6, 8, 10, and so on. With 50 original records, the last new row should have key 100. The original records should have keys 1 through 99, using odd numbers.
Select the complete range. Select every data column, the helper column, the original records, and all the added rows. Include the header row if there is one. A reliable way is to drag across the full rectangular block, or click its first cell and Shift-click its last cell. Confirm that no neighboring data you need to keep is outside the selection.
Sort by the helper column. On the ribbon, choose Data > Sort. In the Sort dialog, choose the helper column as the Sort by field, select Cell Values if that option appears, and set the order to Smallest to Largest. If the header row is included, make sure My data has headers is checked so Excel does not treat the header as a record. Choose OK to sort.
Check the result before deleting the helper. The first original record should remain first, followed by a blank row, then the second record and another blank row. Continue checking down the list. The last original record should also be followed by a blank row. Confirm that each original row still has all its values, formulas, and formatting together.
Remove the helper column and keep the added rows. Once the order is correct, delete the temporary helper column or clear its contents. Do not delete the newly created blank rows. Save the workbook and check any formulas, filters, charts, or summaries that refer to the changed range.

For a concrete example, suppose the list has three records beneath a header. Give those records keys 1, 3, and 5, then add three empty rows with keys 2, 4, and 6. Sorting from smallest to largest produces record 1, blank row, record 2, blank row, record 3, blank row. The even and odd keys interleave the rows without requiring you to insert each one individually.

Method 2: Insert rows manually for a short list

For just a few records, manual insertion can be easier than setting up a helper column. Work upward from the last record so each inserted row does not change the position of the rows you still need to process.

Start with the bottom record in the list. Click its row number on the left edge of the worksheet to select the entire worksheet row immediately below it.
Choose Home > Insert > Insert Sheet Rows. Excel inserts a blank row above the row you selected, which places it directly after the record you are working on.
Move to the record above, select the worksheet row immediately beneath it, and use the same ribbon command. Repeat upward until every record has a blank row after it.
Review the first and last records and check that the new rows are in the intended places. If there is a header, do not insert a blank row after the header unless you specifically want one there.

For example, if records are in rows 2 to 6, begin below the record in row 6, then continue with the records that were originally in rows 5, 4, 3, and 2. Moving upward avoids accidentally skipping a record because an earlier insertion shifted its row number.

Common Mistakes

Sorting only the helper column. This separates the sort numbers from their records. Always select the complete rectangular data range, including all fields and the added rows, before sorting. If Excel asks whether to expand the selection, choose to expand it.
Including the header in the numbering. The header must stay at the top. Leave its helper cell empty or label it as a header, and use the header checkbox in the Sort dialog. If the header gets sorted into the data, undo immediately with Ctrl+Z on Windows, then correct the range and sort settings. On Mac, use Command+Z.
Using consecutive keys for both groups. If records and empty rows both receive overlapping or identical sort numbers, the order may not interleave as intended. Use odd numbers for original records and even numbers for the new rows, with no duplicates.
Forgetting to add one blank row per record. If there are fewer added rows than records, some records will not have a blank row after them. Count the records first and create the same number of added rows. If you want a blank after every record, include one after the final record too.
Sorting records that should stay together. A total row, section label, or note can be moved into the wrong place if it is included as though it were an ordinary record. Decide whether to exclude it, move it separately, or assign it a deliberate position in your sort plan.
Deleting the wrong rows after sorting. The blank rows are the result you want; the helper column is temporary. Clear or delete only the helper column after confirming the result.

Tips

Use a filter or Table carefully. If the range is an Excel Table, keep the helper field inside the Table and add the temporary blank records to the Table before sorting. A Table may automatically extend when you type directly below it. Confirm that the added rows are included in the Table and sort the complete Table, not a disconnected worksheet range.
Preserve formatting intentionally. Newly added rows may not match the height, borders, or fill of nearby rows. After sorting, copy the format from a neighboring row with Home > Format Painter, or use the desired cell formatting controls. Avoid copying cell contents if you want the row to remain empty.
Check formulas and references. Sorting moves entire records, but formulas that depend on fixed positions or ranges may need review. In particular, inspect totals, charts, named ranges, and formulas that assume the data is contiguous. Recalculate if Excel is set to manual calculation.
Keep the helper column visible until you verify the order. It is much easier to spot a missing or duplicated sort key before you remove the helper. Check the sequence from the top through the last record, then remove the temporary field.
For repeated work, save a clean template. If you often need alternating blank rows, keep an untouched copy of the original layout. That reduces the chance of sorting a partial range or mixing old helper values with a new list.
Do not rely on Ctrl+Z after unrelated edits. Undo can reverse the sort if it is the latest action, but later changes may make that inconvenient. Save a copy before sorting and verify the full result before continuing.

Troubleshooting

The rows did not alternate. Check that original records have 1, 3, 5, and so on, while the added blank rows have 2, 4, 6, and so on. Then confirm that the sort order is Smallest to Largest and that the helper column is selected as the sort field. Correct the keys and sort the entire range again.

Some of my data moved out of alignment. Undo immediately if possible, then repeat the sort with the full data block selected. In the sort prompt, choose to expand the selection rather than sorting only the helper column. If undo is no longer available, restore the saved copy and redo the operation carefully.

The header moved into the list. Undo the sort. Include the header row in the selected range and check My data has headers in the Sort dialog. Alternatively, select only the records and added rows, leaving the header outside the selection.

The fill handle copied the same number instead of creating a series. Start with two different values, such as 1 and 3, select both cells, and drag the fill handle. If Excel still copies them, use the Auto Fill Options button that appears and choose Fill Series. Check a few resulting values before sorting.

The blank rows are not truly empty. A cell may contain a space, formula, or old value that is not obvious. Select the added rows and use Delete to clear their contents before assigning the helper keys. Make sure you do not clear the helper numbers afterward, because those are what place the rows during the sort.

Excel says the sort range is not valid or the command is unavailable. Check for merged cells within the range; merged cells can prevent sorting. Unmerge them if appropriate, make the selection a consistent rectangle, and try again. Also confirm that the worksheet is not protected. If it is protected, you may need permission to unprotect it before changing rows.

The result looks right but formatting is inconsistent. Sorting does not guarantee that new rows inherit the exact appearance you want. Apply borders, row height, or other formatting after sorting, and check that conditional formatting rules still cover the expanded range.

Frequently Asked Questions

Can I insert a blank row after every row without a macro?
Yes. The helper-column sort method uses ordinary worksheet values and the Data tab, so it does not require VBA or a macro. Manual insertion is also an option for a small list.

Will this method work if my data has a header row?
Yes. Keep the header at the top and do not number it as a record. Include it in the selected range and check My data has headers in the Sort dialog, or leave the header outside the selection.

Does the last record get a blank row too?
Yes, if that is your goal. Add as many blank rows as there are records and give the final added row the next even key. The result ends with a blank row after the last record.

Can I do this in Excel for the web or on a Mac?
The helper-column approach is based on worksheet sorting and can be used in Excel for the web and Excel for Mac, although ribbon labels or dialog layouts may differ slightly. If a menu option is not where described, look for Sort on the Data tab. The ribbon paths in the steps are for Excel for Microsoft 365 on Windows.

Can I undo the sort if I make a mistake?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the sort, before making other changes. Because undo history can be lost as you continue working, saving a separate copy first is the safer option.

Should I leave the helper column in the finished sheet?
Usually not. Once you have checked the order, remove the temporary helper column or clear its values. Keep the newly created blank rows, and verify that deleting the helper has not removed a real field.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.