Working with PivotTable Data

Expanding and Collapsing Data

In a PivotTable or PivotChart, you can expand or collapse to any level of data detail, and even for all levels of detail in one operation. You can also expand or collapse to a level of detail beyond the next level. For example, starting at a country/region level, you can expand to a city level which expands both the state/province and city level. This can be a time-saving operation when you work with many levels of detail. In addition, you can expand or collapse all members of each field in an Online Analytical Processing (OLAP) data source.

You can also see the details that are used to aggregate the value in a value field.

Expand or collapse to different levels of detail

In a PivotTable or PivotChart, you can expand or collapse to any level of data detail, and even for all levels of detail in one operation. You can also expand or collapse to a level of detail beyond the next level. For example, starting at a country/region level, you can expand to a city level which expands both the state/province and city level. This can be a time-saving operation when you work with many levels of detail. In addition, you can expand or collapse all members for each field in an OLAP data source.

Expand or collapse levels in a PivotTable

In a PivotTable, do one of the following:

1. Click the expand or collapse button next to the item that you want to expand or collapse.
2. Double-click the item that you want to expand or collapse. 
3. Right-click (Ctrl+Click on a Mac) the item, click Expand/Collapse, and then do one of the following:

• To see the details for the current item, click Expand.
• To hide the details for the current item, click Collapse. 
• To hide the details for all items in a field, click Collapse Entire Field. 
• To see the details for all items in a field, click Expand Entire Field.
• To see a level of detail beyond the next level, click Expand To "<Field name>".
• To hide to a level of detail beyond the next level, click Collapse To "<Field name>".

Expand or collapse levels in a PivotChart

In a PivotChart, right-click (Ctrl+Click on a Mac) the category label for which you want to show or hide level details, click Expand/Collapse, and then do one of the following:

• To see the details for the current item, click Expand.
• To hide the details for the current item, click Collapse. 
• To hide the details for all items in a field, click Collapse Entire Field. 
• To see the details for all items in a field, click Expand Entire Field.
• To see a level of detail beyond the next level, click Expand To "<Field name>".
• To hide to a level of detail beyond the next level, click Collapse To "<Field name>".

Show or hide the expand and collapse buttons in a PivotTable

The expand and collapse buttons are displayed by default, but you may have hidden them (for example, when you don't want them to appear in a printed report). To use these buttons to expand or collapse levels of detail in the report, you must make sure that they are displayed.

• In Excel 2016 and Excel 2013: On the Analyze tab, in the Show group, click +/- Buttons to show or hide the expand and collapse buttons. 
• In Excel 2010: On the Options tab, in the Show group, click +/- Buttons to show or hide the expand and collapse buttons. 

• In Excel 2007: On the Options tab, in the Show/Hide group, click +/- Buttons to show or hide the expand and collapse buttons.

Note: Expand and collapse buttons are available only for fields that have detail data. 

Filtering Data

To focus on a smaller portion of a large amount of your PivotTable data for in-depth analysis, you can filter the data. There are several ways to do that. Start by inserting one or more slicers for a quick and effective way to filter your data. Slicers have buttons you can click to filter the data, and they stay visible with your data so you always know what fields are shown or hidden in the filtered PivotTable.

Sorting Data

Sorting data in alphabetical order or from highest to lowest values (or vice versa) is helpful when you have large amounts of data in a PivotTable or PivotChart. Sorting lets you organize the data so it’s easier to find the items you want to analyze.

Important: When you sort data, be aware that:

• Sort orders vary by locale setting. Make sure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows Help system. 
• Data that has leading spaces will affect the sort results. For optimal results, remove any leading spaces before you sort the data. 
• You can’t sort case-sensitive text entries. 
• You can’t sort data by a specific format, like a cell or font color, or by conditional formatting indicators, such as icon sets.

In a PivotTable, click the arrow next to Row Labels and Column Labels cells to sort.

1. Click a field in the row or column you want to sort.
2. Click the arrow on Row Labels or Column Labels, and then click the sort option you want.
   

To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.

Text entries will be sorted in alphabetical order, numbers will be sorted from smallest to largest (or vice versa), and dates or times will be sorted from oldest to newest (or vice versa).

Sort on a column that doesn't have an arrow button

You can sort on individual values or on subtotals by right-clicking a cell, picking Sort, and choosing a sort method. The sort order is applied to all the cells at the same level in the column that contains the cell.

In the example shown below, the data under the category level (Helmet, Travel bag) is sorted alphabetically, A to Z.

To see the grand totals for products sorted largest to smallest, choose any number in the Grand Total column, and sort on it.

Tip: To quickly find what you need, you can group, filter, or apply conditional formatting to the PivotTable or PivotChart.

Set custom sort options

To sort specific items manually or change the sort order, you can set your own sort options.

1. Click a field in the row or column you want to sort. 
2. Click the arrow on Row Labels or Column Labels, and then click More Sort Options.
   

In the Sort dialog box, pick the type of sort you want:

• Click Manual to rearrange items by dragging them. You can't drag items that are shown in the Values area of the PivotTable Field List. 
• Click Ascending (A to Z) by or Descending (A to Z) by, and then choose the field you want to sort. 
  
• For additional options, click More Options, and then pick the option you want in the More Sort Options dialog box:
  

Under AutoSort, check or uncheck the Sort automatically every time the report is updated box to allow or stop automatic sorting whenever the PivotTable data is updated.

Under First key sort order, pick the custom order you want to use. This option is available only when the Sort automatically every time the report is updated box under AutoSort has been unchecked.

Excel has day-of-the-week and month-of-the year custom lists, but you can also create your own custom list for sorting.

Under Sort By, click Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.

Note: A custom list sort order is not retained when you update (refresh) data in your PivotTable.

Tip: Click Data source order to return items to their original order. This option is available for Online Analytical Processing (OLAP) source data only.

Grouping Data

Grouping data in a PivotTable can help you show a subset of data to analyze.

1. In the PivotTable, right-click any numeric or date and time field, and click Group. 
2. In the Starting at and Ending at the box, enter this (as needed): 

• The smallest and largest number to group numeric fields.
• The first and last date or time you want to group by.

The entry in the Ending at box should be larger or later than the entry in the Starting at the box.

      3. In the By box, do this:

• For numeric fields, enter the number that represents the interval for each group.
• For date or time fields, click one or more date or time periods for the groups.

You can click additional time periods to group by. For example, you can group by Months and Weeks. Group items by weeks first, making sure Days is the only time period selected. In the Number of days box, click 7, and then click Months.

Tip: Date and time groups are clearly labeled in the PivotTable; for example, as for Apr, May, Jun for months. To change a group label, click it, press F2, and type the name you want.

Note: Time grouping feature is new in Excel 2016. With time grouping, relationships across time-related fields are automatically detected and grouped together when you add rows of time fields to your PivotTables. Once grouped together, you can drag the group to your Pivot Table and start your analysis.

Refreshing Data

By default, PivotTables are not refreshed automatically, but you can specify that the PivotTable is automatically refreshed when you open the workbook that contains the PivotTable.

Manually Refresh

1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon. 
2. Click Analyze > Refresh, or press Alt+F5.
   

Tips:

• To update all PivotTables in your workbook at once, click Analyze > Refresh All. 
• If refreshing takes longer than you expect, click Analyze > Refresh arrow > Refresh Status to check the refresh status.
• To stop refreshing, click Cancel Refresh. 

Prevent column widths and cell formatting from adjusting

If the column widths and cell formatting of your data adjust when you refresh the PivotTable data, and you don’t want that to happen, make sure the following options are checked:

1. Click Analyze > Options.
2. On the Layout & Format tab, check the Autofit column widths on update and Preserve cell formatting on update boxes.

Refresh data automatically when opening the workbook

1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon. 
2. Click Analyze > Options. 
   
3. On the Data tab, check the Refresh data when opening the file box. 

Editing Data Source

To change the data source of a PivotTable if it's a range of cells or an Excel table, do the following:

• Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon. 
• Click Analyze > Change Data Source. 
  
• In the Table/Range box, enter the range you want to use.
  

Tip: Leave the dialog box open, and then select the table or range on your worksheet. If the data you want to include is on a different worksheet, click that worksheet, and then select the table or range.