Microsoft Office Tutorials and References
In Depth Information
In the Lab
In the Lab continued
Instructions Part 1: Create the table shown in Figure 5–75 on the previous page using the techniques
learned in this chapter and following the instructions below.
1. Bold the entire worksheet.
2. Enter the table title in row 6 and apply the Title cell style. Enter and format the ﬁ eld names
in row 7.
3. Use the Format as Table button on the Home tab on the Ribbon to create a table using data from
the range A7:J7. Use Table Style Medium 3 to format the table. Format the ﬁ rst row below the
ﬁ eld names and then enter the rows of data shown in rows 8 through 20 of Figure 5–75. Change
the Sheet1 tab name to Whitman Publishing Sales Reps and delete Sheet2 and Sheet3.
4. With a cell in the table active, click the Design tab on the Ribbon and then click the Total Row
check box in the Table Style Options group. Show the record count in the Gender column, the
average age in the Age column, and sums in the Quota and Sales columns as shown in Figure 5–75.
5. Add the icon set 3 arrows (colored) using conditional formatting to the Age column (G8:G20): Age
>=50; 35=<Age<50; Age<35 as shown in Figure 5–75. To add the conditional formatting, select the
range G8:G20, click the Conditional Formatting button on the Home tab on the Ribbon, and click
the New Rule command. When Excel displays the New Formatting Rule dialog box, click the Icon
Style box arrow, scroll up and click 3 Arrows (Colored). Click the Value box and enter the Age limits
described earlier. Change the Type boxes to Number.
6. Change the document properties as speciﬁ ed by your instructor. Change the worksheet header
with your name, course number, and other information requested by your instructor.
7. Use the Orientation button on the Page Layout tab on the Ribbon to change the orientation to
Landscape. Print the table. Save the workbook using the ﬁ le name, Lab 5-1 Whitman Publishing
Sales Rep Table. Submit the assignment as requested by your instructor.
Instructions Part 2: Open the workbook Lab 5-1 Whitman Publishing Sales Rep Table created in Part 1.
Do not save the workbook in this part. Step through each query exercise in Table 5–7 and print (or write
down for submission to your instructor) the results for each. To complete a ﬁ lter exercise, use the AutoFilter
technique. If the arrows are not showing to the right of the column headings when the table is active, then
click the Filter button on the Data tab on the Ribbon. Select the appropriate arrow(s) to the right of the ﬁ eld
names and option(s) on the corresponding menus. Use the Custom Filter option on the Number Filters list
for ﬁ eld names that do not contain appropriate selections. Following each query, click the Filter button on
the Data tab on the Ribbon twice to clear the query and reactivate the arrows in the ﬁ eld names. You should
end up with the following number of records for Filters 1 through 12: 1 = 7; 2 = 6; 3 = 1; 4 = 2; 5 = 4; 6 = 1;
7 = 2; 8 = 3; 9 = 0; 10 = 4; 11 = 9; and 12 = 13. When you are ﬁ nished querying the table, close the workbook
without saving changes. Submit the assignment as requested by your instructor.