SOLVED

Excel function to capture columns of pivot table values?

Brass Contributor

Hello,

I am aware of the GETPIVOTDATA function in Excel, but, as far as I can tell, it can only grab a value from a particular cell in a given Pivot Table.  Is there a way to copy a whole area of Pivot Table values (like multiple rows within a column(s)), rather than just one cell at a time?  I'm trying to avoid having to manage multiple cells with GETPIVOTDATA formulas in them.

 

Thank you! 

7 Replies
best response confirmed by PatDools (Brass Contributor)
Solution

@PatDools 

You can use the INDEX and MATCH functions together to capture columns of Pivot Table values in Excel.

Here’s how:

  1. Select the cell where you want to display the first value from your Pivot Table.
  2. Type =INDEX( in the formula bar and select the range of cells that contains your Pivot Table data.
  3. Type ,MATCH( and select the cell that contains the column heading for the data you want to capture.
  4. Type ,0) to complete the formula.

This will return the first value from your Pivot Table column.

To capture multiple values, you can copy this formula down to other cells in the same column.

 

Here’s an example:

Let’s say you have a Pivot Table with the following data:

Region

Sales

East

$100

West

$200

North

$300

South

$400

To capture the values in the “Sales” column, you would use the following formula in cell B2:

=INDEX($A$2:$B$5,MATCH($A2,$A$2:$A$5,0),2)

This formula will return the value in the “Sales” column for the region listed in cell A2.

To capture the values for other regions, you can copy this formula down to other cells in column B.

 

I hope this helps! 

Hi @PatDools 

 

With Excel 2021/365 (PivotTable DataSource is an Excel table named Order:(

SampleOne.png

Thank you - this does the trick!
I am glad that you were helped here in the forum.
I wish you continued success with Excel!

@NikolinoDE Hi.. supposing the Pivot table has variable content, for example, the data may sometimes be between A2 to A5 and sometimes, between A2:A10, can this method be modified to suit this scenario?

@anirbangoa 

You can modify the method to handle variable content in the Pivot Table range. Instead of hardcoding the range in the INDEX function, you can use dynamic range references. One common approach is to use structured references or Excel tables, which automatically adjust their size as data is added or removed.

Here is how you can modify the formula to use structured references:

=INDEX(Table1[Sales], MATCH($A2, Table1[Region], 0))

In this formula:

  • Table1 is the name of your Excel table, which automatically expands and contracts as you add or remove data.
  • Sales and Region are the column headers within your table.

This formula will adjust automatically based on the size of your table. If the Pivot Table range changes, the formula will still capture the correct values based on the updated range.

If you are not using Excel tables, you can still achieve dynamic range references using functions like OFFSET, INDEX, and COUNTA. These functions can help you create range references that adjust dynamically based on the size of your data range. However, using Excel tables (structured references) is generally the more straightforward and user-friendly approach.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

@PatDools 

Perhaps this might be a solution.

If someone will tell me how to link to my workbook I will post it.

I use measures.

Row LabelsSumCtIndXtCf>Indx<Grand Total1659816.800

A18901.4400
C11761.2800
E17871.4400
G12820.6412820.64
I11831.2800
K9521.2800
M17672.400
O8861.2800
Q14891.7600
S13410.9613410.96
U10700.9610700.96
W8901.2800
Y8850.800
1 best response

Accepted Solutions
best response confirmed by PatDools (Brass Contributor)
Solution

@PatDools 

You can use the INDEX and MATCH functions together to capture columns of Pivot Table values in Excel.

Here’s how:

  1. Select the cell where you want to display the first value from your Pivot Table.
  2. Type =INDEX( in the formula bar and select the range of cells that contains your Pivot Table data.
  3. Type ,MATCH( and select the cell that contains the column heading for the data you want to capture.
  4. Type ,0) to complete the formula.

This will return the first value from your Pivot Table column.

To capture multiple values, you can copy this formula down to other cells in the same column.

 

Here’s an example:

Let’s say you have a Pivot Table with the following data:

Region

Sales

East

$100

West

$200

North

$300

South

$400

To capture the values in the “Sales” column, you would use the following formula in cell B2:

=INDEX($A$2:$B$5,MATCH($A2,$A$2:$A$5,0),2)

This formula will return the value in the “Sales” column for the region listed in cell A2.

To capture the values for other regions, you can copy this formula down to other cells in column B.

 

I hope this helps! 

View solution in original post