PivotTables part 3: More clickable, more compact, and nicely styled
Today, I would like to cover some of the improvements we made in Excel 12 to make PivotTables easier to read and explore.
Let’s look at an example. In the PivotTable below, I have added three fields to the row area and the sales amount field to the values area. Currently only items of the first field, year, are showing. To display the details below 2001, all I have to do is to click the expand indicator:
And now I’m looking at the sales amount for each product category in 2001:
To go to a lower level of detail, I can expand mountain bikes as well and I get the sales amount for each bike model:
Note that I am now at the lowest level of my “hierarchy”, so there are no expand or collapse indicators. The indicators do not print by default, and they can be turned off altogether once you are done exploring the data and are getting ready to present the result.
And here is the same PivotTable in Excel 2003.
To significantly improve the readability of PivotTables, we have added a new layout option for displaying items in the row area, which the team refers to as “compact”. In the Excel 12 screenshot above, you’ll notice that items from all of the three different fields in the row area are displayed in a single column. To distinguish between items from different fields, Mountain Bikes is indented under 2001 and the individual mountain bike models are indented even further under Mountain Bikes. One of the key benefits of this feature is that PivotTable row labels take up far less room on your screen, so that there is much more room for your numbers.
This compact form is the new default layout for PivotTables in Excel 12. That said, we have provided three different “row area layout options” to choose from. The layout settings can be controlled for each field individually but it is very easy to set them for all fields at once. This is done in the Report Layout drop down on the PivotTable Styles tab.
In addition to the compact form that we have already looked at, the tabular form displays one column per field displayed and leaves space for field headers. Here is what the tabular form looks like for the same PivotTable – much like current versions of Excel.
The outline layout is very similar to tabular except that you can have subtotals at the top of every group, since items in the next column are displayed on row below the current item. To illustrate the difference, the screenshot below shows outline form where Mountain Bikes is one row below 2001:
As the screenshots above illustrate, the great advantage of the new compact form is that the PivotTable utilizes space a lot better, making it much easier to read. Tabular and outline form include a lot of white space making the report wider and the result is that the values are pushed out of view in many cases.
Well, the good news is that we have done the same for PivotTables. In Excel 12, we have added PivotTable styles, which are another important part of our work to make PivotTables easier to read and understand. In the same way as table styles, the PivotTable UI offer styles in a gallery.
Clicking a style in the style gallery will immediately apply the style to the entire PivotTable. Below are two examples of PivotTable styles. The first example is a style that highlights the top part of the report while formatting everything below similarly:
The next example demonstrates that you can make each group stand out to make it easier to find subtotals in the report. In this example, 2001 and Mountain Bikes are in bold text since they represent subtotals whereas the individual mountain bike models are in regular text since they are at the lowest level of detail.
Excel 12 will come with a large set of predefined PivotTable styles that you can pick and choose from. In addition, just like table styles, you can create your own styles that fit your specific needs whether that might be corporate guidelines or individual preferences. PivotTables, however, are more complex than tables, so there are more table elements available for users to define formatting on. For example, you can define formatting for multiple levels of subtotals, you can define striping at different levels in the PivotTable. (UI is not final.)
We think that users will really enjoy this feature – once a style has been applied to a PivotTable, the PivotTable continues to look good through sorts, filters, pivots, addition or removal of fields, etc.
Next time up, a bunch more features that make PivotTables easier to read and explore.
Published Tuesday, December 13, 2005 9:03 AM by David Gainer
注：本文翻译自http://blogs.msdn.com/excel，原文作者为David Gainer(a Microsoft employee)，Excel home授权转载。严禁任何人以任何形式转载，违者必究。