• 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.

   

Then, create a PivotTable from that table:

3. In the Insert tab, select Recommended PivotTables.

   

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

   

5. 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.

   

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.)

      

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

      

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.

      

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.