You can set a pivot table to automatically refresh when the workbook opens.Pivot tables do not update automatically, if the source data changes.Here are a few cautions about this technique, to keep in mind: Formulas refer to row and column headings, for flexible data summariesĬautions - Custom Reports from Pivot Table.Custom report structure can be formatted exactly the way you need it.Manually building the custom report structure, ensures the layout does not change unexpectedly.GetPivotData function does not slow down a large workbook, unlike some Excel functions.Pivot table is quick and efficient way to summarize a large amount of data.Why would you create a custom report, based on a pivot table? What are the benefits of this technique? You can use the same technique to create other types of custom reports.īenefits of Custom Reports from Pivot Table.This example builds a Profit & Loss statement, like the one in the screen shot below.In this tutorial, you'll see how to use the GetPivotData function, along with a pivot table, to get exactly the report that you need. Thanks for watching.Related Tutorials Create Custom Reports from Pivot TableĪ pivot table is a quick and easy way to summarize large quantities of data, but sometimes the pivot table can't be organized in the layout you need. Stay tuned next time and we’ll dig a little deeper.

Notice how when we change Medicare to a cell reference, A9, we can copy the GETPIVOTDATA formula as we need to. In our example, we want data from the Sum of Allowed area in the Pivot Table that contains cell A8 and we want to look in the Primary Insurance field for an item called Medicare. You’ll see there are four parameters in a GETPIVOTDATA formula the name of the field in the Values area, a cell reference from the Pivot Table, the name of the field (in the rows area this time), and the item from the rows area. Today we’ll start deconstructing GETPIVOTDATA so that we can get it to do what we need it to do. Last time I showed you how to get around GETPIVOTDATA. As you’ll remember from our last Excel Video, Excel automatically creates a GETPIVOTDATA formula for you when you click to create a formula referencing a cell from a Pivot Table.

Excel Video 310 is the first step in our effort to demystify GETPIVOTDATA.

Excel getpivotdata function true