We’ve been able to create PivotTables in Excel since the mid-1990s. Ever since then, people have been asking how to extract data from PivotTables using formulas. As great as PivotTables are for analysis, they are not always the best for presentation, which is why extracting data using a formula is so useful. We currently have 3 options for this, standard cell linking, GETPIVOTDATA, and CUBE functions.
In this post, we will compare these three methods. This is not an in-depth look into any one approach but intended to highlight the differences so you can make the best decisions for your scenario.
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 0053 GETPIVOTDATA vs CUBE Functions – Start.xlsx
All examples in this post use the same initial data set. This is available in the download file.
From that data set, I have created a PivotTable:
Please note that when using CUBE functions, the PivotTable creation process requires an extra step. I will take you through this in the relevant section.
PivotTables exist on Excel’s grid, with each cell on the grid having its own cell reference. Therefore, the simplest method of getting values from a PivotTable is to use standard cell references within a formula.
Extracting data from the PivotTable
As stated already, we can link any PivotTable item by using cell reference.
In the screenshot above, the formula in cell M3 is:
This formula extracts the value of 36 from the PivotTable, which is the Sum of Values for Carolyn in England.
Now let’s change our source data. For this scenario, it turns out that Christian isn’t in Brazil, but in Finland. So, in our source, change cells B3 and B7 from Brazil to Finland, then click Data > Refresh All.
Look at the screenshot above. Our cell reference to G3 has been maintained. But the data point in that cell has changed, so we are now pointing to an empty cell. The Sum of Values for Carolyn in England is in cell F3, rather than G3.
The main lesson from cell linking is that cell references do not change when the position of the underlying data changes.
We could use a formula like INDEX / MATCH / MATCH to perform a 2-dimension lookup and ensure we extract the right data for Carolyn in England. However, this defeats the benefit of using a PivotTable.
To avoid this issue, we can use one of the other extraction methods.
GETPIVOTDATA is the function most of us think about when needing to extract values from a PivotTable.
Turn GETPIVOTDATA on/off
We can set Excel to automatically create the GETPIVOTDATA function when we click on a PivotTable cell in a formula. To do this, select any cell in a PivotTable and click PivotTable Analyze > Options > Generate GetPivotData to ensure the option is turned on.
Now each time we link to a PivotTable cell, the GETPIVOTDATA formula is created for us automatically.
Extracting data from the PivotTable
Let’s change our source data back to its original state and refresh the PivotTable. Next, let’s use GETPIVOTDATA to extract cell G3 from the PivotTable.
In the screenshot above, cell M3 contains the formula:
When clicking on cell G3, the GETPIVOTDATA function was automatically created for us. The individual elements breakdown as follows
- =GETPIVOTDATA – the name of the function
- “Value” – the name of the value field
- $E$1 – the top-left cell of the PivotTable
- “Name” – the name of the first Pivot Field
- “Carolyn” – the specific element from the first Pivot Field
- “Country” – the name of the second Pivot Field
- “England” – the specific element from the second Pivot Field
If there are more fields, we can continue to add these in pairs, just like “Country” and “England” have been above.
Now let’s change our source data again from Brazil to Finland and click Data > Refresh All.
While the cell containing the data point has changed from G4 to F4, through GETPIVOTDATA, we are not linking to a cell. Instead, we are linking to the Sum of Value for Carolyn in England; therefore, the result returned is maintained.
Changing the GETPIVOTDATA function
We can make GETPIVOTDATA more flexible by using worksheet cells instead of hardcoded field names.
Linking Pivot Fields to cells
Let’s link an element from the Country field to a cell.
In the screenshot above, England has been entered into cell M5. GETPIVOTDATA has changed to the following:
The formula references cell M5, which contains the field element.
We can change this to Spain, or USA, etc., and the GETPIVOTDATA will automatically recalculate.
Linking Data Fields to cells
So, what happens if we link the Data Field to a cell?
Oh! It doesn’t work. Cell M7 now contains the text Value, which is the name of the field in the value section of the PivotTable.
The formula in cell M3 is:
But all is not lost. There is an odd workaround where we can add an empty text string at the start or end of the cell reference, and it will work.
If we change the formula in cell M3 to the following, it will work:
Try it for yourself; it works!
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
Let’s start this section by going back to our original data set.
A PivotTable can display information that is stored in either the Pivot Cache or the Data Model. These are the two methods of storing the data for the PivotTable. At creation, the Pivot Cache is used by default, but by clicking a single tick box, we can use the Data Model instead.
The Data Model is a more modern and efficient engine for handling data. Therefore, we should try to use the Data Model where we can.
The CUBE functions are a group of functions that can extract data from the Data Model. Since PivotTables and formulas can use the same source, the CUBE functions create the equivalent of extracting values from a PivotTable.
There are 7 CUBE functions, but for this post, we will only look at two: CUBEMEMBER and CUBEVALUE.
Creating a PivotTable with the Data Model
If we plan to use CUBE formulas, we must create a PivotTable in the right way.
Select a cell in the data set, then click Insert > PivotTable
From the PivotTable from table or range window select the Add this data to the Data Model option, then click OK
That’s it; there is only one additional click. The PivotTable will be created using the Data Model instead of the Pivot Cache.
We can add Pivot Fields and Value fields in the normal way.
Convert PivotTable to formulas
A PivotTable created from a Data Model can be converted into CUBE formulas.
Select a cell in the PivotTable, then click PivotTable Analyze > OLAP Tools > Convert to Formulas.
If your PivotTable has a field in the Filters section, the Convert to Formulas dialog will appear. For this example, we haven’t used the Filters section, so this box will not appear. If it does for your scenario, select the option you want and click Convert.
The PivotTable will now be converted into CUBE formulas. There is no PivotTable anymore!
We will cover how to manually change these formulas shortly.
While the formulas have been created by converting a PivotTable, if we learn the syntax required for CUBE formulas, we can write these ourselves just like other standard functions.
Now let’s change our source data again, from Brazil to Finland, then click Data > Refresh.
As we no longer have a PivotTable, it is the result of the formulas which are updated. Since there is no longer a field element called Brazil in our data set, this is now displaying as #N/A.
Prove to yourself that this works. Change a number in the source data and click Data > Refresh All; the formula results will update.
Changing the CUBE functions
The transformation process to change the PivotTable to formulas uses two different functions: CUBEMEMBER and CUBEVALUE. We’ll take a look at each in turn.
The CUBEMEMBER formula is used for the dimensions/fields.
The formula in cell G2 is:
This formula breakdowns as follows:
- =CUBEVALUEMEMBER – the name of the function
- “ThisWorkbookDataModel” – refers to the name of the data model being used. When using this methodology, the value will always be “ThisWorkbookDataModel”
- “[Table1].[Country].&[England]” – this is an MDX statement referring to the field element of England in the Country field from a table called Table1
The formula in cell E3 is:
Notice that this is a very similar structure to cell G2 that we saw above. The difference is that we are referring to the field element of Carolyn in the Name field from a table called Table1.
The formula in cell E1 differs slightly:
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of Value]")
Cell E1 is not a field/dimension to filter by, but the calculation to be applied. Therefore, it has a slightly different format.
“[Measures].[Sum of Value]” – This is the syntax for referring to an implicit measure created in a PivotTable. The first item in the square brackets will always be Measures, with the name of the calculation in the second square brackets.
For CUBE functions, anything in double quotes is a text value and can be linked to a cell, or concatenated with other text strings.
The CUBEVALUE formula retrieves values from the Data Model based on the filter context created by the CUBEMEMBER functions it is linked to.
The formula in cell G3 is:
This formula breakdowns as follows:
- =CUBEVALUE – the name of the function
- “ThisWorkbookDataModel” – refers to the name of the data model being used
- $E$1,$E3,G$2 – these are all cell links to the relevant CUBEMEMBER functions above
The CUBEMEMBER function provides the fields to use, and the CUBEVALUE retrieves the value for that filter context.
It is possible to change the reference to the calculation to be performed, for example, changing from [Measure].[Sum of Value] to [Measure].[Max of Value]. However, there is a quirk to be aware of.
Unless writing explicit measures using DAX in PowerPivot, all implicit calculations must be created within a PivotTable before referring to it in a formula. If we wanted to use [Measures].[Max of Value] we would first need to create a PivotTable, with that calculation included.
Once created, that implicit measure/calculation is available for the CUBE functions to use.
Which is best?
Which is the best option to use? Unfortunately,…. it depends.
The best is relative to your scenario. Since the Data Model is more efficient at handling data than the Pivot Cache, that would always be my preferred method. Plus, there is the added benefit that this is a steppingstone to using the Power Pivot tool in Excel.
This was an introduction to the differences between the GETPIVOTDATA and CUBE functions, but please don’t end your journey here, there is so much more to learn:
- Tips & Tricks for Writing CUBEVALUE Formulas – Excel Campus: https://www.excelcampus.com/cubevalue-formulas/
- Cube Formulas – The best Excel formulas you’re not using – Macrodinary: https://macrordinary.ca/2020/08/19/cube-formulas-the-best-excel-formulas-youre-not-using/
- An intro to Cube functions for PowerPivot – BI Gorilla: https://gorilla.bi/excel/cube-functions/
- How to use PivotTable – GetPivotData – Contextures: https://www.contextures.com/xlpivot06.html
- Excel GETPIVOTDATA function: Excel Jet: https://exceljet.net/excel-functions/excel-getpivotdata-function
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: