Objective 7. Format Percents, Move Formulas, and Wrap Text
After you have designed your worksheet, you will likely want to make additional adjustments to refine the layout of the data in your worksheet.
Activity 2.11. Formatting Cells with the Percent Style Button
A percentage is part of a whole expressed in hundredths. For example, .75 is the same as 75%. The Percent Style button on the Formatting toolbar formats the selected cell as a percentage rounded to the nearest hundredth.
|
1. |
Click cell F3 and notice the number 0.245713662.
This number is the result of dividing the value in E3 (Total Retail Value of Polo Shirts) by the total in cell E9 (Total Retail Value for All Products). Applying the Percent Style will result in 25%, which is 0.245713662 rounded up to the nearest hundredth and expressed as a percentage.
|
|
2. |
Select the range F3:F8, click the Percent Style button
Rounding percentages to the nearest hundredth may not display a percentage precise enough to analyze important financial information. To display more precise data, add additional decimal places. You can see, for example, that Polo Shirts represent almost 25% of the total retail value of all the products in the shop and that Sun Block is a small percentage of the total retail value of the shop's products.
|
|
3. |
Save
|
Activity 2.12. Inserting Rows in a Worksheet Containing Formulas
If you need to insert additional rows or columns, or move a formula that calculates values used by other formulas, Excel will adjust formulas for you. Additionally, you can edit formulas in the same manner as you edit text. In this activity, you will add a row for golf socks, which is another item carried by the pro shop.
|
1. |
Double-click cell E9 and confirm that the range finder shows the formula to be the sum of cells E3:E8. Press
|
||||
|
2. |
Click cell E6, and then from the Insert menu, click Cells. In the Insert dialog box, click the Entire row option button, and then click OK. Alternatively, you can click the row 6 heading and then, from the Insert menu, click Rows.
|
||||
|
3. |
Click cell E10. On the Formula Bar, notice that the range was edited and changed to sum the newly expanded range E3:E9.
As you design a worksheet, you do not have to think of all the possible rows and columns. You can easily insert new rows or columns, and then Excel edits the affected formulas.
|
||||
|
4. |
In cells A6:D6, type the following:
|
||||
|
5. |
Select the range E5:F5 to select the two formulas above the new row, and then drag the fill handle to fill both formulas down to E6:F6.
|
||||
|
6. |
Click cell E10. Move the pointer to the bottom edge of the cell until the Move pointer
Figure 2.32. |
||||
|
7. |
Drag the pointer downward until cell E11 is outlined, and then release the mouse button. Double-click cell E11. In cell E11 and on the Formula Bar, notice that the range, E3:E9, did not change when you moved the formula. Compare your screen with Figure 2.33.
Figure 2.33. If you move the formula to another cell, the cell references do not change.
|
||||
|
8. |
Press
|
Activity 2.13. Wrapping Text in a Cell
Title text often becomes the longest entry in a column. You can wrap text to two lines so that columns are not unnecessarily wide.
|
1. |
In the row heading area, point to the line between rows 2 and 3 to display the two-headed pointer
|
|
2. |
Select the range B2:F2. Display the Format Cells dialog box, and then click the Alignment tab. Under Text control, select the Wrap text check box. Under Text alignment, click the Horizontal arrow, and then click Center. Compare your screen with Figure 2.34.
Figure 2.34. The text in the selected cells will be wrapped onto more than one line when the cell is not wide enough to display the contents.
|
|
3. |
In the Format Cells dialog box, click OK.
The titles in cells C2, E2, and F2 wrap to two or more lines.
|
|
4. |
Click cell A2 and click Center
Figure 2.35. |
|
5. |
Save
|
|
[Page 730 (continued)] Objective 8 Make Comparisons Using a Pie Chart
|