Objective 6. Copy Formulas

1.

In cell E3, type =b3*d3 and press . Compare your worksheet with Figure 2.25.

Figure 2.25.

(This item is displayed on page 721 in the print version)

The Total Retail Value of all the Polo Shirts in the shop1125equals the Quantity (25) times the Retail Price (selling price) of 45 dollars. In Excel, the asterisk (*) represents multiplication.

2.

Take a moment to study the symbols you will use to perform mathematical operations in Excel, as shown in the table in Figure 2.26.

3.

Click cell E3.

You can see that in cells E4:E8, you need a formula similar to the one in E3, but one that refers to the cells in row 4, row 5, and so forth.

4.

With cell E3 selected, position your pointer over the fill handle in the lower right corner of the cell until the pointer displays. Then, drag downward through cell E8. Compare your screen with Figure 2.27.

Figure 2.27.

5.

Click cell E4, look at the Formula Bar, and notice the formula =B4*D4. Click cell E5, look at the Formula Bar, and notice the formula =B5*D5.

In each row, Excel copied the formula but adjusted the cell references relative to the row number. This is called a relative cell reference. The calculation is the same, but it is performed on the cells in that particular row. Use this quick method to insert numerous formulas into large spreadsheets.

6.

Select the range C3:E3 and click the Currency Style button to apply the Accounting format. Select the range C4:E8 and click the

Comma Style button . Click cell E9, click the AutoSum button , and then press .

The Accounting format is automatically applied to cell E9. Recall that the Currency Style button applies the Accounting format with dollar signs at the left edge of the cell, and space on the right for a right parenthesis in case of negative numbers. The Comma Style button applies the same format, but without the dollar sign. The Total Retail Value of the six products in inventory is $4,578.50. The format of the cell containing the formula is the same as the format of the cells used in the formula. If the formula uses a range of cells and those cells contain a mix of formats, the format from the cell in the upper left corner of the range is applied.

7.

Save your workbook.

1.

Click cell F3, type = and then click cell E3. Type / and then click cell E9.

The formula created, =E3/E9, indicates that the value in cell E3 will be divided by the value in cell E9. Why? Because Mr. Peterson wants to know the percentage by which each product's Total Retail Value makes up the Total Retail Value for All Products. Arithmetically, the percentage is computed by dividing the Total Retail Value for each product by the Total Retail Value for All Products. The result is a percentage expressed as a decimal.

2.

Press . Click cell F3 and notice that the formula displays in the Formula Bar. Then, point to cell F3 and double-click.

The formula, with the two referenced cells displayed in color and bordered with the same color, displays in the cell. This is the range finder, and is useful for verifying formulas or quickly positioning the insertion point within the cell to perform editing directly in the cell.

3.

Press to redisplay the result of the calculation in the cell. Click cell F3 again, and then drag the fill handle down through cell F8. Compare your screen with Figure 2.28.

Figure 2.28.

(This item is displayed on page 724 in the print version)

Each cell displays an error message#DIV/0!; each cell also displays a Trace Error triangle in the upper left corner, and the Auto Fill Options button displays.

4.

Click cell F4, and then point to the Trace Error button to display its ScreenTip, as shown in Figure 2.29.

Figure 2.29.

The ScreenTip indicates The formula or function used is dividing by zero or empty cells.

5.

Look at the Formula Bar to examine the formula.

The formula is =E4/E10. The cell reference to E4 is correct, but the cell reference following the division operator (/) is E10, and E10 is an empty cell.

6.

Click cell F5, and in the Formula Bar examine the formula =E5/E11.

7.

Point to cell F3 and double-click to have the range finder display the cell's formula and place the insertion point within the cell. Alternatively, click in the Formula Bar to place the insertion point for editing the formula there.

8.

Edit the formula so that it indicates =E3/$E$9 and then compare your screen with Figure 2.30.

Figure 2.30.

To make a cell reference absolute, dollar signs are inserted into the cell reference. The use of the dollar sign to denote an absolute reference is not related in any way to whether or not the values you are working with are currency values. It is simply the symbol used by Excel to denote an absolute cell reference.

9.

On the Formula Bar, click the Enter button so that F3 is still the active cell. Then, drag the fill handle to copy the formula down through cell F8. Compare your screen with Figure 2.31.

Figure 2.31.

10.

Click in several of the copied cells and look at the formula in the Formula Bar.

You can see that for each formula, the cell reference for the Total Retail Value of each product changed relative to its row, but the value used as the divisorTotal Retail Value for All Products in cell E9remained absolute. Thus, using either relative or absolute cell references, it is easy to duplicate formulas without typing them.

11.

Save your workbook.

[Page 726 (continued)]

Objective 7 Format Percents, Move Formulas, and Wrap Text