Quick Navigation
CUBESET Function
Summary
The CUBESET function creates a named set from an OLAP cube by sending a set expression to the server. This powerful function enables dynamic set creation for advanced multidimensional analysis in Excel.
Syntax
CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| connection | text |
Yes | Name of the workbook connection to the OLAP cube |
| set_expression | text |
Yes | MDX set expression defining members or tuples to include |
| caption | text |
No | Custom display name for the set (optional) |
| sort_order | number |
No | Sort method: 0=None, 1=Ascending, 2=Descending, 3=Alpha Asc, 4=Alpha Desc, 5=Natural Asc, 6=Natural Desc |
| sort_by | text |
No | Measure or field name used for sorting (required when sort_order needs it) |
Using the CUBESET Function
CUBESET is essential for OLAP analysis in Excel, allowing users to create reusable named sets from cube data. Use it to build dynamic Top N lists, custom hierarchies, or filtered member sets for complex reports and dashboards.
Common CUBESET Examples
Top 10 Products by Sales
=CUBESET("Sales Cube","TopCount([Product].[Product].Members,10,[Measures].[Sales Amount])","Top 10 Products",2,"[Measures].[Sales Amount]")Creates set of top 10 products sorted descending by sales amount
Products by Category (Alphabetical)","code":"=CUBESET("Sales Cube","[Product].[Product Category].Members","Product Categories",3)","description":"Returns all product categories sorted alphabetically ascending"}]},
=CUBESET("Sales Cube","[Product].[All Products].Children","Products",1,"[Measures].[Sales Amount]")Sorts product children ascending by sales amount
Natural Order Categories
=CUBESET("Sales Cube","[Product].[Product Category].Members","Categories",5)Displays categories in natural hierarchy order
Frequently Asked Questions
Common Errors and Solutions
#NAME? error
Cause: Invalid connection name or OLAP server unavailable
Solution: Verify connection exists in Data > Connections and server is running
#N/A error
Cause: Invalid MDX syntax or mixed dimensions in set
Solution: Check MDX expression and ensure all members from same dimension
#VALUE! error
Cause: set_expression exceeds 255 characters or missing sort_by
Solution: Use cell reference for long expressions and provide sort_by when required
Notes
- Displays '#GETTING_DATA…' during evaluation
- Set names must be unique within workbook
- Alpha sorting on tuples uses last tuple element
- Use with CUBESETCOUNT and CUBERANKEDMEMBER functions
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003 and earlier
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+