Excel Fill Data with Blank Rows Between Items
Question details
The user needs to transfer records to another sheet while leaving a consistent number of blank rows between each item.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A destination sheet contains blank rows between records, so source data must be retrieved at fixed intervals instead of being copied into every consecutive row.
- Observed behavior
- Direct cell references advance for every destination row and do not automatically skip the blank rows between items.
Confirm the source data range, the destination starting cell, and the number of blank rows required between records. If using INDEX and MATCH, prepare a helper column containing sequential source row numbers.
Use a helper sequence with INDEX and MATCH
Use this method when you need a reliable formula that works in versions without dynamic-array functions and leaves a fixed number of blank rows between records.
For two blank rows between records, each item occupies a three-row pattern. A sequence such as 1, 4, 7, and 10 tells the lookup formula which source record to return. In the example below, the source helper column contains sequential row numbers and the source values are in column B.
On the source sheet, insert a helper column next to the records. In the first helper cell, select the formula bar or press F2, enter =ROW(A1), and fill the formula down so the helper values run 1, 2, 3, and so on.
On the destination sheet, select the first output cell and enter =INDEX(OrigSheetName!$B$1:$B$1000,MATCH(ROW(A1)*3-2,OrigSheetName!$A$1:$A$1000,0)). Replace OrigSheetName and the ranges with your actual sheet name and source columns.
Use the fill handle on the destination cell or choose Home > Fill > Down. The formula returns source records for destination rows 1, 4, 7, and 10, leaving two rows between each item.
For a different layout, replace the multiplier 3 with the total pattern height. For example, use ROW(A1)*4-3 when leaving three blank rows between records.
Use a dynamic array to expand records into a repeating row pattern
Use this option in Microsoft 365 when the source range is named and your Excel version supports EXPAND and TOCOL.
Fill Spreadsheet Records with Blank Rows Using WPS Office
WPS Spreadsheet can create a helper sequence and use INDEX with MATCH to place source records at fixed intervals while preserving blank rows. Its familiar formula tools support a smooth transition from Excel workbooks.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the source and destination sheets.
- 2. Create the helper sequence: On the source sheet, select a helper-column cell and enter =ROW(A1), then use Home > Fill > Down to create sequential row numbers.
- 3. Enter the interval lookup: On the destination sheet, select the first output cell and enter =INDEX(OrigSheetName!$B$1:$B$1000,MATCH(ROW(A1)*3-2,OrigSheetName!$A$1:$A$1000,0)), replacing the sheet name and ranges with your own.
- 4. Fill the formula through the destination: Select the formula cell, drag its fill handle downward, or use Home > Fill > Down. The records will appear with two blank rows between items.
Frequently Asked Questions
Why does a direct cell reference fill every destination row?
A direct reference such as =Source!B1 increases its source row by one each time it is filled down. It does not know that some destination rows should remain blank, so use a controlled sequence with INDEX and MATCH instead.
How do I leave two blank rows between records?
Use a three-row pattern. In the destination formula, use ROW(A1)*3-2 to generate source positions 1, 4, 7, and 10 as the formula is filled down.
How can I leave three blank rows between records?
Use a four-row pattern and change the sequence to ROW(A1)*4-3. This returns source positions 1, 5, 9, and 13, leaving three blank rows between displayed records.
Do I need a helper column for the INDEX and MATCH method?
The recommended formula uses a helper column containing sequential numbers so MATCH can locate each requested source position. If the source range is already ordered and contiguous, INDEX can sometimes retrieve rows directly without MATCH.
Can dynamic arrays create the blank-row pattern automatically?
In Microsoft 365, =TOCOL(EXPAND(arr,,3,"")) can spill a named range into a three-row pattern. Use the helper-column method when EXPAND or TOCOL is unavailable or when you need broader workbook compatibility.
