Formulas, Relative References, Order of Operations, and Auditing Formulas

Auditing Formulas

Excel provides a few tools that you can use to review the formulas entered into a worksheet. For example, instead of showing the outputs for the formulas used in a worksheet, you can have Excel show the formula as it was entered in the cell locations. This is demonstrated as follows:

  1. With the Budget Detail worksheet open, click the Formulas tab of the Ribbon.
  2. Click the Show Formulas button in the Formula Auditing group of commands. This displays the formulas in the worksheet instead of showing the mathematical outputs.
  3. Click the Show Formulas button again. The worksheet returns to showing the output of the formulas.

Figure 8 Show Formulas Command shows the Budget Detail worksheet after activating the Show Values command in the Formulas tab of the Ribbon. As shown in the figure, this command allows you to view and check all the formulas in a worksheet without having to click each cell individually. After activating this command, the column widths in your worksheet increase significantly. The column widths were adjusted for the worksheet shown in Figure 8 Show Formulas Command so all columns can be seen. The column widths return to their previous width when the Show Formulas command is deactivated.

Show Formulas Command

Figure 8 Show Formulas Command


Two other tools in the Formula Auditing group of commands are the Trace Precedents and Trace Dependents commands. These commands are used to trace the cell references used in a formula. The Trace Dependents command shows where any given cell is referenced in a formula. The Trace Precedents command shows what cells have been referenced in a formula that exists in an activated cell. The following is a demonstration of these commands:

  1. Click cell D3 in the Budget Detail worksheet.
  2. Click the Trace Dependents button in the Formula Auditing group of commands in the Formulas tab of the Ribbon. A double blue arrow appears, pointing to cell locations C3 and F3 (see Figure 9 Trace Dependents Example). This indicates that cell D3 is referenced in formulas that are entered in cells C3 and F3.
  3. Click the Remove Arrows command in the Formula Auditing group of commands in the Formulas tab of the Ribbon. This removes the Trace Dependents arrow.
  4. Click cell F3 in the Budget Detail worksheet.
  5. Click the Trace Precedents button in the Formula Auditing group of commands in the Formulas tab of the Ribbon. A blue arrow running through cells D3 and E3 and pointing to cell F3 appears (see Figure 10 Trace Precedents Example). This indicates that cells D3 and E3 are references in a formula entered in cell F3.
  6. Click the Remove Arrows command in the Formula Auditing group of commands in the Formulas tab of the Ribbon. This removes the Trace Precedents arrow.


Figure 9 Trace Dependents Example
shows the Trace Dependents arrow on the Budget Detail worksheet. The blue dot represents the activated cell. The arrows indicate where the cell is referenced in formulas.

Trace Dependents Example

Figure 9 Trace Dependents Example


Figure 10 Trace Precedents Example shows the Trace Precedents arrow on the Budget Detail worksheet. The blue dots on this arrow indicate the cells that are referenced in the formula contained in the activated cell. The arrow is pointing to the activated cell location that contains the formula.

Trace Precedents Example

Figure 10 Trace Precedents Example

Key Takeaways

  • Mathematical computations are conducted through formulas and functions.
  • An equal sign (=) precedes all formulas and functions.
  • Formulas and functions must be created with cell references to conduct what-if scenarios where mathematical outputs are recalculated when one or more inputs are changed.
  • Mathematical operators on a typical calculator are different from those used in Excel. Table 2 Excel Mathematical Operators lists Excel mathematical operators.
  • When using numerical values in formulas and functions, only use universal constants that do not change, such as days in a week, months in a year, and so on.
  • Relative referencing automatically adjusts the cell references in formulas and functions when they are pasted into new locations on a worksheet. This eliminates the need to retype formulas and functions when they are needed in multiple rows or columns on a worksheet.
  • Parentheses must be used to control the order of operations when necessary for complex formulas.
  • Formula auditing tools such as Trace Dependents, Trace Precedents, and Show Formulas should be used to check the integrity of formulas that have been entered into a worksheet.