Formulas, Relative References, Order of Operations, and Auditing Formulas
Read this overview of how to create formulas in Excel. Pay close attention to the figures that show how to enter formulas in a budget spreadsheet.
The second section explains relative referencing. Excel will adjust the cell reference used in a formula when you copy and paste the formula into a new location. For example, if you are summing numbers in column A, you may have a cell with the formula =SUM(A1:A10). If you copy the formula cell into the next column (column B), it will automatically change to =Sum(B1:B10). This feature is useful when you are performing the same type of calculation on multiple sets of data.
The third section discusses the order of operations. When you perform complex calculations, your formula must follow the correct order of operations. For example, to calculate (2+8)/5, you first calculate the parentheses (2+8) and then divide its result by 5. Spreadsheet programs have formatting requirements for the order of operations. Pay special attention to Table 2.3, which shows the order of operations Excel follows.
The final section discusses auditing formulas, which allows you to check or review the formulas you use in a spreadsheet using a simple command. You can also see which cells are referenced in each spreadsheet formula.
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:
- With the Budget Detail worksheet open, click the Formulas tab of the Ribbon.
- 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.
- 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.
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:
- Click cell D3 in the Budget Detail worksheet.
- 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.
- 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.
- Click cell F3 in the Budget Detail worksheet.
- 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.
- 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.
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.
Figure 10 Trace Precedents Example
- 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.