Formula Errors

Update an Excel Table Formula Without Overwriting Manual Edits

Huda QurayshiHuda Qurayshi Nov 14, 2026 868 views

Question details

The user needs to update a formula in an Excel table while preserving manually edited cells in the same column.

Update an Excel Table Formula Without Overwriting Manual Edits
Product
Microsoft Excel
Device & OS
not provided
Scenario
A calculated table column contains both formulas and manual exceptions, and the user wants to change the formula.
Observed behavior
Changing the calculated-column formula may propagate it through the column and replace manual edits.
Before you start

Identify the cells containing manual values or exceptional formulas before changing the column. Decide whether to keep those exceptions in their existing cells or move them to a separate override column.

Solution 1Recommended

Use a separate formula column and manual override columns

Use this approach to update the calculation without editing the existing column of manual exceptions.

A calculated column can propagate a formula throughout the table. Keeping manual overrides in separate input columns makes the exceptions independent of future formula changes.

1
Add override columns

Add table columns named Use override and Manual override. Enter Yes in Use override for rows that need an exception, and enter each exception in Manual override.

2
Add an output column

Add a new table column for the result. In its first data cell, enter a formula such as =IF([@[Use override]]="Yes",[@[Manual override]],[@[Base value]]*1.1). Replace Base value and the calculation with your actual column name and formula.

3
Check the results

Compare the new output column with the existing column, especially on rows with manual edits. Leave the original column unchanged until you have verified every exception.

4
Update future calculations

When the calculation changes, edit the formula in the new output column. Keep exception values in Manual override rather than typing over output formulas.

Use a separate formula column and manual override columns
Blank overrides: The separate Yes flag lets you distinguish a deliberate blank override from a row that should use the calculated result.
Work with formulas in WPS Spreadsheets

Keep formula results and manual overrides separate in WPS Spreadsheets

Use a new formula column and separate override inputs in WPS Spreadsheets to preserve manual exceptions when the calculation changes.

  1. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 2. Add override inputs: Add columns for an override flag and an override value. Enter Yes and the manual value on each row that needs an exception.
  3. 3. Create the result formula: In a new result column, enter a formula such as =IF(C2="Yes",D2,B2*1.1), adjusting the cell references and calculation to match your sheet.
  4. 4. Fill and verify: Drag the formula cell's fill handle down the result column, then compare the results with the original column, especially on exception rows.
Keep manual exceptions separate from formulasUpdate calculations without editing the original result columnOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface
QA img-9

Frequently Asked Questions

Can Excel update a calculated-column formula while preserving every manual edit automatically?

There is no built-in method that reliably updates the calculated-column formula while preserving every manual edit. Use a separate output column and override inputs, or fill only selected formula cells.

Where can I turn off automatic calculated columns in Excel?

In Excel for Windows, go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and clear Fill formulas in tables to create calculated columns. The location may differ in other versions.

Will turning off calculated-column creation protect edits in an existing column?

Not necessarily. An existing calculated column may still propagate formula edits. Add a new column and verify its behavior rather than assuming the setting protects existing exceptions.

How can I keep a deliberately blank manual override?

Use a separate flag column, such as Use override, alongside the override value. Set the flag to Yes even when the override value is blank, and have the formula test the flag.