279
Using New Pivot Table Features in Excel 2007
Figure 11.22 shows the results of a pivot table with three custom calculation settings, as dis-
cussed earlier.
11
Figure 11.22
This pivot table presents
four views of Sum of
Revenue.Column T is the
normal calculation.
Column U is % of Total.
Column V is % change
from previous month.
Column W is the running
total.
Using New Pivot Table Features in Excel 2007
Pivot tables offer a variety of new features in Excel 2007. The new label and value filters,
conditional formatting, table formatting, and layout views are significant improvements to
the pivot table environment.
If you want to utilize any of these features, the pivot table must exist in a file that is stored
in Excel 2007 file format. If your file is in compatibility mode, none of the new features are
available in the user interface nor in VBA.
Similarly, if you use any of these features, the code runs only in Excel 2007. There is no
hope of going backward to share the code with someone using Excel 2003.
Using the New Filters
In previous versions of Excel, the filtering feature allowed you to choose one or more pivot
items from a drop-down list. The only conceptual filter was the top 10 AutoShow filter.
Excel 2007 offers new conceptual filters that are easy to access. In the PivotTable Field List,
hover the mouse cursor over any active field in the field list portion of the dialog box. In the
drop-down that appears, you can choose either Label Filters, Date Filters, or Value Filters.
In Figure 11.23, the flyout menu shows the list of Label filters available for the Branch
field.
To apply a label filter in VBA, use the
PivotFilters.Add method. The following code filters
to the Branches that start with 1:
PT.PivotFields(“Branch”).PivotFilters.Add _
Type:=xlCaptionBeginsWith, Value1:=”1”
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 279
To Next Page
Loading...
Need help?
General
