Pivot Tables: Advanced Techniques in Excel

Users of UW-Madison’s Institutional Tableau workbooks may need to export data into Excel and create their own charts using pivot tables. This KB article explains how to further customize pivot tables in Excel.

How to Sort Fields
How to Move Rows or Columns
How to Hide Rows or Columns
How to Calculate Percentages
How to Group/Ungroup Date fields
Important Note on Refreshing Data

 
How to Sort Fields  
  1. Right-click the field you want sorted 

  2. Select Sort. In this example, Divisions with the largest percentages are on top.
    Sorting

How to Move Rows or Columns  
  1. Click on the header you want to move 

  2. Hover over the edge of the cell, until your cursor changes and looks like this cursor

  3. Click and drag the field to the desired location
    Hover Over Edge of Cell

How to Hide Rows or Columns  
  1. Click the carrot next to either Column Labels or Row Labels 

  2. Deselect the desired column or row
    Hide Columns or Rows
How to Calculate Percentages  
  1. Click on the field in the Values quadrant 

  2. Select Value Field Settings

  3. Select Show Values As
    Pivot Table Percentages
How to Group/Ungroup Date fields  
Excel may automatically group certain fields for you, such as grouping dates into years or months. To remove the automatic grouping, right-click on the date field and select Ungroup. To modify the Grouping:
  1. Right-click the desired date field in the pivot table and select Group 
    Grouping Date Fields

  2. Make your desired selections. This example shows data by year and month.
    Year and Month Date Grouping

  3. To drill up to the Year level, click the - signs. Or, right click > select Expand/Collapse > select Collapse Entire Field

Important Note on Refreshing Data  

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 update your pivot tables after every change. 


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.