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.
Click on any cell inside your data.
Select Insert and then Table.
Then, create a PivotTable from that table:
In the Insert tab, select Recommended PivotTables.
Choose from one of Excel’s Recommended PivotTables. In this example, Excel chose to summarize by Student ID or Count of Students.
Your PivotTable will be generated in a new tab.
Customize Pivot Tables in Excel
Click anywhere inside the pivot table to open the PivotTable Fields menu.
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.
How to Use the 4 Quadrants
The Filters quadrant:
Drag the desired field(s) to the Filter quadrant. (You can have multiple filters.)
Select a filter option from the drop down above the pivot table. You also have the option to Select Multiple Items.
Drag fields to the Columns or Rows quadrants until you reach your desired layout.
Use the Values quadrant to calculate Average, Sum, Count, and more:
Click on the desired field in the Values quadrant.
Select Value Field Settings.
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.
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.