Microsoft Office Tutorials and References
In Depth Information
Cases and Places
Apply your creative thinking and problem solving skills to design and implement a solution.
• E ASIER •• M ORE D IFFICULT
• 1: Inventory Level Priority
Create an inventory table from the data in Table 5–8. Also include an Amount ﬁ eld and a Priority ﬁ eld.
Both are calculated columns. Amount equals Inventory times Price. Create a Priority Code table in the
range I1:J6 using the data shown in Table 5–9. Use the VLOOKUP function to determine the priority
to assign to each record. Add the total row to the table. Print the worksheet in landscape orientation
using the Fit to option. Save the workbook.
Table 5–9 Priority Codes
Table 5–8 Educational Percussion, Inc. Inventory List
• 2: Conditional Formatting and Sorting a Table
Open the table created in Cases and Places Exercise 1. Add conditional formatting to the Priority ﬁ eld
using the Icon Sets format style and the 5 Ratings icon style. Complete the following three sorts, print
each sorted version of the table, and then undo the sorts in preparation for the next sort: (a) sort the
table in ascending sequence (smallest to largest) by inventory, (b) sort the table by amount (ascending)
within priority code (descending), and (c) sort the table in descending sequence by priority code. With
the table sorted by priority, toggle off the total row, convert the table to a range, and then use the
Subtotal button on the Data tab on the Ribbon to determine subtotals for each priority code. Print the
table with the subtotals. Save the workbook with the subtotals.
• 3: Filtering a Table and Multiple Conditional Formats
Open the table created in Cases and Places Exercise 1. Add a second conditional format to the priority
code ﬁ eld using a Data Bar format style and a Bar color of your choice. If necessary remove the subtotals
and then convert the range back to a table. Filter (query) the table using the column heading arrows.
Make sure you show all records before each query. Print the table for each of the following queries:
(1) priority code equal to 2, (2) inventory greater than 250 and less than 600, (3) priority code equals 1
and inventory greater than 30, and (4) price greater than 9.00. The number of records that show in the
queries are: (1) 3, (2) 5, (3) 2, and (4) 8.