# excel project 61

Step Instructions Point Value
1 Start Excel. Open, save, the downloaded Excel workbook named go16_xl_ch07_grader_7e_as.xlsx. 0
2 Click cell A2. Insert the Recommended PivotTable, Sum of Amount by Location. Rearrange the fields so that Location is the first field in the Rows area, and then Month. Place the Program field in the Columns area. Close the PivotTable Fields pane. 10
3 Insert a slicer for the Location field and for the Program field. Apply Slicer Style Light 5 to the Location slicer and Slicer Style Light 4 to the Program slicer.
Note, depending upon the version of Office used, the Slicer style names may be Ice Blue, Slicer Style Light 5 and Light Turquoise, Sliver Style Light 4, respectively.
10
4 Using the Location and Program slicers, filter the data to show the total revenue for pre-school in Grove Beach Recreation Center.

8
5 Change the height of each slicer to 1.5″. Reposition the Program slicer so that the top left corner is positioned in cell E3. Reposition the Location slicer so that the top left corner is positioned in cell E12. 6
6 Modify the value field settings in the PivotTable so that values are formatted as numbers with zero decimal places and a 1000 separator. 8
7 Make a copy of the Sheet1 worksheet. Rename the copied worksheet Grove 1Q Pre-School. Rename the Sheet1 worksheet 1Q Revenue. Note, Mac users, on the IQ Revenue sheet, on the ribbon, click Filter Connections. Remove both check marks.

6
8 On the 1Q Revenue worksheet, clear all filters and remove the slicers. In cell A1, type First Quarter Youth Program Revenue. 6
9 Display the Field List, and then remove the Month field from the Rows area to display only the Grand Totals for each item. Close the PivotTable Fields pane. 8
10 From the 1Q Revenue worksheet, insert a PivotChart using the stacked bar chart type. Note, Mac users select the range A4:E7 in the PivotTable. On the Insert tab, click Recommended Charts, and then click Stacked Bar to create the chart. Then, on the Chart Design tab, click Switch Row/Column.

Move the chart to a new worksheet using the name 1Q Youth Programs Chart.

10
11 Change the layout of the PivotChart to Layout 3 and change the chart style to Style 2. 6
12 Change the chart title to 1st Quarter Youth Programs Revenue and then make sure data labels have been added to the center of the data points. 8
13 Format the Plot Area with the Gradient fill Light Gradient Accent 4. Format the Chart Area with a solid fill using Green, Accent 1, Lighter 60%. Hide all of the field buttons on the chart. 8
14 Hide the Youth Programs worksheet. 6
15 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Grove 1Q Pre-School, 1Q Youth Programs Chart, 1Q Revenue. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0