CUBESET in Excel: Define a Cube Selection and Control Its Order
Create an OLAP set with a clear caption and sorting rule, distinguish measure sorting from alphabetical order, and diagnose set-expression errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
CUBESET defines a set of members or tuples for a cube-backed report. The server evaluates the selection and Excel retains a set result that other cube functions can consume. The caption is just a readable summary; it is not a spilled list of the members, a numeric total or a count of the worksheet cells used to define the set.
Syntax and prerequisites
=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
Use an existing workbook connection. The set expression can be MDX text or a reference to a range containing members, tuples or sets. A caption is optional. Sorting defaults to 0, retaining the existing order. Orders 1/2 require a sort-by expression; the other orders ignore it. Alphabetical tuple ordering uses the last tuple component. Microsoft’s syntax and sorting contract
The model must contain the members, expose them to the user, and permit the requested expressions. A correct-looking formula cannot supply those prerequisites. Check the intended report’s authorized connection and schema before replacing the fictional names in this guide.
Worked selection: two regions
Assume the fictional connection TrainingCube exposes two distinct members in the same Region hierarchy:
[Region].[Region].&[North]
[Region].[Region].&[South]
Assume the brace expression below is valid in that model and evaluates to those two members, in the stated order. In A2, the proposed formula is:
=CUBESET("TrainingCube","{[Region].[Region].&[North],[Region].[Region].&[South]}","Selected regions",0)
If evaluation succeeds, the visible caption would be Selected regions. The set would still represent North and South. The formula does not itself display two rows. When designing a report, put the selection summary near the results and preserve the expression separately so someone reviewing the report can tell exactly what the summary includes.
A more editable version stores the connection name in A1 and the full set-expression text in B1:
=CUBESET($A$1,B1,"Selected regions",0)
This separates report layout from the model expression. It also makes long expressions easier to inspect. It does not make invalid MDX valid or authorize a connection.
Sorting choices
| sort_order | Meaning | sort_by |
|---|---|---|
| 0 | Preserve existing order; default | Ignored |
| 1 | Ascending by a value | Required |
| 2 | Descending by a value | Required |
| 3 | Alphabetical ascending | Ignored |
| 4 | Alphabetical descending | Ignored |
| 5 | Natural ascending | Ignored |
| 6 | Natural descending | Ignored |
Natural ordering is a model/provider concept, not a promise to sort embedded digits the way a particular worksheet user expects. For a report whose order matters, document the selected rule rather than relying on how a small test happened to look.
Advanced example: sort the selection by units
Extend the toy model: North has 30 units, South has 20, and [Measures].[Units] is a valid sort expression under the selection’s context. The proposed descending formula is:
=CUBESET("TrainingCube",B1,"Regions by units",2,"[Measures].[Units]")
Under these assumptions, North would precede South. With order 1, South would precede North. This comparison follows from 20 < 30; neither order was observed on a server. The numbers are not captions and the caption is not a sorting key. Do not infer a tie-breaking policy from this two-value example: equal values require separate provider-specific validation when report stability matters.
A corresponding caption such as Regions by units explains the ordering but does not state the values. A reader needing the values should see separate measure cells. If a report labels a row Top region, make sure the set’s sorting expression matches the metric shown alongside it; alphabetical position cannot establish top performance.
Failures and maintenance
Microsoft documents #VALUE! when orders 1/2 omit sort_by, or a direct set-expression string exceeds 255 characters. Longer text can be stored in a referenced cell, up to 32,767 characters. Connection/server problems produce #NAME?; malformed expressions or dimension mismatches can produce #N/A. Session-based objects may cease to resolve after PivotTable changes. Evaluation can briefly show #GETTING_DATA…. Microsoft’s remarks
Check one concern at a time: first the connection, then membership without sorting, then the sorting metric. Keep an unsorted comparison when introducing a new order. Use LEN to inspect expression size, FORMULATEXT for an audit column and IFERROR only after preserving useful diagnostics. Replacing a failed set with a friendly label can otherwise conceal the failure from downstream report readers.
Availability and evidence
Microsoft’s dedicated reference, checked October 18, 2026, lists Excel for Microsoft 365 and Excel 2024/2021 on Windows and Mac, Excel 2019/2016, iPad, iPhone, Android tablets/phones and Excel Web App. Those labels describe the article’s scope; they do not prove that every listed client can create or refresh every OLAP connection. Check the actual workbook, provider, permissions and client before deploying a report. No subscription entitlement beyond those product labels was established here.
First introduction and last functioning build are unknown. No Excel installation, workbook connection or server query was performed. Every model and output in this guide is hypothetical or a documented conditional expectation; no native result or screenshot is claimed.