Excel CUBE Formulas: A Must-Know for Data-Heavy Consulting Projects
Excel CUBE formulas, like CUBEVALUE and CUBEMEMBER, might just be some of the most underappreciated yet powerful tools in Excel’s arsenal. These formulas offer capabilities that extend far beyond what SUMIFS, COUNTIFS and lookups can achieve, and the best part? They’re surprisingly easy to master.
In this article, we'll explore the unique advantages of CUBE formulas, break down how they work and provide a quick tutorial to help you start using them today.
Work Faster with Accelerate Excel
Simplify your Excel work with the Accelerate ribbon comprising tools for M&A Transaction/Deal Advisory, FDD and other Excel power users.
Advantages of Excel CUBE Formulas
Dynamic Data Manipulation
Slice and dice your data like a PivotTable, but without the constraints. By integrating PivotTable filters or Pivot slicers into your formulas, you can create dynamic breakdowns, analyses as well as sophisticated dashboards.
Need to switch between normalized and reported view or focus on a specific entity? No problem!
Single Source of Truth
Reduce errors by pulling data from the same place, i.e., an OLAP cube/Data Model, ensuring consistency and accuracy.
Easy Updates
When your source data updates, simply refresh the Data Model. All connected analyses update automatically, saving you time and effort.
Integration with PowerQuery
Load, transform, and clean your data with PowerQuery. Then add CUBE formulas connected to your PowerQuery output, creating a seamless connection between data preparation and analysis.
Integration with PowerPivot
Values from the cube/Data Model can be displayed in a PivotTable, allowing dynamic exploration of data. Once satisfied with the structure, convert the PivotTable to normal Excel formulas. This feature is a real game-changer for consultants as PivotTables are rarely included in client deliverables.
Better than GetPivotData
Excel’s CUBE formulas eliminate the need for GetPivotData formulas. The CUBE formulas link directly to your dataset, avoiding the necessity of an intermediary PivotTable.
How Do CUBE Formulas Work?
The two key functions are CUBEVALUE and CUBEMEMBER. Together, they offer a more advanced and flexible alternative to the SUMIFS formula.
The CUBEVALUE function shows aggregated values retrieved from the Data Model / OLAP cube.
The syntax is:
=CUBEVALUE(connection, member_expression1, [member_expression2], …).The CUBEMEMBER function defines what the CUBEVALUE formula should aggregate / calculate. It returns a "member" that identifies elements within the cube's structure, such as a date, account number, account hierarchy, etc. It can also specify "Measures" for aggregation logic, usually a simple SUM.
The syntax is
=CUBEMEMBER(connection, member_expression, [caption]).
Note: While you can include CUBEMEMBER formulas directly in the CUBEVALUE formula, it's much easier to manage and adjust formulas later if you break down the arguments into different cells and reference those cells in the CUBEVALUE function.
An Example from Financial Due Diligence (FDD) / Transaction Advisory Services (TAS)
The CUBEVALUE formula is linked to cells containing filter and aggregation arguments
In cell F10, we use the following formula:
Let’s break it down:
"ThisWorkbookDataModel": This part refers to the connection.
Not important, as this is always the same in Excel’s Data Model.
C7:
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of VALUES]")This measure instructs the CUBEVALUE to aggregate by summing the values in the VALUES column of the Data Model.
D5: PivotTable filter
Filter to distinguish between reported numbers and adjustments (values for TYPE), such as Quality of Earnings normalizations. The slicers allow easy switching between different views.
D4: PivotTable filter
Filter for different business entities (values for ID_ENTITY).
E10:
=CUBEMEMBER("ThisWorkbookDataModel","[Account Mapping].[ID_Account].&[3000]")This formula retrieves the member "[3000]" from the "[ID_Account]" column within the "Account Mapping" dimension, instructing the CUBEVALUE to display values for account number 3000.
F9:
=CUBEMEMBER("ThisWorkbookDataModel","[VALUES].[FINANCIAL_YEAR].&[FY21]")This formula retrieves the member "FY21" from the "FINANCIAL_YEAR" column within the "VALUES" source table, instructing the CUBEVALUE to display values for the year 2021.
A Quick Tutorial
Step 1: Prepare Your Source Data
Ensure your data is in a flat file structure, which means having multiple columns, a header row with field names, and data records in the rows below.
Example of Source Data in Flat File Structure
Step 2: Load Your Source Data into Excel’s Data Model
Go to the ribbon ‘Data’ > ‘From Table/Range’.
Click ‘Close & Load’ > ‘Close & Load To’.
In the ‘Import Data’ window, select ‘Only Create Connection’ and ‘Add this data to the Data Model’.
Example of Source Data in Flat File Structure
Now, your data is added to the Data Model / OLAP cube. You can use PowerPivot, create PivotTables pulling data from the Data Model, and use CUBE formulas.
Step 3: Insert a PivotTable Linked to the Data Model (PowerPivot)
Go to the ribbon ‘Insert’ > ‘PivotTable’ > ‘From Data Model’.
Select ‘New Sheet’ > Click ‘OK’.
Arrange the PivotTable in the structure you need.
Step 4: Convert to Formulas
Click anywhere on the PivotTable.
Go to the ribbon ‘PivotTable Analyze’ > Click ‘OLAP Tools’ > ‘Convert to Formulas’ > Click ‘OK’.
Note: If you select ‘Convert filters’, the PivotTable’s filter fields are converted as well. This makes it easier to move sheets between workbooks. Moving a sheet with a Data Model-linked PivotTable object to another workbook will duplicate the Data Model, which can get messy and should be avoided.
How Does Accelerate Excel Facilitate the Use of CUBE Formulas?
Convert Pivot
When you use Excel’s native functions, the formulas come without formatting and groupings. All hierarchy levels are converted to CUBE functions, requiring manual addition of SUM or SUBTOTAL functions for higher hierarchy levels.
If you've ever compiled and formatted a detailed P&L or balance sheet, you know the effort involved, especially with multiple granular account hierarchy levels. Accelerate Excel is a game-changer, automating all the formatting work for you. Learn more.
Note: You typically aggregate or lookup at the lowest hierarchy level and use SUM or SUBTOTAL for higher levels to ensure formula integrity.
Remove Data Model
While CUBE formulas are excellent for internal analysis, sharing workbooks with the Data Model can be problematic:
The Data Model includes all your source data, even if it's not displayed on a worksheet, which you may not want to share.
It can confuse recipients who aren't familiar with the Data Model.
Accelerate Excel allows you to:
Extract selected sheets without the Data Model, converting all CUBE formulas to values.
Convert CUBE formulas in the selection to values. Learn more.
Some Practical Advice
If you’re reading this, you’re probably an Excel pro and tech-savvy. Your first instinct might be to use CUBE formulas everywhere (that was mine back in the day). Resist that urge. While powerful, CUBE formulas may not always be suitable.
When to Use vs. Not Use the CUBE Approach
When to Use
Sell-side projects: Typically longer duration, less tailored analyses (more data dump) due to a broader audience, multiple source data updates.
Multiple slicing and dicing options: By entities, profit/cost centers, business units, etc.
Multiple views analysis: Reported/Adjusted P&L on a granular level.
In situations where you would use GetPivotData. We believe CUBE formulas make GetPivotData obsolete.
When Not to Use
Small projects with little data: For instance, projects with only a few P&L line items or a single entity.
High time pressure and little experience: Avoid using CUBE formulas on tight-deadline projects if you're inexperienced. Instead, practice during downtime by replicating key analyses from past projects using the CUBE approach to build your skills.
DAX Measures
In the Data Model / PowerPivot tables, you can define how values are calculated. For example:
Create a dynamic KPI calculation, specifying that gross profit is calculated by net revenue.
Define a lookup that returns the account name based on an account number.
While many data & analytics experts praise DAX, we generally recommend avoiding it for typical deal settings due to its complexity. Most people can understand a KPI calculation based on two cell references, but not many people know how and where DAX measures are defined.
Why Not Just Use PowerBI?
PowerBI is fantastic but not suited for most TAS / deals projects. In our opinion, PowerBI excels in:
Accounting, FP&A, and controlling reports with static structures.
Longer-running projects (e.g., large sell-sides) with diverse stakeholders, where interactive dashboards are preferred over static PDFs or detailed Excel databooks.
Standardizable analyses where input and output are predictable and too large for Excel, such as bottom-up sales and price/volume analyses.
For most transaction advisory projects, we prefer PowerPivot and CUBE formulas due to their ease and speed of setup, similarity to SUMIFS (which is widely known and used in the industry) and customizability (live changes during client calls are a significant advantage).
Conclusion
CUBE formulas in Excel offer dynamic, powerful data manipulation far beyond what SUMIFs can do. For those in Transaction Advisory Services, they provide greater flexibility, accuracy and easier updates. If you're dealing with complex data or working on a sell-side project, mastering CUBE formulas can significantly enhance your efficiency and insights.
Keywords:
CUBEVALUE, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBESET function, CUBESETCOUNT, CUBERANKEDMEMBER, CUBEKPIMEMBER, Microsoft Excel, Excel Formulas, Excel CUBE formulas, Excel CUBE functions