Excel Data Cube Overview

What is a Data Cube?

A data cube is a live window into your dataset. It allows for fast aggregation and analysis across different dimensions (such as time, customer, product) and measures (such as revenue, count, margin). Cubes behave like pivot tables, but with deeper connections to your data source.

What is a Pivot Table?

A PivotTable is Excel’s data summarization tool that allows you to:

• Automatically sort, total, count, or average data.

• Drag and drop fields to restructure reports.

• Rotate (or “pivot”) your view to look at data from different angles.

Cubes and PivotTables work hand in hand — the cube supplies structured measures and dimensions, while the PivotTable provides the interface for analysis.

The PivotTable Field List

When you open a cube in Excel, the PivotTable Field List appears. It is divided into two areas:

• Field area (top): Lists all available fields. Check/uncheck boxes to add or remove them.

• Layout area (bottom): Drag fields into Rows, Columns, Values, or Filters to design your report.

Managing the Field List

• If the Field List is hidden, click anywhere inside the PivotTable.

• To reopen a closed Field List, right-click in the PivotTable → Show Field List, or use the Ribbon (PivotTable Tools → Options → Show group).

• If fields are missing, right-click in the PivotTable and select Refresh.

• The Field List can be docked, resized, or undocked for easier layout adjustments.

Adding and Arranging Fields

• Row Labels: Drag a field here to group results down the left side.

• Column Labels: Drag a field here to create groups across the top.

• Values: Drag numeric measures (such as Revenue, Gross Profit, Customer Counts) into this section. Note: only fields listed under the “Measures” section of the Field List can be added here.

• Filters (Report Filter): Apply filters that affect the entire PivotTable.

Removing Fields

• Clear the field’s checkbox,

• Drag it out of the layout area, or

• Click the drop-down on the field and select Remove Field.

Expand & Collapse Data

PivotTables allow you to drill into details or collapse to summary levels.

Ways to expand or collapse:

• Double-click an item.

• Use the +/– icons next to items.

• Right-click → Expand/Collapse → choose options such as Expand Entire Field or Collapse Entire Field.

Refreshing the Cube and PivotTable

Your PivotTable only shows the latest cube data after refresh.

• From the EBM Office Bridge ribbon: click Refresh All Cube(s).

• In the Excel Ribbon → PivotTable Analyze tab → Refresh → choose Refresh or Refresh All.

• Or right-click the PivotTable → Refresh.

Subtotals & Grand Totals

You can customize how subtotals and grand totals appear in reports.

• Subtotals:

  • Select a field → Ribbon → Options tab → Field Settings.

  • Choose Automatic to show subtotals, or None to remove them.

• Grand Totals:

  • Ribbon → Design tab → Grand Totals.

  • Choose to show totals for rows, columns, both, or neither.

Freeze Panes (Keep Headers Visible)

When working with large cubes, it’s often helpful to keep headers visible while scrolling.

• In Excel, go to View → Freeze Panes.

• Options include:

  • Freeze Top Row

  • Freeze First Column

  • Freeze Panes (for locking multiple rows and columns at once)

Best Practices

Turn Off GetPivotData

By default, Excel inserts long GETPIVOTDATA formulas when referencing PivotTable cells. To simplify formulas:

• Go to Excel Options → Formulas → Working with Formulas.

• Uncheck Use GetPivotData functions for PivotTable references.

Before:

After:

This makes cell references cleaner and easier to audit.

Set Default PivotTable Layouts

You can define how new PivotTables behave:

• Excel Options → Data → Edit Default Layout → PivotTable Options…

Recommended settings:

1. Define how to handle error values (e.g., display zero instead of #DIV/0).

2. Define how to display empty cells (e.g., dash or blank).

3. Turn off Autofit column widths on update to preserve formatting.

4. Hide expand/collapse (+/–) buttons for presentation-ready reports.

5. Enable Show items with no data on rows/columns to keep structure consistent across reports.

How Often Does the Cube Update?

• Scheduled: Cubes pull data from underlying systems at defined intervals.

• Manual: An administrator can trigger processing when needed.

• Once processed, updates are available by refreshing in Excel or Power BI.

Next Up

For next steps, explore how to modify existing cubes in Excel and formatting best practices to optimize your reporting experience.

Excel Data Cubes and PivotTables together give you a powerful way to analyze financial and operational data:

• Cubes supply the structured measures and dimensions.

• PivotTables provide the drag-and-drop interface for analysis.

• With refresh options, field list controls, and formatting best practices, you can build consistent, presentation-ready reports that update in seconds.