Yes. In Excel for Microsoft 365 on Windows, a table normally carries a calculated-column formula into new rows, so you do not have to copy it down manually. Add the row as part of the table and keep the formula-fill setting enabled; pasted data or a formula entered inconsistently can still create exceptions.
What happens to formulas in an Excel table
An Excel table is more than a formatted range. When a column contains a consistent formula, Excel treats it as a calculated column: it applies the same formula pattern to the other cells in that table column and can extend it when the table grows. For instance, if every row in a Revenue column calculates quantity multiplied by unit price, the next table row can receive that calculation automatically.
This behavior differs from a normal worksheet range. In a regular range, entering a formula in one row does not reliably mean Excel will fill it into a later row. A table provides a named, expandable structure, and formulas can use column names rather than fixed cell addresses. The key qualification is that Excel must recognize the formula as belonging to a calculated column, and the new row must actually become part of the table.
Formula propagation does not mean every cell in every new row receives a formula. Excel fills the corresponding calculated column; other columns remain blank unless they have their own formula or you enter values. A formula in the table’s Total Row is also different: it summarizes the column and is not a row-level calculation that gets copied into each new record.
Add a row so it becomes part of the table
Several common methods add a row to an existing table. After the new row is included, Excel can extend the table’s formatting, calculated-column formulas, and other table behavior.
- Type directly below the table: Enter a value in the first row immediately beneath the table. If Excel’s table-expansion option is on, the table generally expands to include that row.
- Use Tab from the last cell: Click the final cell in the table’s last row and press Tab. Excel creates a new table row, ready for entry.
- Insert a table row: Right-click a cell in the table, choose Insert, then choose a table-row option such as Table Rows Above. The exact wording can depend on the selected cell and Excel build.
- Resize the table: Select a cell in the table, open Table Design, choose Resize Table, and adjust the range to include the additional row.
Typing below the table is convenient, but do not assume a row has joined the table just because its formatting looks similar. Check whether the table’s banded formatting extends, whether the Table Design tab appears when you select the new cell, or whether the table’s filter controls and structured references include the record. Formatting can be copied independently of table membership.
Example: a calculated column
Suppose a table has columns named Quantity, Unit Price, and Line Total, with one record per row. In the Line Total column, enter a formula using the table’s column names. With the table headers Quantity and Unit Price, the formula for a row is =[@Quantity]*[@[Unit Price]]. The @ references the values in the current row, so the formula remains meaningful as the table expands or is sorted.
For example, if the current row has Quantity 3 and Unit Price 12, the formula returns 36. When a new row is added and the formula is recognized as the calculated-column formula, Excel supplies the equivalent row-specific calculation. You can verify the result by entering a quantity and price in that new row and checking that Line Total calculates without dragging a fill handle.
Excel may show a small AutoCorrect Options button near a newly filled formula. Depending on the situation, that control lets you accept or undo the automatic fill. If the formula is not what you intend, inspect it before relying on the resulting totals; table formulas are convenient, but they do not validate whether the business logic is correct.
Check the formula and table expansion settings
On Excel for Microsoft 365 for Windows, the relevant options are in File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. Check the options named Include new rows and columns in table and Fill formulas in tables to create calculated columns. The first controls whether nearby entries expand a table; the second controls automatic formula filling for calculated columns. Names or availability can differ slightly across Excel releases.
If formula propagation is important in a shared workbook, check these settings on the computer where the row is being added. They are Excel application preferences, not a formula stored in the workbook that guarantees every other user’s Excel will behave identically. If a setting is off, you can still expand the table and copy or fill the formula yourself.
In Excel for Mac, table formulas also support calculated columns, but settings are reached through the Excel application menu rather than Windows File > Options. In Microsoft 365 for Mac, look under Excel > Settings (called Preferences in some older releases) > AutoCorrect > AutoFormat As You Type for the corresponding options, if present in that build. Excel for the web has a different settings interface; if automatic expansion does not occur there, add the row within the table or resize the table, then check whether the formula is present.
Why a new row may not keep the formula
The most common cause is that Excel did not treat the entry as a table row. A row typed well below the table, or a pasted block separated from it by blank rows, may remain outside. Another cause is that formula filling or table expansion is disabled. It is also possible that the formula column is not consistent: if some cells contain different formulas or manually typed values, Excel may not treat the entire column as one calculated column.
Pasting deserves particular care. Pasting values into a new row is different from pasting a whole row that includes formulas, blanks, or formatting. A pasted blank can replace a formula, and a pasted formula may introduce a different calculation. If you import or paste many records, check a few cells in the formula column afterward rather than assuming Excel filled every missing calculation. A table can contain a gap or a deliberate exception even when most rows look uniform.
To diagnose a missing formula, first select the new cell and inspect the formula bar. Then confirm that the row belongs to the table, compare the cell with the formula directly above it, and check both AutoFormat As You Type options. If needed, copy the formula from the row above into the missing cell, or select the table-column cells that need the calculation and fill the formula down. Confirm the result for more than one row when working with pasted data.
If Excel shows an error such as #VALUE! or #REF!, that is not necessarily a failure to fill the formula. The formula may have propagated correctly but refer to unsuitable input or a deleted/moved reference. Examine the formula and its inputs separately from the question of whether the row is inside the table.
Structured references and ordinary cell references
Table formulas can use structured references such as [@Quantity] to mean the Quantity value in the current row. They often remain easier to read when columns move or when the table grows. A formula written with ordinary references, such as a reference to cells in a particular row, may also be copied into a calculated column, with Excel adjusting relative references in the usual way. The choice of reference style does not replace the need for the row to be part of the table.
Structured references are especially useful when formulas are used in table columns, but they can look unfamiliar at first. Renaming a table header can update references that depend on that header, while changing the logic of a formula is still your responsibility. If a header contains spaces, Excel’s structured-reference syntax may include brackets around the header name, as in the Unit Price example above.
Tables compared with ordinary ranges
If your main requirement is for new records to inherit a formula, an Excel table is usually more dependable than a manually formatted range. Tables can expand, keep column formulas consistent, and make formulas easier to interpret with meaningful headers. They also provide built-in sorting and filtering controls. A plain range may be sufficient for a fixed worksheet, but you may need to extend formulas, formatting, and chart or summary ranges yourself when adding records.
A table is not a substitute for checking a calculation. It can propagate a wrong formula just as readily as a correct one, and a manually changed cell can break consistency. Consider using a table when the data is a growing list of records and each row follows the same structure. If the worksheet is a report with irregular sections, merged cells, or different formulas by row, a single table may not fit the layout.
Frequently Asked Questions
Do Excel tables automatically copy formulas to new rows?
Usually, yes. Excel fills a recognized calculated-column formula when the new row is part of the table and formula filling is enabled. Check the row and the relevant AutoCorrect options if the formula is missing.
How do I add a row to an Excel table without losing formulas?
Use Tab from the last cell in the table, insert a table row, type immediately below the table with expansion enabled, or resize the table. Then check that the formula column contains the expected calculation.
Why does Excel not autofill formulas in a table?
The row may be outside the table, automatic table expansion or calculated-column filling may be off, or the formula column may contain inconsistent entries. Pasting can also overwrite a formula with a blank or a different formula.
Can I turn off automatic formula filling in tables?
Yes. In Windows Excel, go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and clear Fill formulas in tables to create calculated columns. This affects automatic filling; you can still enter or copy formulas manually.
Does the Total Row formula copy into new rows?
No. The Total Row is a summary at the end of the table, not a calculated column for individual records. New data rows receive row-level formulas from calculated columns, while the Total Row remains a separate summary feature.
Related
- Excel Shortcut to Capitalize All Letters in a Cell?
- Excel Sheet Keyboard Shortcuts: Essential Keys and Tips
- Excel Shopping List Template: Create, Organize, and Print
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMIF Between Two Dates: The Right FormulaTo sum values for dates within an inclusive start-to-end range in Excel, use SUMIFS, which supports more than…
- Excel Online Filter: See Just Your Rows in a Sheet ViewYes. In Excel for the web, use a Sheet View to filter a shared worksheet so you see…
- Excel Consolidate Text Data: Formulas and Power QueryTo consolidate text in Excel, join cells with TEXTJOIN or combine rows from multiple tables with Power Query.…
- Excel AVERAGEIF Formula Examples: Criteria, Ranges, FixesUse Excel’s AVERAGEIF function to average values that meet one condition: =AVERAGEIF(A2:A6,"North",B2:B6). If A2:A6 contains regions and B2:B6…
- COUNTIF Excel Partial Text: Match Words with WildcardsYes. To count cells that contain partial text in Excel, use COUNTIF with asterisks as wildcards. With the…
- Is INDIRECT Volatile in Excel? Performance ExplainedYes. INDIRECT is a volatile Excel function, so it recalculates whenever Excel recalculates, even when the cells named…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.