Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



GETPIVOTDATA Data Field a cell reference

GETPIVOTDATA is a brilliant formula that allows you to effectively run a VLOOKUP within a Pivot Table. It is possible to make the GETPIVOTDATA use dynamic references (i.e. you can make it look up information within a Pivot Table by referring to a cell which you can change), but the one that doesn’t work so well is the Data Field (the one that says something like Sum of Last Year). There is a small trick to make GETPIVOTDATA Data Field a cell reference

Estimated reading time: 3 minutes

YouTube GETPIVOTDATA Data Field as a cell reference

Watch, like, subscribe

To learn more about GETPIVOTDATA and its uses in Dashboards or Management Reports, have a look at our online MS Excel Dashboarding course.

Adjusting the GETPIVOTDATA formula for dynamic references

As shown below, when you create a GETPIVOTDATA formula you can refer to cells in the spreadsheet to do the lookup. So in the Item1 below instead of typing in Apr 2018, we have linked to cell C3 which has a dropdown with possible dates. This works well and makes the GETPIVOTDATA (and any reports it is in) dynamic like a Pivot Table without forcing you to be constrained with Pivot Table look and feel.

GETPIVOTDATA Data Field a cell reference 1

The problem is the Data_field. As shown above, if we link to cell C2, which contains exactly what we want (“Sum of NET”) it results in a #REF! error message, as if it doesn’t exist in the Pivot Table. If you type it in, it works.

Trick GETPIVOTDATA to read Data Field

There are a few ways to do this, but we will use a common function that most people should know, i.e. CONCATENATE (now CONCAT in the latest versions).

By putting a CONCATENATE around our reference to cell C2 (even though we are not joining anything together as shown below), it now ‘sees’ the text and pulls through the correct numbers. More importantly you can now set up the Data_Field to be dynamic as well, so that the user can decide if they want to see, for example, this years numbers, or last years numbers!

GETPIVOTDATA Data Field a cell reference

You can learn more about Pivot Tables in our MS Excel Pivot Tables Online Course.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Repeat Pivot Table row labels

How can I fill the empty labels with the headings in a Pivot Table

Excel Dynamic Range- Pivot table includes new data as it grows