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. If you haven’t used Pivots, this is a great reason to go for Advanced Excel Training.
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…
If you want to learn more, you can join us for our Basic-Intermediate Excel Training course, or for the Advanced Excel Training in Singapore.
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.
And if you are wondering What to Do after Learning Advanced Excel, check out the other exciting courses.
Cheers,
Vinai Prakash
Founder & Editor of Microsoft Excel Tips Blog at Intellisoft Systems in Singapore
P.S: Have you checked out the 5 Cool Career Options in Data Analysis?