Skip to main content
Workforce LibreTexts

18.7: Formula Auditing Group

  • Page ID
    13704
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)

    clipboard_e3263685eb742d038c6673373adad17b6.png

    The Formula Auditing group helps the user understand which cells are being used by formulas and check for errors to make sure formulas are calculating accurately.

    The Trace Precedents icon clipboard_e3e7aadd40ac199cd654760881d0b2a57.png Displays arrows that indicate what cells affect the value of the currently selected cell. Usually, these cells are included in a formula. As provided in the previous example, a Trace President shows that the Sum of cells A1-A5 is 15 and the cells included are outlined.

    clipboard_ebac52704f5a928da1ea08388c4c28996.png

    The Trace Dependents icon clipboard_ed8d0c824d1e5bad865b35b2fac188c08.png Displays arrows that indicate what cells are affected by the value in the currently selected cell. Trace Dependents is the opposite of Trace Precedents.

    The Remove Arrows icon clipboard_eddf5a7cb693a43ee693403579a0c962e.png Button with Drop-Down. The button removes all the arrows drawn by the trace precedents and trace dependents. The drop-down contains the commands for more fine-tuning: Remove Arrows, Remove Precedent Arrows and Remove Dependent Arrows.

    The Show Formulas icon clipboard_e130b437cd9db3f419f8a672bc529e092.png - (Ctrl + ') allows the user to toggle between showing formulas in the cells and the result of the formula in all cells in a worksheet.

    The Error Checking icon clipboard_e3d1b0a1487851736b7c86dd2a4ef64ac.png with more drop-down options displays the "Error Checking" dialog box. If you have created a formula that is not working correctly, the Error Checking dialog box can assist. As demonstrated below, the formula was changed to include the addition of cell B1, which contains no data. Error checking provides an analysis of the formula, and options to correct the error.

    clipboard_e1f2230c3ff3118dec98b7641beee35ca.png

    The drop-down contains the commands: Error Checking, Trace Error, and Circular References. All commands will only activate if the worksheet contains an error.

    The Evaluate Formula icon clipboard_e10b97fada6f8ce561e1cc0cdf1260454.png displays the Evaluate Formula dialog box. In the previous example provided, the formula did not include a value in cell B1, so it was incorrect, the Evaluate formula dialog box provides information that the problem is cell B1 because it is empty. The buttons Step in and Step Out allow the user to review each aspect of the formula to determine where the error is (in this case it is in Cell B1 as described in the dialog box.

    clipboard_e56a0946e47c3e0d7e965fe0270a1f536.png

    Finally, the Watch Window icon clipboard_ee510465f820cafe31215c03feb2f68c5.png - Displays the. Watch Window

    clipboard_eb0c783ccf76bde19a6919bcb0c45f74e.png

    Allows you to view the contents of cells and their results as you make changes in other places in the document. Using the watch window, you can Add Watch by selecting a range of cells and click Add Watch. This will allow the user to continue to view the value of those cells as they work in other places in the workbook. This can be helpful if a user is working in a complicated formula that may change a value that is very important.


    18.7: Formula Auditing Group is shared under a CC BY license and was authored, remixed, and/or curated by Nick Heisserer.

    • Was this article helpful?