A new approach to Excel pivot tables

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 wizard Excel 2007 'Create PivotTable' dialog
Microsoft Excel list or database Select a table or range
External data source Use an external data source
Multiple consolidation ranges Not available
Another 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:

  • There is a 'Move PivotTable' button to make it easy to move a PivotTable to a new location or different worksheet.

  • A Change Data Source button makes it much easier to change the source of the data or view the detailed Connection Properties.

  • A Show/Hide section of the ribbon controls the display of not only the Field List but also +/- Buttons and Field Headers. The +/- buttons control the display of detail rows and columns where more than one field is included in a row or column area. Using the +/- buttons is a lot easier than the Group and Show Detail, Hide Detail/Show Detail option from the old right-click menu. The Active Field section of the ribbon also includes buttons to Expand or Collapse an Entire Field in one go.

    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.

    Other changes
    As well as the obvious changes, there are several more subtle improvements, particularly over pre-Office XP versions of Excel:

  • Undo is now available for most PivotTable actions.

  • Sorting and filtering have been extended and made easier to use. The right-click menu now includes Sort and Filter options and you can also click the dropdown for a field in the top of the PivotTable Field List to access sort and filter options for that field. Sort displays options relevant to the type of field, for example A to Z,Z to A for text fields and Oldest to Newest, Newest to Oldest for dates. Further, more advanced, options are also included via a More Sort Options command. The Filter right-click menu includes some very useful options - you can clear an existing filter from the selected field; Keep or Hide only the selected items; set up a 'Top 10' filter; or set up detailed filters for either dates/labels or values. In this example of the Top 10 filter, we have set up a filter to show the top 5% of items by the sum of the ExtendedPrice field, summed by ProductName:

    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:

    PivotCharts
    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

    You may like these other stories...

    Whenever I speak to accountants about creating a cloud practice, the most common question is, “How do I charge my clients?” Ten years ago, maybe even five years ago, if I would’ve posed this question...
    While reputational risk is the No. 1 nonfinancial concern among corporate directors, cybersecurity/IT risk is gaining steam. In fact, both private companies and organizations with more than $1 billion in revenue felt they...
    Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...

    Upcoming CPE Webinars

    Jul 31
    In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
    Aug 5
    This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
    Aug 20
    In this session we'll review best practices for how to generate interest in your firm’s services.
    Aug 21
    Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.