MrExcel.com - Your One Stop for Excel Tips & Solutions

January Actuals and February Plan

March 31, 2023 - by

January Actuals and February Plan

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.
Figure 1070. Show Actuals for some months and Plan for other months.

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.
Figure 1071. Create a named set.

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.
Figure 1072. You can delete specific columns from the set.

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.
Figure 1073. Keep deleting columns until only the desired items are left.

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.
Figure 1074. The named set leaves an asymmetric pivot table.

This article is an excerpt from Power Excel With MrExcel

Title photo by Brett Jordan on Unsplash