About MrExcel Consulting Services Learn Excel Resources Challenge of the Month MrExcel Seminars Message Board MrExcel Store Podcast Search Media Contact Home

Figure Out Existing Formulas

Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.

The book shown in this show is Learn Excel from MrExcel.

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. For the BEST TV show on technology, check out Call for Help.

This tip was originally published on March 6, 2006 and aired on TechTV in Canada and Australia on March 6, 2006.