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