Excel Cube Functions

Excel Cube Functions - An Overview

Cube Functions are one of Excel's best kept secrets for those who use Excel 2010 and higher to build reports from data in:
  • Microsoft Analysis Services (MSAS) cubes (Excel 2007+)
  • an Excel Data Model, with or without the Power Pivot add-in (Excel 2013+)
  • Power BI Data Models using the Analyze in Excel feature
  • Local cube files
  • OLAP views of some non-Microsoft databases
So what is so great about them? Watch this video or read on. Those of you who use PivotTables may have tried to move parts of the PivotTable or insert rows or columns in the middle in order to achieve a particular report layout. But Excel frowns upon that and warns you "We can't make this change for the selected cells because it will affect a PivotTable ...":
Error when moving part of a PivotTable
Excel Cube Functions, that allow you to pull data into a worksheet using functions like other Excel functions, can be placed almost anywhere. They retain the live link to the underlying database and they have parameters that determine what data value each cell should pull. After the database is updated, all users of the shared data can get the same version of the latest numbers.
OLAP Cubes
A cube or OLAP cube is a multi-dimensional database commonly used for storing analytical data (as opposed to transactional data which is usually stored in a relational database). Data is categorized by dimensions which are the "by" words when you analyze data by product, by month, by region, by channel etc. Excel PivotTables help you display and analyze data multi-dimensionally (even data in relational tables). If you have used them you know you can easily switch rows and columns and choose different slices of data (such as different geographical regions). And you may have done this with a live link to data in an Excel worksheet or an Access database. But cubes support much more complex, embedded calculations and business logic as well as hierarchies (such as roll-ups of days to months to quarters to years and organization structures) that make analysis fast and navigating the data intuitive for non-IT users. More ...
Management report example
Maintaining your reports (adding additional months or a new region) can be as simple as copying a row or column and changing the label or heading. Remember, because they are just Excel functions you can leverage your existing skills and all of Excel's other functionality - no new products to learn.

To learn more about Excel Cube Functions click here.
Share by: