how does scenario manager work if values with formulas change

Graeme Smith 0 Reputation points
2026-09-15T03:54:52.7633333+00:00

If some of my changing values for a scenario are formulas, how can I ensure they go back to the initial value. It doesn't seem like they can. Is there another way?

Microsoft 365 and Office | Excel | Other | Other
0 comments

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-09-15T03:55:07.18+00:00

    Scenario Manager saves and substitutes values in the changing cells. It is designed around entering values for those cells, while the result cell is typically a formula based on those changing cells.

    If a changing cell contains a formula, Scenario Manager does not provide a separate “restore original formula” behavior. The documented pattern is:

    1. Put the inputs you want to vary in the changing cells.
    2. Put formulas in separate result cells that depend on those changing cells.
    3. Switch scenarios to change the input values, and let the formula cells recalculate the result.

    So if formulas need to remain intact, do not use those formula cells as changing cells. Use Scenario Manager only for the input cells, and keep the calculated cells as result cells.

    If the model requires more than that, there are two alternatives mentioned in Excel’s What-If Analysis tools:

    • Goal Seek: use when the target is a desired result and the need is to find an input value that produces it.
    • Data Tables: use when analyzing one or two variables across many values.

    Also note that scenario summary reports are not automatically recalculated. If scenario values change, create a new summary report.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Kai-H 26,840 Reputation points Microsoft External Staff Moderator
    2026-09-15T10:17:10.7133333+00:00

    Hi, Graeme Smith

    A practical workaround is to keep a protected copy of the original formulas. Scenario Manager substitutes the stored scenario values into its changing cells, so the formulas in those cells may be overwritten when a scenario is shown. Excel Scenario Manager documentation

    Before applying a scenario:

    Copy the formula cells to an unused area or, preferably, a separate backup worksheet.

    After you finish testing scenarios, copy those backup cells.

    Select the original cells, then use Home > Paste > Paste Special > Formulas. This restores the formulas without replacing the existing formatting.

    For extra protection, duplicate the entire worksheet before running scenarios. You can then test scenarios on the copy and keep the original sheet unchanged.

    If this is something you do frequently, it is also possible to use a short VBA macro that stores each cell’s .Formula and restores it afterward. However, for occasional use, a backup sheet and Paste Special > Formulas is usually the simplest and safest method.

    Thank you for your patience in reading, I hope this information has been helpful to you. 

    If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.