| Use auditing to troubleshoot - excel | | Print | |
We've all clicked a cell to see the formula, wanting to find out where the result came from, only to find a formula that went on forever. It has everything: nested functions inside of conditionals, pulling data from cells all over the workbook. Just when you think you'll never figure this monster out, you remember Excel's Auditing features.
Select the cell containing the complex formula and click Tools | Auditing | Trace Precedents. (In Excel XP, choose Tools | Formula Auditing | Trace Precedents.) Excel will draw blue arrows from every cell the formula references to the cell containing the formula. Now you can see where that data is coming from.
Auditing also allows you to select a cell containing a value and choose Trace Dependants, which will draw arrows from the cell to all the cells containing formulas that reference that value.
And if you have a cell that shows an error value, you can select it and choose Trace Error. Excel will show you all the cells that contribute to the error.
Using Goal Seek
You have a worksheet that has the price of a house, closing costs, and interest rate, and it calculates your monthly payment. It works great, but now you want to enter a desirable payment and find the price you can afford to get that payment.
This is a common situation when dealing with worksheets (although usually more complex than a simple mortgage formula), especially when dealing with projections or forecasts. Goal Seek is just what you need for this situation. It allows you to specify a target value for a cell and tell Excel to change the value of another related cell to obtain this value.
Highlight the cell you want to change and click Tools | Goal Seek. The cell reference for the selected cell will appear in the Set Cell field. Choose a target value and enter it in the To Value field, then specify what cell should be changed to meet your target. When you click OK, Excel will attempt to meet your target.
| Users' Comments (0) |
|
No comment posted
