Fed Up with GETPIVOTDATA Function!

Pivot tables plays the important role in analyzing huge chunk of data but with that comes GETPIVOTDATA Function which probably upsets many people while dragging or Ctrl+D 😅.

GETPIVOTDATA Function with Syntax

Biggest Question to many People:

Why Dragging or Ctrl+D is not working with GETPIVOTDATA Function? 🧐

Before understanding how the GETPIVOTDATA function works first, we should understand how functions in general work when we assign them the fields. Each Function has its own Syntax like VLOOKUP Function has the following Syntax:

VLOOKUP Function with Syntax

1. lookup_value >> input or value to search for
2. table_array >> table in which the above input should be looked up
3. col_index_num >> nth column from which the data has to be taken from
4. range_lookup >> Whether the output has to be Exact Match (0, FALSE) or Approximate Match (1, TRUE)
5. Syntax with [] box bracket was optional in every function

Like VLOOKUP and every other function, the GETPIVOTDATA function also has its own syntax to work, only difference this function is auto-generated whenever we refer to the data in Pivot Table or can be constructed manually also. let us take a quick example to understand the syntax of the GETPIVOTDATA:

Image 1 of GETPIVOTDATA Example

Decoding the Syntax of GETPIVOTDATA:

1. data_field >> header of original data from which the Pivot table is created
2. pivot_table >> reference to the first cell of the Pivot Table which refers to the Pivot table from which data has to be taken from
3. [field1, item1] >> Field - Reference to Field in Pivot Table; Item - Reference to Cell from which input has to be looked up.

If I drag the formula down or press Ctrl+D you know the same reference and the same data will be replicated throughout the cells, because the reference in the GETPIVOTDATA has not been changed and is static in the formula.

Image 2 of GETPIVOTDATA Example

If you notice we have updated the formula by inserting a reference to the cell A11 which contains Central instead of “Central” (locked the column) and inserted a reference to the cell B10 which contains Pen instead of “Pen” (locked the row). Now if you drag the formula downwards to other cells or press Ctrl+D the formula will work well! 🤩

Image 3 of GETPIVOTDATA Example

Even after this lengthy explanation still you want to get rid of this function when referring to the Pivot Table🤪. Don’t worry there is a way!

Two Ways to disable the GETPIVOTDATA function:

1. If you have any Pivot Table in hand:

PivotTable Analyze >> Pivot Table >> Options >> Untick the Generate GetPivotData.

Image 1 of Disabling the GETPIVOTDATA feature!

2. If you don’t have any Pivot Table with you at the moment:

File >> Options (Alt+F+T) >> Formulas >> Untick the Use GetPivotData Functions for PivotTable references.

Image 1 of Disabling the GETPIVOTDATA feature!

Idea Credits: Partially inspired by YouTube Video in Profectus Academy Channel by Viswanathan M B

Hi Folks, I am Aswin, a qualified Chartered Accountant having 5 years of experience in the field of Finance, Accounts, and Auditing, currently working as an Executive — Finance & Accounts @ Jay Jay Mills (India) Private Limited (Multinational Garments Manufacturing Company).

Check out my Portfolio: Aswin’s Portfolio

Sample Excel File available at Telegram Group