Create a spreadsheet step by step photo snapshots

FILE:https://drive.google.com/file/d/1ZlIRhuFnj73tGBlXtwIGTW42JXOnsiK-/view?usp=sharing

1. Open e05m2Fiesta and save it as e05m2Fiesta_LastFirst.(Information of speadsheets at bottom of page)

2. Create a relationship between the Items table using the Color Number field and the Colors table using the Color Number field.

3. 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.

4. Display the names of both tables in the PivotTable Fields List.

5. Display the Color field as rows and the sum of the Replacement Value field as values.

6. 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.

7. Apply the Pivot Style Medium 7.

8. Format the values with Accounting Number Format with 2 decimal places. Create a custom name Replacement Value. Change Row Labels in cell A3 to Retired Colors.

9. Add a column to show calculations by completing the following steps:

   • 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.

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

11. Customize the slicer by completing the following steps:

    • Apply the Slicer Style Light 6 style.

    • Display 3 columns within the slicer window.

    • Change the button width to 1.5". Move the slicer so that the top-left corner starts in cell E2.

12. Create a clustered column PivotChart and place it on a new chart sheet named Retired PivotChart.

13. Modify the chart by completing these steps:

    • 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.

    • Change the category axis font size to 11 and apply Black, Text 1 font color.

    • Hide the field buttons on the PivotChart.

14. 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 of all worksheets.

15. Save and close the file. Based on your instructors directions, submit e05m2Fiesta_LastFirst.