In Excel 2013, scenarios are input values that you can create and save, which return different calculated results. You can use them in what-if scenarios.
Utilize the Watch Window
The Watch Window is a feature that allows you to keep formulas that you need to view in sight, rather than having to jump around in a worksheet.
To use the Watch Window:
- Select the FORMULAS tab, and in the Formula Auditing group, select Watch Window.
- In the worksheet, select the cells you want to watch and click
Add Watch in the Watch Window.
- In the Add Watch dialog box, click Add.
To consolidate data from multiple worksheets into one master worksheet:
- Select the DATA tab, and from the Data Tools tab, select Consolidate.
- In the Consolidate dialog box, click and drag to select cells.
- Use the Add button to continue to add data, and click OK when you are done.
Enable Iterative Calculations
You can enable iterative calculations to locate circular references.
To enable iterative calculations:
- From the FILE menu tab, select Options.
- Select the Formulas section, and from the Calculation options section, check the Enable iterative calculations check box.
The DATA tab's Data Tools group contains the What-If Analysis drop-down list, which contains a number of what-if tools. One of these tools is Goal Seek.
To use Goal Seek:
- Select the cell with the formula you are going to solve for.
- Select the DATA tab, and in the Data Tools group, select What-If Analysis, and then select Goal Seek.
- In the To value field, enter the goal.
- In the By changing cell field, enter the cell where you want the result to be and then click OK.
Use the Scenario Manager
To manage what-if models so you can quickly access them, you can use the Scenario Manager.
To use the Scenario Manager:
- Select the DATA tab, and in the Data Tools group, select What-If Analysis, and then select Scenario Manager.
- In the Scenario Manager dialog box, click Add.
- Type a name for the scenario in the Scenario name text box, and in the Changing cells text box, type the names of the cells you want to change and click OK.
- In the Scenario Values dialog box that appears, type the values for the changing cells.
- Click Add to add more scenarios and click OK when you are done.
Use Financial Functions
The Excel 2013 financial functions are complex financial formulas that contain multiple steps. These functions cover things as calculating net present value, the depreciation of an asset, and loan payments, amongst others.
To access the financial functions, from the FORMULAS tab, in the Function Library group, select Financial.