Pivot Tables: Transform Exported Data in Excel

After exporting data, users of UW-Madison’s Institutional Tableau workbooks can transform their data into pivot tables that provide summary information they need. This KB article explains how to create, customize and refresh pivot tables in Excel.

Create Pivot Tables in Excel

First, create a Table from your data.

  1. Click on any cell inside your data.

  2. Select Insert and then Table.

    Create a Table

Then, create a PivotTable from that table:

  1. In the Insert tab, select Recommended PivotTables.

    Create a Recommended PivotTable
  2. Choose from one of Excel’s Recommended PivotTables. In this example, Excel chose to summarize by Student ID or Count of Students.

    Select one of the Recommended PivotTables
  3. Your PivotTable will be generated in a new tab.

Customize Pivot Tables in Excel

  1. Click anywhere inside the pivot table to open the PivotTable Fields menu.

  2. Drag fields to any of the 4 quadrants to modify the Pivot Table. In this step, be sure to try out various options in order to achieve the design that best suits your needs. Play around by moving fields between each of the 4 quadrants which are described below. You will not break it! If can click Ctrl+Z to undo any changes you make or simply start over with a new Pivot Table. The more you play with it the better you will understand how Pivot Tables works and the faster you will be able to reach the data you need.

    Customizing Pivot Tables

How to Use the 4 Quadrants

  1. The Filters quadrant:

    1. Drag the desired field(s) to the Filter quadrant. (You can have multiple filters.)

      Filter Quadrant
    2. Select a filter option from the drop down above the pivot table. You also have the option to Select Multiple Items.

      Filter Selection
  2. Drag fields to the Columns or Rows quadrants until you reach your desired layout.

  3. Use the Values quadrant to calculate Average, Sum, Count, and more:

    1. Click on the desired field in the Values quadrant.

    2. Select Value Field Settings.

      Value Field Settings

Tips:

  • Go to the Design tab to modify formatting, add subtotals or change the report layout.

  • Right click on the pivot table for shortcuts.

  • For more Pivot Table tips, see the KB article on Pivot Tables: Advanced Techniques in Excel.

Important Note

Changes, additions or deletions you make to the data source tab in Excel do NOT automatically appear in Pivot Tables. Follow instructions in the KB article Pivot Tables: Refresh Data in Excel to refresh your pivot table after changes are done to the source tab.

Need More Information?

If you have questions about this document, please contact the Office of Data Management and Analytics Services (ODMAS, https://data.wisc.edu/) at info@data.wisc.edu.