CUBEVALUE Function

Returns an aggregated value — like total sales for North in 2026 — from the Data Model or a cube.

Category

Cube

Available in

Excel 2007+ for Windows, Microsoft 365

Returns

A number (or text) aggregated by the cube

Overview

CUBEVALUE is the workhorse of the cube functions. You tell it which measure you want (such as Total Sales) and which slice of the data (a region, a year, a product), and the Data Model hands back the total.

It gives you PivotTable numbers without the PivotTable's rigid layout — you can place each value anywhere on a report, in any order, with your own formatting.

Syntax

=CUBEVALUE(connection, [member_expression1], [member_expression2], …)

Arguments

ArgumentRequired?What it does
connectionYesThe connection name in quotes. For the workbook's own Data Model it's "ThisWorkbookDataModel"; for an Analysis Services cube, use the name shown in Data › Queries & Connections.
member_expression1, 2, …OptionalText such as "[Measures].[Total Sales]" or "[Sales].[Region].&[North]", or a cell holding a CUBEMEMBER/CUBESET. Together they pick the slice to total.

Examples

Example 1 — One number from the Data Model

Total sales for the North region, typed directly as member expressions.

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Sales].[Region].&[North]") Explain

Example 2 — A flexible report grid

Row and column headers are CUBEMEMBER cells. Each value points at its row header, column header and the measure in B1.

B3fx=CUBEVALUE("ThisWorkbookDataModel",$B$1,$A3,B$2)
ABC
1MeasureTotal Sales
220252026
3North412,300455,900
4South389,150401,700
5East298,400336,250
Illustrative values from Northstar's Data Model.
  1. B1 holds =CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales]").
  2. B2:C2 and A3:A5 hold CUBEMEMBER formulas for the years and regions.
  3. Fill the CUBEVALUE formula across and down — the $ signs keep each part pointing at the right header.

Common mistakes

  • #N/A. A member expression doesn't exist — check spelling, brackets and the & before the item name. Point at a CUBEMEMBER cell instead of typing long expressions.
  • #NAME? or #GETTING_DATA… that never finishes. The connection name is wrong, or the connection needs refreshing (Data › Refresh All).

Notes

  • Fastest way to start: build a PivotTable from the Data Model, then PivotTable Analyze › OLAP Tools › Convert to Formulas. Excel rewrites every cell as CUBEMEMBER and CUBEVALUE formulas.
  • Slicers connected to the Data Model can drive CUBEVALUE too: pass the slicer's name (such as Slicer_Region) as a member expression.
  • Cube functions talk to a live Data Model or Analysis Services cube, so they can't run in our practice Sandbox.
  • Related functions: CUBEVALUE, CUBEMEMBER, CUBESET, CUBERANKEDMEMBER, CUBESETCOUNT, CUBEMEMBERPROPERTY, CUBEKPIMEMBER, GETPIVOTDATA.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.