Excel Table: Change a Formula in One Cell Only?

Formulas & functions · Updated November 19, 2026

Yes. To change a formula in just one Excel table cell, enter the replacement formula in that cell; if Excel fills it down the calculated column, undo the change, turn off automatic calculated-column formula filling, and try again. This keeps the table intact while allowing one row to use a different calculation.

Why Excel tables repeat formulas

An Excel table is more than a formatted range. When you enter a formula in a table column, Excel can treat it as the formula for a calculated column and copy it to the other rows. That behavior keeps formulas consistent as the table grows and makes it less likely that a new row will be left without a calculation.

For example, a sales table might have Quantity, Price, and Total columns. The usual Total formula calculates each row’s quantity multiplied by its price. Excel recognizes the formula as a column rule, rather than just a calculation that belongs to one cell. If you later add a row, the table can extend that calculation automatically.

This automatic filling is helpful when every row should follow the same rule. It can be surprising when one row needs a special case, such as a discount, a correction, or a different rate. The important distinction is that the table can contain an individual formula exception, but automatic filling may need to be disabled while you create it.

Change one cell without changing the table

First, identify the table row and the exact cell you intend to change. If you are editing a formula that already exists, select the cell and use the formula bar or press F2 to edit it. If you are replacing the formula, type the new one and press Enter. Check the cells above and below afterward: if their formulas changed too, press Ctrl+Z immediately to undo the fill.

To prevent Excel from repeating the replacement formula, turn off the calculated-column setting first:

  1. In Excel for Microsoft 365 on Windows, choose File > Options > Proofing.
  2. Select AutoCorrect Options, then open the AutoFormat As You Type tab.
  3. Clear Fill formulas in tables to create calculated columns, then choose OK.
  4. Replace the formula in the one target cell and inspect the rest of the column.

This setting affects automatic formula filling in tables, not the table’s existence. The table can still use filters, structured references, banded formatting, and other table features. If you want Excel to resume filling formulas in new calculated columns later, return to the same option and turn it back on.

In Excel for Mac, the equivalent preference is generally under Excel > Preferences > AutoCorrect, on the AutoFormat As You Type tab; wording and placement can vary by release. Excel for the web has a different interface and may not expose the same desktop options. If you cannot find the setting there, make the change in the desktop app or check the formulas in the other rows before relying on a one-cell edit.

Example: one row gets a different calculation

Suppose a table named Sales has Quantity and Price columns, and the row you want to change contains quantity 5 and price 20. Its normal total is 100. With the table columns named Quantity and Price, the standard per-row formula is =[@Quantity]*[@Price]. The @ references the values from the current table row.

Assume that same row is eligible for a 10% discount, while all other rows should keep the ordinary calculation. The row-specific formula is =[@Quantity]*[@Price]*0.9. With quantity 5 and price 20, it returns 90: 5 multiplied by 20 is 100, and 100 multiplied by 0.9 is 90. Enter that formula only in the intended Total cell after disabling automatic calculated-column filling if necessary.

Structured references are useful because they refer to the table’s column names rather than fixed worksheet addresses. They also make the formula easier to read. A different formula in one row does not mean that every other row must use the same exception; each cell can retain its own calculation once the table stops automatically replacing the column’s formulas.

How to confirm only one formula changed

Do not rely on the displayed result alone. Two different formulas can return the same number for the current inputs, and a formula may look right even if Excel has copied it farther than intended. Select the target cell and inspect its formula in the formula bar, then inspect at least the cells immediately above and below it.

For a larger table, use the formula bar to spot-check a few rows, including the first and last data rows. You can also select individual cells and compare their formulas. A one-cell exception may show Excel’s green error-checking indicator for an inconsistent calculated-column formula. That indicator is a warning about inconsistency, not proof that the calculation is wrong. Review the exception and keep it if it is intentional.

If you accidentally replaced the whole column, press Ctrl+Z on Windows before making other edits. On Mac, use Command+Z. Undo is most dependable immediately after the fill; after further changes, it may undo those later actions instead. If undo is no longer available, restore the other formulas from a known correct row or a backup, then verify the results.

What happens when the table grows

A single-cell exception can be convenient, but it creates a maintenance question: what should happen when another row is added? The ordinary calculated-column formula may populate new rows, while the special-case row remains an exception. Do not assume that a future row will inherit the special formula simply because it is next to that row. Check the new row’s formula and result.

If several rows need the exception, a separate input column is often clearer than a growing collection of unusual formulas. For instance, add a Discount column and enter a discount rate for each applicable row. Then the Total calculation can refer to that row’s rate. This makes the reason for different totals visible to colleagues and easier to audit than a formula that hides a special rule in one cell.

Another option is a condition in the calculated-column formula, based on a field such as Customer Type or Promotion. That is usually preferable when the exception follows a repeatable business rule. A one-off correction may reasonably belong in one cell; a rule that will recur is generally easier to understand when represented explicitly in the table.

One-cell edit versus converting the table to a range

Converting a table to a normal range can stop table-specific calculated-column behavior, but it is usually excessive if the only goal is to change one formula. A range loses table conveniences such as structured references, automatic table expansion, and built-in header filtering. If you want to keep those features, leave the table in place and manage the formula-fill setting instead.

To convert a table in Excel for Microsoft 365 on Windows, select a cell in it, open Table Design, and choose Convert to Range. Excel asks you to confirm. The cells and their data remain, but they no longer function as a table. On Mac the Table Design tab and command are also available, though ribbon placement can differ. Convert only when you want a range, not as a quick workaround for a single exception.

Another distinction is between changing a formula and replacing a cell’s contents with a fixed value. If you type 90 rather than a formula, that cell no longer recalculates when its quantity or price changes. That may be correct for a manually entered adjustment, but it is not equivalent to a formula-based exception. Decide whether the result should update when the row’s source values change.

Common problems and fixes

  • Several rows changed: Undo immediately, disable automatic calculated-column filling, and enter the formula again in the intended cell.
  • The formula changes back: Check whether the calculated-column fill setting is still enabled or whether you accepted a prompt to replace the column’s formulas. Undo and repeat with the setting disabled.
  • A green triangle appears: Excel may be flagging a formula that differs from its calculated column. Check the logic; ignore the warning only if the exception is deliberate.
  • The result is not updating: Make sure you entered a formula rather than a fixed value, and check that Excel calculation is not set to Manual under Formulas > Calculation Options.
  • A new row has the wrong formula: Inspect the new row after adding it. A one-off exception is not a reliable template for future rows.

When working in a shared workbook, explain unusual formulas with a cell comment or a nearby note. That small bit of context can prevent someone from “correcting” the exception later or copying it into rows where it does not belong.

Frequently Asked Questions

Can I have different formulas in the same Excel table column?
Yes. A table can contain a formula exception in an individual cell. If Excel tries to fill the formula through the calculated column, undo and disable automatic formula filling before editing that cell.

Why does Excel copy my formula to every row?
Excel recognizes formulas entered in table columns as calculated-column formulas and can fill them through the column. This keeps the table consistent and helps new rows receive formulas automatically. The behavior is controlled by the calculated-column AutoCorrect setting.

Will turning off formula filling remove my table?
No. It changes automatic formula filling; it does not convert the table to a range. Your table’s data, filters, formatting, and structured references remain in place.

Can I edit just one cell without changing settings?
Sometimes Excel allows the individual formula edit directly. Because calculated-column behavior can fill or offer to replace formulas across the column, inspect the surrounding cells and undo if needed. Disabling the setting first is the more predictable approach.

Should I use a different formula or type a fixed number?
Use a formula if the result should update when the row’s inputs change. Use a fixed number only when the value is intentionally static. For recurring exceptions, consider adding a table field that records the condition or adjustment.

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.