PivotTables V: Context menus
Today I wanted to talk about PivotTable context menus, for two reasons … first, because we have updated them to make them more useful, and second, because we have added some neat new features that are exposed primarily in context menus. All of this is part of our work to make PivotTables easier to explore and manipulate. Let’s take a look at some of the improvements and additions.
Sorting by items on rows or columns
Below is a screenshot of the context menu for an item on rows or columns (we have a different context menu for values which is covered below). The item used in the example is Mountain Bikes of the Product Category field.
(Click to enlarge)
We have added sorting and filtering options to the context menu to make it fast/easy to sort/filter the current selection. (I will go into more depth on sorting and filtering improvements in a later post. Today I’ll just review the sort and filter context menu options.)
The context menu allows you to apply an ascending or descending sort based on the item names, or you can manually move items of a field to the position you want. Here is a screenshot of the sort menu in the beta version of Excel illustrating those options.
(Click to enlarge)
Filtering
Located just below the sort menu, the filter menu provides direct access to the dialogs for setting up a dynamic filter on the selected field (I’ll cover dynamic filters in a later post), and it also provides the option of defining a manual filter by just displaying the items selected, hiding all the other items of the field, or hiding the selected items. Here is a screenshot of the filter menu … being able to hide or show only selected items is a very handy feature that we think PivotTable users will love.
(Click to enlarge)
Turning on and off subtotals
In many reports, subtotals are not needed for specific fields. This is often tied to the report layout chosen, but it can also be simply because it doesn’t really make sense to add up the numbers in a given context. For this reason, we added the option to turn on and off the display of subtotals of the selected field to the context menu. For example, consider this PivotTable.
(Click to enlarge)
Pretend that the source data for this PivotTable does not contain sales data for all bikes and, before sending out this report, you want to remove the subtotals for Mountain Bikes and Road Bikes so that the report does not contain subtotals that can be considered incorrect since they only represent a subset of the bikes sold. All you have to so is to right-click Mountain Bikes or Road Bikes and select the subtotal option.
(Click to enlarge)
And now the PivotTable no longer contains the subtotals for Mountain Bikes and Road Bikes.
(Click to enlarge)
Expand/Collapse
The Expand/collapse menu offers a fast way to expand multiple fields at once (this has come up in comments as well as a few emails folks have sent this week). Say you have the following PivotTable and you want to display all the available details for Canada.
(Click to enlarge)
In the context menu, all you have to do is to select the field that you want to expand to, in this example Product Name.
(Click to enlarge)
And the PivotTable now displays all the details for Canada.
(Click to enlarge)
The context menu will show you all the fields on the axis, so you can collapse or expand to any field you want. As you can imagine, this is even more useful when there are more than the three fields of this simple example.
Sorting by values
As I mentioned, the context menu users see for values is different from the one they see for rows and columns. Here is what the values area context menu looks like in current builds.
(Click to enlarge)
Just like in the context menu for items on rows and columns we’ve added a sort menu. However, applying a sort here will sort the items by the values in the Values area instead of by item names so, for example, you can sort products ascending by sales amount.
Expand to detail
Expand to detail allows you to query for the rows from the source data that were included for calculating the aggregated value in the selected cell.
(Click to enlarge)
Here is a screenshot of the source data rows returned in the example when performing the “expand to detail” operation. The rows are placed in a new worksheet.
(Click to enlarge)
Summarize data by …
The last context menu option I’ll cover today is the “Summarize Data By” menu, which we hope is a time saver. Sometimes you may want to count the number of instances in the source data for certain criteria instead of simply adding the individual numeric values, or you may want to use one of the other numeric functions for aggregating the data (like Max, Min, Average etc.). To make this very easy, the aggregation functions are now exposed in the context menu of cells in the Values area. To illustrate, here is the PivotTable I’ve been using for other examples today.
(Click to enlarge)
This PivotTable is aggregating the source data using the Sum function. Now, let’s try and change this to Count.
(Click to enlarge)
And that’s all it takes to have the PivotTable use Count instead of Sum to aggregate the source data. Here is a screen shot of the result.
(Click to enlarge)
That wraps up our quick tour – I know I didn’t cover everything, so feel free to ask questions about other items you saw you would like explained. Next up, filtering improvements.
