Excel Cube Functions

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
  • 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 (see sidebar)

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 gives you this error message:

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

Management report

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. Click here to learn more.

 
 

© 2011-17 Excelcraft.com. Microsoft and Excel are registered trademarks of Microsoft Corporation in the USA and other countries.
Template design by Andreas Viklund

Terms of Use            Privacy            Cookies            Contact