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.
When working with PivotTables, you often come across scenarios where you want to work with the same set of items from the data over and over again. For example, you might be a regional manager and you want to create a set of reports about the branches in your particular region, but the list of which are “your” branches isn’t in the data source, so you always have to filter to the same eight branches, like so:
Example of two PivotTable reports created using a named set of store branches
Of course the examples are endless: you might be a product manager who wants to show the set of products that you are responsible for in many different PivotTables to give different perspectives on the performance of your product line, you might want to have a set of your Top 10 partners, you might have a set with your best suppliers, you might have a set of your “gold” salespeople, etc. The point is that the Named Sets feature in Excel 2010 provides you the ability to easily create and reuse this logical grouping of items as a single object that you can add to OLAP PivotTables, regardless of whether this existed in the data source.
Named sets are a very flexible tool that allows users to do a lot more than just create a re-usable group of items for use in PivotTables. With Named Sets in Excel 2010 you can:
Let’s see how easy it is to create one of these asymmetric reports with Excel 2010 that wasn’t previously possible in Excel PivotTables.
A very common report layout needed by our customers is to show actual sales for the year 2008 while showing forecasted sales for the year 2009. Here is a PivotTable showing actual, budgeted and forecasted sales quotas for the last two fiscal years (click to see larger image).
As you can observe there is a lot of irrelevant information in our report such as Forecast for 2008 (since we have actual data for 2008) and Actual for 2009 (since this only includes 9 months of data). If I tried to hide Actual it would actually get hidden for both 2008 and 2009, but since I have Excel 2010 I can go ahead and create a set out my information on columns by simply going to PivotTable Tools -> Options in the ribbon, and selecting “Create set based on column items”.
Now the Named Set creation UI will pop up (click to see larger image):
The UI contains all the tuples that currently define the column labels of my Pivot report. Now I can easily remove the tuples I no longer want. In this case I am going to go ahead and remove:
Note: Subtotals and Grand totals can be easily identified because they contain an “All” member.
I’ll also go ahead and change my set’s name to something easier to recognize in the future like “2008-09 Actual vs. Forecast”. Let’s hit OK and see what my report looks like now.
The new set is placed on the corresponding axis
If a display folder was not specified it will be stored under the Sets folder by default.
So now I have a reusable named set that can be used in new PivotTable reports or as part of cube functions. Of course this same feature can be used to create any “set” of items (like product bundles offered for Christmas or locations belonging to a particular regional manager), and I can use these across multiple reports. A set could also be used to build a report layout that would otherwise not be possible with PivotTables.
I have covered how to easily create these sets through the UI, but this is just some of the power and flexibility that named sets add.
For users who are familiar with MDX, you are now able to create just about any PivotTable you’d like to by creating sets based on your own custom MDX definition. The set manager allows you to create a new set using an MDX editor, and also allows you to set advanced options on your set, including making the set recalculate its items based on its context (a “dynamic set”). For example, you can make a set that shows combinations of products and salespeople when you’re filtering by one manager, but shows products and sales channels when you’re filtering by a second manager.
You may be saying to yourself “haven’t I used named sets in Excel before?” Well, Excel has allowed you to access some named sets that exist on an OLAP data source for a number of versions. In fact, you could even create named sets using the Excel object model in previous versions of Excel, so long as you could figure out how to do so, write MDX for the set, and so long as your set was one that previous versions of Excel could support (the named set consisted only of members from a single hierarchy and was not dynamic). More detail on this can be found here.
Lastly, in Excel 2010, only OLAP PivotTables support named sets. We hope to add named sets to all PivotTables in a future release of Excel.
So there you go; a “short” introduction to the world of Named sets in Excel. We think they’re an addition to Excel 2010 that will make PivotTables a whole lot more flexible and powerful for users. I would love to hear back about how you are putting this feature to use.
"We hope to add named sets to all PivotTables in a future release of Excel."
I have an even better idea - Named Views. Named Views would extend Name Sets by allowing you to pick Named Sets from a drop-down menu, thus eliminating the torture of having to drag sets in and out of the PivotTable field list.
Couldn't agree more, Colin. This would essentially bring the 'custom views' functionality to pivot tables. While you're at it, how about allowing us to finally group pivot data by non-calander years?
You used the acronym MDX several times but never explained what the letters stand for or what a MDX is or does. Not all your readers (at least not this one) may be as in-tune with all the jargon as you.
Jeff Weir is right on. We should be able to define fiscal years once and use it in any PivotTable in any workbook.
One more comment. When fields are added to the data area of a PivotTable, they should automatically inherit the same formatting the field has in the source data. For example, the Sales field in the table is formatted as Accounting with zero decimal places. When you drop the Sales field in the data area of a PivotTable, it should be formatted as Accouting with zero decimal places.
@General Ledger: MDX is a query language for OLAP data sources. I think this is a great enhancement to pivot tables, and I'm looking forward to being able to use it.
I didn't intend for this to turn into a "Pivot Table lacking features" discussion. I have several pages detailing Pivot Table enhancements I'd like to see in Excel 15 (including alternate date calendar groupings). I wanted to mention one feature related to the topic at hand. I should indicate that surfacing Named Sets in the UI is a welcome enhancement for OLAP Pivot Tables.
"I have several pages detailing Pivot Table enhancements I'd like to see in Excel 15 (including alternate date calendar groupings".
Would you care to pass that on to me ? I would really like to go through the suggestions.
Just a quick clarification for those of you trying to take advantage of the dynamic set functionality. For the OLAP server to be aware that you are creating a dynamic set the check box in the MDX editor that reads "Recalculate set with every update" must be checked.
Would you recommend Named Sets for comparing, say.... payroll figures from last year to this year, where there will be common and unique names on each year's list?
It depends really on what exactly you are trying to do. What determines which names should show on your PivotTable ?
If they are determined by certain conditions you could be using a dynamic set to create these sets.
If you want to send me a screenshot or a sheet with some fake data as to what you are trying to do I can definitely take a look.
This is good news. I suppose an alternative would be an extra field to provide your own "named set", which could then be filtered on. Is there an advantage to setting up named sets - speed or simplicity?
The clearest advantage of Named sets is reuse. If you have a set of say Product or Sales people that you will be using in multiple reports you don't want to have to be re filtering for every single one of them on every PivotTable. With a Named set you create it once and use it as many times as you want in the workbook.
I am just now digging into the possibilities for this, but a limitation i see is that i am not able to put the set in the report filter. I have a lot of account codes that belong to a specific group and i am trying to look at data for only this group and i don't care about the breakdown into each account so i created a new group, but it is really not useful as of yet. Is there any way around this?