Pivot Tables grand finale: Tricks with the Values field
This is going to be the last PivotTable post, at least for a while. Unlike the last several posts, the subject matter that follows applies to any PivotTable, not just those connected to SQL Server Analysis Services.
In current versions of Excel, one of the capabilities that exist in PivotTables is the ability to adjust the position of the labels that describe the values in the Values region of the PivotTable (i.e. “Sum of Sales”). Excel PivotTables offer significant flexibility in this area – the labels can be on rows, on columns, and anywhere in the hierarchy on either of those areas. When we visit customers to talk to them about how they use PivotTables, though, we see a couple of things. First, the majority of users aren’t fans of our initial placement of the labels. Second, most people have never figured out that the labels can be repositioned. We have tried to address both of these items in Excel 12.
This area is probably best explained by walking through an example, so here goes. To start with, imagine you were building the following PivotTable. It has some items on rows and columns, and Sales Amount summarized in the Values area.
(Click to enlarge)
If you add a second field to the Values area – say Product Cost – then Excel adds some captions (“Sum of Sales Amount”, “Sum of Product Cost”) below the years (“2003”, “2004”) to help the user distinguish which numbers are Sales and which numbers are Product Cost.
(Click to enlarge)
Those of you familiar with PivotTables have probably already spotted one change from current versions of Excel. In current versions of Excel, the captions are placed in the Row area, not the Column area. Here is a visual of what that looks like.
(Click to enlarge)
This one change – putting the labels on columns and not rows when a second field is added to the Values area – makes PivotTables with multiple items in the Values area more readable, and was the default positioning that most users wanted. So far, feedback on this one small change has been very positive.
As I said above, PivotTables are flexible enough to show the labels at any point in the hierarchy on either the Row or Column areas . To move the labels around in current versions of Excel, you can drag and drop a “Data” field in the Excel grid. This is not terribly obvious, though, and those folks that did spot this capability often had trouble putting the labels at the point in the hierarchy that they wanted. In Excel 12, we have tried to make this a more straightforward task by putting a field for the labels in the Drop Zone area of the field list that people can move around exactly like any other field. So, when you add more than one field to the Values area, we add a field labeled “∑ Values” to the field list, initially in the Column Label area.
(Click to enlarge)
We don’t show this field until you add a second field to the Values area because we don’t put captions in the PivotTable until there are multiple items in the Values area.
If you want to re-position the captions elsewhere, you can simply drag-and-drop the “∑ Values” field to another drop zone or another position in the drop zone that currently contains the “∑ Values” field. Say I wanted to see the labels above the “Year” field in the Column area. All I need to do is start dragging the “∑ Values” field, and I get feedback as to where the field will end up when I am done (blue bar, new cursor).
(Click to enlarge)
If I drag the “∑ Values” field above the “Year” field and let go, the PivotTable will refresh, and the captions will now be outside the year information, showing me a different view of my data. Here is what that looks like.
(Click to enlarge)
I can also move the captions to the Row area. Here is what the PivotTable looks like when I have moved the “∑ Values” field to the bottom of the hierarchy on the Row area.
(Click to enlarge)
Further, I can move the captions anywhere else in the hierarchy in the Row area. Here is what the PivotTable looks like when I have moved the “∑ Values” field in between the Product Name and Product Category fields.
(Click to enlarge)
By now you are probably getting the idea – the placement of the labels is infinitely flexible, allowing you to see the data pretty much any way you want. Hopefully folks see this as a useful feature. As always, I am interested to hear your feedback.
Next up, OLAP functions.
