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
Customize Pivot Tables in Excel
How to Use the 4 Quadrants
Advanced Pivot Table Techniques

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:

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 or Help?

If you have questions about this Tableau document, please contact Melissa Chan, Office of Data Management and Analytics Services (ODMAS) at melissa.chan@wisc.edu.