Insert a Row in Excel and Keep Formulas

To insert a row in Excel and keep formulas, select the row heading where the new row should go, then choose Home > Insert > Insert Sheet Rows. In an Excel Table, formulas in calculated columns usually fill into the new row automatically; in a normal range, check the new row and copy the formula down if needed. Excel adjusts many existing references when you insert a row, but it may not create every formula you need.

What You’ll Need

A workbook you can edit in Microsoft Excel for Microsoft 365 on Windows.
A clear idea of where the new row belongs and which columns should contain formulas.
If the data is important, a saved copy of the workbook or a recent version you can restore.

You do not need an add-in or macro for the usual methods. The steps below cover both Excel Tables and ordinary worksheet ranges, because formula behavior differs between them.

Step-by-Step

Identify the row and formulas involved. Find the rows immediately above and below the intended insertion point. Look across the relevant columns for formulas, totals, or other calculated values. Click a formula cell and inspect the formula bar if you are unsure whether it contains a formula or a typed value. Also note whether the data is an Excel Table: clicking inside it should show a Table Design tab on the ribbon.
Choose the insertion point. Excel inserts a new worksheet row above the row you select. For example, to add a row between rows 5 and 6, select the row 6 heading at the far left of the grid. If you select a cell instead of the row heading, Excel may insert only a cell or a smaller range, depending on the command you use. Selecting the row heading is the straightforward choice when you need a complete worksheet row.
Insert the row. On Windows, with the row heading selected, go to Home > Insert > Insert Sheet Rows. You can also right-click the selected row heading and choose Insert. Excel adds a blank row above it and shifts the selected row and the rows beneath it down. If you use the keyboard, select the row heading and press Ctrl+Shift+=; on some keyboards, the plus key requires Shift. The ribbon or right-click method is easier if the shortcut behaves differently on your keyboard.
Check how Excel handled existing formulas. Look at formulas above and below the new row, especially totals and formulas that refer to a range containing the insertion point. Excel updates many cell references to account for inserted rows. That does not necessarily mean it has placed a formula in every blank cell of the new row. Click the cells that should calculate something and check the formula bar rather than assuming the row is complete.
If the data is an Excel Table, check its calculated columns. A Table usually carries a calculated-column formula into a new row when you insert the row within the Table. Click the formula cell in the new row and confirm that it contains the expected calculation. If the Table does not fill it, copy the formula from the same column in a neighboring data row, or type the intended formula in the new row. Table behavior can be affected by inconsistent formulas in the column or by Excel’s automatic calculation options.
If the data is a normal range, copy the formula into the new row. Select the corresponding formula cell in the row above or below, copy it, then select the new row’s cell in that same column and paste. For example, with quantities in B2:B10, unit prices in C2:C10, and line totals in D2:D10, the formula in D2 could be =B2*C2. If B2 contains 3 and C2 contains 12, the result is 36. Copying the formula down one row normally adjusts its relative references to the new row. Verify the result against the new row’s inputs.
Copy formulas across other calculated columns. Repeat the copy-and-paste action for each column that needs a formula. If several adjacent formula cells form one continuous block, you can copy the corresponding block from a neighboring row and paste it into the new row. Be careful not to copy over columns meant for manual entry, such as a date, name, or note. If a formula contains absolute references or a special range, compare it with nearby rows before using it as a pattern.
Check totals, formatting, and the final row. Confirm that the inserted row has the expected number formats, borders, and data validation, as well as formulas. Check any total at the bottom of the data. A SUM range may adjust when rows are inserted within it, but a total can miss new data if the row was added outside the range it includes. If the range is a Table, verify the Table still includes the new row and that its total row, if enabled, shows the right calculation.

In Excel for Mac, the same basic approach works: select the row heading and use the Home tab’s Insert command or the row heading’s context menu. Keyboard shortcuts can differ by keyboard and Excel version; use the menu if the Windows shortcut is not applicable. Excel for the web also offers row insertion from the row heading’s context menu, though some keyboard shortcuts may be intercepted by the browser. After inserting, use the same formula checks described above.

Common Mistakes

Expecting Excel to invent a missing formula. Inserting a worksheet row and adjusting existing references are not the same as filling every calculated cell. In a normal range, a new row can remain blank until you copy or enter its formulas.
Inserting only cells by mistake. Selecting a few cells and using Insert can shift only part of the sheet, which may misalign records. Select the row heading and use Insert Sheet Rows when the whole worksheet row needs to move.
Copying the wrong neighboring formula. Rows may use different logic, such as a discount, exception, or subtotal. Compare the formula pattern with other rows before copying. Do not assume the nearest formula is appropriate just because it is nearby.
Overwriting manual entries. Copying a whole row can replace names, dates, notes, or other values with duplicates. Copy only the formula cells or the exact block of cells that should repeat.
Breaking relative or absolute references. A copied formula usually changes relative references to match its new position. References marked with dollar signs remain fixed. This is often intentional, but check formulas that point to tax rates, assumptions, lookup cells, or other fixed inputs.
Forgetting totals and ranges. A total that looks correct may still omit the inserted record if the formula’s range does not include it. Check the formula bar or use a Table, which can make data ranges easier to extend consistently.

Tips

Use an Excel Table for growing lists. Select the data and choose Insert > Table, or use Home > Format as Table. Tables expand as you add rows and generally propagate calculated-column formulas. Confirm the Table’s headers and formula behavior after converting existing data.
Insert inside the data, not after a total row. If the new record belongs with the records above, add it within the data area. Inserting below a total or outside a defined range can leave calculations or formatting unchanged.
Use Undo immediately if the wrong thing shifts. Press Ctrl+Z on Windows, or Command+Z on Mac, before making more edits. Then select the full row heading and insert again. Undo is most reliable before other actions complicate the change.
Check whether calculation is automatic. If a formula is present but its displayed result does not update, go to Formulas > Calculation Options and check whether Automatic is selected. If the workbook uses Manual calculation, recalculate with F9 on Windows; the Mac shortcut can vary by keyboard and Excel version.
Preserve the pattern deliberately. When the new row needs the same formula and formatting, copying a neighboring formula cell is usually safer than retyping it. When the logic is intentionally different, enter the appropriate formula instead of forcing the surrounding pattern.

Troubleshooting

The new row is blank where a formula should be. Select the cell and check the formula bar. If there is no formula, copy the corresponding formula from a suitable neighboring row and paste it into the blank cell. If the range is a Table, check whether the new row is inside the Table and whether the calculated column has inconsistent formulas.

The formula appears, but the answer is wrong. Compare the new formula with the rows above and below. Check that it points to the new row’s inputs, that fixed references still point to the intended cells, and that the source cells contain numbers rather than text. If you copied from a special-case row, undo the paste and use a more representative row.

The total did not change. Click the total cell and inspect its formula. Check whether the inserted row is within the referenced range, and whether the total is a Table total or a regular formula outside the Table. Extend or correct the range if needed, then verify the result against the records included.

Formatting or data validation is missing. Formula copying does not always bring every desired feature with it. Copy the appropriate neighboring cell and use the paste options to preserve the relevant formatting or validation, or apply the format using Home > Format Painter. Check that you have not replaced a formula or typed value while doing so.

Excel inserted cells instead of a full row. Undo the change, select the numbered row heading at the left edge of the worksheet, and use Home > Insert > Insert Sheet Rows. This shifts the entire row structure together rather than moving only selected cells.

Frequently Asked Questions

Will Excel automatically copy formulas when I insert a row?
Often in an Excel Table, where calculated-column formulas generally fill into added rows. In a standard range, Excel may adjust existing formulas but leave the new row’s formula cells blank. Check the row rather than relying on automatic fill.

Will inserting a row change formulas that refer to other cells?
Excel updates many references to reflect the inserted row, but the result depends on where the row is inserted and how the formula is written. Inspect important formulas and totals afterward, especially formulas that refer to fixed ranges or cells outside the inserted area.

How do I insert a row without changing the formulas above it?
Insert the full row using the row heading. Excel may update references in formulas above or elsewhere when their referenced cells shift; that is usually necessary to preserve what those formulas point to. Review them if you need a particular fixed reference to remain unchanged.

Can I insert several rows and keep formulas?
Yes. Select the same number of row headings as the number of rows you need, then use Home > Insert > Insert Sheet Rows. In a Table, check that calculated columns fill all new rows. In a normal range, copy the needed formulas into each inserted row and verify the references.

Why does my inserted row not have the same formatting as the row above?
Excel may take formatting from an adjacent row or may apply a different style, particularly near Tables or totals. Use the insert options that appear after insertion to choose a formatting source, or copy the relevant formatting from a neighboring row without overwriting its data.

Does this work in Excel for Mac and Excel for the web?
Yes. Insert rows from the row heading’s menu or the Home tab. The exact shortcut can vary on Mac, and browser shortcuts can interfere in Excel for the web, so the menu is a dependable alternative. Formula propagation still depends mainly on whether the data is a Table or a normal range.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.