Excel tables (introduced in Excel 2007 as an evolution of the older List feature) automatically propagate formulas across an entire column when you enter a formula in a single cell. This feature, called calculated columns, is powerful but can be confusing when the automatic behavior is not what you want. This guide explains how calculated columns work, how to edit them, and how to control or disable the automatic formula fill.

What Are Calculated Columns

When data is formatted as a table (via Insert > Table or Ctrl+T), Excel treats each column as a structured entity. If you enter a formula in any cell within a table column, Excel automatically copies that formula to every other row in the column. This is a calculated column.

For example, if your table has columns for Price and Quantity, and you type =[@Price]*[@Quantity] in a cell in the Total column, Excel instantly fills the entire Total column with that formula.

Understanding Structured Références

Inside tables, Excel uses structured references instead of traditional cell references. Structured references use column names and special keywords:

Structured ReferenceMeaning
[@ColumnName]The current row’s value in that column
[ColumnName]The entire column (all data rows)
[[#Headers],[ColumnName]]The header cell of that column
[[#Totals],[ColumnName]]The totals row cell of that column
[[#All],[ColumnName]]The entire column including header and totals
[[#Data],[ColumnName]]All data rows (same as [ColumnName])

Example

=[@Price]*[@Quantity]

This multiplies the Price and Quantity values from the current row. When Excel fills this formula down the column, each row references its own Price and Quantity values.

Mixed Références

You can mix structured references with regular cell references:

=[@Price]*$B$1

This multiplies the current row’s Price by a fixed cell outside the table (such as a tax rate in cell B1).

How to Edit a Calculated Column Formula

Editing All Rows at Once

Because calculated columns are designed to be consistent, editing the formula in any single cell updates all rows:

  1. Click any cell in the calculated column.
  2. Edit the formula in the formula bar at the top of the screen.
  3. Press Enter.
  4. Excel updates every row in the column with the new formula.

Creating an Exception (Different Formula in One Row)

If you need a different formula in one specific row:

  1. Edit the formula in that cell to something different from the rest of the column.
  2. Excel will show the AutoCorrect Options button (a small icon near the cell).
  3. The column is no longer a fully consistent calculated column — Excel marks it as having an exception.

Note that exceptions make the table harder to maintain. If you frequently need different formulas per row, the data may not be well-suited for a table format.

Undoing Automatic Formula Fill

When Excel auto-fills a formula across the column and you did not want that:

Option 1: Immediate Undo

Press Ctrl+Z right after Excel fills the column. This reverts the auto-fill while keeping your formula in the original cell.

Option 2: AutoCorrect Options Button

After the auto-fill occurs, a small AutoCorrect Options icon appears near the cell. Click it and choose from:

  • Undo Calculated Column — removes the formula from all rows except the one where you typed it.
  • Stop Automatically Creating Calculated Columns — disables the feature entirely (same as changing the AutoCorrect setting described below).

Disabling Automatic Calculated Columns

If you want to permanently prevent Excel from auto-filling formulas in tables:

Excel 2007

  1. Click the Office Button (top-left corner).
  2. Click Excel Options.
  3. Click Proofing in the left pane.
  4. Click AutoCorrect Options.
  5. Click the AutoFormat As You Type tab.
  6. Uncheck Fill formulas in tables to create calculated columns.
  7. Click OK twice.

Excel 2010, 2013, 2016, 2019, 2021, and Microsoft 365

  1. Go to File > Options.
  2. Click Proofing in the left pane.
  3. Click AutoCorrect Options.
  4. Click the AutoFormat As You Type tab.
  5. Uncheck Fill formulas in tables to create calculated columns.
  6. Click OK twice.

After disabling this setting, formulas you enter in a table column will stay in the single cell where you typed them, just like in a regular worksheet range.

Working with the Totals Row

Tables have a built-in Totals Row that provides aggregate functions without writing formulas manually:

  1. Click anywhere in the table.
  2. On the Table Design tab (or Design tab in older versions), check Total Row.
  3. A new row appears at the bottom of the table.
  4. Click any cell in the Totals row. A dropdown arrow appears.
  5. Select an aggregate function: Sum, Average, Count, Min, Max, StdDev, Var, or More Functions.

The Totals row uses the SUBTOTAL function, which automatically excludes hidden (filtered) rows from the calculation.

Adding Custom Formulas to a Table

Adding a New Calculated Column

  1. Click the cell in the first data row of an empty column adjacent to the table (or the first row of a new column you add to the table).
  2. Type your formula using structured references.
  3. Press Enter.
  4. Excel fills the entire column and gives it a default header name (such as “Column1”).
  5. Click the header cell and type a descriptive name.

Using Regular Formulas in Tables

If you prefer traditional cell references (like =C2*D2) instead of structured references, you can type them. However, Excel will automatically convert them to structured references when the formula is inside a table. To prevent this conversion, add the formula as a regular cell reference outside the table or disable the structured reference behavior through the AutoCorrect settings.

Common Table Formula Scenarios

Calculating Row Totals

=[@Price]*[@Quantity]

Looking Up Values from Another Table

=VLOOKUP([@ProductID],Products[#All],2,FALSE)

Or using the modern XLOOKUP (Excel 365/2021+):

=XLOOKUP([@ProductID],Products[ProductID],Products[ProductName])

Conditional Calculations

=IF([@Status]="Shipped",[@Price]*[@Quantity],0)

Referencing the Current Row Number Within the Table

=ROW()-ROW(Table1[[#Headers],[Column1]])

This returns the relative row number within the table (1 for the first data row, 2 for the second, and so on).

Dépannage

Formula Does Not Auto-Fill

  • Check that the data is formatted as a table (look for the Table Design tab when clicking inside the data).
  • Verify that the Fill formulas in tables to create calculated columns option is enabled in AutoCorrect settings.
  • If the column already has mixed content (some cells with formulas, others with values), Excel may not auto-fill.

Structured Références Show Column Names Instead of Values

This is the expected display. Structured references like [@Price] are resolved to values when Excel calculates. If you see an error, ensure the column name in the reference matches the actual table header exactly.

Formula Returns Unexpected Results After Filtering

Table formulas always reference the full data set. However, the Totals row (SUBTOTAL function) automatically adjusts for filtered rows. If you need a formula that only considers visible rows, use SUBTOTAL or AGGREGATE functions.

Résumé

Excel tables automatically fill formulas across entire columns to maintain consistency, a feature called calculated columns. To edit a calculated column, change the formula in any cell and Excel updates all rows. To undo an unwanted auto-fill, press Ctrl+Z or use the AutoCorrect Options button. To disable the feature entirely, uncheck Fill formulas in tables to create calculated columns in File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. Use structured references like [@ColumnName] for readable, self-documenting formulas within tables.