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

  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

  1. Click the carrot next to either Column Labels or Row Labels 

  2. Deselect the desired column or row
    Hide Columns or Rows
  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


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.



Keywords:Tableau Workbook Dashboard IDE Export Exported Output Excel Download Downloaded Pivot Table Tables Drag Quadrant Filter Advanced   Doc ID:88560
Owner:Melissa C.Group:Office of Data Management & Analytics Services KB
Created:2018-12-18 11:56 CSTUpdated:2018-12-18 16:08 CST
Sites:Office of Data Management & Analytics Services KB
Feedback:  0   0