In the following project, you will assist James Davis, the Recreation Director, in creating and modifying a PivotTable report and a PivotChart report.
Navigate to your Excel Chapter 4 folder, and then double-click the Excel file you downloaded from MyLab IT that displays your name—Student_Excel_4G_Recreation_as.xlsx.
Click cell A2, and then insert a Recommended PivotTable by choosing the Blank PivotTable option in the lower left corner of the dialog box. Add the Month field to the Filters area, add the Center field to the Rows area and the Program field to the Columns area. Place the Amount field in the Values area and then Close the PivotTable Fields list.
Note, Mac users, move fields as necessary so that they are placed in the correct areas of the layout section.
Format the values in the PivotTable using the Number category to display zero decimal places and the 1000 Separator.
Insert slicers for the Program and Center fields, and then filter by the Bard Hall Senior Center and Ceramics and Water Color revenue. Move the Program slicer so that it’s upper left corner aligns with the upper left corner of E3. Move the Center slicer so that its upper left corner aligns with H3. Make a copy of this worksheet, and then name the copied worksheet Bard Hall.
Display Sheet1 and clear the filters from the slicers and remove the slicers from the worksheet. Rename the sheet 3Q Revenue
Insert a PivotChart using the Stacked Column chart type. Move the chart to a new worksheet named 3Q Revenue Chart
Apply the Layout 2 chart layout. Hide all of the field buttons on the chart. As the Chart Title, type 3rd Quarter Program Revenue and then insert a custom footer with the file name in the left section.
Add a new worksheet to the workbook. On the Data tab, click Get Data and then from your downloaded files, import the Microsoft Access Database Excel_4G_Rec_Centers.accdb. Load both of the tables in the database.
Note, Mac users, on the Data tab, click From Text. From your downloaded files, click Excel_Rec_Center_Supplies_csv.csv, and then click Get Data. Use Comma delimiters and General column data format. Put the data in the Existing sheet in A1.
Insert a PivotTable in the Existing Worksheet and verify that Use this workbook’s Data Model is selected.
To create the PivotTable, place the Supplier field from the Suppliers table in the Columns area. From the Supply Order Summary table, 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.
Insert a row above the PivotTable, if necessary, and in cell A1, type Recreation Center Supplies and then apply the Title cell style. Rename the sheet Recreation Supplies
Click in the PivotTable, and then insert a 3-D Pie PivotChart. Move the chart to a new sheet with the name Supplies Chart
Display the Field List and then modify the chart so that only the Supplier field displays in the Axis (Categories) area. Remove any fields that display in the Legend (Series) area.
Apply Style 3, remove the legend from the chart, and display only the Category Name and Percentage data labels positioned in the Center.
Change the chart title to 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.
Hide the Program Revenue sheet. Select all the sheets (excluding the chart 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.
Display the properties. As the Tags/Keywords, type rec center and as the Subject, type your course name and section #. Under Related People, be sure that your name displays as Author. Save your workbook.
Ensure the worksheets are in the following order:
Bard Hall, 3Q Revenue Chart, Supplies Chart, Recreation Supplies, 3Q Revenue
Save and close the file, and then submit for grading.