Skip to content

Using Named Sets in a PivotTable

    Sets allow you to select which field items are displayed within a PivotTable.  Take the following scenario to see exactly how useful Sets can be.

    In the PivotTable below we are showing budget and budget variance data for each department.  The final year 2017 should only show budget figures as at the time of writing 2017 is in the future.

    02-08-2016 14-25-28

    Unfortunately, PivotTables don’t allow us to display fields for some items and not for others.  The field is either present or not present within the PivotTable.  That is true unless you employ named Sets. With Sets we can have a report that looks like this – note without the 2017 VARIANCE column.

    02-08-2016 14-38-08

    So how do we achieve this? Well the bad news is that you can’t create Sets in normal PivotTables – you have to use the Data Model which is part of Power Pivot.  The good news is that the Data Model is readily available in certain versions of Excel and/or if you have the Power Pivot add-in installed. If you have Excel 2010 you will need to install the Power Pivot add-in. Excel 2013 has the Data Model installed in some versions, including Excel 2013 stand-alone. Excel 2016 has it installed by default.

    So assuming you have a version of Excel that includes the Data Model, how do you go about using it?

    STEP 1: Start by creating your PivotTable in the normal way, but in the Create PivotTable dialog make sure you tick the Add this data to the Data Model checkbox.

    02-08-2016 14-20-15

    STEP 2: Construct your PivotTable and when ready start to create the Set or Sets you require. To create a Set, click into your PivotTable and then click the Ribbon’s Analyze tab (Options tab in older versions).  Now click the Fields, Items & Sets button. In the menu click Create Set Based on Column Items or Create Set Based on Row Items.

    Sets dialog

    The New Set dialog will open.

    02-08-2016 14-33-56

    STEP 3: Give the Set a name in the Set Name box and then delete the rows that you don’t want to include in the Set. We need to delete the 2017 VARIANCE row.

    02-08-2016 14-36-15

    STEP 4: Click OK to confirm and your PivotTable will now exclude this column.

    In the Fields List the Set appears as an additional field.  As we were creating a Set for column items the Set was automatically added to the columns area.  The Set can of course be moved to the rows area but it cannot be used in the filters area.

    Field List Showing Sets

    Watch our video demonstration of how to created named sets…