AL TE RI Working Data Magic with Calculations O PY RI GH TE D MA nce data is entered in a workbook, you’re ready to perform calculations on it (after all, calculations are why Excel exists). To perform calculations in a worksheet, you write formulas; to perform complex calculations, you use functions in your formulas (functions are built-in mathematical equations that save you time and effort, and are covered in Chapter 5).
110 PART II ■ GETTING THE DATA IN AutoCalculate Figure 4.1. AutoCalculate is always at work. All you need to do is select two or more cells. To use AutoCalculate, select the cells you want to calculate (two or more) and look at the AutoCalculate box. AutoCalculate sums cells by default, but it can also average cells, count entries, count numeric entries, and tell you the maximum or minimum number in a range.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 111 AutoSum To enter a formula in the worksheet that calculates a group of numbers without actually writing the formula yourself, use the AutoSum button on the Standard toolbar. AutoSum can write sum and average numbers, count all entries, and display the maximum and minimum numbers in a range. To use AutoSum, click a cell directly below a column of numbers you want to sum, then click the AutoSum button (shown in Figure 4.3). Figure 4.3.
112 PART II ■ GETTING THE DATA IN Sum is the function most people want to use with AutoSum (and it’s the function most people use in a workbook), so the default calculation is sum. To use AutoSum with a different calculation, when you click the AutoSum button, click the arrow on the button and select a different function (see Figure 4.5). Figure 4.5. Change the AutoSum function.
CHAPTER 4 Inside Scoop ■ WORKING DATA MAGIC WITH CALCULATIONS 113 Inside Scoop You can make the calculator much more high-tech by choosing View Scientific (I leave it to you to understand the higher-level mathematics available there). By the way, if you want to do a quick square root, it’s on the small Standard calculator, but not on the Scientific calculator. You can open the calculator from an Excel toolbar button.
114 PART II ■ GETTING THE DATA IN When you write formulas that include cells, the cells in the formula are identified by their references. For example, in Figure 4.8, the formula =A1+B1 sums the values in cells A1 and B1. This formula... ...references these two cells. Figure 4.8. Formulas use cell references. A range reference is a rectangular range of cells that are identified by the references of the range’s upper-left corner cell and lower-right corner cell.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 115 For any cell, there is only one reference but four reference types: relative, absolute, and two mixed types. Dollar signs ($) in the reference determine the type: ■ A1 is called relative. ■ $A$1 is called absolute. ■ $A1 and A$1 are called mixed. An absolute cell reference is a fixed geographical point, like a street address, such as 123 Cherry Street. A relative cell reference is a relative location, as in “one block west and two blocks south.
116 PART II ■ GETTING THE DATA IN Click in reference and press F4 Figure 4.11. Open the cell, click in the reference (in the cell or in the Formula bar), press F4 to cycle, and press Enter. Writing formulas You’ll probably need to do more calculations in your workbooks than AutoSum can do for you, which means learning how to write formulas. Arithmetic operators A simple formula might consist of adding, subtracting, multiplying, and dividing cells. Excel’s arithmetic operators are detailed in Table 4.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 117 Cell references versus static entries Occasionally you’ll want to write formulas that include a static (constant, not calculated) entry such as a sales tax rate or a product name, but it’s more efficient to write formulas that only use cell references. For example, if you want to write a formula that totals prices and calculates sales tax, you can write a formula like =SUM(F6:F17)* .
118 PART II ■ GETTING THE DATA IN Hack You can quickly show all the formulas in a worksheet if you press Ctrl+` (the grave accent on the same key as the tilde (~). Press Ctrl+` again to hide the formulas. cell because of the relative references. The advantages and disadvantages of using relative references become clear when you copy formula cells to a new location. Figure 4.12.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 119 Relative references changed the results Results still correct Figure 4.13. Moving and copying cells with relative references Relative references, on the other hand, give the input cells’ locations relative to the formula cell, and when you copy a formula cell to a new location, relative references continue to refer to locations relative to the formula cell. This behavior is quite handy when you expect it, and frustrating if you aren’t aware of it.
0 PART II ■ GETTING THE DATA IN Bright Idea If you want a formula to calculate one changing cell with one unchanging cell, such as the cells in a Price column and an unchanging TaxRate cell, write the formula quickly with relative references and then change the TaxRate cell reference to absolute. Better yet, name the TaxRate cell. double-click the Fill handle, the formula or cell entry is filled all the way down the column until there’s no entry in the cell to the left.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 121 Hack If you only need to display a value from a different worksheet, start the formula with =, then click the cell on the other worksheet that you want to display and press Enter. To write a formula that includes a cell on another worksheet, click the sheet tab and click the cell (the sheet name and cell reference are entered in the formula, as shown in Figure 4.15).
122 Inside Scoop PART II ■ GETTING THE DATA IN Inside Scoop If you open a source workbook while the dependent workbook is open, the linking formula automatically recalculates with the current data in the source workbook. This is faster than waiting for recalculation from a closed workbook. The workbook with the formula is called a dependent workbook, because it depends on input from other workbooks.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 123 No matter what method you use to name cells, names must follow certain rules: ■ Names must start with a letter or an underscore character (_). ■ No spaces are allowed. For multiword names, use an underscore or, better yet, use initial capital letters to separate words, as in LastName.
124 PART II ■ GETTING THE DATA IN The Create Names dialog box If the names you want to use are already headings in a table or labels for specific cells (such as Total or TaxRate), the fastest way to name the cells to which the labels refer is the Create Names dialog box. The Create Names dialog box not only uses existing names (no typing), but it can also create several names at once (for example, it can name all the columns in a table using the table headings).
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 125 Figure 4.19. The Define Name dialog box Why would you want to define a tax rate in a named constant value instead of a named cell? Because it doesn’t appear in the worksheet, yet it is available to formulas throughout the workbook and all formulas can be updated by editing the named constant value. To define a name for a formula (for example, a long, complex formula with multiple nested segments), choose Insert Name Define (shown in Figure 4.20).
126 Inside Scoop PART II ■ GETTING THE DATA IN Inside Scoop You won’t see formula or constant names in the Name box or the Apply Names dialog box, but you can type them in formulas (so use easily remembered names). To use a named formula in a cell, type = and the formula name (as shown in Figure 4.21). If the formula is complex and you use it more than once a year, naming it saves a lot of time. Figure 4.21.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 127 a workbook with lots of formulas and 85 named ranges, many of them duplicates! (I had to do that for a client once, and it was not fun.) Names live on long after the data is deleted. To delete a name, choose Insert Name Define. In the Define Name dialog box, click the name you want to delete and click Delete, then click OK or Close (either button works). Select the name Edit the name’s reference Figure 4.22.
128 Inside Scoop PART II ■ GETTING THE DATA IN Inside Scoop If you want to remember what all of your names refer to without slogging through the Define Name dialog box, you can paste a list of all the workbook names and their definitions onto a worksheet. Click a cell, choose Insert Name Paste, and click the Paste List button. You can type defined names in formulas as you write them, or in the Function Arguments dialog box (covered in Chapter 5).
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 129 (as many cells as you like, including cells that don’t contain formulas), and choose Insert Name Apply. Click every name you want to apply and then click OK (see Figure 4.25). You can even name cells after you write the formulas and apply those names to the formulas that have cell references. Figure 4.25. Apply names to replace references in formulas.
130 PART II ■ GETTING THE DATA IN Editing formulas You can easily change a formula in any way (function, arithmetic operators, referenced cells, or constant values, which pretty much covers everything). To edit a formula, double-click the cell and select and replace whatever needs changing, as shown in Figure 4.26. Press Enter to finish your edits. Figure 4.26. You can edit a formula in the cell or in the Formula bar.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 131 Tracing a formula In some worksheets, formulas reference other formulas that reference still other formulas. When you need to dig into a complicated worksheet to understand its architecture, Excel has tools to help you. The process of tracing formulas is called auditing, and there’s a toolbar with buttons that do the work.
132 PART II ■ GETTING THE DATA IN Precedents back two levels Precedents back one level Trace Precendents Dependents forward one level Remove All Arrows Remove Precendents Remove Dependents Trace Dependents Figure 4.27. Tracing a formula Locating worksheet errors Errors and invalid data seem to sneak into even the most scrupulously designed and maintained worksheets. You can find them with the help of a few tools on the Formula Auditing toolbar. If you see an error, refer to Table 4.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 133 Table 4.2. Error values This error Usually means this To fix it, do this ##### The column isn’t wide enough to display the value. Widen the column. #VALUE! Wrong type of argument, value, or cell reference (for example, calculating a cell with the error value #N/A). Check values, references, and arguments; make sure references are valid. #DIV/0! Formula is attempting to divide by zero or by an empty cell.
134 Error Checking PART II ■ GETTING THE DATA IN Trace Error Figure 4.28. The Formula Auditing toolbar If a perceived error is located, the Error Checking dialog box appears and tells you what it thinks the error is. You can use any of the helpful buttons to understand and fix the error; if you know the error is not an error, click Ignore Error to continue the check.
CHAPTER 4 ■ WORKING DATA MAGIC WITH CALCULATIONS 135 Invalid data Circle Invalid Data Invalid data Figure 4.29. Circling invalid data in a data-validation range If data validation was not set up before the invalid data was entered, you can set up validation after the fact and then run the Circle Invalid Data button to find the bad data. Just the facts ■ Use AutoCalculate for instant, on-the-fly calculations and AutoSum to write fast, simple formulas.
136 PART II ■ GETTING THE DATA IN ■ Edit formulas by double-clicking to edit in the cell, or clicking to edit in the Formula bar. ■ Locate errors and invalid data with the buttons on the Formula Auditing toolbar.
