If you’re a pivot table fan, like I am, you know how quick and easy it is to summarize a massive amount of data, with just a few clicks. You can show sums, counts, averages, and other totals, without using any fancy formulas.
In the screen shot below, the pivot table is summarizing income and expenses, and there is a Slicer at the top left, for quick filtering.
Formatting Restrictions
As wonderful as pivot tables are, they do have some limitations, and you might not be able to get the layout exactly the way you need it. In the screen shot below, you can see a P & L statement, based on the same data as the previous pivot table.
You’d never be able to get the pivot table in exactly this layout, with its blank rows, and formatting, and there are additional formulas at the right side too.
Create a Custom Report
Roger Govier is a pivot table fan too, and he has created a solution for building his own custom reports, like the P & L statement shown above. Roger creates a pivot table first, and then he uses the GetPivotData function, to pull specific data into his custom layout.
In the formulas, Roger uses cell references to the row and column headings, so he just has to create one GetPivotData formula, then copy it into all the data cells of the custom layout.
Another smart trick is that Roger adds headings at the top of the sheet too, and refers to those cells, instead of hard coding the field names into the formulas.
Use INDEX and MATCH Instead
If you don’t want to use the GetPivotData function, Roger also show how you can create named ranges, based on the pivot table. Then, use the INDEX and MATCH functions to extract the applicable data, and build the custom report.
He even has sample code that you can use, to automate building the named ranges.
Download the Sample File
To see the detailed instructions, and download Roger’s sample file (with or without the VBA code), please visit the Build Custom Reports With GetPivotData page, on my Contextures website.
________________
It’s almost Halloween, and perhaps you’re familiar with the horror story that I’m about to tell you. It’s a frightening tale of an Excel workbook that was destroyed forever, by a well-intentioned, but easily-distracted genius.