Microsoft Office Tutorials and References
In Depth Information
Customizing Your Pivot Table
Figure 14-32: All sales periods display even if there is no data.
Now that you’re confident that the structure of the pivot table is locked, you can use it as the source
for all charts and other components in your dashboard.
When you show items with no data, you will see plenty of empty cells. Excel gives you
the option of replacing empty cells with a value of your own (such as 0 or n/a). This will
give your customers a clear indication that there is truly no data for the items that show
empty. To replace empty cells with your own value, right-click your pivot table and
select PivotTable Options. In the PivotTable Options dialog box, you see a For Empty
Cells Show setting. Simply enter the value you want to show instead of empty cells.
Sorting your pivot table
By default, items in each pivot field are sorted in ascending sequence based on the item name. Excel
gives you the freedom to change the sort order of the items in your pivot table.
Like many actions that you can perform in Excel, lots of different ways exist to sort data within a pivot
table. The easiest way, and the way that we use the most, is to apply the sort directly in the pivot
table. Here’s how:
1. Right-click any value within the target field (the field you need to sort).
In the example shown in Figure 14-33, you want to sort by Sales Amount.
2. Select Sort and then select the sort direction.
Figure 14-33: Applying a sort to a pivot table field.
The changes take effect immediately and persist while you work with your pivot table.