In common with many of the features of the Office suite, working with Excel pivot tables has been changed both by the application of the new Office 2007 interface and by several specific enhancements.
Creating a pivot table
First find the PivotTable option. Although PivotTables used to live in the Data menu, the new ribbon interface has moved the PivotTable option to the Table section of the Insert ribbon. The PivotTable button is a button of two halves: the top half launches the Create PivotTable sequence, while the bottom half gives the choice of creating a PivotTable or a combined PivotTable and PivotChart.
Once you've found the option and clicked on it, the Create PivotTable or Create PivotTable with Chart dialogue will appear. This dialogue is significantly different to the old PivotTable wizard:
Excel 2003 PivotTable wizardExcel 2007 'Create PivotTable' dialogMicrosoft Excel list or databaseSelect a table or rangeExternal data source Use an external data sourceMultiple consolidation rangesNot availableAnother PivotTable or PivotChart report Not available
Although the multiple consolidation ranges and existing report/chart options are not immediately obvious, there is a way of getting at them. The 'old' PivotTable wizard still exists in Excel 2007 but to use it you will either need to customize the Quick Access Toolbar to add the button 'PivotTable and PivotChart Wizard' from the 'Commands Not in Ribbon' or 'All Commands' category, or alternatively you can use the Excel 2003 keyboard shortcut sequence Alt-D-P to launch the wizard.
Choosing the Use an external data source option enables the Choose Connection button which provides access to existing data sources and to the Browse for More button which allows connections to be created to a wide range of different data sources including web queries, OLAP queries/cube files, and XML files and XML schema files for example.
Once the data source has been chosen, Excel displays the empty table and the Field list task pane:
Items can be dragged to the table area in a similar way to previous versions, but in addition, they can be dragged to the Areas section of the task pane or just ticked. Ticking a text field will add it to the Row Labels area (bottom left of Filed List dialogue pane), ticking a number field will add it to the Values area (bottom right). You can drag fields between areas in the Field List or, as before, in the table itself. At the bottom of the Field List task pane is the Defer Layout Update check box - you can turn this on if you are working with a large set of data and want to set up all the areas without waiting for the PivotTable itself to be updated each time. Once this option is selected, the Update button becomes available to allow you to update the table when you wish to.
Working with a PivotTable
Having created your PivotTable you will find that whenever the table, or any part of it, is selected, the Excel Ribbon will include two 'contextual' PivotTable Tools ribbons – Options and Design.
The Options ribbon includes most of the PivotTable options that were previously found on the PivotTable toolbar or via the right-click menu. There are some additions:
Moving on to the Design ribbon, the Report Layout button makes it very easy to switch between Compact, Outline, and Tabular form:
There is also an extensive gallery of PivotTable styles. Note that the PivotTable Style Options section of the ribbon controls elements of the styles that you will see in the Styles gallery as well as the style of the active PivotTable. You can choose to emphasise row and column headers and to 'band' rows and columns. The style gallery then provides a range of colors and presentations, split into Light, Medium, and Dark variants. The 2007 Live Preview feature allows you to see the effect of any of the styles on the active PivotTable just by hovering over it before you need to select it. Finally, at the bottom of the PivotTable Styles dropdown, there are options to Clear the style of the current PivotTable and to create a New PivotTable Style. The latter option lets you specify the format for each individual element of the PivotTable and also to set the style created as the default PivotTable quick style for this document. The PivotTable Style Gallery is also dependent on any overall workbook theme set via the Themes option of the Page Layout ribbon.
As well as the obvious changes, there are several more subtle improvements, particularly over pre-Office XP versions of Excel:
In this example of a label filter we have chosen "contains" from the list of different operators and then entered the word "marmalade" to display only Products containing marmalade:
Conditional formatting If you select a value cell in a PivotTable and apply Conditional Formatting, when you accept the chosen format a Smart tag will appear allowing you to apply that formatting to all values for that field, or to values for that field for the current row and column heading fields. Hopefully an example may make this a bit clearer. Here we are summing the ExtendedPrice field by City by Country - we can either apply the conditional format to the single cell we selected originally, or we can apply it to all Sum of ExtendedPrice values, whichever row and column headings we use, or just for ExtendedPrice values where City rows intersect with Country columns:
Charts have been extensively improved in Excel 2007 and these changes apply to PivotCharts as well. The three contextual chart ribbons - Design, Layout and Format - are displayed for PivotCharts and, in addition, there is an Analyze ribbon specifically for PivotCharts. The Analyze ribbon provides access to expanding and collapsing fields, refreshing the data, clearing filters and displaying the PivotChart Filter pane. This pane makes it easy to access the filter options for the fields included in the chart.
Reprinted from our sister site, AccountingWEB.co.uk