CUBESET Function
Defines a set of members — such as every product, sorted by sales — for other cube functions to use.
Category
Cube
Available in
Excel 2007+ for Windows, Microsoft 365
Returns
A set of members (shows the caption)
Overview
CUBESET asks the cube for a whole group of members at once: all products, the top regions, every month of 2026. The cell shows a caption, and other functions use the set.
Pair it with CUBERANKEDMEMBER to list the members one by one, CUBESETCOUNT to count them, or CUBEVALUE to total across them.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| connection | Yes | The 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. |
| set_expression | Yes | Text describing the set, such as "[Sales].[Product].[Product].Members", or a range of CUBEMEMBER cells. |
| caption | Optional | Text to show in the cell. |
| sort_order | Optional | 0 none (default), 1 ascending, 2 descending, 3 alphabetical A–Z, 4 Z–A, 5 natural ascending, 6 natural descending. |
| sort_by | Optional | The measure to sort by — needed for sort orders 1 and 2, e.g. "[Measures].[Total Sales]". |
Examples
Example 1 — Every product, best sellers first
The cell shows the caption “Products”; the set is sorted by Total Sales, largest first.
Example 2 — Total across a set
With the set in B1, add up Total Sales for everything in it.
Common mistakes
- Sorting doesn't happen. Sort orders 1 and 2 need the sort_by measure; without it nothing changes.
Notes
- Large sets slow the workbook — filter them in the set expression when you can.
- 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.

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.