You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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:
“Show As”
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.
New Calculations
Excel 2010 also has introduced 6 new calculations for the “Show As”:
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.
Comments: (16) Collapse
These are all welcome enhancements. I couldn't get database functions working with the "repeat row labels" view. I hope that's just a CTP bug.
This Repeat Down Labels is an excellent addition, congrats! The VLOOKUP function is exactly what we often want to use together with pivots but could not in the previous versions.
Great thing.. however the feature we have waiting for years is the PERCENTILE calculation on PivotTables.
Any chance for 2010?
Sorry, but I couldn't find Visual totals for PivotTable and Sets. Is it a problem with my CTP???
With respec to pivot tables one thing I still haven't seen is a way to change all the columns in a pivot table to the same function at the same time.
For example if all my columns are counting and I want to change all of them to sum, how is this possible without clicking on each column and changing it.
@ Javier
We have not added a percentile calculation on PivotTables for 2010.
@ Mike
With your PivotTable selected , go to PivotTable Tools -> PivotTable Options - > Total & Filters tab. If you are still having trouble finding it send me an email at diego@microsoft.com and I can send you screenshots.
@Drew
At this time the setting is per column and therefore you cannot change it for more than one column at a time.
This is all great feedback and questions ! Please keep it coming.
Repeat down is OUTSTANDING. I used as a work around Copy>Paste Special>Values. Then on each label column I did Find>GoTo Special>Blanks. I entered a formula to reference the cell above (in A2 enter =A1) and press Ctrl+Enter to put the formula in each empty cell. Then Copy>Paste Special>Values to change the formulas to values. Unfortunately, now all the data is static.
How about: Data fields inhereting their formatting from the source data. My source data might be formatted as Currency or as Number with three decimal places. I want those fields to automatically have the same formatting when I create a PivotTable. Don't make me format each column in my PT one by one when my source data is already formatted the way I want it.
Can we expect to be able to update values directly in the pivot table with the official realease. I believe this is the biggest draw back of pivot tables at this point. Pivot4U has taken a stab at creating an addin that allows cell editing but frankly the softawe is not commercial quality. I know excel 2010 suppots writeback to OLPAP cubes...but It would be owesome if every average Joe can edit values on local pivot tables.
These changes look great, congrats.
Personally one of the BIG problems that I face with one of my clients is that they want to be able to type in a single (of about 30000) customer id and see the profit for that customer.
At the moment in Excel 2007, unless they know of another way to find the customer via a hierarchy, it is painful since they have to wait for the list of customers to populate before being able to filter. An unbound filter parameter that they could type into (like in the OLAP Tools add in) would definitely help !
@Lum
No, currently What-if Analysis is only available for OLAP PivotTables in Excel 2010.
@dan
The new search functionality seems like it would provide this for you.
I don't where to post this question. Someone please guide to the right place:
1. With Excel 2010 what are the changes/improvements in VBA that we can look forward to?
2. Until Excel 2007, selecting a range of filtered cells defaults to selecting "all" the cells in the range (including the hidden ones).
You have to either manually use GoTo and select Visible Cells Only or write a procedure and assign a key to do that.
Wouldn't it make more sense if it could default to selecting only the visible cells?
Thanks.
A feature I have long been waiting for in Pivot Tables is a "count distinct" in the list of possible aggregations. I have seen this feature requested on various blogs and forums, and to me, this would be a HUGE win. I run into this situation so many times, and the known workarounds are suboptimal. As an example, suppose my data has the following fields: [Product], [Brand], [Date], [Sales]. Now suppose I want to see sales by Brand, along with a count of the products that are included in that Brand. If I move Brand to the row area, and Sales to the values area, I can get my sum of Sales. But when I move Product to the values area and select count, I get the total count of records with that product, which in this example, is the count of "Product/Date" combinations. What I really want is the distinct count of products that are associated with a given Brand.
This is something that can be done easily using SQL, and seems like such a natural fit for Pivot Tables. Is there any chance this can be included in 2010? Please?
Sachin: for your first question:
- Object Model support for new Excel functionality, like slicers and sparklines
- More chart/shape Object Model parity with Excel2003
- Performance improvements for common Object Model scenarios.
- 64-bit support
- More parity with XLM
Some of the above we've already discussed on this blog. Others we'll have more detail on in future posts.
@Jeff Ireland
Count Distinct is not included in 2010. We have heard the feedback loud and clear and hope to be able to add this functionality in future versions.
One of the least heralded features in PowerPivot is also one of the most powerful features in the engine i.e. calculated fields.
Creating calculated fields for stuff like a distinct count - an impossible task for an Excel "calculated field" - is a breeze for PowerPivot.
The limit to the calculated fields you can create with PowerPivot is governed by its function support, which, while missing some basic functions like standard deviation and variance, isn't too bad.
Comments: (loading) Collapse