Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2021, 2019, 2016 and Mac versions more…

Excel CUBEVALUE function

This article explains how the Excel CUBEVALUE function can be used to extract data from an Excel data model.

An Excel data model is a type of OLAP database.  If you have no understanding of OLAP I recommend that you begin with my Excel Power Pivot 2-minute overview article first.  That puts the whole subject of OLAP and Power Pivot into context and then the lesson below will make a lot more sense. 

In 2019 Microsoft added Power Pivot to Excel making it a credible OLAP tool.  Power Pivot enabled any Excel users with the right training to create a perfect OLAP database – in Excel terminology a Data Model. 

The article below is an unedited lesson from one of the 68 short focused lessons in my Excel Expert Skills book/e-book that seeks to empower any Excel user with a complete mastery of the skills needed to create perfect Excel data models from both Excel workbook data and external data sources.

Leave me a comment if you find the article useful.

note

Why does the Excel CUBEVALUE function have the prefix CUBE?

It is common to teach and visualize OLAP concepts in the context of an imaginary three-dimensional OLAP Cube

The imaginary cube is a little like a Rubik’s cube but contains a set of aggregated values in each cell.

OLAP concepts are taught in the context of Slicing, Dicing and Rotating (Pivoting) the cube of data.

MDX queries (created by the CUBE functions) retrieve aggregated totals from specified cells (intersections of three values) from a cube of data.

note

The GETPIVOTDATA function is also supported by OLAP pivot tables

In: Lesson 9‑23: Use the GETPIVOTDATA function, you learned how to use the GETPIVOTDATA function to access values in a regular pivot table from outside the pivot table.

The GETPIVOTDATA function can only be used to reference values that are visible in a pivot table

This can be a huge problem, as a user may change the fields displayed in a pivot table, causing functions that use the GETPIVOTDATA function to fail.

If there were a function that directly accessed data from the pivot table data cache (that regular pivot tables use instead of the data model) this problem would be overcome.

Unfortunately, there is no way to do this in a regular pivot table.

The Excel CUBEVALUE function has none of these problems as data is accessed directly from the data model without any interaction with a pivot table.

OLAP pivot tables still support the GETPIVOTDATA function but you’ll probably prefer to use Excel CUBEVALUE functions in their place when working with data that originates in a data model.

Lesson 12 20: Use the Excel CUBEVALUE function to query a data model

In: Lesson 9‑23: Use the GETPIVOTDATA function, you discovered a way to reference data that is visible on the screen within a pivot table.

If you’ve completely understood this session so far, you’ll now realize that an OLAP pivot table is simply a visual and user-friendly way to display, format and present the values stored in a data model.

The Excel CUBEVALUE function enables you to obtain values directly from the data model without a pivot table.  This opens up many new possibilities when presenting and formatting data.

1. Open the Stock List-4 sample file in Excel

This workbook contains the pivot table that you created from a data model in: Lesson 12‑6: Use an OLAP pivot table to analyze data residing in a data model.

Excel cubevalue function lesson illustration

2. Attempt to insert a column or row within the pivot table.

Notice that (as with all pivot tables) this isn’t possible. 

When you attempt to insert a row or column, a warning dialog is shown:

excel cubevalue function error dialog
Click on the image above to see it more clearly

3. Convert the pivot table into a set of Excel CUBEVALUE functions.

  • Click inside the pivot table to activate it.
  • Click: PivotTable Analyze>Calculations->OLAP Tools->
    Convert to Formulas

(This option is grayed out when working with regular pivot tables).

Notice that the OLAP pivot table no longer exists but the data previously displayed remains:

Excel cubevalue function used to show same data as pivot table

Each value is now directly accessed from the data model using Excel CUBEVALUE functions.

4. Examine the Excel CUBEVALUE function for the Sum of UnitsInStock column in the Beverages category.

  • Click once in cell C4.
  • Look at the function displayed in the formula bar:

=CUBEVALUE(“ThisWorkbookDataModel”,$B4,C$3

The first argument identifies the name of the connection to the data model.  In this case it is the data model that you defined in: Lesson 12‑3: Add tables to a data model and Lesson 12‑5: Add a relationship to a data model using Power Pivot.

The last two arguments are MDX expressions.  You learned about the MDX query language in: Lesson 12‑19: Understand MDX queries and OLAP pivot table limitations.

  • Click on cell B4 and C3 to see how the MDX expressions have been created.

With your understanding of data models, you should be able to broadly understand how the references work.You have used an OLAP pivot table to automatically create the MDX code for you.  This technique allows you to create Excel CUBEVALUE functions without any understanding of the MDX language.

5. Insert a column before column C and a row before row 5.

Excel cubevalue function used to insert rows into pivot table

There is now no barrier to inserting rows and columns, as each cell contains a normal function.

You can also format the values in any way you wish.

6. Save your work as Stock List-5.

This lesson is excerpted from the above book. 

This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.

It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

Covered in Session 12 – Power Pivot, Data Modelling, OLAP and Business Intelligence

Covered in Lesson 12-20: Use the CUBEVALUE function to query a data model.

Covered in Session 13: An Introduction to DAX

Covered in Lesson 12-6: Use an OLAP pivot table to analysze data residing in a data model.

Covered in Lesson 11-33 Create a merged query using fuzzy logic.

Covered in Session 10: 3D Maps.

Covered in Lesson 11-7: Create and use a custom data type.

Covered in Lesson 9-2: Use Natural Language Queries.

Covered in Lesson 11-8: Use custom data types in formulas.

Covered in Lesson 9-1: Use automatic data analysis to create data insights.

Related Articles

Understand modern data analysis

Excel modern data analysis

This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.

Understand OLAP Pivot Tables

Excel OLAP pivot tables

This article explains the difference between a regular Excel pivot table and the new OLAP pivot table used by Power Pivot.

Understand the Power Pivot window

Excel Power Pivot window

This lesson discusses the Power Pivot window and how it differs from the regular Excel window. A sample file is included.

OLAP Star Schema

Excel data model Design

A Power Pivot data model is an OLAP database. OLAP databases can be modeled as a Snowflake or Star schema. This article explains both designs.

OLTP database example

Understand OLTP database design

A Power Pivot data model is an OLAP database. Business databases are OLTP databases. This article discusses how OLTP databases are structured.

Share this article

Leave a Reply

Your email address will not be published. Required fields are marked *

Cross-lesson hyperlinks

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader

Which tutorial do you need?