Microsoft Office Tutorials and References
In Depth Information
Customizing a PivotTable Layout
TROUBLESHOOTING
When you refresh the PivotTable, you lose your custom sort order
For row and column labels, the default settings have the AutoSort option enabled. That
means you can manually move rows and columns, but as soon as you refresh the
PivotTable, the sort order pops back to its normal settings. Disabling this behavior requires
that you find a well-hidden setting and change it. Right-click the field you want to
manually reorder, click Sort, and then click More Sort Options. Click the More Options
button (we warned you it was deeply buried) and then clear the check box under
AutoSort.
You can also filter data in a PivotTable in a variety of ways. The most formal is to add a field
to the Report Filters section of the PivotTable layout. This allows you to reuse the current
data arrangement with specific entries in that field. To see the list uniltered, choose All
Items from that list.
To filter a PivotTable on an ad-hoc basis, use the field header. (If this drop-down list isn’t
visible, click the Field Headers button in the Show group on the Options tab for the
PivotTable.) The filter options are very similar to those we described earlier in this chapter for
ordinary tables and ranges. (See “Filtering Data in a Table” on page 401.) You can show or hide
specific entries in a list of values or labels or use the search box to find entries that match
the text, numbers, or dates you supply.
To quickly filter a list to show or hide items you specify or one or more items from that
field, right-click, click Filter, and then click either Keep Only Selected Items or Hide Selected
Items.
Grouping and ungrouping data in a PivotTable provides a dramatic way to zoom in on
complex data sets with minimal effort. The source data for the PivotTable in Figure 12-14,
for example, includes more than 20,000 separate data points listing activity on a major
stock exchange for every trading day for more than 80 years. We added the Date field
to the Row Labels section, right-clicked an individual date under that column heading,
and then clicked Group on the shortcut menu. Note that we were able to both filter the
PivotTable (using the Starting At and Ending At boxes under the Auto heading) and group
it by quarters and years with a few clicks.
Search JabSto ::




Custom Search