The Excel CUBEVALUE function returns an aggregated number — a measure — from a Power Pivot Data Model or OLAP cube, filtered by any members you pass it. It is the engine behind “Convert to Formulas” pivot reports: every value cell of a converted PivotTable is a CUBEVALUE.
Syntax
| Argument | Description | |
|---|---|---|
connection | Required | Connection name as text. For the built-in Data Model: "ThisWorkbookDataModel". |
member_expression1, … | Optional | Any number of members/sets that slice the result (a measure plus dimension members). Cell references to CUBEMEMBER results work too — and perform better. |
What you need first: cube functions only work against a Power Pivot Data Model (or an external OLAP cube). Add your table to the model — check “Add this data to the Data Model” when inserting a PivotTable, or use the Power Pivot tab — then reference it with the connection name "ThisWorkbookDataModel" and MDX-style member strings like "[Measures].[Total Sales]" or "[Products].[Category].[Displays]".
How to use it
Pair it with CUBEMEMBER cells so each member is defined once and reused:
The shortcut nobody knows: build a Data Model PivotTable, then PivotTable Analyze → OLAP Tools → Convert to Formulas. Excel rewrites the whole pivot as CUBEVALUE/CUBEMEMBER formulas — a free-form report layout that GETPIVOTDATA can’t match.
Try it: interactive demo
Build a CUBEVALUE formula by picking a measure and a category — the formula and the illustrative result update.
Errors & common pitfalls
#NAME? — bad connection name. The first argument must exactly match a workbook connection; for the built-in Data Model it is "ThisWorkbookDataModel", quotes included.
#N/A — invalid member expression. Table, column, or item names in the MDX string don’t match the model. Check spelling and bracket every level: [Table].[Column].[Item].
“#GETTING_DATA” flashes in cells. Normal — Excel retrieves cube results asynchronously. It resolves when the query finishes.
Performance: hundreds of separate CUBEVALUE calls query the model one by one. Pull shared members into CUBEMEMBER cells once and reference those cells in your CUBEVALUEs.
Practice workbook
Frequently asked questions
What is the connection name for Power Pivot?
"ThisWorkbookDataModel" (with quotes). External OLAP connections use the name shown in Data → Queries & Connections.CUBEVALUE or GETPIVOTDATA — which should I use?
Why is my CUBEVALUE report slow?
Can CUBEVALUE return text?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class