PivotTables are powerful but inflexible. Their layout is fixed and cannot be freely formatted. CUBEVALUE and CUBESET solve this. They let you query the Excel Data Model directly from worksheet formulas, returning exactly the values you want, placed exactly where you want them. The result looks like a normal spreadsheet but is fully powered by the Data Model.
This guide covers how CUBEVALUE and CUBESET work and six practical examples. These include a single KPI cell, a custom layout report, dynamic member sets, conditional filtering, multiple measures side-by-side, and refreshing CUBE formulas automatically.
How CUBEVALUE and CUBESET Work
Essentially, CUBEVALUE retrieves a single aggregated value from the Data Model. It takes a connection name, a measure expression, and one or more member expressions that define the filter context. CUBESET, by contrast, defines a named set of members from a dimension — such as a filtered list of products or dates — which can then be used as a filter argument inside CUBEVALUE.
Examples 1–4: CUBEVALUE and CUBESET in Practice
This is the simplest CUBEVALUE use case. A single cell displays a Data Model measure. The measure updates automatically when the underlying data changes. This is particularly useful for dashboard header cells that show a KPI without the visual noise of a PivotTable.
Each row in a custom report can show a different member. For example, row 5 shows North revenue, row 6 shows South revenue, and row 7 shows East revenue. Each uses the same CUBEVALUE formula with a different member expression. The layout is completely free — you can merge cells, add branding, or place values anywhere on the sheet.
CUBESET defines a reusable set of members. For instance, you might define a set of the top five products by revenue. That set can then be used as a filter argument in multiple CUBEVALUE formulas. This approach is more efficient than writing individual member expressions for each cell.
A single CUBEVALUE row can reference multiple measures side-by-side using separate formula cells in adjacent columns. For example, column B shows Total Revenue and column C shows Total Units for the same region. This creates a dense, custom-formatted report that a PivotTable cannot replicate without significant manipulation.
Examples 5–6: Dynamic Members and Slicer Integration
You can build the member expression dynamically using a cell reference. For example, a dropdown in cell A2 contains region names. The CUBEVALUE formula uses CUBEMEMBER to convert the selected region name into a valid MDX member. This consequently creates a dynamic report that changes based on the dropdown selection.
CUBE formulas do not respond to slicers directly. However, you can connect a slicer to a hidden PivotTable and then reference the PivotTable’s current filter in the CUBE formula via CUBERANKEDMEMBER. This approach effectively lets slicers drive a custom CUBEVALUE-based layout. It is a common advanced dashboard technique.
Common Issues and How to Fix Them
The CUBEVALUE formula returns #N/A or #VALUE!
The most common cause is an incorrect measure or member name. MDX member names are case-sensitive and must exactly match the name in the Data Model. Specifically, verify the measure name by inserting a PivotTable from the same Data Model and checking the exact field name in the Values area. Also confirm that the connection name is "ThisWorkbookDataModel" — this is the standard name for the built-in model.
CUBE formulas display #GETTING_DATA indefinitely
This usually means the Data Model has not finished loading. CUBE formulas are asynchronous and display #GETTING_DATA while they wait for the model to calculate. If the model never finishes, the data source may have an error. Specifically, go to Power Pivot > Manage and check for any refresh errors in the tables listed there. Refreshing the Data Model manually often resolves this.
Frequently Asked Questions
-
What is CUBEVALUE in Excel?+CUBEVALUE is an Excel function that retrieves a single aggregated value from the Data Model. It takes a connection name, a measure name in MDX format, and one or more member expressions that define the filter context. The result behaves like a regular cell value and can be formatted, charted, or used in other formulas. Unlike a PivotTable, CUBEVALUE lets you place the result in any cell, in any layout.
-
What is CUBESET used for?+CUBESET defines a named set of dimension members — for example, the top five products by revenue, or all products in a specific category. The set is stored in a cell and can then be referenced as a filter argument in CUBEVALUE. This avoids repeating complex MDX expressions across many cells and makes complex filtered reports easier to build and maintain.
-
Do CUBE functions work without Power Pivot?+Yes. CUBE functions work with the native Excel Data Model, which is available without enabling the Power Pivot add-in. You need at least one table loaded into the Data Model (via Data > Relationships, or by loading a Power Query table to the Data Model). Power Pivot adds advanced features like DAX measures and the Diagram View, but it is not required for basic CUBEVALUE use.
-
Why is CUBEVALUE showing #GETTING_DATA?+CUBE formulas are asynchronous — they display #GETTING_DATA while waiting for the Data Model to finish calculating. This is normal on first open or after a refresh. If it persists, the Data Model may have a load error. Check Power Pivot > Manage for any table errors. Also check that all data connections are valid and have not expired or lost credentials.