Microsoft Office Tutorials and References
In Depth Information
In the Lab
Instructions Part 1: Start Excel and perform the following tasks.
1. Open the workbook Lab 5-2 Consultant Specialist Table from the Data Files for Students. See
the inside back cover of this topic for instructions for downloading the Data Files for Students
or see your instructor for information on accessing the ﬁ les required in this topic. Do not save
the workbook in this part.
2. Complete the following tasks:
a. Sort the records in the table into descending sequence by name. Susie Wongley should appear
ﬁ rst in the table. Li Bhua should appear last. Print the table. Undo the sort.
b. Sort the records in the table by age within gender. Select ascending sequence for the age and
descending sequence for gender. Li Bhua should be the ﬁ rst record. Print the table. Undo the sort.
c. Sort the table by Java within C++ within C# within VB. Apply sort descending for all four
ﬁ elds. Sort the table ﬁ rst on Java, then C++, then C#, and ﬁ nally VB. Those who are proﬁ cient
in all four programming languages will rise to the top of the table. Jorge Diaz should be the
ﬁ rst record. Print the table. Close the workbook without saving it. Submit the assignment as
requested by your instructor.
Instructions Part 2: Open the workbook Lab 5-2 Consultant Specialist Table (Figure 5–76) from the
Data Files for Students. Do not save the workbook in this part. Select a cell within the table. If the col-
umn heading arrows do not appear, then click Filter on the Data tab on the Ribbon. Use the column
heading arrows to ﬁ nd the records that meet the criteria in items 1 through 4 below. Use the Show All
command on the Filter submenu before starting items 2, 3, and 4. Print the table for each problem.
You should end up with the following number of records for items 1 through 4: item 1 should have
6; item 2 should have 3; item 3 should have 3; and item 4 should have 7. Close the workbook without
saving the changes. Submit the assignment as requested by your instructor.
1. Find all records that represent employees who are male and are proﬁ cient in Java.
2. Find all records that represent employees with more than 9 years of experience (Yrs) and who are
certiﬁ ed in VB and HTML.
3. Find all records that represent female employees who are at least 30 years old and are proﬁ cient in
4. Find all records that represent employees who have at least 5 years of experience (Yrs) and who are
proﬁ cient in VBA and HTML.
Instructions Part 3: Open the workbook Lab 5-2 Consultant Specialist Table from the Data Files for
Students and then save the workbook using the ﬁ le name, Lab 5-2 Consultant Specialist Table Final.
Perform the following tasks:.
1. Add a criteria range by copying the table title and ﬁ eld names (range A9:M10) to the range A1:M2
(Figure 5–77). Change cell A1 to Criteria Area and then color the title area as shown in Figure 5–77.
Use the Name box in the formula bar to name the criteria range (A2:M3) Criteria.