LibreOffice Calc · What-if analysis · Intermediate
Compare Saved Scenarios in LibreOffice Calc
A worked Calc example to compare saved scenarios, with source checks, changed inputs and recovery.
Documentation-qualified for Calc 26.2; native workflow and persistence checks pending · Editorial standards
Scenarios store named sets of input values in one workbook. Use them to switch among coherent plans without copying the entire workbook or overwriting formulas. Decide whether edits should update a saved scenario before creating the alternatives.
Prepare three assumptions, one calculation
Use LibreOffice Calc 26.2 desktop with English standard menus on Windows/Linux. This is a documentary workflow with independently calculated expectations, not a native-tested one. Work on an unprotected practice sheet in an ODS file.
Enter A1:B5:
| Row | A label | B value/formula |
|---|---|---|
| 1 | Units | 100 |
| 2 | Price | 20 |
| 3 | Unit cost | 12 |
| 4 | Fixed cost | 500 |
| 5 | Profit | =B1*(B2-B3)-B4 |
Profit is 300. Our scenario range is B1:B2 only: units and price vary, while costs and the profit formula remain outside the saved range. Calc requires at least two selected cells for a scenario. Selecting an entire sheet would obscure which inputs are meant to change.
The three plans are Base (100, 20), Busy (150, 19), and Premium (90, 24). Their expected profits are 300, 550 and 580 respectively. More units do not necessarily produce the most profit because the price also changes.
Save fixed alternatives safely
- Select B1:B2 and choose Tools > Scenarios.
- Name the scenario Base and add a comment explaining “100 units at 20; costs remain shared”.
- Enable Display border so the scenario range and selector are visible. Clear Copy back for this initial fixed baseline. Leave Copy entire sheet off. Confirm with OK.
- Enter 150 and 19 in B1:B2. With the same range selected, create Busy, again clearing Copy back. The newly created scenario becomes active.
- Enter 90 and 24 and create Premium from the identical B1:B2 range, with Copy back off.
The Copy back choice matters: when enabled, changes to the active scenario’s cells can overwrite that saved scenario. Creating the next scenario after first changing cells in a Copy-back-enabled scenario risks losing the previous plan. Our initial fixed alternatives avoid that trap.
Switch and reconcile the plans
Use the down arrow in the displayed scenario border and select Base. Check B1=100, B2=20 and B5=300. Select Busy and verify 150, 19 and 550; then Premium and verify 90, 24 and 580. Also check B3=12 and B4=500 throughout.
Alternatively choose View > Navigator, select its Scenarios icon, and double-click a scenario name. The comments provide context when names alone are ambiguous. This is named assumption management, not simply navigating worksheets.
Change shared unit cost B3 to 13 and repeat the switches. Profits become Base 200, Busy 400 and Premium 490. The scenarios did not save that cost cell, so each calculation uses the new shared assumption. Restore B3 to 12 before the next exercise.
Deliberately revise one saved scenario
Apply Busy. In Navigator, right-click Busy and choose Properties. Enable Copy back and confirm. On this unprotected sheet, change B1 from 150 to 160 while Busy is active. Its profit should become 620: 160 × 7 − 500. Switch to Base and back to Busy; the revised 160 should return.
If it does not, inspect Copy back and the sheet/cell protection state before repeating edits. The guide documents interactions with Prevent changes and protection; this exercise deliberately avoids them. Do not remove someone else’s protection merely to force a scenario update.
To restore Busy, apply it, set B1 to 150 while Copy back is enabled, switch away and back, and verify 550. Then clear Copy back again if you want the plan fixed. Temporary edits with Copy back off can affect the currently displayed result without updating the stored scenario; switching away and back distinguishes the two.
Save and reopen the ODS, then cycle through all three plans and check names, range and totals. Do not use CSV as the master: it cannot preserve named scenario structures. If an alternative is wrong, repair that scenario’s inputs or rebuild it from recorded assumptions, rather than changing the shared formula to make one displayed total look right.
Official references
Sources were captured on 20 October 2026 and reviewed from retained snapshots. All outcomes above are expected checks, not a claim of native execution.