The Excel CUBESET function defines a set of members from a Data Model or OLAP cube — all categories, the top 5 customers, the months of one quarter — optionally sorted. Sets feed CUBERANKEDMEMBER (pull items out) and CUBESETCOUNT (count them).
Syntax
| Argument | Description | |
|---|---|---|
connection | Required | "ThisWorkbookDataModel" for Power Pivot. |
set_expression | Required | MDX set, e.g. "[Products].[Category].children" or "{[A],[B]}" literals. |
caption | Optional | Display text for the cell. |
sort_order | Optional | 0 keep order (default) · 1 asc by sort_by · 2 desc by sort_by · 3 alpha asc · 4 alpha desc · 5 natural asc · 6 natural desc. |
sort_by | Optional | Measure to sort by, e.g. "[Measures].[Total Sales]" (required for orders 1-2). |
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
The top-N pattern: a sorted CUBESET in one cell, CUBERANKEDMEMBER pulling rank 1, 2, 3… below it:
Try it: interactive demo
Choose a sort mode and see how the set expression changes.
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 does .children mean in a set expression?
[Products].[Category].children means “every member one level below” — all categories. It’s the workhorse of CUBESET expressions.How do I build a top-10 list with CUBESET?
The cell just shows my caption — where's the set?
Can I list specific members instead of .children?
"{[Products].[Category].[Video],[Products].[Category].[Displays]}" defines exactly those two.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