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...

    You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
    Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...
    For bitcoin users, the taxman cometh. And you best know how to calculate taxes owed on what the IRS calls convertible virtual currency.In March 2014, the IRS issued Notice 2014-21, which declares virtual currency will be...

    Already a member? log in here.

    Upcoming CPE Webinars

    Sep 18
    In this course, Amber Setter will shine the light on different types of leadership behavior- an integral part of everyone's career.
    Sep 24
    In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
    Sep 30
    This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
    Oct 23
    Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.