Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel

The team blog for Microsoft Excel and Excel Services.
A Few More PivotTable Improvements in Excel 2010

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.

“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.

image

New Calculations

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.

    image 

    • 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.

  • % 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.

    image 

    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.

    image 

    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.

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:

image

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:

image

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.

image

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.

Posted: Thursday, October 15, 2009 11:37 AM by Joseph Chirilov

Comments

Colin Banfield said:

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.

# October 15, 2009 12:43 PM

Fabio F. said:

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.

# October 15, 2009 1:26 PM

Javier said:

Great thing.. however the feature we have waiting for years is the PERCENTILE calculation on PivotTables.

Any chance for 2010?

# October 15, 2009 4:16 PM

Mike said:

Sorry, but I couldn't find Visual totals for PivotTable and Sets. Is it a problem with my CTP???

# October 16, 2009 2:39 AM

Drew said:

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.

# October 16, 2009 8:07 AM

Diego Oppenheimer said:

@ 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.

# October 16, 2009 1:17 PM

General Ledger said:

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.

# October 17, 2009 7:46 AM

Lum said:

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.

# October 17, 2009 4:21 PM

dan said:

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 !

# October 20, 2009 4:22 AM

Diego Oppenheimer said:

@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.

# October 20, 2009 3:15 PM

Sachin Acharya said:

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.

# October 27, 2009 2:06 AM

Jeff Ireland said:

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?

# October 27, 2009 6:54 AM

Joseph Chirilov said:

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.

# October 27, 2009 1:45 PM

Diego Oppenheimer said:

@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.

# October 28, 2009 8:46 PM

Colin Banfield said:

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.

# October 30, 2009 11:03 AM

Colin Banfield said:

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.

# October 30, 2009 11:04 AM
New Comments to this post are disabled
Page view tracker