| Step |
Instructions |
Points Possible |
| 1 |
Open the downloaded file go16_xl_ch07_grader_7g_hw.xlsx. Save the file as Lastname_Firstname_7G_Aquatics in your Excel Chapter 7 folder. |
0 |
| 2 |
Click cell A2, and then insert a Recommended PivotTable by choosing the Blank PivotTable option in the lower left corner of the dialog box. Filter by the Month field. Use the Pool field as the row labels and the Item field as the column labels. Place the Amount field in the Values area. Close the PivotTable Fields list. Note, Mac users insert a PivotTable and then arrange the fields as indicated. |
10 |
| 3 |
Format the values in the PivotTable using the Number category to display zero decimal places and the 1000 Separator. |
5 |
| 4 |
Insert a slicer for the Pool field and for the Item field. Apply Slicer Style Light 4 and Slicer Style Light 6 to the slicers, respectively.
Note: The style names may be Light Yellow, Slicer Style Light 4 and Light Green, Slicer Style Light 6, respectively, depending on the version of Office used. |
10 |
| 5 |
By using the two slicers, filter the data to show, for the Tropics Pool, the total revenue for Spa Fees and Swim Lessons. Recall that you can use CTRL to select multiple filters in a slicer. Move the two slicers to the right of the filtered PivotTable, and then make a copy of this worksheet. Name the copied worksheet Tropics Pool.
Note, Mac users, after creating and naming the sheet, on the PivotTable Analyze tab, click Filter Connections. Remove all check marks. |
6 |
| 6 |
Display Sheet1 and clear the filters from the slicers and remove the slicers from the worksheet. Rename the sheet 2Q Revenue |
5 |
| 7 |
Insert a PivotChart using the Stacked Column chart type. Move the chart to a new worksheet named 2Q Revenue Chart
Note, Mac users select the range A4:G10 in the PivotTable. On the Insert tab, click Recommended Charts, and then click Stacked Column to make the chart. Move the chart to a new worksheet named 2Q Revenue Chart |
5 |
| 8 |
Apply the Layout 3 chart layout and Chart Style 9. Add centered data labels, and then hide all of the field buttons on the chart. As the Chart Title, type 2nd Quarter Aquatics Program Revenue and then insert a custom footer with the file name in the left section. |
7 |
| 9 |
Add a new worksheet to the workbook, and then import the Access database e07G_Pool_Supplies.accdb into the Data Model as a PivotTable Report using both tables in the database. Rename the sheet Supplies PivotTable
Note, Mac users, import the data from the downloaded, comma-delimited text file Pool_Supplies.csv and then convert the data to a table with headers. Insert a PivotTable using the imported data. Rename the PivotTable sheet Supplies PivotTable |
5 |
| 10 |
To create the PivotTable, place the Supplier field in the Columns area. Place the Item field in the Rows area, and the Quantity field in the Values area. Apply the Number format to the values in the PivotTable with zero decimals and the 1000 separator. |
5 |
| 11 |
Insert a row above the PivotTable, if necessary, so that the PivotTable begins in cell A2. In cell A1, type Aquatics Program Purchases and then apply the Title cell style. |
5 |
| 12 |
Click in the PivotTable, and then insert a 3-D Pie PivotChart. Move the chart to a new sheet with the name Aquatic Supplies Chart
Note, Mac users continue to next step. |
9 |
| 13 |
Modify the chart so that only the Supplier field displays in the Axis (Category) area. Remove any fields that display in the Legend (Series) area.
Note, Mac users display the Field pane and remove Item field from the Row area. Move the Suppliers field to the Rows area. Select the range A3:B7 in the PivotTable. On the Insert tab, click Pie, and then click 3-D Pie to create a traditional chart. Move the chart to a new sheet with the name Aquatic Supplies Chart |
4 |
| 14 |
Apply Style 7, remove the legend from the chart, and display only the Category Name and Percentage data labels positioned in the Center. Format the data labels by changing the font size to 12, the Font color to Black, Text 1, and then apply Bold. |
6 |
| 15 |
Change the chart title to Aquatic Supply Purchases and then change the font size to 24. Hide all of the field buttons on the chart, and then insert a footer with the file name in the left section. |
10 |
| 16 |
Hide the Aquatics Revenue sheet. Select all the sheets and insert a footer in the left section that includes the file name, and then center the worksheet Horizontally on the page. Change the Orientation to Landscape and scale the Width to fit to 1 page. |
6 |
| 17 |
Display the properties. As the Tags/Keywords, type aquatic revenue, supplies and as the Subject, type your course name and section #. Under Related People, be sure that your name displays as Author. Save your workbook. |
2 |
| 18 |
Save and close the file, and then submit for grading. |
0 |