If Excel formulas are not working in a table, first check that the cell is not formatted as Text, that calculation is set to Automatic, and that the formula uses valid table column names. Then check whether Excel is meant to fill the formula down a calculated column; a setting or a manually changed cell can interrupt that behavior.
How formulas behave in an Excel table
An Excel table is more than a range with colored headings. It has named columns, expands when you add rows, and can treat a formula entered in one column as a calculated column formula. When that behavior is working, typing a formula into one row usually fills the same formula through the other rows in that table column. New rows can inherit it too.
Tables also support structured references: formulas can refer to a column by its header instead of relying only on cell addresses. For example, assume an Excel table named Sales has columns Quantity and Unit Price, and the row being calculated contains 3 and 20. Enter this in the table’s Amount column:
=[@Quantity]*[@[Unit Price]]
The result for that row is 60. The at-sign means the formula uses values from the current table row. The brackets around Unit Price are required because the column heading contains a space. If a header changes, Excel generally updates references to that table column, which can make structured references easier to maintain than fixed cell addresses.
Start with a quick diagnostic
Look at the cell and the formula bar before changing the workbook. The symptom often points to the cause: a formula displayed literally may be text; a formula that shows an old result may not have recalculated; and a correct result in one row but not the next may indicate a calculated-column issue.
- Formula characters appear in the cell: Check for Text formatting, an apostrophe before the equals sign, or Show Formulas mode.
- The cell shows a result that seems outdated: Check calculation mode and whether the formula refers to the intended cells or table columns.
- Only one row has the formula: Check whether formula filling is enabled and whether the table column contains inconsistent manual entries.
- You see an error such as #NAME? or #SPILL!: Check spelling, supported functions, and whether the formula needs to return an array outside the table.
Before editing a large table, try a correction in one row and confirm that the result and any automatic fill behave as intended. If the workbook is important, save a separate copy before making broad changes.
When Excel treats a formula as text
A formula can be stored as text rather than evaluated. This commonly happens when a cell or entire column was formatted as Text before the formula was entered, or when the entry begins with an apostrophe. In those cases, Excel may display the equals sign and formula characters in the cell instead of returning a result.
Select the affected cell and choose Home > Number Format > General. Then edit the cell and press Enter so Excel re-reads the entry; changing the format alone may not convert a formula that has already been stored as text. On Windows, you can press F2 and then Enter to re-enter the selected cell. If a leading apostrophe is present, remove it. Also check whether there is a space before the equals sign.
Another possibility is that Show Formulas is on. That view displays formulas across the worksheet instead of their calculated results, so it can look as if formulas stopped working even though the cells still contain formulas. On Windows, use Ctrl+` to toggle the view; the key is usually the grave accent beneath Esc. You can also look for the Show Formulas command on the Formulas tab. If every formula on the sheet is visible, this setting is more likely than a problem confined to one table column.
Check calculation settings and stale results
Excel can be set to calculate automatically or manually. In manual calculation mode, a formula may remain visible with an old result until calculation is triggered. This setting is especially easy to overlook in a workbook received from someone else. For Excel for Microsoft 365 on Windows, check Formulas > Calculation Options > Automatic. Excel for Mac also provides calculation options on the Formulas tab, although ribbon layout can vary by release.
With item amounts 60, 90, and 120 in a table named Sales, with the values in its Amount column, this formula totals the column:
=SUM(Sales[Amount])
The result is 270. If the visible total remains different after you change an amount, confirm that the formula refers to the right table and column, then recalculate. On Windows, F9 recalculates; Ctrl+Alt+F9 forces a full calculation. On Mac, F9 may require the Fn key depending on the keyboard and system settings. Recalculation cannot fix an incorrect reference or text-formatted formula, so diagnose those separately.
Calculation mode is a workbook-level consideration, and changing it can affect more than the table you are inspecting. If a workbook contains many formulas, automatic recalculation may take time, but switching to manual mode without a clear reason can leave results out of date. Before sharing a workbook, check that its displayed results have been recalculated.
Fix a formula that does not fill down
If the formula works in one row but does not appear in the rest of the table column, Excel may not be creating or maintaining a calculated column. In Windows Excel, check File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and look for Fill formulas in tables to create calculated columns. If the option is cleared, formulas may not propagate automatically. Excel for Mac has related AutoCorrect and AutoFormat As You Type settings under Excel > Settings or Preferences, depending on version.
A calculated column is expected to use a consistent formula down the column. If someone typed a different value or formula into one row, Excel may preserve that exception rather than overwrite it. Review the column for blanks, constants, or formulas that differ. If you want the same formula throughout, select the intended data cells in that table column, enter the corrected formula, and confirm the fill prompt if Excel offers one. Be careful: replacing the whole column can remove deliberate exceptions.
It is also possible that the new row is outside the table. Click a cell and check whether the Table Design tab appears; if it does not, that cell may be an ordinary worksheet cell. Add rows directly beneath the table or use the table’s last row and press Tab to create a new table row. You can also resize a table from Table Design > Resize Table. A formula outside the table will not necessarily inherit its calculated-column behavior.
Structured references and common formula errors
Structured references rely on the table name and exact column headings. A misspelled table name, a renamed heading, or punctuation in a heading can cause an error or an unintended reference. For a formula intended to use the current row, check whether it needs the row marker form, such as [@Quantity], rather than a whole-column reference such as Sales[Quantity]. Excel may insert structured-reference syntax as you select table cells while building a formula, which can help avoid typing errors.
Errors also have useful clues. #NAME? can mean Excel does not recognize a function name or a text name in the formula. #REF! indicates a reference is invalid, often because referenced cells or columns were removed. #VALUE! often points to an unsuitable value or data type in an operation. If formulas are copied from a source with different regional settings, the argument separator may differ: some installations use semicolons rather than commas. Excel’s formula bar and local settings determine which separator to use; do not replace separators blindly in a workbook.
Some functions are not available in every Excel release. XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, and LET require Excel 2021 or Microsoft 365; LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. An unsupported function can produce #NAME? in an older version. Check the version of Excel used to open the file before assuming the table itself is broken.
Dynamic arrays and table limitations
Dynamic-array formulas can return multiple results that spill into neighboring cells. Excel does not support spilled array formulas inside table cells, so a formula that works in a normal worksheet range may return #SPILL! when entered in a table. Put the formula in an ordinary cell outside the table and leave enough empty space for the results. If you need the output to become table data, you may need to copy the results and paste values into a separate table, or use a different formula design.
For example, assume a table named Sales has a Region column containing West, East, and West. In a blank cell outside the table, Microsoft 365 or Excel 2021 can use:
=SORT(UNIQUE(Sales[Region]))
The results spill as East and West, in sorted order. Entering that same spill formula inside a table column is not supported. This is a limitation of where the array result can spill, not proof that the source table or its Region values are damaged.
Table formulas versus ordinary cell references
Structured references are often clearer when a calculation belongs to a table, especially if the table grows or columns move. A formula that names Sales[Amount] describes the data by its role, while a reference such as D2:D4 describes its current location. Excel adjusts many table references as the table changes, but it is still important to verify formulas after reorganizing a workbook.
Ordinary A1-style references remain useful for calculations that combine cells outside a table or depend on a particular worksheet layout. The choice is not a matter of one reference style always being correct. Use structured references when they make the relationship to table columns clearer; use cell references when the calculation is naturally tied to specific cells. Either style can fail if the referenced data is missing, stored as text, or different from what the formula assumes.
Frequently Asked Questions
Why does Excel show the formula instead of the answer in a table?
Check whether the cell is formatted as Text, the entry begins with an apostrophe, or Show Formulas is enabled. Change a text-formatted cell to General and re-enter the formula; if formulas appear throughout the sheet, turn off Show Formulas.
Why does a formula not copy to every row in my table?
Check the calculated-column AutoFormat setting, confirm that the new row is part of the table, and look for manually edited exceptions in the formula column. Excel may leave a different value in a row rather than replace it automatically.
Why is my table formula not updating when I change a value?
Check Formulas > Calculation Options and select Automatic if the workbook is in manual mode. Also verify that the formula points to the value you changed; recalculation will not correct a wrong reference.
Can I use a dynamic-array formula inside an Excel table?
No. A formula that needs to spill multiple results cannot spill from within a table. Enter it in a suitable blank worksheet range outside the table, where its output cells are clear.
Do structured references work in older Excel versions?
Structured references are a feature of Excel tables, but formulas using newer worksheet functions still depend on the Excel version that opens the workbook. If a formula returns #NAME?, verify that the function is supported in that version and that the table and column names are spelled correctly.
Related
- Excel Find the Minimum Value in a Range With Criteria
- Excel Find the Minimum Date With Criteria
- Excel Find Maximum Value Based on Criteria with MAXIFS
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Solver Too Many Variable Cells? Fix the 200-Cell LimitExcel’s built-in Solver add-in allows up to 200 changing variable cells in a model. If your model needs…
- Excel Yes/No Formula Not Working? Common Causes and FixesIf an Excel yes/no formula is not working, check that the formula points to the right cell, uses…
- Excel Error 0x426-0x0: What It Means and How to Fix ItExcel error 0x426-0x0 is usually an Office desktop startup problem, often involving the Microsoft Office Click-to-Run service that…
- Excel FILTER #N/A Error: Causes and FixesWith records in A2:C20 and region names in C2:C20, use =FILTER(A2:C20,C2:C20="West","No matches") to return West-region rows and display…
- Excel File Won’t Print? Fix Page Setup and Printer IssuesIf an Excel file won’t print, first choose the correct printer in File > Print, then check the…
- Excel File Format Is Not Valid: Causes and How to Fix ItIf Excel says a file format or file extension is not valid, first check that the file finished…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.