January Actuals and February Plan
March 31, 2023 - by Bill Jelen
![January Actuals and February Plan January Actuals and February Plan](/img/excel-tips/2023/03/january-actuals-and-february-plan.jpg)
The example in Can I Save Formatting in a Template? used GetPivotData to show Actual for some months and Plan for other months.
Good news: Excel 2010 offers a new feature called named sets that finally allows you to create asymmetric reports.
Bad news: Named sets work only with OLAP pivot tables for this release, so you cannot use them with regular pivot tables.
Good news: If you take your regular Excel data through Power Pivot, the data becomes an OLAP pivot table, and therefore you can use named sets.
To recap the problem, you want to show Actuals for January through April and Plan for the remaining months:
![Store name is down the side. Jan Actual is in column B. Jan Plan is in column C. Feb Actual is in column D, Feb Plan is in column E.](/img/content/2023/03/21Fig24.jpg)
In the PivotTable Tools Option tab, choose Fields, Items, Sets and choose Create Set Based on Column Items.
![Open the Fields, Items, and Sets drop-down and choose Create Set Based on Column Items.](/img/content/2023/03/21Fig25.jpg)
Excel shows you a dialog with a new row for every column in your pivot table. Highlight a column and click Delete Row to remove it from the pivot table.
![In the New Set dialog, you have rows that say Jan Actual and Jan Plan. You can choose any row and click Delete Row to remove that row from the set.](/img/content/2023/03/21Fig26.png)
You eventually end up with a list of only the desired columns.
![After completing the set, you have Actuals for Jan, Feb, Mar, and April with Plan for the remaining months.](/img/content/2023/03/21Fig27.jpg)
The resulting pivot table is shown below. It is amazing how hard this is in regular pivot tables, but certainly possible with Power Pivot datasets.
![This shows the pivot table with the set applied.](/img/content/2023/03/21Fig28.jpg)
This article is an excerpt from Power Excel With MrExcel
Title photo by Brett Jordan on Unsplash