Excel CUBE Functions: Beginner to Advanced

Part 1 · Beginner

What CUBE functions are and why they matter

A normal Excel formula such as SUMIFS reads cells on a worksheet. A CUBE function reads a model instead: either the Data Model built into your workbook with Power Pivot, or an external OLAP cube such as SQL Server Analysis Services. The model already knows your tables, relationships and calculations (called measures). A CUBE function simply names which slice of the model you want and returns the answer.

Think of a PivotTable. Every value cell in a PivotTable is the intersection of a measure (for example Total Rejects) with the row and column items (for example Grade X70 and Mill A). CUBE functions let you place each of those intersections in any cell you like, mixed with normal formulas, text and formatting. You keep the power of the model and lose the rigid PivotTable grid.

The seven functions at a glance

When to use CUBE functions instead of the alternatives

Part 1 · Beginner

Set up the Data Model with the practice data

The practice workbook holds one fictitious table named Tests with 96 rows: six months (January to June 2026), two mills, two shifts and four line-pipe grades. Each row records the pipe joints inspected (Pieces), the joints rejected (Rejects) and the batch average Charpy energy in joules (CharpyJ). A Data Dictionary tab documents every column.

Requirements: Excel for Windows (Microsoft 365 recommended) is needed to build a Data Model and DAX measures. Every value quoted in this lesson comes from this workbook, so you can check your formulas against the text.

Step by step

1. Open the workbook. The data on the Tests sheet is already an Excel table named Tests (check under Table Design → Table Name). Name your tables before loading them: the table name becomes part of every member expression.
2. Add the table to the Data Model. Click inside the table, then choose Insert → PivotTable → From Table/Range and tick Add this data to the Data Model. If you have the Power Pivot tab, Power Pivot → Add to Data Model does the same job.
3. Create four explicit measures. In the PivotTable Fields pane, right-click the Tests table and choose Add Measure (or use Power Pivot → Measures → New Measure). Create the DAX measures below.
4. Confirm the connection name. A workbook Data Model is always reached through the connection named ThisWorkbookDataModel. You will type it as the first argument of every CUBE function in this lesson.

Total Pieces  := SUM ( Tests[Pieces] )
Total Rejects := SUM ( Tests[Rejects] )
Reject Rate   := DIVIDE ( [Total Rejects], [Total Pieces] )
Avg Charpy J  := AVERAGE ( Tests[CharpyJ] )

Part 1 · Beginner

Member expressions: the language inside the quotes

Every CUBE function takes text arguments that name things in the model. These strings use MDX (Multidimensional Expressions) naming, the query language of OLAP cubes. You only need a small part of MDX to be productive.

Rules that prevent most errors

• Every name sits in square brackets, especially names with spaces: [Mill A], [Total Rejects].
• The whole expression is a text string, so it is wrapped in double quotes in the formula: "[Tests].[Mill].&[Mill A]".
• Names come from the model, not the worksheet. [Tests] is the table name in the Data Model, not the sheet name.
• Let Excel help. When you type =CUBEVALUE("ThisWorkbookDataModel","[ Excel offers a list of tables, measures and members. Press Tab to accept a suggestion.
• Dates and numbers have keys that look different. A date member in the Data Model has a key such as &[2026-03-01T00:00:00]. That is why the practice data stores the month as text (2026-03). If you are unsure of a key, build a PivotTable and use Convert to Formulas (Part 6) to see the exact text Excel generates.

Part 1 · Beginner

CUBEMEMBER: name one member (or a tuple)

=CUBEMEMBER(connection, member_expression, [caption])

The cell shows the member's caption (for example X70), but behind the scenes it holds a reference to the member itself. That is what makes it useful: other CUBE functions can point at the cell.

Basic: a header cell

B1  =CUBEMEMBER("ThisWorkbookDataModel","[Tests].[Grade].&[X70]")
A2  =CUBEMEMBER("ThisWorkbookDataModel","[Tests].[Mill].&[Mill A]")
A1  =CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Total Rejects]","Rejects")

B1 displays X70, A2 displays Mill A, and A1 displays your custom caption Rejects while still pointing at the Total Rejects measure.

Intermediate: build the expression from a cell

Because the member expression is just text, you can assemble it with &. Put a grade such as X65 in cell G1 (a data-validation drop-down works well) and write:

=CUBEMEMBER("ThisWorkbookDataModel","[Tests].[Grade].&["&G1&"]")

Change G1 and every formula that points at this member updates. The first & inside the quotes is part of MDX (the key marker); the & operators outside the quotes are Excel text joins.

Advanced: a tuple in one cell

=CUBEMEMBER("ThisWorkbookDataModel",
   "([Tests].[Grade].&[X70],[Tests].[Mill].&[Mill A],[Measures].[Total Rejects])",
   "X70 rejects, Mill A")

A tuple combines one member from each of several columns, plus (optionally) a measure. If this formula sits in cell D1, then =CUBEVALUE("ThisWorkbookDataModel",D1) returns 154, the X70 rejects made on Mill A. Tuples keep long reports tidy because a whole slice lives in one cell. In everyday reports, though, the more common pattern is to keep one member per CUBEMEMBER cell and pass several of those cells to CUBEVALUE, as the next part shows.

Part 1 · Beginner

CUBEVALUE: calculate the slice

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

CUBEVALUE evaluates the model at the intersection of every member you pass. Each member_expression can be a text expression, a cell that contains CUBEMEMBER, a cell that contains CUBESET, or the name of a slicer. Columns you do not mention are not filtered (they stay at their All member). If you name no measure, the model's default measure is used, so always name one.

Basic: one number

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Pieces]")

Returns 42,130, every joint inspected in the practice data. Add a slicer member to narrow it:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Rejects]","[Tests].[Grade].&[X70]")

Returns 327 rejected X70 joints (out of 893 rejects overall).

Beginner+: several filters at once

=CUBEVALUE("ThisWorkbookDataModel",
   "[Measures].[Total Rejects]",
   "[Tests].[Grade].&[X70]",
   "[Tests].[Mill].&[Mill A]",
   "[Tests].[Month].&[2026-03]")

Returns 27. Swap the measure for [Measures].[Total Pieces] and you get 867; with [Measures].[Reject Rate] you get 0.0311, which displays as 3.11% once the cell is formatted as a percentage. Each argument may name only one column; to filter one column by several values at once, use a set (Part 7).

Intermediate: the report grid pattern

Professional CUBE reports put CUBEMEMBER headers along the top and side and a single CUBEVALUE formula in the body, copied across and down with mixed references.

A1  =CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Total Rejects]")
B1  =CUBEMEMBER("ThisWorkbookDataModel","[Tests].[Mill].&[Mill A]")
C1  =CUBEMEMBER("ThisWorkbookDataModel","[Tests].[Mill].&[Mill B]")
A2  =CUBEMEMBER("ThisWorkbookDataModel","[Tests].[Grade].&[X52]")    (X60, X65, X70 below)
B2  =CUBEVALUE("ThisWorkbookDataModel",$A$1,$A2,B$1)          copy to B2:C5

$A$1 locks the measure, $A2 locks the column (so each row reads its own grade) and B$1 locks the row (so each column reads its own mill). Change A1 to [Measures].[Reject Rate] and the whole grid switches measure.

Intermediate: blanks and arithmetic

When an intersection has no data, CUBEVALUE returns an empty, zero-length text value rather than a zero. Arithmetic on it can then produce #VALUE!. Wrap the value in N() (or test it with ISNUMBER) before doing maths with it:

=N(CUBEVALUE("ThisWorkbookDataModel",$A$1,$A2,B$1)) - N(CUBEVALUE("ThisWorkbookDataModel",$A$1,$A2,C$1))

Intermediate: drive formulas with a slicer

Insert a slicer from any Data Model PivotTable (PivotTable Analyze → Insert Slicer), then open Slicer → Slicer Settings and read Name to use in formulas, for example Slicer_Shift. Pass that name, without quotes, as an argument:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Reject Rate]",$A2,Slicer_Shift)

The formula now respects whatever the user selects in the slicer, including several selected items. You can delete the PivotTable you used to create the slicer; the slicer keeps working with CUBE formulas.

Part 1 · Beginner

Shortcut: let Excel write the formulas (Convert to Formulas)

You never have to memorise exact member keys. Excel can turn a Data Model PivotTable into CUBE formulas for you:

1. Build a PivotTable from the Data Model with Grade on Rows, Mill on Columns and Total Rejects in Values.
2. Select any cell in it and choose PivotTable Analyze → OLAP Tools → Convert to Formulas. (In older versions the tab is called Analyze or Options.)
3. If the PivotTable has report filters, Excel asks whether to convert them too. Choose to convert them if you want every cell as a formula.
4. Inspect the result: headers become CUBEMEMBER, values become CUBEVALUE, with the exact member text Excel uses.

Convert to Formulas is only available for PivotTables based on the Data Model or an OLAP source. A PivotTable built from a plain range has no model behind it, so the option is greyed out.

Part 2 · Intermediate

CUBESET: define a group of members

=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

A CUBESET cell shows only its caption. Its value is the set itself, which you pass to CUBESETCOUNT, CUBERANKEDMEMBER or CUBEVALUE.

Set expressions from simple to advanced

All grades, largest reject count first
=CUBESET("ThisWorkbookDataModel","[Tests].[Grade].[All].Children","Grades by rejects",2,"[Measures].[Total Rejects]")

A hand-picked list (set literal)
=CUBESET("ThisWorkbookDataModel","{[Tests].[Grade].&[X65],[Tests].[Grade].&[X70]}","High grades")

A range of members (first quarter)
=CUBESET("ThisWorkbookDataModel","[Tests].[Month].&[2026-01]:[Tests].[Month].&[2026-03]","Q1 2026")

Everything except one member
=CUBESET("ThisWorkbookDataModel","Except([Tests].[Grade].[All].Children,{[Tests].[Grade].&[X52]})","All but X52")

The top 2 grades by rejects (MDX TopCount)
=CUBESET("ThisWorkbookDataModel","TopCount([Tests].[Grade].[All].Children,2,[Measures].[Total Rejects])","Top 2 grades")

Only grades above a 2.5% reject rate (MDX Filter)
=CUBESET("ThisWorkbookDataModel","Filter([Tests].[Grade].[All].Children,[Measures].[Reject Rate]>0.025)","Over 2.5%")

In the practice data, the TopCount set holds X70 (327 rejects) and X65 (241 rejects), and the Filter set holds X70 alone (3.07% reject rate). A range such as &[2026-01]:&[2026-03] follows the column's sort order, which is why sortable text keys like 2026-03 are so useful.

Intermediate: a set as a multi-select filter

Pass a CUBESET cell to CUBEVALUE and the measure is aggregated over every member in the set. This is how you filter one column by several values without a slicer:

H1  =CUBESET("ThisWorkbookDataModel","{[Tests].[Grade].&[X65],[Tests].[Grade].&[X70]}","X65 + X70")
H2  =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Rejects]",H1)

H2 returns 568 (241 + 327). Because Reject Rate is a DAX ratio, using it with H1 correctly divides the combined rejects by the combined pieces instead of averaging two percentages.

Part 2 · Intermediate

CUBESETCOUNT: how many items are in the set?

=CUBESETCOUNT(set)

The only argument is a CUBESET, normally as a cell reference. With H1 holding all grades, =CUBESETCOUNT(H1) returns 4. With a Filter set it returns however many members passed the test, which makes it ideal for counting things like "how many grades are above target this month?".

=CUBESETCOUNT(CUBESET("ThisWorkbookDataModel","Filter([Tests].[Grade].[All].Children,[Measures].[Reject Rate]>0.025)"))

Returns 1 for the practice data. Its most important job, though, is to guard CUBERANKEDMEMBER so a Top N list never asks for a rank that does not exist.

Part 2 · Intermediate

CUBERANKEDMEMBER: the nth member of a set

=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

rank is a whole number: 1 returns the first member of the set, 2 the second, and so on. The order is the order of the set, so sort the set first with CUBESET's sort_order. Like CUBEMEMBER, the result is a member that other formulas can reference.

The dynamic Top N report

E1  3                                   (N, typed by the user)
F1  =CUBESET("ThisWorkbookDataModel","[Tests].[Grade].[All].Children","Grades by rejects",2,"[Measures].[Total Rejects]")
F2  =IF(ROWS(F$2:F2)<=MIN($E$1,CUBESETCOUNT($F$1)),
       CUBERANKEDMEMBER("ThisWorkbookDataModel",$F$1,ROWS(F$2:F2)),"")
G2  =IF(F2="","",CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Rejects]",F2))
      copy F2:G2 down for as many rows as the largest N you allow

ROWS(F$2:F2) produces 1, 2, 3... as you copy down, and the IF stops the list at N or at the size of the set, whichever is smaller. With N = 3 the list reads X70 (327), X65 (241), X60 (176). Change F1's sort_order to 1 for a Bottom N list.

Part 3 · Advanced

CUBEMEMBERPROPERTY: read a member's property (OLAP cubes)

=CUBEMEMBERPROPERTY(connection, member_expression, property)

In a multidimensional OLAP cube (for example SQL Server Analysis Services Multidimensional), a cube designer can attach member properties to members: a product's colour, a customer's region, a mill's commissioning year. CUBEMEMBERPROPERTY returns one of those values. property is the property name as text (or a cell holding it).

=CUBEMEMBERPROPERTY("PlantCube","[Equipment].[Line].&[Mill A]","Commissioned Year")

The connection, member and property above are illustrative names for an OLAP cube that defines such a property. The practice workbook does not.

Part 3 · Advanced

CUBEKPIMEMBER: value, goal, status and trend of a KPI

=CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])

A KPI (key performance indicator) is a measure plus a target and status thresholds, defined in the model. In the Data Model you create one with Power Pivot → KPIs → New KPI: choose the base measure (for example Reject Rate), an absolute target such as 0.02, and status thresholds. CUBEKPIMEMBER returns the KPI component you ask for as a member, and CUBEVALUE turns it into a number.

B1  =CUBEKPIMEMBER("ThisWorkbookDataModel","Reject Rate",2,"Goal")
B2  =CUBEVALUE("ThisWorkbookDataModel",$B$1,$A2)          goal for the grade in A2
C2  =CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","Reject Rate",3),$A2)   status

Pair the status with conditional-formatting icon sets (and a text label such as "Above target") so the meaning never depends on colour alone. Not every model defines trend, weight or a current time member; when a component is missing, there is nothing for CUBEVALUE to return.

Part 3 · Advanced

Advanced patterns

1. Drop-down driven dashboards

Give users data-validation drop-downs (for grade, mill, month) and build member strings from them. One header cell per drop-down keeps the formulas short:

K1  X70                                     (drop-down)
K2  =CUBEMEMBER("ThisWorkbookDataModel","[Tests].[Grade].&["&K1&"]")
L1  2026-03                                 (drop-down)
L2  =CUBEMEMBER("ThisWorkbookDataModel","[Tests].[Month].&["&L1&"]")
M2  =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Reject Rate]",K2,L2)

Add an "All" choice by testing for it: IF(K1="All","[Tests].[Grade].[All]","[Tests].[Grade].&["&K1&"]").

2. Period-over-period comparisons

Compute the previous month's key with ordinary Excel date functions, then pass both members:

L3  =TEXT(EDATE(DATEVALUE(L1&"-01"),-1),"yyyy-mm")        returns 2026-02
M3  =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Rejects]","[Tests].[Month].&["&L3&"]")
M4  =N(CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Rejects]",L2)) - N(M3)

With L1 = 2026-03, March has 144 rejects and February 159, so M4 shows -15. For year-to-date or rolling windows, use a range set such as "[Tests].[Month].&[2026-01]:[Tests].[Month].&["&L1&"]" inside a CUBESET and pass the set to CUBEVALUE.

3. Asymmetric reports

A PivotTable forces every row to share the same columns. CUBE reports do not. Row 5 can show Mill A by month while row 6 shows a year-to-date set for Mill B and row 7 shows a KPI status, all in one tidy block. This is the main reason quality and finance teams switch to CUBE formulas for monthly packs.

4. Long expressions and the 255-character limit

A single member expression longer than 255 characters makes the function return #VALUE!. Long sets (many hand-picked members) hit this quickly. Split the work: build the set once in its own CUBESET cell, or build members in CUBEMEMBER cells, and pass the cell references. References also make formulas easier to read and audit.

5. Refresh and performance

• CUBE functions calculate asynchronously. Cells show #GETTING_DATA briefly while the model answers; this is normal.
• Data → Refresh All reloads the model from its source and then recalculates the CUBE formulas.
• Reuse header cells. A sheet with 2,000 CUBEVALUE cells that reference shared CUBEMEMBER headers is faster and easier to audit than 2,000 formulas that each repeat long text expressions.
• Put the logic in DAX measures, not in Excel arithmetic on top of CUBEVALUE. A ratio measure such as Reject Rate stays correct at every level of aggregation; dividing two CUBEVALUE cells works only if you remember to do it at each level.
• CUBE functions return one value per cell. They do not spill like dynamic-array formulas, so lay out one formula per cell.

6. Error-proofing a finished report

=IFERROR(N(CUBEVALUE("ThisWorkbookDataModel",$A$1,$A2,B$1)),0)

Use IFERROR only on finished, tested reports. While building, let errors show: a #N/A tells you a member name is wrong, and hiding it would turn a typo into a silent zero.

Reference

Errors and troubleshooting

Reference

Syntax cheat sheet

=CUBEMEMBER(connection, member_expression, [caption])
=CUBEVALUE(connection, [member_expression1], [member_expression2], ...)
=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
=CUBESETCOUNT(set)
=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])
=CUBEMEMBERPROPERTY(connection, member_expression, property)
=CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])

Connection for the workbook Data Model:  "ThisWorkbookDataModel"
Measure:            [Measures].[Total Rejects]
Member by key:      [Table].[Column].&[Key]
All member:         [Table].[Column].[All]
All children:       [Table].[Column].[All].Children
Tuple:              ([Table].[ColA].&[a],[Table].[ColB].&[b])
Set literal:        {[Table].[Col].&[a],[Table].[Col].&[b]}
Range:              [Table].[Col].&[first]:[Table].[Col].&[last]
MDX helpers:        TopCount(set,n,measure)  BottomCount(set,n,measure)  Filter(set,condition)  Except(set1,set2)

Practice

Practice exercises

Use the practice workbook. Try each exercise before opening the answer, then check your number against it.

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Pieces]","[Tests].[Mill].&[Mill B]")

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Rejects]","[Tests].[Mill].&[Mill B]")

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Reject Rate]")

=CUBEVALUE("ThisWorkbookDataModel",$A$1,$A2,B$1)

H1  =CUBESET("ThisWorkbookDataModel","{[Tests].[Grade].&[X65],[Tests].[Grade].&[X70]}","X65 + X70")
H2  =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Rejects]",H1,"[Tests].[Month].&[2026-03]")

=CUBERANKEDMEMBER("ThisWorkbookDataModel",
   CUBESET("ThisWorkbookDataModel","[Tests].[Month].[All].Children","Months",1,"[Measures].[Total Rejects]"),1)

=CUBESETCOUNT(CUBESET("ThisWorkbookDataModel","Filter([Tests].[Grade].[All].Children,[Measures].[Reject Rate]>0.02)"))

Where to go next

You now know all seven CUBE functions. Rebuild one of your own recurring reports (a monthly quality pack, a scrap summary or a KPI scorecard) as a CUBE report: model the data once, write the measures in DAX, then let the formulas place every number exactly where you want it.