Microsoft Office Tutorials and References
In Depth Information
EX 150
Excel Chapter 2 Formulas, Functions, Formatting, and Web Queries
In the Lab continued
Instructions Part 1: Perform the following tasks to build the worksheet shown in Figure 2–88.
1. Apply the Aspect theme to the worksheet by using the Themes button on the Page Layout tab on the
Ribbon.
2. Increase the width of column A to 19.00 points and the width of columns B through F to 13.50 points.
3. Enter the worksheet title Facade Importers in cell A1 and the worksheet subtitle Sales
Analysis in cell A2. Enter the column titles in row 3 as shown in Figure 2–88. In row 3, use
ALT + ENTER to start a new line in a cell.
4. Enter the sales data described in Table 2–8 in columns A, B, C, and E in rows 4 through 9. Enter
the row titles in the range A10:A14 as shown in Figure 2–88 on the previous page.
5. Obtain the net sales in column D by subtracting the sales returns in column C from the sales
amount in column B. Enter the formula in cell D4 and copy it to the range D5:D9.
6. Obtain the above quota amounts in column F by subtracting the sales quota in column E from the
net sales in column D. Enter the formula in cell F4 and copy it to the range F5:F9.
7. Obtain the totals in row 10 by adding the column values for each salesperson. In the range B11:B13,
use the AVERAGE, MAX, and MIN functions to determine the average, highest value, and lowest
value in the range B4:B9. Copy the range B11:B13 to the range C11:F13.
8. Determine the percent of quota sold in cell B14 by dividing the total net sales amount in cell D10
by the total sales quota amount in cell E10. Center this value in the cell.
9. If necessary, click the Home tab on the Ribbon. One at a time, merge and center the worksheet
title and subtitle across columns A through F. Select cells A1 and A2 and change the background
color to red (column 2 in the Standard Colors area on the Fill Color palette). Apply the Title cell
style to cells A1 and B1 by clicking the Cell Styles button on the Home tab on the Ribbon and
clicking the Title cell style in the Titles and Headings area in the Cell Styles gallery. Change the
worksheet title in cell A1 to 28-point white (column 1, row 1 on the Font Color gallery). Change
the worksheet subtitle to the same color. Assign a thick box border from the Borders gallery to the
range A1:A2.
10. Center the titles in row 3, columns A through F. Apply the Heading 3 cell style to the range A3:F3.
Use the Italic button on the Home tab on the Ribbon to italicize the column titles in row 3 and the
row titles in the range A10:A14.
11. Apply the Total cell style to the range A10:F10. Assign a thick box to cell B14. Change the
background and font colors for cell B14 to the same colors applied to the worksheet title in Step 9.
12. Change the row heights of row 3 to 33.00 points and rows 11 and 14 to 30.00 points.
13. Select cell B14 and then click the Percent Style button on the Home tab on the Ribbon. Click the
Increase Decimal button on the Ribbon twice to display the percent in cell B14 to hundredths.
14. Use the CTRL key to select the ranges B4:F4 and B10:F13. That is, select the range B4:F4 and
then while holding down the CTRL key, select the range B10:F13. Use the Format Cells: Number
Dialog Box Launcher button on the Home tab on the Ribbon to display the Format Cells dialog
box to assign the selected ranges a Floating Dollar Sign style format with two decimal places and
parentheses to represent negative numbers. Select the range B5:F9 and click the Comma Style
button on the Home tab on the Ribbon.
15. Rename the sheet tab as Sales Analysis. Change the document properties, as speciﬁ ed by your
instructor. Change the worksheet header with your name, course number, and other information