Create Custom Reports From Pivot Tables

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.

getpivotdatarg01

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.

getpivotdatarg02

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.

getpivotdatarg06

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.

________________

You may also like...

10 Responses

  1. XLarium says:

    Hello Debra

    The link is not correct:
    http://blog.contextures.com/archives/2014/11/13/create-custom-reports-from-pivot-tables/www.contextures.com/excelgetpivotdatareports.html

  2. David Henderson says:

    I think the correct link should be:

    http://www.contextures.com/excelgetpivotdatareports.html

    Have to say that this looks really useful stuff. Can’t wait to ready fully.

  3. Omar says:

    So I have been dabbling in pivot tables, but never can get them to give me the final information I want. And they always look ugly. I hadn’t been thinking of them as intermediate tables.

    Roger’s article has given me some good food for thought on how I can use pivot tables for the heavy summarizing, and then I can take that information to put into the final report. My default solution has been to use SUMIFS and COUNTIFS (along with a selection of INDEX/MATCH formulas) to pull my information from the data tables, but on large reports, they can be very processor intensive. The pivot table as the middleman won’t reduce the complexity of development or design in my circumstances, but could make the report much more efficient.

    Don’t tell my boss, I’ve selling them on needing a high end laptop for running these reports I’ve been creating!

  4. Roger Govier says:

    @Omar
    PT’s are so much faster and efficient, especially with large datasets so the processing load is much lower.
    A more powerful laptop / computer is never wasted however, especially if you can then use the 64 bit versions of Office.
    Then, you can really have fun with the new “gems” inside Excel, Power Query (it’s to die for) and then Power Pivot and Power view

    Regards
    Roger

  5. Omar says:

    I’ve tested this now. An interesting feature is that GETPIVOTDATA only sees filtered information when returning the results. I was able to put Slicers on a report page to control the pivot table. In this way, the user will have very easy control of what range of information they see. They don’t have to even know the pivot table exists!

  6. Roger Govier says:

    @Omar
    Not quite
    That is not an interesting feature of GetPivotData, it is the way that Pivot Tables work.

    When you apply a Slicer, you are applying a filter, and the data returned within the Pivot Table represents what is true for the Slicer applied.
    GetivotData is not “creating” this feature, it is just plucking off the Pivot Table those pieces of data in the Row and Column elements you have specified that are the result of the Slicer setting applied to the PT.

  7. Paul says:

    Thanks for this more comprehensive approach.
    As someone who goes back to before Excel even existed I have always looked at pivot tables as a means to an end rather than an end in themselves. GetPivotData is a great way to allow presentation of data in useful ways for end users without them needing to understand how the figures got there.

Leave a Reply

Your email address will not be published. Required fields are marked *