• Excel Book Excerpt

Excel January Actuals and February Plan

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

January Actuals and February Plan

The example in “Can I Save Formatting in a Template?" on page 375 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 PowerPivot, 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:

21Fig24.jpg

Figure 987 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.

21Fig25.jpg

Figure 988 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.

21Fig26.jpg

Figure 989 You can delete specific columns from the set.

You eventually end up with a list of only the desired columns.

21Fig27.jpg

Figure 990 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 PowerPivot datasets.

21Fig28.jpg

Figure 991 The named set leaves an asymmetric pivot table.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy