Excel PivotTable Named Sets enable you to pick and choose which columns or rows you want included in your PivotTable report, but there's a catch. They require you to use Power Pivot to create your PivotTable. Don't worry, it's so easy you won't even realise you're using Power Pivot.
Note: Named Sets are only available in Excel 2010 (with the free Power Pivot Add-in), or Excel 2013 where you have a version that includes Power Pivot.
For example let’s say you are preparing budget, forecast and actual reports spanning multiple years and you have the following data:
- 2013 actual and budgeted sales
- 2014 actual, forecast and budgeted sales
- 2015 budgeted sales
You’d like your PivotTable report to look like this:
But a regular PivotTable looks like the one below with columns for Actual, Forecast and Budget for every year. Ugh, annoying:
We want to get rid of the unwanted columns:
The only choice we have, if you don’t want to resort to formulas, is to hide the columns you don’t want, but that’s a bit laborious, not to mention deadly if you’re showing grand totals and they don't add up. Yes ‘deadly’…. your boss may kill you if he/she makes decisions based on that report. Who said accounting was boring? It's life and death stuff!.
However, in Excel 2010 and 2013 it’s easy with Named Sets.
Creating Named Sets in Excel
In Excel 2013 it’s pretty straight forward (you don’t even need to know how to use Power Pivot). Let me show you:
Step 1 Excel 2013: Insert a PivotTable and at the Create PivotTable dialog box check the ‘Add this data to the Data Model’ check box (tip: the ‘Data Model’ is Excel 2013 speak for ‘Power Pivot’):
In Excel 2010 it’s a bit more involved:
Step 1 Excel 2010: Select your data > go to the PowerPivot tab > click on the ‘Create Linked Table’ button.
Step 2 Excel 2013: Create your PivotTable (Insert tab > PivotTable) bring in all the fields you need (it’ll be ugly at first but stick with me):
Step 2 Excel 2010: In the Power Pivot window > Home tab > PivotTable > PivotTable:
Step 3: Create the Named Set by selecting any cell in the PivotTable > PivotTable Tools: Analyze tab/Options tab > Fields, Items & Sets > Create Set Based on Column Items:
Step 4: In the New Set dialog box (1) give your set a name, (2) click beside the fields you don’t want in your report (a blue background will appear to show which field is selected), (3) click the Delete Row button to remove them:
When you’re done removing the fields you don’t want your New Set dialog box looks like this:
And once you click OK, your PivotTable will look like this:
Notice in the PivotTable Field List that you have a new field called ‘Sets’:
This field can be added to any PivotTables you create which share the same source data and Pivot cache. Nice.
Other uses for Named Sets
Create groupings of regions/departments for specific salespeople or department managers. Heck, any grouping you want.
In the example above we created groupings based on Column items but you can also create groupings of Row Items.
Things to know about Named Sets
- What you may not have realised (particularly in Excel 2013) is that you have actually created a Power Pivot model, and the PivotTable you created with the Named Sets is in fact a Power Pivot PivotTable.
- In Power Pivot you cannot group dates so you’ll notice in my file that I have inserted a column for the Year field.
- Once you have added your fields to a Named Set and you’re using that named set in your PivotTable, you cannot add any of the fields to your PivotTable a second time.
BTW, Power Pivot is now officially two words however it is still written as one in the Excel Ribbon for Excel 2013.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
If you liked Named Sets why not look at what else Power Pivot has to offer. See a demonstration of Power Pivot in action and check out my Power Pivot course here.