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
Right click the field you want sorted.
Select Sort. In this example, Divisions with the largest percentages are on top.
How to Move Rows or Columns
Click on the header you want to move.
Hover over the edge of the cell, until your cursor changes and looks like
.Click and drag the field to the desired location.
How to Hide Rows or Columns
Click the caret next to either Column Labels or Row Labels.
Deselect the desired column or row.
How to Calculate Percentages
Click on the field in the Values quadrant.
Select Value Field Settings.
Select Show Values As.
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:
Right-click the desired date field in the pivot table and select Group.
Make your desired selections. This example shows data by year and month.
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.