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
Item number
Description
Inventory
Price
Inventory
Priority
0
1
B60338
Bar Chime
619
14.25
250
2
M44910
Maraca
873
9.50
400
3
C71610
Castanet
579
17.60
600
4
S80787
Shekere
537
22.50
800
5
T36275
Tambourine
764
12.45
T74695
Triangle
208
8.30
W59366
Woodblock
268
7.95
C24890
Clave
385
13.80
C87343
Cabasa
387
14.05
W15840
Whistle
699
6.85
C49955
Cowbell
237
18.25
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.

Search JabSto ::

Custom Search