If you’ve ever had to decide between two or more financial situations, you may have done some spreadsheet manipulation. You might have entered different numbers to see the varying outcomes. But did you know you can use Excel’s Scenario Manager for just that?
You may be deciding between jobs, projects, products, or something similar where the difference between them is financial, or basically, numeric. With the Scenario Manager in Microsoft Excel, you can enter values for each different situation, save them, and then switch between them with a click.
Add your first scenario’s data into your sheet. Here, we have the salary for Job 1 in cell B2, minus our fuel cost in cell B3 and monthly bills and cell B4. We enter a simple formula into cell B5 that shows us the amount of money left over.
Go to the Data tab, click the What-If Analysis drop-down arrow, and pick “Scenario Manager.”In the Scenario Manager window, click “Add” to include this first scenario.Give your scenario a name; we’ll use Job 1.
For our example, our changing cells are B2 for the salary and B3 for the fuel cost. Click “OK.”
In the subsequent pop-up box, enter the values. Since this is the first scenario and you’ve already entered the amounts in the sheet, you should see those values in the corresponding boxes. Confirm and click “OK.”You’ll then see the Scenario Manager window display your first scenario. Select “Add” to set up the second scenario.Follow the same steps to give the scenario a name and enter the Changing Cells. The changing cells will likely be the same cell references since you are comparing situations. However, you may use different or additional cells than those shown if necessary. Click “OK.”Now, enter the values for the second scenario in the box (not in the sheet). For our example, we enter the salary (B2) and fuel cost (B3) for Job 2. Remember, these are the two variables that change and that we are comparing. Click “OK.”Now you have both scenarios set up and should see them in the Scenario Manager window.You already see the first scenario for Job 1 in the sheet since you initially entered those details. To see the second scenario, select it in the window and click “Show.”
You’ll see your spreadsheet update to display the values and calculations for the second scenario.
To display the first once again, select it in the Scenario Manager window and click “Show.”This lets you quickly and easily flip back and forth between the scenarios.
When you land on the one you want to keep in your sheet, make sure it’s displayed there and click “Close” in the Scenario Manager window.
To show a comparison in one spot, open the Scenario Manager, click “Summary,” and mark Scenario Summary. You’ll see a new tab open with a nice visual of your comparison that you can save or share.
