PivotTables – overview of improvements in Excel 12
PivotTables are designed to help users make sense of large amounts of data by providing an easy way to build a summarized report. In addition, PivotTables can be rearranged easily, so that once you have some summary data in a PivotTable, you can look at the same information in many different ways with only a few mouse clicks (the name “PivotTable” is derived from the fact that the process of rearranging your data is known as “pivoting” your data).
To illustrate the core capability of PivotTables, let’s imagine you have sales records listed in a worksheet something like this.
(Click to enlarge)
To see a sales summary, all you have to do is to click anywhere inside this range of data, create a PivotTable, and specify how you would like your data summarized. For example, if you wanted to see Sales Amount organized by Product Category and SubCategory, it would look like this (UI is our Beta 1 build, in which the PivotTable UI is not final and in some areas not yet complete).
(Click to enlarge)
Without a PivotTable, summaries like these are typically built using formulas like SUBTOTAL, VLOOKUP etc. However, in cases where you want to build more complicated reports, or where you want to look at the data in numerous different ways, or where the data itself changes quite frequently (i.e. Categories and Subcategories show up or disappear frequently), PivotTables are a great tool.
I deliberately chose a small data set for the example above (and I demonstrated a fraction of what PivotTables can do), but as soon as you have even a few dozen rows of data that you want to summarize, PivotTables can deliver magical results (I love showing PivotTables to customers for the first time and watching their eyes light up). For this reason, the PivotTable feature has been a very popular tool for quite some time, at least with those people who have learned to use it. Unfortunately, there are a large number of users who are not yet aware of this feature, or have not figured out how to use it. While planning for Excel 12, we did a lot of customer research and we found a couple of things. First, we found that many users need the summary capabilities that PivotTables offer, but some currently consider PivotTables “too advanced” and don’t use them regularly. Second, we found that users that did use PivotTables regularly had lots of requests to make them more powerful and more capable. Finally, we found that customers that had adopted SQL Server Analysis Services as their business intelligence platform wanted great support for Analysis Services in Excel.
Essentially, it became clear that there was still a lot of additional capability that we could add in this area that would benefit all sorts of users, so we set out to improve the feature in a number of ways. Specifically, we set out to:
- Make PivotTables easier to build, read, and explore … make them more broadly accessible to any type of Excel user
- Using the Ribbon and new dialogs to expose PivotTables’ capabilities to a much bigger range of users
- Improve PivotTables’ visual appearance for presentation and printing … we wanted to make PivotTables look professional so that they can be used more widely for presentations and printed reports
- Provide new filtering and sorting capabilities ... making it possible for people to see exactly the data they need to see
- Address top customer requests - many users have requested that we make it easier to change or modify the data source for OLAP PivotTables, retain formatting applied by the user across operations, make the PivotTables more readable, etc. We wanted to address as many common requests as possible.
- Make Excel 12 PivotTables a first-class tool for working with SQL Server Analysis Services data - the combination of SQL Server 2005 Analysis Services and Excel 12 provides business users access to tremendous amounts of corporate data, enabling them to quickly and easily answer a wide variety of business questions without assistance
These goals translated in to the following work:
- A new dialog for creating PivotTables that streamlines the overall experience
- A new field list with checkboxes making it very easy to add and remove fields from a PivotTable
- Drop zones in the new field list to make it easier to rearrange fields within the PivotTable
- New expand/collapse indicators in the PivotTable to make it visually clear to users when there are more details to show
- New filtering capabilities … we added Label Filters, Date Filters and Value Filters
- Improved sorting capabilities … it is now possible to sort by the values in a specific row or column in addition to the grand total values
- New layout options to make the PivotTable more readable and presentable
- PivotTable-specific styles for making PivotTables look great
- Better layout tools … for example, the ability to expand/collapse to any level of detail on rows or columns in the PivotTable without having to expand/collapse each level one-by-one
- A fast, easy way to clear a PivotTable
- New and improved contextual menus, Options dialog, and Field Settings dialog
Specific for PivotTables connected to SQL Server Analysis Services cubes, we undertook the following:
- Support for SQL Server 2005 Analysis Services “metadata” in the PivotTable Field List so users can find relevant data quickly and easily
- Support for more Analysis Services features including key performance indicators (KPIs), named sets, drill-through, actions and server side formatting
- The ability to hide any level of a hierarchy in an Analysis Services cube
- Retaining user-applied formatting across operations performed on the PivotTable
This is a pretty big, exciting area of work for our team. Over the next two weeks or so, I am going to review all this in detail.
