Excel Table Not Auto Filling Formula? Fix It

If an Excel table is not auto filling a formula, first check that the range is a real Excel table and that the calculated-column setting is enabled: in Windows, go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and select “Fill formulas in tables to create calculated columns.” Then enter the formula in a cell in that table column. If it still does not fill, check for inconsistent formulas, a row outside the table, or a formula type that cannot spill inside a table.

How formula filling works in an Excel table

An Excel table is more than a formatted range. When you enter a formula in one cell of a table column, Excel can treat that formula as a calculated column and copy its pattern through the other data rows. It also applies the formula to new rows added to that table, provided the relevant automatic behavior is enabled and the column remains a consistent calculated column.

For example, suppose a table named Orders has columns Quantity, Unit Price, and Line Total. With Quantity and Unit Price values of 3 and 15 in a data row, enter this in that row’s Line Total cell: =[@Quantity]*[@[Unit Price]]. The result is 45. The structured references identify values in the current table row, so the same formula works for each record without adjusting row numbers.

Auto-filling a formula is separate from calculating its result. Excel may copy the formula correctly while showing an old value if calculation is set to manual. Conversely, a formula may calculate correctly in one cell but not propagate through the table. Identify which behavior is failing before changing settings.

Confirm the range is an actual table

Colored banding alone does not prove that a range is a table; formatting can be applied to an ordinary range. Click a cell in the data. If the ribbon shows a Table Design tab (called Table on some Mac versions), Excel recognizes it as a table. You can also create one by selecting the data and choosing Insert > Table, then confirming whether the first row contains headers. On Windows, Ctrl+T is the shortcut; on Mac, use Command+T.

A formula entered just below the last row may not be part of the table. Look for the table’s bottom border and confirm that the new record is included. To add a row reliably, type in the row immediately below a table, use the Tab key from its last cell, or use the table’s insert-row commands. If Excel has not expanded the table, formula propagation cannot operate on that outside cell as a table calculated column.

When a table grows, structured references generally adjust to include its rows. This is one reason tables are useful for lists that change over time. A normal range with similar formatting does not automatically gain those table behaviors.

Turn on the calculated-column setting

In Excel for Microsoft 365 on Windows, open File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. Select “Fill formulas in tables to create calculated columns,” then choose OK through the open dialogs. If the box was already selected, toggling it off and back on can be a useful way to confirm the setting, but it should not be necessary in a normally functioning workbook.

Excel for Mac has different application preferences and dialog layouts. Look under Excel > Preferences or Excel > Settings for AutoCorrect and its AutoFormat As You Type options; the exact labels and location can vary by release. Excel for the web has a more limited set of desktop options, so if you cannot find this preference there, open the workbook in the desktop app and check it in the Windows or Mac settings.

This setting controls whether Excel automatically creates calculated columns; it does not repair a malformed formula or convert an ordinary range into a table. Also check the nearby option for including new rows and columns in tables if Excel is not expanding the table when you add data. That behavior concerns table expansion, while the formula-fill checkbox concerns calculated columns.

Check for inconsistent formulas in the column

A calculated column is intended to use one formula pattern throughout its data rows. If one cell has been manually changed to a different formula or replaced with a value, Excel may recognize an exception and stop treating every cell as part of one consistent calculated column. A warning icon may appear near the edited cell, offering an option to restore the calculated-column formula. The exact prompt depends on the workbook and Excel version.

Compare the formula in the first data row with the formulas below it. Click a few cells and inspect the formula bar; if some cells contain a different expression or a fixed number, decide whether that difference is intentional. Restoring one consistent formula is usually the simplest repair when every row should follow the same rule.

For the Orders example, with Quantity and Unit Price as table columns and the result in Line Total, the formula =[@Quantity]*[@[Unit Price]] should be the same pattern in each data row. If one row has a special adjustment, consider adding a separate Adjustment column and including it in the calculation rather than replacing just one row’s formula. That keeps the logic visible and makes future table rows predictable.

Repair formulas that are already missing

Enabling automatic filling may affect future entries but does not always restore formulas that were previously deleted. For a missing formula in an existing row, put the correct formula back in a cell in the calculated column and see whether Excel offers to fill the column. If not, copy a correct formula cell and paste it into the missing cells, or select the cells to fill and use Home > Fill > Down.

For example, if a table has Quantity and Unit Price columns and the Line Total formula is missing, enter =[@Quantity]*[@[Unit Price]] in the appropriate Line Total data cell. With a row containing 4 and 12.50, the result is 50.00. Check that the formula references the current row and the intended columns before filling many records.

When using Fill Down, select only the intended data cells in the table column and ensure the top selected cell contains the correct formula. On Windows, Ctrl+D fills downward in a selected range. If you fill a whole table column, avoid including its header or total row unless you intend to change those cells. Save the workbook before making a large repair so you can recover if the selection was wrong.

Make sure new rows are inside the table

Typing below a table may expand it automatically, but Excel can fail to do so if there is a blank row, another block of data, or a setting that prevents automatic table expansion. Click the last table cell and press Tab to add a new row at the bottom; this is a reliable way to create a row within the table. Then enter values and check whether the calculated-column formula appears.

If a row already exists outside the table, resize the table rather than assuming it is included. Click within the table, open Table Design, and choose Resize Table; select the full range that should belong to it. Verify that the header row and all intended records are inside the selected range. Menu wording or ribbon placement can vary slightly by Excel release, but the table design controls appear only when a table cell is active.

Do not confuse a table’s Total Row with a normal data row. The Total Row can contain summary calculations and is designed for column totals or other summaries, not for copying a record-level formula. Add a data row above it or use Tab from the last data cell if you need another record.

Distinguish formula filling from calculation problems

If every row contains the formula but the displayed results do not update, check calculation mode. In Windows Excel, choose Formulas > Calculation Options > Automatic. In manual calculation mode, formulas can remain unchanged until Excel recalculates. Press F9 on Windows to recalculate; Mac keyboard behavior can depend on the keyboard’s function-key settings, so use Formulas > Calculate Now if needed.

Look for errors such as #VALUE! or #REF! as well as blank results. Those indicate that the formula may have filled but its inputs or references need attention. A structured reference can also be incorrect if a header was renamed or contains a spelling difference. Click the formula cell and verify that its referenced column names match the table headers.

There is also an important limitation: dynamic array formulas that need to spill results into neighboring cells cannot spill inside an Excel table’s data area. A formula using a function such as FILTER may return #SPILL! there. Put a spill formula in a regular range outside the table, or use a suitable row-by-row formula in a table column. This is different from ordinary calculated-column filling, which copies one row-level formula down.

Common causes and a quick diagnostic order

Work through these checks in order; they separate the most common causes without changing unrelated workbook settings:

Click a data cell and confirm the Table Design tab appears.
Check that the formula cell is inside the table’s data area, not below the table or in its Total Row.
Confirm “Fill formulas in tables to create calculated columns” is enabled in AutoFormat As You Type.
Inspect several cells in the formula column for manually changed formulas or values.
Enter a known-good row formula and see whether Excel fills the column or presents a calculated-column prompt.
If formulas exist but results look stale, set calculation to Automatic and recalculate.
If a formula returns #SPILL!, move a dynamic-array formula outside the table.

Protected worksheets can also prevent editing or inserting rows. If Excel refuses to change the formula or extend the table, check Review > Unprotect Sheet, if you have permission to do so. Do not remove protection on a workbook you do not own or understand; protection may be part of its intended workflow.

Frequently Asked Questions

Why does Excel fill a formula in some table columns but not others?
Each column can have its own formula history. A column with inconsistent formulas, manually entered values, or an exception may no longer be treated as one calculated column. Check its rows for differences and restore a consistent formula if that is the intended design.

Will turning on the setting fill formulas that are already missing?
Not necessarily. It primarily controls automatic creation of calculated columns when formulas are entered. Restore the formula in the table column, copy a correct formula into missing cells, or select the relevant cells and use Home > Fill > Down.

Why does a formula work in one row but not fill to new rows?
Check that the row is actually part of the table and that the calculated-column setting is on. Also look for an inconsistent formula elsewhere in the column. Adding the row with Tab from the last table cell can help ensure it is inside the table.

Does Excel need to be in Automatic calculation mode for formulas to fill?
No. Calculation mode controls when results update, not whether a formula is copied down. A formula can fill while calculation is manual, but its displayed result may remain stale until recalculation.

Can a FILTER formula auto fill inside a table?
A dynamic-array formula that needs to spill cannot spill within a table’s data area. Place it in a regular worksheet range outside the table, or use a row-by-row formula that returns one result per table row.

Is a formatted range the same as an Excel table?
No. Applying colors or borders does not create table behavior. Use Insert > Table or Ctrl+T on Windows (Command+T on Mac), then verify that the Table Design tab appears when you click inside the range.

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.