Excel Table Formula Autofill: How It Works

Formulas & functions · Updated November 22, 2026

Excel table formula autofill is the automatic copying of a formula down an entire column when you enter it once in a table column. In a proper Excel Table (created with Ctrl+T or Insert > Table), typing a formula in one cell instantly fills the whole column, and new rows added later inherit the formula automatically. This behavior differs from a normal range, where you must drag the fill handle or double-click it to copy down.

What Makes a Table Different from a Range

A normal worksheet range (like A1:C10) is just a block of cells with no structure. An Excel Table is a defined object with its own name, filter buttons, and special column references called structured references. When you type a formula into any cell in a table column, Excel assumes you want that formula for the entire column and fills it down immediately. This is called the calculated column feature. It only works inside a real Table, not in a plain range. To convert a range to a Table, select any cell in the data and press Ctrl+T (Mac: Ctrl+T or Cmd+T), then confirm the range.

How to Enable or Disable Autofill in Tables

If the calculated column feature is not working, it may be turned off in Excel options. Go to File > Options > Proofing > AutoCorrect Options, then the AutoFormat As You Type tab. Check or uncheck Fill formulas in tables to create calculated columns. On a Mac, go to Excel > Preferences > AutoCorrect and find the same option. By default it is on. If you turn it off, you must drag the fill handle or copy-paste formulas manually, even in a Table.

How to Use Autofill in a Table (Step by Step)

  1. Select any cell in your data range and press Ctrl+T (Mac: Cmd+T). Confirm the range and check My table has headers.
  2. Click in the first empty cell of a column where you want a formula.
  3. Type the formula, for example = [@Price]*[@Quantity] (structured reference) or a normal reference like =D2*E2. Press Enter.
  4. Excel fills the entire column with the formula instantly. If you later add a new row by typing at the bottom of the table, the formula is already there.

For a plain range (not a Table), you can double-click the fill handle (the small square at the bottom-right of the selected cell) to autofill down to the last adjacent cell with data. That works only if there is a continuous column of data to the left or right.

Structured References vs. Normal Cell References

When you type a formula in a Table, Excel often converts normal references to structured references automatically. For example, if you type =D2*E2 in a Table, Excel may change it to =[@Price]*[@Quantity] if your headers are named Price and Quantity. Structured references are readable and stay correct when you rename columns. However, they can be confusing if you are new. You can turn off the automatic conversion in File > Options > Formulas (uncheck Use table names in formulas on the “Working with formulas” section). On Mac: Excel > Preferences > Formulas. If you keep normal references, autofill still works, but the formula will not update if you rename a column.

Common Problems and Fixes

  • Formula not filling down: Check that the cell is truly inside a Table (the Table Tools Design tab appears when you click a cell). Also check the AutoCorrect option mentioned above.
  • Autofill stops at a blank row: In a Table, blank rows break the column into separate segments. Fill the blank row with data or delete it, then re-enter the formula.
  • Formula overwrites existing values: If you type a formula into a column that already has data, Excel asks if you want to overwrite. If you see a warning, choose to replace the values.
  • Autofill shows the same value for all rows: That happens if you use an absolute reference like =$D$2*$E$2 intentionally. If you meant relative, remove the dollar signs.
  • New rows added at the bottom do not get the formula: This usually means the range is not a Table, or the Table has been converted to a range. Recreate the Table.

Autofill in Excel for the Web and Mac

Excel for the web (browser) supports calculated columns exactly like the desktop version. When you type a formula in a Table column, it fills down automatically. The option to disable it is under Options > Data (look for “Fill formulas in tables”). On Mac, the feature works the same, but the shortcut to create a Table is Ctrl+T (or Cmd+T). The fill handle double-click also works on Mac. If you use a laptop with a touchpad, double-clicking the fill handle may require a precise click; you can also drag it down manually.

Comparing Table Autofill with Fill Handle and Flash Fill

Table autofill is for formulas. The fill handle (drag or double-click) works for formulas and also for series like dates or numbers (e.g., 1, 2, 3). Flash Fill (Ctrl+E) is a separate feature that detects patterns in your data and fills values without formulas—for example, extracting first names from full names. Table autofill is the best choice when you need a formula that recalculates when source data changes. Fill handle is fine for one-off copying. Flash Fill is for text manipulation, not calculations. Do not confuse them: Flash Fill does not update if the source changes, while a Table formula does.

Frequently Asked Questions

Why does my Excel table not autofill formulas? The most common reason is that the calculated column feature is disabled in AutoCorrect options. Go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and check “Fill formulas in tables”. Also confirm your data is actually a Table—select a cell and see if the Table Tools tab appears.

How do I autofill a formula down a column without dragging? If you are in a Table, just type the formula once and press Enter—it fills the whole column. If you are in a normal range, select the cell with the formula, then double-click the fill handle. That copies down to the last row with data in the adjacent column. Alternatively, select the range and press Ctrl+D to fill down.

Can I stop Excel from filling formulas down in a table? Yes. Turn off the calculated column feature in AutoCorrect options as described above. Alternatively, after typing the formula, click the small smart tag that appears and choose “Stop Automatically Creating Calculated Columns”. That stops it for that column only.

What is the difference between a calculated column and a normal autofill? A calculated column is a Table feature that automatically fills the entire column and continues to fill new rows. Normal autofill (fill handle) copies a formula to a specific range you select. Calculated columns are dynamic; normal autofill is static until you extend it again.

Why does my formula show the same result in every row? If every row shows the same value, you probably used an absolute reference (e.g., =$A$1*$B$1) instead of relative references (e.g., =A1*B1). In a Table, structured references like =[@Column1]*[@Column2] are relative by default. Check the formula bar to see if dollar signs appear.

Does autofill work with structured references if I rename a column? Yes. Structured references update automatically when you rename a column header. For example, if you rename “Price” to “Cost”, the formula =[@Price]*[@Quantity] becomes =[@Cost]*[@Quantity]. Normal references (like D2) do not update on rename.

Related

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.