Thanks to Diego Oppenheimer for putting together this post.
In todayâ€™s post I will be covering a couple of smaller PivotTable features that we incorporated in Excel 2010. Most of these features have been longstanding customer requests or pain points that we felt could be addressed in this release. This includes fixing functionality that worked in versions of Excel previous to Excel 2007 (Filtering on calculated members) as well as making it easier to access features that historically our users have had a hard time finding (Show As calculations).
These features include:
- New additions to â€œShow Asâ€ including adding access to this feature via the right click menu.
- Repeat down labels
- Advanced filtering with calculated members.
- Ability to toggle Visual Totals ON/OFF for PivotTables and sets.
New Entry point
We have added a new entry point to the â€œShow Asâ€ menu for easy access. With Excel 2010 when right clicking on a PivotTable field you will find a â€œShow Values Asâ€ context menu with all the calculations that can be applied for this feature. This makes it a lot easier to try out different calculations until you get exactly what you were looking for. Since you can still add the same value fields to the PivotTable multiple times you could have the actual value and say a running total calculation side by side.
Excel 2010 also has introduced 6 new calculations for the â€œShow Asâ€:
- % of Parent Row Total / % of Parent Column Total
These calculations display all the values in each column or row as a percentage of the total for the column or row.
- % of Parent Total
This calculation displays a value as a percentage of the parent item value of a chosen base field. We can choose a base field that serves as a starting point for the calculation.
In the example above I have applied the % of Parent total calculation and chose as my base field Country so that I can see how much each Product Category (Accessories, Bikes and Clothing) contribute to the Countries total Internet Order Count.
- % Running Total in
This calculation displays a value as a running total percentage for a chosen base field.
In the example above I am interested in getting a visual aid to see how my internet orders have been growing year to year therefore I have a applied % running total with a base field of Calendar year.
- Rank Smallest to Largest / Rank Largest to Smallest
Gives us the ability to assign a ranked number based on the field the calculation is applied to. I would want to do this in cases where I am interested in finding out where my largest or smallest values are.
In the example above I have applied % Parent row total to my sales amount so that I can easily see how much each individual product groupâ€™s sales contribute to their parentâ€™s total. In the case of our top level product groups how much they contribute to the grand total.
Repeat Down Labels
Wouldnâ€™t it be nice if you could use a VLOOKUP, or INDEX(â€¦,MATCH(â€¦),â€¦) together with PivotTables to retrieve values ? Or use SUMIFS to do your own custom summarization of PivotTables? With all the power that PivotTables provide it has still always been hard to use them as a data source for our formulas until Excel 2010. Repeating labels in PivotTables is the ability to show the item captions of outer nested fields in all rows/columns.
Letâ€™s consider the following example:
In this case using VLOOKUPs would be not possible to lookup South Australia based on looking through Australia since the country column only contains 2 labels for each country. By applying repeat down labels and turning off subtotals and grand totals we get the following PivotTable:
With this layout and new feature I have easily converted my PivotTable into a good data source layout. Now given that every row in my PivotTable contains all the information I would need. VLOOKUPS, HLOOKUPS amongst other formulas can easily be used with the PivotTable as a reference.
Filtering on calculated members
From the transition from Excel 2003 to Excel 2007 and due to some limitations on the OLAP cube engine filtering with calculated member did not work in Excel 2007. For this release we have fixed the issue but you will need the forthcoming version of SQL Server Analysis Services.
Visual totals for PivotTable and Sets
In Excel 2010 we have added some visual total settings in PivotTable options. Visual Totals refer to what totals should be showing when one or more members are filtered. In the case of Visual Totals being ON all my totals will reflect the aggregation of only the members present and visible in the PivotTable at that time. In the case of having Visual Totals OFF all filtered items will also count towards the aggregation of totals.
You will be able to find the checkboxes to turn on and off Visual totals under PivotTable Options – > Totals and filters.
This brings to a conclusion our blogs series on PivotTables. As you can see we have spent a lot of time trying to make it easier to work with PivotTables as well as making them more powerful. All of this is a part of making Excel an even better tool for doing Business Intelligence.