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.
- Right-click the field you want sorted
- Select Sort. In this example, Divisions with the largest percentages are on top.
- 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
- Click the carrot next to either Column Labels or Row Labels
- Deselect the desired column or row
- 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:
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
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.