Let’s assume that from the following sales database:
…you have created a small but pretty pivot table:
But your manager doesn’t really want her, but something similar to:
That is, we have several tangible difficulties:
- original Pivot table looks wrong — report design must comply with corporate standards (colors, logos, sparklines, arrows, etc.). “File-finishing” the design of the pivot is a long and painful process. And it’s not a fact that beauty won’t fly off after recalculation and updating.
- From the entire summary to report to you not all data needed, but only specific Ford models according to Peter will have to be filtered by hand.
- Standard the results in the summary do not suit us, because you need amounts for revenue in green cells, but the average for the month in the totals – the summary does not know how.
- The results obtained in the summary are not the end yet, we need to make some additional calculations: recalculate revenue in thousands, add forecast for April, compare this year with the past. Much of what is listed in the summary is either impossible in principle (especially for summary based on OLAP cubes) or is done, but “through one place” using calculated fields and objects.
- It is necessary to build a cunning diagram (regular pivot charts have many limitations).
An elegant solution to all these problems can be the function GET.PIVOT.TABLE.DATA (GETPIVOTDATA), which can extract the data we need from the summary in order to use them in other tables or calculations.
Let’s make a report blank on a separate sheet from the summary sheet:
Select the first cell in the green range, enter an equal sign, and click on the cell in the pivot that contains the desired data, i.e. on B8, where Fiesta’s revenue for January lies. Instead of the usual reference a la “sea battle”, Excel will insert the GET.DATA.PIVOT.TABLE function:
Let’s break it down in detail:
- Her first argument“Revenue”) is the name of the field to retrieve.
- Second (Sheet1!$A$4) is the address of the first cell of the pivot table where we get the data from. This parameter is needed because there can be several summary ones on the sheet and Excel must understand from which one you need to pull out the number.
- All other arguments, starting from the third, are the name of the field and its value in pairs, i.e., in our case, this is the name of the model (Name=”Fiesta”) and time period (Date=1). Since the grouping of dates by months was applied in the summary, in the GET.DATA.SUMMARY.TABLE function we received not the name of the month, but its number. If the original database had a column not with a date, but with the name of the month, then the grouping would not be needed and instead of one, it would simply be “January”.
And now the fun part.
Carefully replace “Fiesta” with $C7 in the formula, and one with D$5 and add division by 1000 at the end of the formula, because we need to display the data in thousands. Then click on Enter and drag the formula over the remaining green cells.
Now you can work with data in green cells as with regular formulas, and not as with a pivot table with its strict restrictions, i.e. then you can safely calculate any results, dynamics, forecasts, build any diagram, etc.
If the source data changes, then you only need to update the summary (right-click – Update & Save). Even if the number of models changes tomorrow or, say, the Fiesta is no longer the third car in the summary, our function will still find it and correctly extract the corresponding result for our report.
- What are pivot tables and how to build them
- Set up calculations in PivotTables
- Grouping dates, numbers, and text in pivot tables