Excel Table Not Copying Formulas to New Rows? Fix It

Errors & fixes · Updated November 19, 2026

If an Excel table is not copying formulas to new rows, first check that calculated-column formula filling is enabled: in Excel for Microsoft 365 on Windows, go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and select Fill formulas in tables to create calculated columns. Then confirm you are adding the row inside the table; a row outside the table will not inherit its formulas as a table row would.

Why formulas normally fill down in an Excel table

Excel tables do more than format a rectangular range. When a column contains a consistent formula, Excel treats it as a calculated column and normally applies that formula to other cells in the column, including new table rows. It adjusts relative references for each row, so a formula that uses the current row’s values continues to work as the table grows.

For example, suppose a table named Orders has columns Quantity, Unit Price, and Line Total. With those column names and a new row in Orders, enter this in the Line Total column:

With an Orders table containing Quantity, Unit Price, and Line Total columns, enter =[@Quantity]*[@[Unit Price]] in the Line Total cell. If that row has Quantity 3 and Unit Price 12.50, the result is 37.50.

The references in square brackets are structured references. The @ means the formula uses values from this row, rather than a fixed worksheet cell address. If formula filling is enabled and the table column is behaving consistently, Excel can use the same calculated-column formula for rows added later. This is why a missing formula is often a table setting or table-boundary issue, rather than a problem with the multiplication itself.

Turn on calculated-column formula filling

On Windows, the setting is in Excel’s AutoCorrect options, even though its name sounds like a table-specific preference:

  1. Choose File > Options, then select Proofing.
  2. Choose AutoCorrect Options and open the AutoFormat As You Type tab.
  3. Select Fill formulas in tables to create calculated columns, then choose OK in each dialog.

This setting controls automatic formula propagation in tables. If it was turned off, Excel may leave a newly added cell blank even though the cells above it contain the expected formula. After enabling it, add a test row to the table and see whether the formula appears. Enabling the setting does not necessarily repair every blank cell already present, so you may still need to fill existing gaps separately.

In Excel for Mac, look in Excel > Preferences or Excel > Settings for AutoCorrect and its AutoFormat As You Type options; labels and dialog organization can vary by version. Excel for the web has fewer desktop-style preference dialogs, so if the setting is unavailable there, open the workbook in the desktop app to check it. The Windows path above is the clearest route for Microsoft 365 on Windows.

Make sure the new row is actually part of the table

A formula will not propagate as a table formula if the row you are editing is outside the table. This can happen when there is a completely blank row between the data and the new entry, when the table ends above the row you are typing in, or when you paste data somewhere that does not extend the table.

  • Click a cell in the new row. If the Table Design tab appears on the ribbon, Excel recognizes that cell as part of a table.
  • Use the table’s last cell and press Tab to create a new row at the bottom. Tab in the final table cell is a straightforward way to extend the table.
  • Alternatively, type directly in the first row immediately below the table. Excel commonly expands the table to include adjacent data, provided there is no separating blank row or conflicting content.
  • To inspect or resize the range, click in the table and choose Table Design > Resize Table. Check that the range includes the row where you expect the formula.

Be careful when inserting a whole worksheet row near a table: the new row’s position and the surrounding data determine whether it becomes part of the table. A formatted row can look like a table row without belonging to the actual table. The Table Design tab is a more reliable check than appearance alone.

Repair a formula that is missing from an existing row

If a row is already inside the table but its formula cell is blank, fill the formula into that cell. First confirm that the formula in a neighboring row is correct; then select the formula cell above and the blank cell below and use Home > Fill > Down. On Windows, Ctrl+D is the Fill Down shortcut. On a Mac, use the ribbon command if the shortcut differs with your keyboard settings.

For a small table, you can also copy the formula from the row above and paste it into the blank table cell. Excel normally adjusts ordinary relative references when a formula is copied down. For example, with Quantity in C2, Unit Price in D2, and Line Total in E2, enter =C2*D2 in E2; if C2 is 3 and D2 is 12.50, the result is 37.50. Copying it to E3 changes the references to C3 and D3.

For a large set of missing formulas, select the affected cells and use Fill Down rather than dragging a tiny fill handle across many rows. Before filling, check whether any cells in the selected range contain values you need to keep: filling down replaces the destination contents. If the table is protected, or the worksheet is protected in a way that blocks editing, you may need permission or to unprotect the sheet before repairing the formula.

Check for inconsistent formulas or manual overrides

Calculated columns work best when the formula column is consistent. If some cells contain a different formula, a typed value, or a blank, Excel may ask whether to overwrite the column formula or may not produce the result you expect. A value typed directly into one formula cell can be a deliberate override, but it also makes the column less uniform.

Compare several neighboring formula cells, not just the first and last. Look for a missing operator, a reference to the wrong column, or a formula that was replaced by a fixed number. If the table is supposed to calculate the same relationship for every row, restore the intended formula consistently. If some rows genuinely need exceptions, document that choice and do not assume those cells will behave like the rest of the calculated column.

Also distinguish between a blank result and a missing formula. A formula may return an empty string or a blank-looking value because its inputs are empty or because its logic says to show nothing. Click the cell and inspect the formula bar. If a formula is present, formula propagation may be working; the issue may instead be the formula’s conditions or the row’s input data.

Other causes: paste behavior, protection, and calculation

When pasting rows from another workbook or worksheet, the pasted material can overwrite formulas with values, blanks, or formulas from a different layout. Check the paste source and the resulting formula bar. If you want new rows to use the table’s calculated-column formula, paste only the input columns when possible, rather than pasting over the formula column as well.

Worksheet protection can prevent Excel from filling a formula into locked cells. If other editing is blocked too, check Review > Unprotect Sheet, if you have permission to do so. In a shared or managed workbook, ask its owner before changing protection. Protection is different from the calculated-column setting: turning on formula filling will not bypass a restriction on editing the destination cell.

Calculation mode is another, separate issue. If a formula is present but its displayed result has not updated, check Formulas > Calculation Options and choose Automatic if appropriate. A calculation setting affects when Excel recalculates formulas; it does not determine whether a formula is inserted into a new row. Keeping those two symptoms distinct prevents changing calculation settings when the actual problem is a blank cell.

A practical troubleshooting order

Use this sequence to isolate the cause without changing unrelated workbook settings:

  1. Click the supposedly new row and confirm it is inside the table by checking for the Table Design tab.
  2. Inspect the same column above it. Confirm that those cells contain formulas rather than values that only look calculated.
  3. Check Fill formulas in tables to create calculated columns in the AutoFormat As You Type options on Windows.
  4. Try adding a test row with Tab from the table’s final cell. This distinguishes a table-boundary problem from formula filling behavior.
  5. If the cell remains blank, check for protection, pasted-over content, or inconsistent formulas in that column.
  6. Fill existing blanks using Home > Fill > Down after checking that the source formula is correct and destination contents can be replaced.

This order also helps explain the issue to someone else: you can report whether the new row is part of the table, whether the setting is enabled, and whether the formula column is consistent. Those details are more useful than saying only that the formula did not copy.

Frequently Asked Questions

Why does my Excel table stop copying formulas after one row?
Check whether that row is part of the table and whether the formula-filling option is enabled. Also inspect the column for a manual value or a different formula that interrupts the calculated-column pattern.

How do I add a table row and keep its formulas?
Click the last cell of the table and press Tab, or enter data directly in the row immediately below the table so Excel can expand it. Confirm the row belongs to the table and that formula filling is enabled.

Why is the formula missing but the cell looks formatted?
Formatting does not prove that a cell belongs to a table. Click it and look for the Table Design tab, then inspect the formula bar. The row may be outside the table, or the cell may contain formatting but no formula.

Will enabling formula filling restore formulas in old blank cells?
Not necessarily. The setting controls automatic filling as table formulas are created or rows are added. Repair existing gaps by copying a correct formula or using Home > Fill > Down after checking the cells that will be overwritten.

Why does the formula copy but show the wrong answer?
Check the formula references, the values in that row, and whether the formula is intended to use relative or structured references. If the formula is present but the displayed result is stale, check calculation mode separately.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.