Essential to Expert Skills progression

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, 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.

Learn Excel 365 Expert Skills with The Smart Method (fourth edition) - Front cover

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.

Lessons in Session 12

Session Twelve: Power Pivot, Data
Modeling, OLAP and Business Intelligence
Lesson 12‑1: Understand data model
memory requirements
Lesson 12‑2: Install the Power Pivot
add-in
Lesson 12‑3: Add tables to a data
model
Lesson 12‑4: Understand the Power
Pivot window
Lesson 12‑5: Add a relationship to a
data model using Power Pivot
Lesson 12‑6: Use an OLAP pivot table
to analyze data residing in a data model
Lesson 12‑7: Understand OLAP pivot
tables
Lesson 12‑8: Understand modern data
analysis
Lesson 12‑9: Create a data model
directly from a relational database
Lesson 12‑10: Understand many-to-many
relationships
Lesson 12‑11: Understand OLTP
database design
Lesson 12‑12: Understand OLAP
database design
Lesson 12‑13: Understand how to
resolve many-to-many relationships
Lesson 12‑14: Create a snowflake data
model from a relational database
Lesson 12‑15: Convert a snowflake
data model into a star data model
Lesson 12‑16: Hide, remove and rename
data model columns
Lesson 12‑17: Add a calendar table to
a data model
Lesson 12‑18: Format values in a data
model
Lesson 12‑19: Understand MDX queries
and OLAP pivot table limitations
Lesson 12‑20: Use the CUBEVALUE
function to query a data model
Lesson 12‑21: Convert CUBEVALUE
functions to include MDX expressions
Lesson 12‑22: Create an asymmetric
OLAP pivot table using Named Sets
Session 12: Exercise
Session 12: Exercise Answers

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

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Leave a Reply

Your email address will not be published.

fifteen + four =

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