EXCEL 2010

Project Description: In this project, you will group and subtotal data. Additionally, you will create a PivotTable, filter and sort the PivotTable, and create a calculated field in the PivotTable. Finally, you will create and modify a PivotChart.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Download, save, and open the Excel workbook named Exploring_e05_Grader_IR.xlsx. 0 2 On the Subtotals worksheet, group together rows 7, 8, and 9 so that they can be collapsed or expanded. Ungroup the rows, and then subtotal the Total column at each change in month using the Sum function. Include summary data. 8 3 Create a PivotTable based on the sales data in the range A6:K15 on the Quarter3 worksheet. Place the PivotTable report on a new worksheet. Rename the new worksheet tab as PivotTable and then add the Location field to the Row Labels area. 14 4 On the PivotTable worksheet, add the Month field to the Report Filter area of the PivotTable. 3 5 On the PivotTable worksheet, add the remaining fields to the Values area of the PivotTable in the order that they appear in the Field List. 14 6 On the PivotTable worksheet, modify the Sum of Total field to summarize the values by average. 6 7 On the PivotTable worksheet, remove the Sum of Home and Sum of Misc. fields from the PivotTable. On the Quarter3 worksheet, modify the value in cell C7 to be 15578.65 and then click on the PivotTable worksheet tab. Refresh the PivotTable. 4 8 On the PivotTable worksheet, sort the row labels in descending order, and then use the report filter to display data for the month of July. 10 9 On the PivotTable worksheet, create a slicer for the Location field. Position the slicer so that the top left corner of the slicer aligns with the top left corner of cell B7. Use the slicer to filter the PivotTable so that only the Brooklyn Drive data is displayed. 10 10 On the PivotTable worksheet, create a calculated field in the PivotTable named Total Food Sales that will subtract the Home and Misc. fields from the Total field. 12 11 On the PivotTable worksheet, change the PivotTable style to Pivot Style Medium 2. 5 12 On the PivotTable worksheet, create a clustered column PivotChart using the filtered data in the PivotTable. Position the chart so that the top left corner is aligned with the top left corner of cell D7. 10 13 On the PivotTable worksheet, change the PivotChart style to Style 11. 4 14 Ensure that the worksheets are correctly named and placed in the following order in the workbook: PivotTable; Quarter3; Subtotals. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points 100

Updated on: 12/18/2010 1 E_CH05_EXPV2_IRCD_Instructions.docx