Formula Auditing

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.

    If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
    MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

    MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.