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

  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 caret 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?

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.