Handy Excel PivotTable Tools
Excel PivotTables provide you with an extremely powerful tool for cross-tabulating worksheet data. And maybe that’s just what you would expect from Microsoft and from Excel. But you know what? I bet you might be surprised at how many easy-to-use tools appear on the PivotTable Tools Analyze tab, which is available whenever a pivot table is selected. Consider, for example, several easy-to-use buttons you have available.
The easiest and perhaps most useful tool is the Refresh Data button. If the underlying data you’re cross-tabulating in the pivot table changes, you need to update your pivot table, right?
Fortunately, updating your pivot table when your data changes is easy. Simply click the Refresh Data button.
You can click the Field Headers button on the Analyze tab to remove and then add back the field headers. And that means you've got an easy way to change the way your pivot tables look and how fields are annotated.
The field header for your rows is initially Row Labels and Column Labels for your columns. But you can change the labeling by replacing the value shown in the worksheet cell holding the field header.
If the rows show product names, for example, you might change the labeling from Row Labels to Products. And if the columns name the states where products are sold, you might change the labeling from Column Labels to States.
Note: Most recent versions of Microsoft Excel (including Excel 2007, Excel 2010, and Excel 2013) provide a Field Header button for the PivotTable.
Expand and collapse PivotTable data
If you’ve used the shortcut menu’s Group and Ungroup commands, Excel adds + and – buttons (buttons labeled with a plus and minus sign) to your pivot table so you can quickly group and ungroup your rows and columns. That’s handy, of course. But the buttons can clutter your pivot table. And the buttons offer any users of your pivot table the opportunity to make a mess if they’re careless or clueless.
You can click the +/- Buttons command on the Analyze tab to tell Excel you don’t want these buttons displayed. If this seems odd, think about the case where you don’t want people working with the data or looking at the data to be grouped or ungrouped.
If you’re using Excel 2013 or a later version, you can click the PivotTables Tools Insert Slicer button to tell Excel you want to filter, or slice, your data even more finely.
When you click the Insert Slicer button (which appears on the Analyze tab), Excel displays the Insert Slicers dialog box, which lists the fields in your PivotTable (see the following figure). The Slicer dialog box lets you choose another filtering level.
You simply choose the field you want to use in your filtering and click OK. Excel then displays a dialog box like the following that you can use to see only data that matches your filter. The following figure, for example, shows a Month slicer dialog box that one could use to see only, for example, the January data or only the February data or only the March data and so on. (You pick a month by clicking its button in the Month slicer dialog box.) The Month slicer dialog box provides clickable buttons you use to filter.
To move the slicer dialog box, click the box and then press the Delete key. When you remove the slicer dialog box, you undo any filtering performed by clicking buttons on the slicer dialog box.
If you’re using Excel 2013 or a later version, another cool tool is the PivotTables Tools Insert Timeline button. The Insert Timeline button tells Excel you want to add a timeline filter to your pivot table chart to make it very easy to look only at a particular interval: a span of a couple of weeks, the quarter before last, or the interval between two dates.
When you click the Insert Timeline button (which also appears on the Analyze tab), Excel displays the Insert Timeline dialog box, which lists the data-formatted fields in your PivotTable (see the following figure).
You select the date field you want to use for your timeline-based filtering and then click OK. Excel opens a dialog box with a slider button that you can use to specify the timeline you want to use.
In the following figure, for example, a Month dialog box shows. It’s a little hard to see the slider button in an image, but that blue bar works as a slider button you can drag around to specify the timeline you want the pivot table to show.
To move the timeline dialog box, click the box and then press the Delete key. And note that removing the dialog box also undoes any filtering performed by using the timeline slider.