Using AutoSum

Using AutoSum

The AutoSum button on the Standard toolbar is one of the most useful tools in Excel. AutoSum automatically totals a range of values. Click into the cell where you want the total to appear and click the AutoSum button. The SUM formula appears in the cell and a marquee surrounds the range of values in the column. Click the AutoSum button again. A total of the range in the column directly appears in the last cell.

AutoSum can total cells in a row, as well as a column. Click the first empty cell in a row that contains values and click AutoSum, as illustrated in Figure 5.8. The SUM formula appears in the cell and a marquee surrounds the range of values in the row. Click the AutoSum button again. The total of the preceding cells appears in the last cell.

AutoSum adds ranges that contain values. If an empty cell appears in the column or row, AutoSum does not add cells that appear before the blank one.

Using Range Names in Formulas

Using range names can sometimes help you create and troubleshoot formulas. The use of range names is especially important if more than one person is going to be working on the file, because names are easier to understand than cell references. For example, a cell named SALES_TAX would make a sales tax calculation easy to understand.

Review the information in Hour 4, "Managing Your Files and Workbooks," if you need help naming a range.

You must name a range before you can use it in a formula. If you remember the range name, you can type it into the formula in place of a cell reference. Remember, though, that you must type the range name perfectly , or Excel won't accept it. If you don't remember the exact name of a range, let Excel provide the name for you.

When you come to the place in the formula where you want to insert a range name, click the Insert menu and choose Name and then Paste. The Paste Name dialog box, as displayed in Figure 5.9, appears. Select the range name you want to include in the formula and click OK. The box closes , and the range name is included in the formula.

Figure 5.9. Choose a range name to include in your formula.

You can select the cells to name and then type a name in the Name Box that appears above the Column A heading. For example, if you select A1:A9 and label it Salaries, you can enter =SUM(Salaries) in some other cell.

Using Relative and Absolute Addressing

When you enter a formula that contains cell references into a cell, Excel keeps track of it in two ways. The first is to record the value of that cell and use the value in the calculation. The second way is to keep track of the relative position of the cells in the formula to one another.

Here's how relative addressing works if you enter the formula =a1+a2 in cell A3.

If the formula could talk, it would say, "Take the cell two rows above me and add the value of that cell to the cell one row above me and display the results in my cell." (Talking formulas would be great, wouldn't they!) If you copied the formula in A3 to C3, the new formula would read =C1+C2 . Why? Because the new formula would be looking at the cells one and two rows above it.

Relative cell referencing is great if you're adding a number of like columns . Imagine how tedious it would be to have to create a formula in the total column for each day. With relative cell referencing, you create the formula in the first total column for January and then copy it to all the other months.

What if your calculation isn't so straightforward? Suppose you want to calculate the projected payroll for the next six months. You would multiply the percentage amount of increase by the current payroll amount. For example, The formula in cell B9 would be =B18*B6 , as shown in Figure 5.10. When you copy the formula to cells in other columns, Excel adjusts the cell references for each column automatically.

After you copy the formula in cell B9 to C9, the February payroll projection in cell C9 would be =C18*C6 . Although the cell reference C6 (February revenue) is correct, the cell reference C18 references an empty cell. The formula for cell C9 should be =B18*C6 rather than =C18*C6 . In this case, you need to keep a cell reference in a formula the same when you copy the formula.

To keep a cell reference constant when you copy a formula or function, Excel uses absolute referencing . The concept of absolute cell referencing is somewhat difficult to understand. To make it easier to understand, keep in mind that the paste function is the only one affected by an absolute cell reference. An absolute cell reference tells the paste function to keep the same cell reference as it copies a formula from one cell to another.

When you're entering a cell reference into a cell, you can type the dollar signs to make the reference absolute. Or, if you're using the mouse to point to the cells you want to use in the formula, click the cell and press F4. The dollar sign character appears before both the column and row indicators, meaning the cell reference is absolute.

To make only the column or row portion of a cell address absolute, press F4 again. Each time you press the F4 key, the $ moves to a different coordinate of the cell address. For example, $A$1 becomes A$1 , $A1 , and so on each time you press the F4 key.

=SUM(A1:A5)