How to Use the CUBESET Function in Excel

Written By Jonathan Burks

Summary: Dive into the versatility of the CUBESET function in Microsoft Excel—a potent tool designed to create custom sets of data from a data model cube. In this tutorial, tailored for data analysts with a penchant for technology, follow step-by-step instructions to seamlessly incorporate the CUBESET function into your data analysis toolkit.

The CUBESET function within Excel empowers you to generate custom sets of data from a cube within your data model, enhancing your data analysis capabilities. To leverage the potential of this function, follow these comprehensive steps:

  1. Launch Excel: Open Microsoft Excel on your computer.

  2. Prepare Data Model: Ensure you have a well-structured data model containing the cube from which you want to create a custom set.

  3. Select a Cell: Choose the cell where you want to display the custom set generated using the CUBESET function.

  4. Enter the Formula: Input the following formula into the selected cell:

    =CUBESET(connection, set_expression, caption)

    Replace "connection" with your data model connection, "set_expression" with the set of members you want to include, and "caption" with an optional caption for the custom set.

  5. Press Enter: After entering the formula, press the Enter key to execute the function. The cell will display the requested custom set from the specified cube.

  6. Example: Suppose you have a data model connection named "SalesData," and you want to create a custom set of product categories including "[Product].[Category].[Electronics]" and "[Product].[Category].[Clothing]." Use this formula:

    =CUBESET("SalesData", "{[Product].[Category].[Electronics], [Product].[Category].[Clothing]}", "Custom Category Set")

  7. View Custom Set: The cell will now display the custom set of product categories generated from the specified cube in your data model.

Summary: Embracing the CUBESET function in Excel empowers you to effortlessly generate custom data sets from your data model's cube. By following the outlined steps in this tutorial, you can seamlessly integrate this function into your data analysis endeavors.

Feel free to ask questions or share your thoughts, fostering further exploration of this topic.

Note: The CUBESET function is available in various versions of Microsoft Excel, including Excel 2013, Excel 2016, Excel 2019, and Microsoft 365.

Jonathan Burks
Previous

How to Use the CUBESETCOUNT Function in Excel
Next

How to Use the CUBERANKEDMEMBER Function in Excel