Practical: how to change one cell and update all formulas in Excel

Learning how to change one cell and update all formulas in Excel takes one habit. Put the value in a single input cell, then make every formula point at it. You edit that cell once, and hundreds of results follow. If your numbers still refuse to move after that, the cause is usually one of a few things, and this guide covers each.

The fix: one input cell that every formula points to

Pick an empty cell, label it, and type the value there. Say B1 holds a tax rate of 0.07, with the label Tax rate in A1. In the price column, write =A5(1+$B$1) instead of =A51.07. The dollar signs lock the reference, so it stays on B1 when you copy the formula down.

Press F4 while your cursor sits on a reference to cycle through the dollar-sign combinations. Microsoft's guide to moving and copying formulas in Excel explains that relative references change when you copy, while an absolute reference such as $A$1 stays put. That single difference is the whole trick. Forget the dollar signs, and row 6 quietly points at B2 instead.

Here is a quick check. With prices of 100, 250 and 80 in A5:A7 and 0.07 in B1, the formulas return 107, 267.5 and 85.6. Change B1 to 0.08, and the same three formulas return 108, 270 and 86.4. Nothing else needed editing.

Replace numbers already buried inside formulas

Inherited files often hide the rate inside every formula, but Find and Replace can fix them in bulk. Select the formula range, press Ctrl+H, type (1+0.07) in Find what, and type (1+$B$1) in Replace with. Go one match at a time at first, because a search for 0.07 alone also catches 0.075 and any constant you meant to keep.

Then test the result. Change B1 to 0.08 and watch a few rows. If every result moves, you are done. If one row stays the same, it still holds a typed number, so click into it and look. Also search the whole workbook, because the same rate may sit in formulas on another sheet.

Name the cell so formulas explain themselves

A named range turns $B$1 into a word. Select B1, open the Formulas tab, choose Define Name, and type TaxRate. The formula now reads =A5*(1+TaxRate), which a colleague can understand without hunting for B1. A name points to an absolute cell by default, so you skip the dollar signs.

Names have one honest limit. A name usually lives inside a single workbook, so a second file needs its own TaxRate. A name also only helps while people keep using it, and a typed 1.07 can sneak back in later.

Why formulas do not update after you change the cell

Many people searching for how to change one cell and update all formulas in Excel actually have a calculation problem, not a formula problem. Microsoft says Excel normally recalculates only when the cells a formula depends on change, but you can switch to Manual. In that mode, results stay frozen until you recalculate. A Microsoft Tech Community thread on a formula that is not updating gives the fix. Open Formulas, then Calculation Options, and choose Automatic.

Because these options affect every open workbook, one file set to Manual can mislead you in another. A cell formatted as Text can also show a formula as plain text instead of a result, so check the format if one cell refuses to calculate. You can force a refresh with a shortcut:

• F9 recalculates changed formulas in all open workbooks.
• Shift+F9 does the same for the active worksheet only.
• Ctrl+Alt+F9 recalculates every formula in all open workbooks, changed or not.

One more surprise: Excel calculates with stored values, not the ones you see. Microsoft's example has two cells holding 10.005 that each display $10.01 but add up to $20.01. So a total can differ from adding the displayed figures by hand. Avoid the Set precision as displayed option unless you need it, since Microsoft warns that it permanently changes stored values.

Change the formula itself once, not row by row

Sometimes the formula changes, not the input. Edit the first cell, then double-click the fill handle or drag it across the range. Microsoft advises you to verify that the copied references still give the result you want. Paste Special, then Formulas, moves formulas without the formatting, while Paste Special, then Values, keeps only the results.

In Excel 2021 and Microsoft 365, a dynamic array formula removes the copying altogether. Type =A5:A14*(1+TaxRate) once in B5, and the results spill down the column. Exceljet's overview of dynamic array formulas in Excel describes this as one formula returning many values. It also says Excel 2019 and earlier lack the feature. If another cell blocks the spill range, you see a #SPILL error until you clear it.

Four ways to centralize a value, side by side

Find every formula that depends on the input

Before you change a shared value, see what it touches. Select the input cell and use Trace Dependents on the Formulas tab to draw arrows to the formulas that use it. Our guide to reading trace dependents in Excel shows how to interpret those arrows. An arrow to a formula you did not expect means the change would have surprised someone.

Where the manual approach stops scaling

Changing one cell and updating all formulas in Excel by hand works well inside one workbook. It gets harder when the same rate feeds a pricing model, a forecast and a reporting file. Names do not travel between files, so copies drift apart, and nobody notices until two reports disagree. Formula Foundry's shared variables keep one value available across the workbook, and its Excel formula generator can help you write the formulas that use it.

Other people editing your input cell raises a second question: who changed it, and when? A formula alone cannot store that history, and this Microsoft Q&A thread on datestamps when a cell value changes shows how common the question is. Agree on who owns the input cell, label it clearly, and keep one note beside it. If you also work in Sheets, our notes on formulas in Google Sheets cover habits that cut errors.

Action steps

1. Create the input cell: Label a cell and type the value once, such as 0.07 in B1.
2. Point formulas at it: Use $B$1 or a name like TaxRate in every formula that needs the value.
3. Replace old numbers: Select the formula range, press Ctrl+H, and replace typed numbers one match at a time.
4. Test the change: Change the input, confirm Calculation Options is set to Automatic, and press F9 if results stay frozen.
5. Check dependents: Use Trace Dependents to see every formula that relies on the input.

Frequently asked questions

Why do my Excel formulas not update when I change a cell?

Calculation is most likely set to Manual. Open Formulas, then Calculation Options, and choose Automatic. Press F9 to refresh right away.

Can I update all formulas at once with Find and Replace?

Yes. Select the formula range, press Ctrl+H, and replace the typed number with a cell reference. Review each match first, because similar numbers can match too.

Does a named range work in other workbooks?

A name usually lives in the workbook where you define it, so a second file needs its own copy. Keep that in mind when you share files.

Which Excel versions support spilled formulas?

Exceljet says dynamic array formulas are available in Excel 2021 and Excel 365. Excel 2019 and earlier do not offer them.