Open the Shirts workbook located in the Excel EX 320 > Case1 folder included with your Data Files, and then save the workbook as Shirts Inventory in the location specified by your instructor.
2. In the Documentation worksheet, enter your name and the date.
3. In the Shirts worksheet, create an Excel table using all of the data in the worksheet. Rename the table as Shirts Tbl. Format the table with Table Style Medium 9. Change the Price data to the Currency format showing no decimal places. Change the In Stock data to the Number format with no decimals.
4. Make a copy of the Shirts worksheet, and then rename the copied worksheet as Sort by StyIe. (Hint: Press the Ctrl key as you drag and drop the Shirts sheet tab to the right of the Shirts sheet tab to make a copy of the worksheet.)
5. In the Sort by Style worksheet, sort the data in ascending order by Style, and then in descending order by In Stock.
6. Filter the ShirtsTbl table by Size to remove the youth extra small (yxsm) and ladies extra small (Ixsm) sizes.
7. Insert a Total row that shows the total shirts In Stock. Change the Total row label to Total Shirts.
8. Split the worksheet window into two horizontal panes. Place the split bar two rows above the bottom row of the worksheet. In the top pane, display the shirt data. In the bottom pane, display only the Total row.
9. Make a copy of the Shirts worksheet, and then rename the copied worksheet as Filter by Color. In the Filter by Color worksheet, filter the ShirtsTbl table to display only T-shirt style.
10. Insert a slicer for Color, position the slicer so its upper-left corner is in cell Gl, resize the slicer’s height to 1.8″ and its width to 1.2″, and then format the slicer with Slicer Style Dark 1.
11. Use the Color slicer to further filter the Shirts Tbl table to display only blue T-shirts and white T-shirts.
12. Filter the Shirts Tbl table so that it displays only blue and white T-shirts with a price greater than $10. Sort the filtered data in ascending order by Price and then in descending order by In Stock.
13. Make a copy of the Shirts worksheet, and then rename the copied worksheet as Subtotals. Convert the table to a range because the Subtotal command cannot be used with an Excel table. Sort the table in ascending order by Style. Use the Subtotal command to display the minimum In Stock for each Style.
14. Based on the Shirts Tbl table in the Shirts worksheet, insert a PivotTable in a new worksheet that calculates the total In Stock for each Style and Color. Display both Style and Color in rows. Use the Value Field Settings dialog box to rename Sum of In Stock as Total Inventory. Apply the Pivot Style Medium 9 style to the PivotTable. Rename the worksheet as PivotTable by Style and Color.
15. In the PivotTable by Style and Color worksheet, insert a PivotChart with the Clustered Column chart subtype. Place the PivotChart to the right of the PivotTable. Remove the legend. Filter the PivotChart to exclude any white shirts. Change the chart title to Inventory by Style and Color.
16. Based on the ShirtsTbl table in the Shirts worksheet, insert a PivotTable in a new worksheet that displays the total In Stock and count of Item IDs by Style and Color. Place Style in the FILTERS area. Rename the worksheet as PivotTable by Style.
17. In the PivotTable by Style worksheet, format the PivotTable with Pivot Style Medium 2 style. In the Value Field Settings dialog box, rename the Count of Item ID as Number of Shirts and change the Number format to Number with no decimal places. Change the Number format of the Sum of In Stock to the Number format with no decimal places.
18. In the PivotTable, change the Style filter to show only Jersey.
19. Save the workbook, and then close it.
