Figure Out Existing Formulas

March 06, 2006

Your evil co-worker gave you a workbook filled with formulas and then left the company. How can you figure out what is going on?

The first trick is to see all cells that contain formulas. Type Ctrl + ~ to toggle into show formulas mode.

The other trick is to follow these steps:

Select all cells with Ctrl + a
Use Edit - GoTo - Special to display the GoTo Special dialog.
In the dialog, check the box for Formulas
This will select just the formula cells.
Use the paint can icon to fill those cells with a color.

Formula Auditing

If you have a formula and you want to know which cells are used in the formula, you can use Formula Auding.

Select the formula. From the menu, choose Tools - Formula Auditing - Trace Precedents. This will draw blue arrows from the current cell to all of the cells on the sheet that are used in calculating the cell.

By the way, the icon around B30 above means that there are precedents on another sheet.

Another trick is to immediately to Tools - Formula - Auding - Trace Precedents again. This will draw blue arrows to all of the precendents of the precedent cells.

To remove arrows, choose Tools -Formula Auditing - Remove Arrows.

Formula Evaluation

This trick is new in Excel 2003. Select a formula and choose Tools - Formula Auditing - Evaluate Formula. This will open a dialog where you can watch the formula be calculated in slow motion. Here, the underline says that E30 is about to be evaluated.

Click Evaluate and the value of E30 is inserted. The underline moves to indicate that C4 will be calculated next.

If you want to watch C4 calculate in slow motion, choose Step In.

Use Step Out to return to the original formula. You can keep clicking Evaluate until you arrive at the final result.