Project 4A Key Points - Part 1

Create a PivotTable Report

A list is a series of rows that contain related data with column titles in the first row. A long list of of numerical data is not useful until it is organized in a way that is meaningful to the reader. Excel's PivotTable report, also called simply a PivotTable, is an interactive Excel report that summarizes and helps you to understand large amounts of data. 

  1. Create a PivotTable Report

    There should be no blank rows and the data in each column should be of the same type. When you open the Fire_Police file, you see that you see that there are 5 column headings - Fire/Police, Month, Location, Classification, and Number. There are 3 months of calls. For Fire calls, there are two classifications, for Police calls, there are three.

Initial Data

First Pivot Table - created using the Recommended PivotTable feature - in the PivotTable Fields pane (at the top), 4 fields are checked: Fire/Police, Month, Location, and Number. All 4 of these show up in the PivotTable below. If the PivotTable Fields pane is not showing, click any cell in the PivotTable report to display it. Alternatively, you can click on Analyze in the menu and then choose Field List in the Show group at the far right. Note that Classification is not checked in the list of fields at the top part of the pane and thus Classification is not showing in the PivotTable below.

By default, non-numeric fields are added to the ROWS area and numeric fields are added to the VALUES area in the layout section at the bottom of the PivotTable Fields pane. You can filter the report based on the Fire/Police field by dragging this field upward to the FILTERS area. This produces the following report which doesn't distinguish between Fire and Police.

.

In the PivotTable Fields pane, in the field section, the Location field check box is selected. Location is a non-numeric field so by default it will display in the Rows area and the Location names display at rows in the PivotTable report.

In the PivotTable Fields pane, in the field section, select the Classification field check box which produces the following:

Notice in the layout section, the Classification fields displays as the second field in the ROWS area. In the PivotTable, the Classification names are added as indented row heading under each police precinct location and under each fire station location. Notice that under each precinct location, only the call classifications related to the police department display. Similarly, under station locations, only the call classifications related to the fire department display.

If it is not already there, In the PivotTable Fields pane, from the field section, drag the Month field down to the COLUMNS area. Verify that the Sum of Numbers displays in the VALUES area. This should produce the PivotTable above.

Using Slicers and Search Filters

  1. Using a Slicer to Filter a PivotTable

    On the Analyze tab, in the Filter group, click Insert Slicer. The Insert Slicers dialog box displays all the field names from the PivotTable report. Select the Fire/Police check box, and then click OK. Move the slicer to align with the top of the report and to the right, so that it is not blocking your view of the PivotTable. On the Fire/Police slicer, click the Fire filtering button, move your pointer out of the slicer and the resulting PivotTable hides the records for the police precincts. The slicer indicates the current filtering state, it is easy to tell which data is shown in the PivotTable report and which is not shown (the background color for the chosen filter had been earlier specified in the Slicer Styles group.



    We can add another slicer. Click any cell in the PivotTable. On the Analyze tab, in the Filter groups, click Insert Slicer. In the Insert Slicers dialog box, select the Classification check box. In the Classification slicer, call classifications associated with the police department are dimmed. Because the PivotTable is currently filtered by Fire, no filters related to Police are available. Click the Emergency Medical Calls box, which produces the following result:


  2. Clearing Filters and Filtering by Using the Search Box

    Clicking a filtering button cancels the selection of another filtering button unless you hold down the control key. At the top right of a slicer, you can click the Clear Filter icon. You can point to the slider header (where the words are) and right click and choose Remove [the name of the filter]. You can also filter by using the Search box.

revised 11/1/21. Comments to Bill Pegram, wpegram@nvcc.edu