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

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

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.
set_expressionYesText describing the set, such as "[Sales].[Product].[Product].Members", or a range of CUBEMEMBER cells.
captionOptionalText to show in the cell.
sort_orderOptional0 none (default), 1 ascending, 2 descending, 3 alphabetical A–Z, 4 Z–A, 5 natural ascending, 6 natural descending.
sort_byOptionalThe 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.

=CUBESET("ThisWorkbookDataModel","[Sales].[Product].[Product].Members","Products",2,"[Measures].[Total Sales]") Explain

Example 2 — Total across a set

With the set in B1, add up Total Sales for everything in it.

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]",B1) Explain

Common mistakes

  • Sorting doesn't happen. Sort orders 1 and 2 need the sort_by measure; without it nothing changes.

Notes

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.