excel+training - pivot tables

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - Pivot Tables

excel+training - Pivot Tables

resolvedResolved · Low Priority · Version Standard

Pivot Tables

In my work I frequently need to write formulae that refer to data kept in Pivot Tables. What is the best way to write a formula that will go and look for a value stored in a pivot table?

RE: Pivot Tables

Hi Will

Thank you for your question.

The function GETPIVOTDATA will allow you to use values from inside a pivot table in a formula created outside of the pivot table area.

Here is an example of how this is used:
In your pivot table, you have Sales as in your data items area and Sum of Sales is showing in cell A3. You have Region in the Row fields area and Quarter in the Column fields area. One of your Regions is North; and you wish to extract the Quarter 2 (Qtr2) figure for the North region.

To extract this value:
Type =GETPIVOTDATA("Sales",$A$3,"Region","North","Quarter","Qtr2")

Note that all text is included inside double quotation marks " "

Alternatively if you are, for example, including other cells outside of the pivot table as part of the formula - lets say you are multiplying the value from the pivot table with another value outside of the pivot table area which is in cell K20 - you can enter =K20* then click on the cell in the pivot table and the GETPIVOTDATA function will automatically be entered as part of the formula for you.

The structure of the GETPIVOTDATA function varies depending on which version of Excel you are using (the above should work in 2002/XP or 2003) however if you have another version it would be a good idea to look up the GETPIVOTDATA function in Help before attempting to use it.

I hope this helps.
Amanda

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Jumping Between Sheets in a Book

PgDn and PgUp keys scrolls up and down a screen page in most applications.

Ctrl+PgDn and Ctrl+PgUp keys jump from one sheet in your workbook to the next, up or down through the pages.

View all Excel hints and tips


Server loaded in 0.08 secs.