1

Openexploring_e05_grader_h3_Fiesta.xlsxand save it as exploring_e05_grader_h3_Fiesta_LastFirst.xlsx.

0

2

Create a relationshipbetween the Items table using the Color Number field and theColors table using the Color Number field. Create a blankPivotTable from within the Items table on the Collectionworksheet to analyze multiple tables. Add the data to the datamodel. Place the PivotTable on a new worksheet and name theworksheet RetiredColors.Name the PivotTable Retired.

3

3

Display the names ofboth tables in the PivotTable Fields task pane. Display the Colorfield as rows and the sum of the Replacement Value field asvalues.

2

4

Add a filter to displayaggregates for retired colors only using the Retired field. Notethat current colors do not have a retirement date, so you mustfilter out the blanks.

2

5

Apply the Pivot StyleMedium 7. Note, depending on the version of Office used, thestyle name may be Light Green, Pivot Style Medium 7.

1

6

In the Retired Colorssheet, format the values with Accounting Number Format with twodecimal places.

1

7

In cell B3, create acustom heading named ReplacementValue.Change Row Labelsin cell A3 to RetiredColors.

1

8

Add a second ReplacementValue field below the current field in the Values area. Selectthe option to display the values as percentages of the grandtotal. Type the custom name Percentof Total.

2

9

Add a slicer for theColor field. Select these colors to display: Apricot, Chartreuse,Lilac, Marigold, Pearl Gray, and Sapphire.

2

10

Apply the Slicer StyleLight 6 style. Note, depending on the version of Office used, thestyle name may be Light Green, Slicer Style Light 6. Displaythree columns within the slicer window. Change the button widthto 1.5inches. Move the slicer so that the top-left corner starts incell E2.

2

11

Create a clusteredcolumn PivotChart and place it on a new chart sheet namedRetired
PivotChart.

1

12

Change the chart titleto ReplacementValue of Retired Items.Change the Lilac data point fill color to Purple. Change thevalue axis font size to 11 and apply Black, Text 1 font color.

1

13

Change the category axisfont size to 11 and apply Black, Text 1 font color. Hide thefield buttons on the PivotChart.

1

14

On all sheets, create afooter with your name on the left side, the sheet name code inthe center, and the file name code on the right side.

1

15

Ensure the worksheetsare in the following order: Color List, Retired PivotChart,Retired Colors, Collection. Save and close the workbook, andsubmit the file as directed.

0