Yes. In Microsoft Excel for Microsoft 365 on Windows, a formula entered in a table column normally fills down the whole column and is copied into new table rows automatically. Excel treats that column as a calculated column; if it does not behave this way, check the table-formula setting and whether your data is actually formatted as an Excel table.
What automatic formula fill means
An Excel table is a structured data range with header names and built-in behaviors for filtering, sorting, formatting, and extending the data. When you enter a formula in one of its data cells, Excel can apply the same formula pattern to every data row in that column. It also carries the formula into rows added to the table later. This is different from copying a formula down a regular worksheet range, where you usually drag the fill handle or copy and paste.
Excel calls the formula-filled table column a calculated column. The formula is generally consistent throughout the column, while its row references adjust for each record. For instance, a calculation using the current row’s quantity and price evaluates separately for each item. If the table has 30 records, Excel can populate all 30 rows after you enter the formula once.
Automatic fill is not the same as Excel guessing any calculation you might want. You must enter a formula in a table data cell, and the automatic behavior depends on the calculated-column option being enabled. It also does not mean every cell in the table receives a formula: headers, unrelated columns, and the optional Total Row have distinct roles.
How to set up a table and enter a formula
Start with a rectangular range that has one header row and no completely blank header names. Click inside the data, then choose Insert > Table. In the dialog, confirm the selected range and check My table has headers if the first row contains column names. You can also use Ctrl+T on Windows. Once created, the table normally shows filter arrows in the header row.
Give the columns descriptive names, such as Quantity and Unit Price. Click a data cell in the column where you want the calculation, type the formula, and press Enter. If Excel offers to overwrite the other cells in that calculated column with the formula, accept if that is what you intend. The table should fill the existing data rows, and the same calculated column should be ready for future rows.
To add a record, type immediately below the last table row or press Tab while the selection is in the last cell of the table’s last row. Excel usually expands the table and carries calculated-column formulas into the new row. If you paste a large block of data, check that the table expanded as expected and that formulas appear in the added records; pasting or importing can behave differently from entering a single row.
Example: a calculated column
Suppose a table named Orders has columns Quantity, Unit Price, and Line Total. In a data row where Quantity is 4 and Unit Price is 12.50, enter a formula in that row’s Line Total cell. With those headers and values in the current table row, the formula is =[@Quantity]*[@[Unit Price]]. The result for that example row is 50.00. Excel can apply the same structured-reference formula to the other Line Total rows, using each row’s own quantity and price.
The notation in this example is a structured reference. The at sign means “this row,” and the bracketed names refer to table columns. That makes the formula easier to read than a cell-address expression, and it can remain meaningful if the table moves or grows. Excel may display or convert formulas differently depending on how the formula was entered and the workbook’s settings, but the calculation is still based on the current row.
Tables can also use ordinary cell references. With the same table data, if the current row’s Quantity is in B2 and Unit Price is in C2, the corresponding formula is =B2*C2. Its result is also 50 when B2 contains 4 and C2 contains 12.50. When entered as a calculated-column formula, Excel generally adjusts the relative references for each row. Structured references are often clearer when a workbook is shared or columns may be rearranged.
Check the setting that controls calculated columns
On Excel for Microsoft 365 for Windows, the relevant option is named Fill formulas in tables to create calculated columns. To find it, open File > Options > Proofing > AutoCorrect Options, select the AutoFormat As You Type tab, and check Fill formulas in tables to create calculated columns. If the box is clear, select it and choose OK in the dialogs. The change affects Excel’s table formula behavior, not just the workbook currently open.
Excel for Mac has AutoCorrect and AutoFormat As You Type preferences too, but the route and wording can vary by release. Look in Excel > Settings (or Preferences) > AutoCorrect for the AutoFormat As You Type options. Excel for the web does not provide the same Windows File > Options dialog; if you cannot change the calculated-column preference there, open the workbook in the desktop app. Existing table formulas can still be inspected and edited in the web version.
If the option is already checked, the next question is whether the selected cells belong to a table. A range that merely has colored rows or filter arrows may not be a formal table. Click a cell and look for a Table Design tab in the ribbon. If it appears, Excel recognizes a table; if not, create one with Insert > Table or check whether the table was converted back to a normal range.
When automatic fill may not happen
Excel may not fill the full column if the calculated-column option is off, if the formula was entered outside the table, or if the data is only a formatted range rather than a table. A column with a mixture of manually entered values and formulas can also be inconsistent. Excel may ask whether to replace the entire column with the new formula. Read that prompt carefully: accepting it replaces other cells in the table column, which could erase intentional exceptions.
Another common source of confusion is typing a formula into the header or Total Row. The header is a label, not a regular data row, and a Total Row uses its own summary behavior. To fill a calculation down records, enter it in a normal data cell. If a table has no data rows yet, add a record first and enter the formula in its calculated column.
Different formulas in different rows can be intentional, but they prevent the column from behaving like one consistent calculated column. For example, a one-off override may be overwritten if you later restore a uniform formula. Consider putting the exception in a separate adjustment column or using a formula that accounts for the exception explicitly. Keep a backup or review the affected cells before accepting any prompt to replace column formulas.
How to repair a formula column
If some rows are blank or use a different formula, first decide what the correct rule should be for every record. Then enter that formula in a data cell in the table column. If Excel asks whether to fill the entire calculated column, choose the option to apply it to all rows only when replacing the existing entries is intended. Otherwise, copy the formula into just the missing cells and preserve deliberate exceptions.
Check a few rows after the repair, including the first and last data rows. Verify that each formula refers to the values on its own row and that results make sense. Also add a temporary test row at the bottom if appropriate, confirm the table expands, and remove the test row afterward. This checks future-row behavior rather than only fixing current records.
For a table that refuses to expand, make sure there is not a completely blank row or column separating the data from where you are typing. You can also add a row from the table’s last cell with Tab or resize the table from Table Design > Resize Table. Avoid assuming that a neighboring cell is part of the table just because its formatting looks similar.
Tables compared with ordinary ranges
In a normal range, formulas do not automatically become a calculated column because no table structure exists. You can fill a formula down with the fill handle, copy and paste, or select the destination range and use Ctrl+D on Windows to fill down from the top cell. On Mac, the fill-down shortcut is Control+D in Excel; shortcut behavior can depend on the keyboard and system settings. These methods copy formulas, but they do not create table-specific behavior for rows added later.
Tables are useful when records are added regularly, formulas should remain aligned with named columns, or filters and structured references make the data easier to manage. A normal range may be preferable for a specially arranged report, a layout with merged cells, or a worksheet where table features would get in the way. Converting a table to a range removes table behavior, so do not do that merely to fix one formula without considering the impact on filters and structured references.
Automatic calculated columns are also different from dynamic array formulas, which can return multiple results that spill into neighboring cells. A table’s calculated column is designed to apply a row-level formula across records. If a formula returns an array, it may not work as a conventional calculated-column formula. Choose a table formula for per-record calculations and a dynamic array formula for a spill result, when the required function is supported by your Excel version.
Frequently Asked Questions
Do Excel table formulas fill down automatically?
Usually, yes. Enter a formula in a data cell in a table column, and Excel normally fills it through the calculated column and into new table rows. The Fill formulas in tables to create calculated columns option must be enabled for that automatic behavior.
How do I make a formula apply to an entire Excel table column?
Enter the formula in a data cell in that table column. If Excel asks whether to apply it to all cells in the column, choose that option only if you want to replace the other entries. Check the top, middle, and bottom rows to confirm the formula was applied correctly.
Why is Excel not copying my formula down in a table?
Check that the cells are in a real Excel table, that you entered the formula in a data row, and that the calculated-column setting is enabled. Also look for inconsistent formulas or manually entered values in the column, which can disrupt uniform fill behavior.
Will a table copy formulas into new rows?
Normally, yes: when you add a row to the table, Excel carries its calculated-column formula into the new record. If you paste data or add a row outside the table boundary, confirm that the table expanded and the formula appeared; you may need to add the row through the table or resize it.
Can I stop Excel from filling formulas down automatically?
On Windows, clear Fill formulas in tables to create calculated columns under File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. On Mac, look for the corresponding option in Excel’s AutoCorrect settings. You can still enter or copy formulas manually when needed.
Related
- Excel Custom Number Format for Millions
- Excel Custom Number Format: Show Numbers in Thousands
- Excel Shortcut to Select the Rest of a Column Down
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Variance Between Two CellsTo calculate the difference between two cells in Excel, subtract one from the other: with the earlier or…
- COUNTIF in Excel: Count Cells That Contain NumbersYes—COUNTIF can count cells that meet a numeric condition, and it can find text containing a particular digit.…
- Concat Excel Multiple Cells: Formulas, Spaces, and TipsTo concat (combine) multiple cells in Excel, use =CONCAT(A2:C2) to join the values in A2 through C2 without…
- VLOOKUP in Excel: Purpose, Examples, and Limits ExplainedVLOOKUP in Excel looks for a value in the first column of a range and returns a related…
- Capitalize in Excel Without a Formula: 3 Easy MethodsTo capitalize text in Excel without a formula, use Data > Flash Fill: type the capitalization you want…
- Concat Excel Columns: Formulas, Methods, and Fixes ExplainedTo concat Excel columns—that is, join values from separate cells into one—use the ampersand operator. With first names…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.