To place a list into Excel with blank rows between entries, put a formula in the first destination cell, fill it down, and—if you need fixed content—copy the results and use Paste Values. For example, a formula can take items from A2:A6 and place them in B2, B4, B6, B8, and B10, leaving the rows between empty. This approach works in Microsoft Excel for Microsoft 365 on Windows and does not change your original list.
What You’ll Need
- A source list in one continuous range, such as A2:A6.
- An empty destination area with enough room for the entries and the blank rows between them. For five source items with one blank row between each item, allow ten destination rows.
- Excel for Microsoft 365. The formulas below use standard Excel functions and also work in many earlier desktop versions of Excel.
Save the workbook before making large changes, and check that the destination area does not contain information you need to keep. The formula method writes results into the cells where you fill it, so using an empty destination range prevents accidental replacement of existing data.
Step-by-Step
- Identify the source and destination ranges. In this example, the source items are in A2:A6, and the destination starts at B2. There are five source items. With one blank row between each item, the output needs ten rows, from B2 through B11: the items go in B2, B4, B6, B8, and B10, while B3, B5, B7, B9, and B11 stay blank. The final blank is optional, but including it makes the destination range a consistent two rows per item.
- Click the first destination cell. Select B2, or the top cell of your own empty destination column. Do not select a whole column unless you intend to fill the formula down the whole column; the source range and destination start in the formula must match your sheet.
- Enter the formula for one blank row between entries. With source items in A2:A6 and the first destination cell B2, enter
=IF(MOD(ROWS($B$2:B2)-1,2)=0,INDEX($A$2:$A$6,INT((ROWS($B$2:B2)+1)/2)),"")in B2, then press Enter. The formula returns the first source item in B2. The empty text result, shown as "", makes the next destination row appear blank. - Fill the formula down far enough. Select B2 and drag its fill handle—the small square at the lower-right corner of the cell—down through B11. Alternatively, select B2:B11 and choose Home > Fill > Down, or press Ctrl+D on Windows. Excel adjusts the formula for each row while keeping the source range fixed. On a Mac, use the fill handle or the Fill command on the Home tab; keyboard shortcuts can differ depending on keyboard settings.
- Check the pattern before using the results. Confirm that B2 shows the first source item, B3 is blank, B4 shows the second item, and the pattern continues. The formula takes values in source order and leaves the intervening rows empty. If an item appears in the wrong row, check that you started in the intended destination cell and filled down the correct number of rows.
- Convert formulas to fixed values if needed. If the output should not change when the source list changes, select B2:B11 and copy it. Keep the range selected, then choose Home > Paste > Paste Values. This replaces the formulas with their current displayed results, including the blank results. On Windows, Ctrl+C copies the range and the Paste Values button is on the Home tab. On a Mac, use Command+C to copy, then choose Paste Values from the Home tab or the paste options.
- Keep the original list until you have checked the output. Compare the first and last items and, if the list is important, check a few items in the middle. Once the output is correct, you can use it as your spaced list. If you only needed a one-time layout, the original source column can remain in place or be removed after you confirm nothing else depends on it.
The example assumes that A2:A6 contains five items and B2 is the first destination cell. Filled through B11, it places the five items in B2, B4, B6, B8, and B10. Because the formula returns an empty string in the alternate rows, those cells look blank, but they contain formulas until you use Paste Values. If you need cells that are truly empty for another process, convert the results to values and then consider whether the empty-string cells need to be cleared separately.
To skip two rows between entries instead of one, use a three-row repeating pattern. With source items in A2:A6 and the first destination cell B2, enter =IF(MOD(ROWS($B$2:B2)-1,3)=0,INDEX($A$2:$A$6,INT((ROWS($B$2:B2)-1)/3)+1),"") in B2 and fill it through B16. The items then appear in B2, B5, B8, B11, and B14. In general, the repeating interval is the number of blank rows you want plus one. Update the 2 or 3 in the formula consistently when changing that interval, and make sure the destination range is long enough for every source item.
Common Mistakes
- Filling too few rows: For one blank row between entries, each source item needs two destination rows. Five items therefore need ten rows. If you fill only five rows, Excel will show only part of the list.
- Using the wrong source range: The example formula refers to A2:A6. If your list is in another column or has a different number of items, change that reference to match. An incorrect range can omit entries or repeat the wrong ones.
- Changing the destination start without changing the formula: The example is designed to start in B2. If your first output cell is D5, replace both references to B2 in the row-count part with D5, and put the formula in D5. Otherwise, the alternating pattern may start in the wrong place.
- Expecting blanks to be truly empty: The formula displays an empty string in the rows that are meant to look blank. Those cells still contain formulas. Use Paste Values if you need to remove the formulas, and check whether a later import or calculation requires genuinely empty cells.
- Pasting over existing content: Filling formulas or pasting values replaces what is already in the destination range. Use a new empty column or make a copy of the existing data first.
- Trying to paste into selected cells one at a time: Standard Excel paste is not a dependable way to distribute a copied block into a manually selected set of separated cells. Use the formula pattern instead of Ctrl-clicking nonadjacent cells and expecting Excel to spread the clipboard contents across them.
Tips
- Use a nearby empty column for a preview. Leaving the source list intact makes it easy to verify the output and correct the formula without losing the original data.
- Choose Paste Values only when the layout is final. Formula results update if the source list changes; values do not. Keep formulas if you want the spaced output to stay linked to the source.
- Match the interval to the task. One blank row means a two-row interval; two blank rows means a three-row interval. Count the occupied row and the skipped rows together when deciding how far to fill.
- Use a clear destination heading. A label such as “Spaced list” helps distinguish the generated output from the original data, especially if both columns remain in the worksheet.
- Check formatting separately from content. The formula returns the source values, but it does not necessarily carry over the source cells’ formatting. If you need matching number formats, fonts, or colors, apply them to the destination range after confirming the results.
- For a one-off result, avoid editing the source. Generating the spaced version in a separate area is easier to review and undo than inserting blank rows into a working data table.
Troubleshooting
The formula displays an error. Check that the source range exists and that the formula was entered in the intended first destination cell. If your Excel uses semicolons rather than commas as formula separators because of regional settings, replace the argument-separating commas with semicolons. Do not remove the quotation marks around the empty text result.
The output starts with a blank or the spacing is inconsistent. Confirm that the first formula is in the cell named in its row-count references. For the example, enter it in B2 and make sure the references begin with $B$2. If you copied a formula from another layout, update those references to your actual starting cell before filling down.
Some source items do not appear. Extend the destination range. With one skipped row between items, fill two rows for every source item; with two skipped rows, fill three. Also inspect the source range in the formula to make sure it includes the final item. If your list has more than five entries, change $A$2:$A$6 to cover the full list.
The blank rows are not accepted as empty by another feature. An empty string returned by a formula can look blank while still being a formula result. Copy the output and use Home > Paste > Paste Values. If a system specifically requires truly empty cells, select the rows that should be empty after converting to values and clear their contents with Delete; confirm first that those cells contain no data you need.
Paste Values is not available or the output changes unexpectedly. Make sure you copied the formula range before opening the paste options, and paste into the same selected range. If you leave the formulas in place, changes to the source list will flow through to the output by design. If the output should remain fixed, convert it to values after checking it.
The formula is visible as text instead of calculating. The destination cells may be formatted as Text. Change their number format to General, re-enter the formula in the first cell, and fill it down again. Also confirm that the formula begins with an equals sign and that Show Formulas is not turned on; pressing Ctrl+` on Windows toggles that worksheet view.
Frequently Asked Questions
Can I paste every other row without using a formula?
Excel does not reliably distribute a normal copied list into a manually selected series of nonadjacent cells. The formula method is a straightforward option: it generates the spaced layout, and you can convert the results to values afterward. For a one-time layout, this is usually safer than manually inserting rows throughout a sheet.
How do I leave two blank rows between each item?
Use the three-row formula shown above and fill it down three destination rows for each source item. With source items in A2:A6 and the output starting in B2, the values land in B2, B5, B8, B11, and B14. The two rows between each value return empty text.
Will the spaced list update when I edit the source?
Yes, while the destination still contains formulas, changes to the referenced source cells are reflected in the output. If you use Paste Values, the output becomes fixed at the values shown at that time and will no longer update from the source.
Can I use this method to paste numbers, dates, or text?
Yes. INDEX returns the source cell’s value, so the method can be used with text, numbers, and dates. Formatting may need to be applied separately, particularly for dates, percentages, and currency values.
Does this work in Excel for the web or on a Mac?
The formula uses standard Excel functions and works in Excel for the web and Excel for Mac as well as Microsoft 365 on Windows. The ribbon layout and keyboard shortcuts can differ. If a shortcut does not work on your device, use the fill handle and the Paste Values command from the Home tab.
Related
- How to Insert an Image Into Excel and Position It
- How to Add an Image Link to an Excel Cell in Microsoft 365
- How to Insert an Image in Google Sheets: Quick Steps
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Formulas vs Excel: Key DifferencesGoogle Sheets and Excel use many of the same formula conventions, so everyday calculations such as sums, averages,…
- Insert an Empty Checkbox in Word: Easy MethodsTo insert an empty checkbox in Word, type 2610 and press Alt+X on Windows to convert it to…
- Freeze Panes in Excel: Lock the First Column and Header RowTo keep column A visible while you scroll in Excel for Microsoft 365 on Windows, select View >…
- 0.3-Inch Hanging Indent in Word: Set It Up PreciselyTo create a 0.3-inch hanging indent in Word, select the paragraph or paragraphs, open the Paragraph dialog, choose…
- Calculate Percentage in Google Sheets: Formulas & TipsTo calculate a percentage in Google Sheets, divide the part by the total, then format the result as…
- Excel Shortcut to Insert a Copied RowIn Excel for Microsoft 365 on Windows, copy the entire row, select the row where the copy should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.