A formula cannot insert a physical worksheet row or move existing cells. To show a blank row inside a formula-generated copy of your data, place the formula below in an empty area: with source data in A2:C6, this inserts a displayed blank row between the second and third source rows. The result is a six-row spill range; your original data stays unchanged. If you need a real row in the worksheet, use Excel’s Insert Sheet Rows command instead.
With the example data in A2:C6, put this formula in a clear cell outside that range:
=VSTACK(A2:C3,{"","",""},A4:C6)
VSTACK is available in Microsoft 365 and Excel 2024. The formula stacks the first two source rows, a one-row array of three empty strings, and the remaining three rows. It displays six rows by three columns. The inserted-looking row is part of the formula result, not a new worksheet row.
What You’ll Need
- Excel for Microsoft 365 on Windows for the VSTACK example. Excel 2024 also includes VSTACK. Excel for the web and Excel for Mac may have the function if your version includes it; older perpetual versions such as Excel 2019 do not.
- A source range with the same number of columns in each section. This example uses A2:C6, so the inserted blank row also needs three columns.
- An empty destination area large enough for the entire result, with no existing values or merged cells blocking the spill.
- If you want to change the worksheet itself rather than create a formula result, permission to edit the sheet and a saved copy if the workbook is important.
Step-by-Step
- Decide whether you need a real row or a displayed gap. A formula can return a new arrangement of values in a separate area, but it cannot add a row to the worksheet, shift cells, or change the source range. Use the formula method for a report, a presentation view, or a rearranged copy that should update when its source values change. Use the ribbon method in step 6 if other cells and formulas need to move down as a result of inserting a row.
- Identify the source range and the insertion point. In the example, the source table occupies A2:C6 and has five records. The blank row is placed after the source rows in A2:C3 and before the rows in A4:C6. When using your own data, divide the source into two adjoining parts at the place where the blank row should appear. Keep the same columns in both parts. For instance, if your data runs from A2:F30 and the gap belongs before row 12, the upper segment ends at row 11 and the lower segment begins at row 12.
- Choose a destination outside the source. Click a blank cell where the new arrangement can expand down and across. For the example, a cell such as E2 is suitable if the cells to its right and below are clear. Do not put the formula in A2:C6: its result would overlap its own source, and a spilled-array formula cannot spill into occupied cells. Leave enough room for one additional row and the source’s full width.
- Enter the formula using matching sections. For source data in A2:C6, with the blank line between rows 3 and 4, enter this in the destination cell:
=VSTACK(A2:C3,{"","",""},A4:C6)
The first range has two rows and three columns, the array constant adds one row and three columns, and the last range has three rows and three columns. The output therefore contains six rows and three columns. Press Enter; in current Microsoft 365, Excel fills the result into the cells below and to the right automatically. You do not need to copy the formula into the spill area.
- Check the result and adjust the range split if needed. Confirm that the blank line appears at the intended position and that all source rows are included once. If the gap needs to move down, extend the first range and start the last range on the next row. For example, to put the gap after row 4, the first range would end at row 4 and the second would begin at row 5. The ranges must not overlap or leave out a source row. If your data has more than three columns, make the array constant just as wide by adding one empty-string entry for every column.
- Insert a real worksheet row when that is what you need. Select the row number where the new row should appear, then choose Home > Insert > Insert Sheet Rows. Excel adds a physical row above the selected row and shifts cells below it down. On Windows, you can select the row and press Ctrl+Shift++ (often typed as Ctrl+Shift+=, depending on the keyboard). On Mac, use the ribbon command if the keyboard shortcut differs with your keyboard layout. In Excel for the web, use the Home tab’s Insert menu or right-click a row heading and choose Insert Rows.
After a physical row is inserted, enter values or formulas into it as needed. Excel may adjust formulas that refer to cells affected by the change, but check important totals, charts, named ranges, and external references. A formula-generated gap does not cause any of those worksheet-level changes.
Common Mistakes
- Expecting a formula to change the sheet structure. Formulas calculate values; they do not add worksheet rows. A VSTACK result only looks like a new arrangement in its output area. If the requirement is to push existing cells down, use Insert Sheet Rows.
- Putting the output over the source. The result needs its own clear spill area. If it overlaps the source, Excel may show a spill error. Move the formula to an empty area or create a separate output sheet.
- Making the blank row the wrong width. The array constant must have the same number of columns as the ranges above and below it. Three empty-string entries fit three-column data; a two-column constant does not fit a three-column source.
- Leaving out or duplicating a source row. The upper and lower ranges should meet at the intended split. Check the final row of the first range and the first row of the second range carefully before relying on the output.
- Expecting a truly empty row. The inserted display row contains empty-string results, not untouched blank cells. It looks empty, but functions that count or test cells can treat formula-generated empty strings differently from genuinely blank cells.
- Typing the formula into an Excel Table. Dynamic-array formulas generally need to spill into ordinary worksheet cells; they cannot spill inside a table’s data body. Put the formula outside the table, or use a normal range for the output.
Tips
- Keep the formula output separate from the original data. This makes it clear which values are source entries and which are calculated, and it avoids accidental edits to the source.
- Use a heading above the output, such as “Report view,” so other people know it is a generated display. Add a note that the blank line is formula-produced if the distinction matters to downstream calculations.
- For wider data, count the columns before writing the array constant. A source range A2:F20 has six columns, so the blank row needs six empty-string entries. Keep the commas between entries and the braces around the row constant.
- When you move the gap, edit both source segments together. If the data grows, update the range references so new records are not silently excluded. If the source changes shape often, consider whether a manually maintained formula output is the right reporting setup.
- Use cell formatting on the output range to set borders, number formats, or row height. Formatting can make the gap visible, but it does not turn the calculated line into a real worksheet row.
- To insert more than one displayed blank line, add another one-row empty array to the VSTACK arguments for each line. Each added array must still have the same column count. Keep the formula readable and verify the spill result after changing it.
Troubleshooting
If Excel displays #SPILL!, select the formula cell and inspect the outlined spill range. Clear any contents blocking the result, unmerge cells in that area, or move the formula to a larger empty space. A cell that looks blank may contain a space or another invisible value. The source ranges themselves should not overlap the output.
If Excel displays #NAME?, your Excel version may not support VSTACK, or the function name may not be recognized in your installation. Use Microsoft 365 or Excel 2024, check for Office updates, or use the physical-row method. In older Excel, you can make a separate report by copying the upper source rows, leaving a blank worksheet row, and then linking or copying the lower rows beneath them; that approach is not a single spilling formula.
If Excel reports a formula syntax error, check that the braces and parentheses are balanced, that the commas separate the three array entries, and that the source references are valid. Excel’s list separator can depend on regional settings. The formula examples here use commas as requested; if your installation expects semicolons between function arguments, use the separator Excel accepts for your locale. Do not remove the braces around the array constant.
If the output is missing records or repeats a record, review the point where the ranges meet. The last row of the first range should be immediately above the first row of the second range. For example, A2:C3 followed by A4:C6 includes rows 2 through 6 once each. If you change one boundary, change the other accordingly.
If you need the formula to include new rows automatically, a fixed range such as A2:C6 will not expand merely because you type a new record beneath it. You can revise the ranges, use a source formatted as an Excel Table with a suitable formula design, or choose a worksheet insertion approach instead. Test the setup after adding data, especially if other formulas depend on the result.
If the gap appears but is not treated as blank, remember that the row contains formulas returning empty text. For a printed report this is usually fine. For calculations that require genuinely empty cells, use a real worksheet row or adjust the downstream calculation to handle empty strings appropriately.
Frequently Asked Questions
Can I insert a real Excel row using a formula?
No. A worksheet formula returns a value or array; it cannot alter worksheet structure or shift existing cells. Select a row heading and use Home > Insert > Insert Sheet Rows to add a real row.
Does VSTACK change my original data?
No. It creates a calculated copy in the formula’s spill area. Editing the source can update the displayed result, but editing the spilled cells directly is not the way to change the source; make changes in the original range instead.
Will this work in Excel 2019 or Excel 2021?
VSTACK is available in Microsoft 365 and Excel 2024, not in the standard Excel 2019 or Excel 2021 feature set. If it is unavailable, insert a worksheet row using the ribbon or build the report manually in a separate area.
Can I add a blank row at the very top of the displayed results?
Yes, but the example is deliberately arranged to insert a gap between two source sections. To put the gap first, place the blank-row array before the source range in VSTACK; make sure the array has the same number of columns as the data.
Can I use the formula inside an Excel Table?
Not as a spilling formula within the table’s data body. Enter it in ordinary cells outside the table, leaving enough clear space for the result. The original table can remain the source.
Why does the inserted-looking row count as nonblank?
It contains empty strings returned by an array constant rather than cells with no contents. Some counting, filtering, or blank-testing formulas distinguish the two. If that difference affects your workbook, use an actual worksheet row or adapt the calculation that reads the output.
Related
- Switch Excel Columns From Letters to Numbers?
- Choose an Excel Data Range: Methods, Tips, and Shortcuts
- Excel Letter Count: Count Letters and Characters in Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR With IF in Excel: Syntax, Examples, and TipsTo use IFERROR with IF in Excel, put the IF function inside IFERROR: =IFERROR(IF(B2=0,"No quantity",A2/B2),"Check values"). IF makes…
- COUNTIF Excel Not Equal: Formulas, Blanks, and ExamplesTo count cells that are not equal to a value in Excel, use =COUNTIF(A2:A20,"<>Apple"). The <> operator means…
- XLOOKUP Entry Examples: Formulas for Common SearchesXLOOKUP entry examples show how to find a value in one range and return its matching value from…
- Excel Formula for the Larger of Two NumbersTo return the larger of two numbers in Excel, use the MAX function. With the values in A1…
- Excel LET Function: How to Name and Reuse CalculationsUse Excel’s LET function to give a name to a value or calculation, then use that name elsewhere…
- SUMIFS in Excel: Sum Values by Multiple Criteria ExplainedSUMIFS in Excel adds values only when they meet one or more conditions. For example, with sales in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.