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

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. So assuming you have a version of Excel that includes the Data Model, how do you go about using it? Excel 2013 has the Data Model installed in some versions, including Excel 2013 stand-alone.

If you have Excel 2010 you will need to install the Power Pivot add-in. 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. 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. With Sets we can have a report that looks like this – note without the 2017 VARIANCE column. That is true unless you employ named Sets. The field is either present or not present within the PivotTable. Unfortunately, PivotTables don’t allow us to display fields for some items and not for others. The final year 2017 should only show budget figures as at the time of writing 2017 is in the future. In the PivotTable below we are showing budget and budget variance data for each department. Take the following scenario to see exactly how useful Sets can be. Sets allow you to select which field items are displayed within a PivotTable.

Excel 2013 create pivot table options