Using GetPivotData in Excel To Get Pivot Values Instantly
Pivot tables from Microsoft are a great boon in Excel. Previously, getting IT departments to write custom reports used to take ages. And even if you could get a report written for your needs, by the time you got it, it was too late, or you wanted to look at information from another angle.
With Pivot tables, you can now create your own reports in no time. And you can slice and dice information pretty easily, with just a few clicks.
However, even though Pivot tables are great, they are not the best tool for presenting information for the senior management. There may be times when you want to pick up certain information from the pivot table, format it nicely, and present it with other summary figures.
To use the summarized data from the pivot table, but make it more presentable, you can use an extremely useful function of Excel called the GetPivotData.
Enabling GetPivotData Button
It is very easy to get this function to work. This little gem is hidden right within the Pivot Toolbar. Just right click on the Pivot Toolbar, right at the end, and select customize. Pick Add/Remove Buttons.
Select GetPivotData button. This is a toggle button – click it once and it gets enabled, and another click disables it. You can see a slight change in the icon when it is enabled or disabled.
Once the button is highlighted, you can begin writing your formula. Start with a = sign in a black cell where you want a pivot table value. then point to any cell in the Pivot Table. Its value is captured in your formula.
As long as the data is available and visible in the Pivot table, you can move the data around from rows to columns or page fields, but it will still appear correctly in the presentation area.
Go ahead. Give it a try and make your data presentation summaries more dynamic and presentable. Any questions or comments, do let me know.
Hope you find this tiny tip useful…
We offer classroom and online trainings for your benefit. The Advanced Excel training does cover Pivot Table reports and options in greater depth.
We’ll teach you how to create a pivot table from scratch, build it, design it, and use it to quickly summarize large amount of data in no time.
Founder & Editor of Microsoft Excel Tips Blog at Intellisoft Systems in Singapore