Excel grader project collections 1.1 Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instr
Excel grader project collections 1.1 Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points Possible
1
Open exploring_e05_grader_h3_Fiesta.xlsx and save it as exploring_e05_grader_h3_Fiesta_LastFirst.xlsx.
0
2
Create a relationship between the Items table using the Color Number field and the Colors table using the Color Number field. Create a blank PivotTable from within the Items table on the Collection worksheet to analyze multiple tables. Add the data to the data model. Place the PivotTable on a new worksheet and name the worksheet Retired Colors. Name the PivotTable Retired.
3
3
Display the names of both tables in the PivotTable Fields task pane. Display the Color field as rows and the sum of the Replacement Value field as values.
2
4
Add a filter to display aggregates for retired colors only using the Retired field. Note that current colors do not have a retirement date, so you must filter out the blanks.
2
5
Apply the Pivot Style Medium 7. Note, depending on the version of Office used, the style name may be Light Green, Pivot Style Medium 7.
1
6
In the Retired Colors sheet, format the values with Accounting Number Format with two decimal places.
1
7
In cell B3, create a custom heading named Replacement Value. Change Row Labels in cell A3 to Retired Colors.
1
8
Add a second Replacement Value field below the current field in the Values area. Select the option to display the values as percentages of the grand total. Type the custom name Percent of Total.
2
9
Add a slicer for the Color field. Select these colors to display: Apricot, Chartreuse, Lilac, Marigold, Pearl Gray, and Sapphire.
2
10
Apply the Slicer Style Light 6 style. Note, depending on the version of Office used, the style name may be Light Green, Slicer Style Light 6. Display three columns within the slicer window. Change the button width to 1.5 inches. Move the slicer so that the top-left corner starts in cell E2.
2
11
Create a clustered column PivotChart and place it on a new chart sheet named Retired
PivotChart.
1
12
Change the chart title to Replacement Value of Retired Items. Change the Lilac data point fill color to Purple. Change the value axis font size to 11 and apply Black, Text 1 font color.
1
13
Change the category axis font size to 11 and apply Black, Text 1 font color. Hide the field buttons on the PivotChart.
1
14
On all sheets, create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.
1
15
Ensure the worksheets are in the following order: Color List, Retired PivotChart, Retired Colors, Collection. Save and close the workbook, and submit the file as directed.
0
