Objective 5. Design a Worksheet

2017-11-03 09:05:06

Use good design techniques to make your worksheet as useful as possible for the reader. Good design techniques include the following: generally, it is best to use rows rather than columns for the most abundant data; if you will print the worksheet, consider how it will look on paper; and, arrange the data so that it is easily sorted and charted. For example, row and column titles that will be used as chart labels should be shortespecially when there are numerous rows or columns.

Activity 2.7. Setting Column Widths and Creating Row and Column Titles

The worksheet title is typically placed in the first row and centered above the columns of data. Thus, it is practical to design the column titles in the second row before you enter the worksheet title.

With Excel open, from the File menu, click New. In the New Workbook task pane, under New, click Blank workbook. Alternatively, on the Standard toolbar, click the New button.

If the Excel program is already open, these are the techniques you can use to begin a new workbook.

From the File menu, display the Save As dialog box, navigate to your chapter folder, and in the File name box, type 2B_Inventory_Value_Firstname_Lastname and then click Save.

Click cell A2, type Item in Inventory and then press . In cell B2, type Quantity and press , type Average Cost and press , type Retail Price and press .

Some of the columns are not wide enough to display all the data; you will adjust the column widths later in this activity.

In cell E2, type Total Retail Value and press , type Percent of Total Retail Value and press . In cell A3 type Polo Shirts and press . Type the following row titles in cells A4:A9 and then compare your screen with Figure 2.21.

T-Shirts
Hats
Sun Block
Logo Golf Balls (3-pack)
Golf Gloves
Total Retail Value for All Products

Figure 2.21.

Click cell A9 and apply Bold . With cell A9 still selected, display the Format menu, point to Column, and then click AutoFit Selection. Save your workbook.

This command adjusts the width of a column to accommodate the value in the selected cell, cell A9 in this instance.

Select columns B:D. From the Format menu, point to Column, and then click AutoFit Selection. Click in any empty cell to cancel the selection.

When an entire column is selected, this command adjusts the width of each selected column to accommodate the column's longest entry.

Select columns E:F. With the two columns selected, in the heading area, point to the right boundary line of either column to display the pointer, and then double-click.

This technique will set the column width to accommodate the longest entry in each columnin the same manner as using the command from the menu in Step 6.

Another Way: To Set Column Widths

You can set the width of a column to an exact measurement from the Column Width dialog box. Click any cell in the column, display the Format menu, point to Column, and then click Width.

Click cell A1 and type Golf Course Pro Shop Inventory and then on the Formula Bar click the Enter button . Select the range A1:F1, and then click the Merge and Center button . Change the Font to Arial Black and the Font Size to 16.

It is good practice to create your row and column titles before entering the worksheet title in row 1, so that you can center your worksheet title over only the columns that contain data.

Recall that serif fonts have extensions, or lines, on the ends of the characters. Sans serif fonts like Arial Black do not have lines on the ends of characters. Because worksheets often contain more numbers than text, a sans serif font is a good choice for worksheets.

With cell A1 still selected, on the Formatting toolbar, click the Fill Color button arrow . From the displayed color palette, in the last row click the third colorLight Yellow. Click the Font Color button arrow , and then in the second row, click the fourth colorGreen. Compare your screen with Figure 2.22. Save your workbook.

Figure 2.22.

The title Golf Course Pro Shop Inventory is formatted with a different font, font size, font color, and background to distinguish it visually from the worksheet data.

Activity 2.8. Entering Data Using Time-Saving Techniques

Excel has time-saving techniques to assist in rapid entry of data in a worksheet. In this activity, you will enter data by first selecting a range of cells. Using the numeric keypad on your keyboard will make this technique even faster.

Select the range B3:D8, type 25 and then press .

The first value is entered in cell B3 and cell B4 becomes the active cell.

Beginning in cell B4 and pressing after each entry, type the following and then compare your screen with Figure 2.23:

75
45
85
150
35

Figure 2.23.

After you enter the last value and press , the active cell moves to the top of the next column within the selected range. Selecting the range in this mannerbefore you enter datasaves time because it confines the movement of the active cell to the selected range.

From the following table, beginning in cell C3 and pressing after each entry, enter the data for the Average Cost and Retail Price columns. Then compare your screen with Figure 2.24.

Figure 2.24.

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

Average Cost	Retail Price
22.7	45
6.5	12.5
7.5	15.75
.75	1.85
4.75	7.5
4.2	15

Recall that the default number format for cells is the General number format, in which numbers display as you type them and trailing zeros will not display, even if you type them. You will format the numbers later in this project.

Save your workbook.

[Page 720 (continued)]

Objective 5. Design a Worksheet

Objective 6 Copy Formulas

Категории