Excel Insert a New Row and Keep Formulas

To insert a row in Excel and keep formulas, select the row number where the new row should go, then choose Home > Insert > Insert Sheet Rows. Excel shifts existing cells down and adjusts many references, but it does not guarantee that every formula will be copied into the new row; use an Excel table or fill the formula down when you need a formula in that row.

What inserting a row actually does

Inserting a worksheet row is different from typing a blank line into a cell or pasting over an existing row. Excel creates space across the worksheet and moves the rows below it down. Formulas elsewhere in the workbook that refer to affected cells are often updated so they continue pointing to the intended data. That reference adjustment is not the same as copying a neighboring formula into every newly created cell.

For example, a formula in a total cell may continue to include a data range after a row is inserted inside that range. A formula that calculates a value separately on each record, such as quantity multiplied by price, may or may not appear automatically in the new record row. The result depends on whether the data is an Excel table, whether the surrounding cells contain formulas and consistent formatting, and exactly where the row is inserted.

So “keep formulas” can mean two different things: preserve formulas already in the sheet, or put a working formula into the new row. The first is generally handled by Excel’s insertion and reference-adjustment behavior. The second may require filling or copying a formula into the inserted row.

Insert a worksheet row with the ribbon

Click the row number on the left where you want the new row. Excel inserts the new row above the selected row.
On the ribbon, choose Home > Insert > Insert Sheet Rows.
Check the new row and nearby formulas. If a formula is missing from the new row, copy or fill the appropriate formula into it.

To insert more than one row, select the same number of existing row headers first. For example, selecting three row numbers and choosing Insert Sheet Rows creates three rows above the first selected row. This is often safer than inserting one row repeatedly, especially when adding a block of records.

You can also right-click a selected row number and choose Insert. If you select cells rather than a whole row, Excel may offer choices about shifting cells, so selecting the row header is the clearest way to insert an entire worksheet row. In Excel for Microsoft 365 on Windows, the keyboard sequence is Shift+Space to select the current row, followed by Ctrl+Shift++ to insert. On Mac, the corresponding insert shortcut is generally Control+Shift+=; keyboard layouts can affect how the plus key is entered. The ribbon or row-header context menu is a dependable alternative on either platform.

How Excel handles formulas and references

Excel distinguishes between a formula’s result and the formula itself. If an existing formula refers to cells that move when a row is inserted, Excel may revise its cell references to keep the calculation connected to the original data. Relative references can also change when a formula is copied to a different row. Absolute references, marked with dollar signs, are designed to remain fixed when copied, though a structural insertion can still affect the referenced location.

Assume values 12, 18, and 25 are in C2:C4, with a total in C5. The formula in C5 is =SUM(C2:C4), which returns 55. If you insert a row within the referenced data range, Excel will generally adjust the range to account for the inserted position. If you insert outside the range, the formula may continue to refer to the original range instead. Inspect the formula bar after inserting if the exact boundaries matter.

A formula that calculates each row is a separate case. Suppose a table has quantity in column B and unit price in column C. A formula for the line amount in column D might be written for row 2 as =B2*C2. If that formula is copied to row 3, its relative references ordinarily become B3 and C3. However, inserting a worksheet row does not always mean Excel will populate the new D cell with the formula you expect. Check it rather than assuming the formula was carried down.

Excel tables usually handle new formula rows best

If your data is a structured Excel table, Excel is usually better at maintaining a formula column. When a formula is entered into one cell of a table column, Excel commonly treats it as a calculated column and fills the formula through the other rows. Adding a row immediately below the table or entering data in the row directly beneath it can extend the table, with its formatting and calculated-column behavior.

To check whether a range is a table, click inside it and look for the Table Design tab on the ribbon. If the data is still an ordinary range and you want table features, select a cell in the data and choose Insert > Table, then confirm the range and whether it has headers. Before converting, check that the selected range is correct and that each column has an appropriate header.

Tables are useful for growing lists such as orders, inventory, or project tasks because formulas and formatting are easier to keep consistent as records are added. They are not essential for every worksheet, and they can change how references look: formulas may use column names such as a table column reference rather than ordinary cell addresses. If a table formula does not fill, check Excel’s calculated-column behavior and whether the new row is actually part of the table.

Copy a formula into the inserted row

For a normal worksheet range, insert the row first and then copy a formula from a neighboring row that uses the same calculation. Select the source formula cell and copy it, select the matching cell in the new row, and paste. Excel normally adjusts relative references to the destination row. You can also drag the fill handle—the small square at the lower-right corner of a selected cell—into the new row.

When you need to fill formulas across several columns, copy the whole formula section from an adjacent record row and paste it into the corresponding cells in the inserted row. Be careful not to copy over manually entered values or special cells, such as a note or a different subtotal formula. If the source row contains both formulas and data, select only the formula cells or use a deliberate paste choice rather than blindly copying the entire row.

After filling, click each important formula cell and inspect the formula bar. Confirm that the references use the new row’s inputs, not the previous record’s inputs. Also check any fixed references, such as a tax rate stored in one cell, because a formula that should keep pointing to a fixed rate may need an absolute reference when copied.

Choose the right insertion method

Use Insert Sheet Rows when you need a full worksheet row and want Excel to move existing content down.
Use a table’s next row when adding a record to a structured list and you want its calculated columns and table formatting to extend.
Copy or fill formulas when an ordinary range has no formula in the new row after insertion.
Insert cells instead of a full row only when you intentionally want to move part of the worksheet; shifting cells alone can misalign related data in neighboring columns.

It also matters where you insert. Adding a row in the middle of a list is more likely to be included in formulas that cover that list than adding a row beyond the range’s edge. If you are adding records at the bottom of a normal range, inspect totals and charts as well as the new row’s formula. A total that originally summed a fixed block may not automatically include a record placed just below that block.

Common problems and how to fix them

The new row is blank in the formula column. This can be normal for an ordinary range. Copy or fill the formula from the matching neighboring row, then verify its references. If this happens repeatedly, consider using a table for a growing list.

The formula appears, but returns the wrong result. Check whether the formula references the new row’s inputs. Relative references move when formulas are copied, while absolute references remain fixed during copying. Also confirm that the inserted row is inside the intended calculation range and that the inputs are numeric where the calculation requires numbers.

A total misses the new record. Look at the range shown in the formula bar. If the inserted record sits just outside a fixed range, update the formula or place the record inside the range. A table-based total or structured reference can be easier to maintain for a list that will keep growing.

Formatting or validation differs from nearby rows. Formula filling and formatting are related but separate. A copied formula may not bring the desired number format, borders, or data validation, and an inserted row’s formatting may be influenced by neighboring rows. Compare the new row with the intended template row and apply only the needed formatting or validation settings.

Excel changes a formula somewhere else. That may be reference adjustment rather than formula loss. Compare the formula before and after insertion, and check whether the new cell location is included. If the workbook contains complex formulas, named ranges, charts, or external references, review those too; row insertion can affect more than the visible cells beside the new row.

Safer habits for worksheets with important formulas

Before making structural changes to a complicated workbook, save a copy or use Save As to create a working version. This gives you a recovery point if the inserted row affects a range or layout you did not intend to change. Excel’s Undo command can reverse a recent insertion, but once you continue editing, saving, or performing other actions, relying on Undo alone is less convenient than having a separate copy.

After inserting, check three things: the new row’s formulas, totals or summary formulas that depend on the data, and any formatting or validation the row should inherit. If the workbook has a template row, use it as the source rather than copying from a row with a different purpose, such as a subtotal or heading. For recurring data entry, converting a clean range to a table can reduce the amount of manual formula maintenance.

Frequently Asked Questions

Does inserting a row automatically copy formulas?
Not in every worksheet. Excel may extend formulas in a table or infer a pattern from nearby cells, but a standard range can receive a blank cell. Check the new row and fill formulas when needed.

How do I insert a row without changing formulas?
Select the row number and choose Home > Insert > Insert Sheet Rows. Excel adjusts many affected references, but inspect formulas whose ranges or results are important because insertion position can affect what they include.

Why did my SUM formula not include the new row?
The new row may be outside the range used by the formula, particularly if it was added at the bottom of a fixed data block. Check the range in the formula bar and extend it if necessary, or use a table for a growing dataset.

Can I insert several rows and keep formulas?
Yes. Select the same number of row headers as the number of rows you need, then use Home > Insert > Insert Sheet Rows. Afterward, fill any row-by-row formulas that are missing and review totals.

Is inserting a row the same as adding a row to a table?
No. A worksheet-row insertion shifts the sheet’s rows, while adding a table row extends a structured data range. Tables commonly extend calculated-column formulas automatically, but you should still verify the new row.

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.