Excel - Pivot Tables

Background Information

Pivot Tables allow you to quickly summarize and analyze large amounts of data in lists and tables - independent of the original data layout in your spreadsheet. This Job Aide outlines the steps to create and customize a Pivot Table.

Create a Pivot Table From an Excel Data Range

Create Pivot Table From the Data Source:

  1. Select the Data Source in Excel – this can be a range of cells, or a table.
  2. Verify the following information:
  • Column labels are included (row 1)
  • There are no blank rows
  • There is only one data type per column (e.g., Number)
  • There are no summary formulas
  1. Click anywhere inside the Data Source (other than the Header row).
  2. From the Insert tab, click the PivotTable button.
  3. Verify the data range is correct in the Create Pivot Table window.
  4. Click the OK button.

Note: A popular feature in Excel is the Recommended Pivot Tables button, located to the right of the Pivot Tables button. This feature will display various Pivot Table options using the selected data, so you can see your own data displayed in different ways.

Build the Pivot Table:

  1. The PivotTable Fields list appears to the right of the Pivot Table. Each field represents a column header from the original data source.
  2. Drag desired fields from the Field List into one of the following areas:
  • Filters
  • Column Labels
  • Row Labels
  • Values – if a number or date is not added to this field, a Count of the data will display.

(Row Labels and Values give a good Pivot Table to use as a starting-point).

  1. Displayed within the pdf attachment is an example of a PivotTable using the Rows & Values section of the Field List Builder. The data source is a training completion report that includes fields representing Employee Name, Division, Position, Training Status, etc. For the example:
    • Division is pulled to the Rows section.
    • Name is pulled to the Values section (this provides a COUNT of employees in the division that was assigned training because the Name field is a TEXT format).
  2. To create a hierarchy of information, drag an additional header from the Field List below the first Header in the Row Labels section. This will display an additional group of information below the original Row Header.
    • In the pdf attachment example, each Division displays as the main Header, with a total count of items to the right. All available Training Status labels appear below the main header with an individual item count.
  3. Click the Row Labels drop-down to access Sort and Filter options.

Excel offers two new Filter options for Pivot Tables – Slicer and Timeline. For More information on Slicer and Timeline, please refer to the Job Aides Library.

Pivot Table Tools Analyze Tab

Please reference the example in the pdf attachment for these tools. Descriptions are below:

1

Options
  • Found under Pivot Table
  • Change global preferences for your PivotTable
  • Separate a PivotTable into multiple worksheets based on the Report Filter

2

Field Settings
  • Change how information is displayed in the Values area of your PivotTable. For example, the values area of a PivotTable can display an average number, a total, or a count of the values
  • Change the format of cells in a field. For example, you can format numbers to display as currency

3

Refresh
  • Update the PivotTable when the Data Source has been changed

4

PivotChart
  • Create a chart based on the PivotTable

 

Pivot Table Tools Design Tab

Please reference the example in the pdf attachment for these tools. Descriptions are below:

1

Subtotals
  • Hide Subtotals in a PivotTable
  • Change the location of Subtotals on a PivotChart (i.e., at the top or bottom of a group)

2

Grand Totals
  • Hide all Grand Totals in a PivotTable
  • Hide Row or Column Grand totals in a PivotTable

3

Blank Rows
  • Add or remove blank rows between items (used when there are multiple Row Labels)

4

PivotTable Style Options
  • Automatically format a PivotTable to increase readability

 

To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.

Print Article