Function Problems

Allow Manual Overrides Without Deleting an Excel Formula

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

The user needs a calculated rating that can be manually overridden and returns to the calculated value when the override is removed.

Allow Manual Overrides Without Deleting an Excel Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A rating is normally calculated by a formula, but users sometimes need to enter a different value.
Observed behavior
The desired result is to display a manual rating when one is entered and otherwise display the calculated rating.
Before you start

Identify the column containing the calculated rating and choose two available columns for the manual override and final rating. The example below uses G2 for the calculated rating, H2 for the override, and I2 for the final rating.

Solution 1Recommended

Use an Override Column and an IF Formula in Excel

Keep the calculation intact while allowing a separate manual entry to take precedence.

A cell cannot retain both a formula and a manually entered value. Use separate cells for the calculation, override, and final result.

1
Keep the calculation

Leave your existing rating formula in G2, or enter it there if you are setting up a new worksheet.

2
Create an override field

Label column H “Manual override” and leave H2 blank when the calculated rating should be used.

3
Enter the final rating formula

Select I2 and enter =IF(H2<>"",H2,G2). Label column I “Final rating.”

4
Apply and test

Copy I2 down the final rating column. Enter a rating in H2 to override the calculation, then clear H2 to show the calculated value again.

Use an Override Column and an IF Formula in Excel
Adjust the cell references: Replace G2 and H2 in the IF formula with the calculated-rating and override cells in your worksheet. A manual value of 0 is treated as an override.
Work with spreadsheet formulas

Create a Manual Rating Override in WPS Spreadsheet

Use WPS Spreadsheet to keep your rating formula in one column and display a manual override when one is entered in another.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to load your workbook.
  2. 2. Set up the columns: Keep the calculated rating in G2 and designate H2 as the manual override cell.
  3. 3. Add the final formula: Select I2 and enter =IF(H2<>"",H2,G2) to display the override when present.
  4. 4. Test the override: Enter a value in H2, then clear it to confirm that I2 returns to the calculated rating. Copy I2 down for additional rows.
Preserve the calculated rating while allowing manual overridesOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and fill controlsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can I type an override into the same cell as the formula?

No. Typing a value into a formula cell replaces the formula. Use a separate override cell and an IF formula to choose which result to display.

How do I restore the calculated rating?

Clear the manual override cell, such as H2. The final formula =IF(H2<>"",H2,G2) will then display the calculated value from G2.

Will zero work as a manual override?

Yes. Entering 0 in H2 makes the IF formula display 0 because the cell is not blank.

How do I use the override formula for multiple rows?

Enter the formula in I2, then copy or fill it down column I. The relative references will adjust to each row.