Expand and collapse lets you zoom into your dimensions directly in your ranges. Instead of building large ranges or switching reports, you can start with a summary view and expand only the rows or columns you need.
Zero-suppression ensures you see just the dimension members with data, so analysis stays quick and focused. This makes it easier to answer ad-hoc questions, validate data, and keep reports clean during meetings.
Before You Start
Select a single cell in a valid parent dimension header inside the range.
The logic applies to rows and columns.
Expand (Rows)
Select a single cell in the parent dimension row header within the range.
Select Expand from the Expand/Collapse dropdown.
Cube inserts the direct child dimension rows above the selected parent row.
Cube applies:
Number formatting from the parent row to each inserted child row.
Formulas from the parent row to each inserted child row.
No styles within the range (styles are not copied to the new child rows inside the range).
Content within the range outside of the expanded parent row.
The range address updates automatically in the add‑on.
Expanding will automatically fetch the range to refresh values for the newly added rows.
Expand (Columns)
Select a single cell in the parent dimension column header within the range.
Select Expand from the Expand/Collapse dropdown.
Cube inserts the direct child dimension columns to the left of the selected parent column.
Cube applies:
Number formatting from the parent column to each inserted child column.
Formulas from the parent column to each inserted child column.
No styles within the range (styles are not copied to the new child columns inside the range).
Content within the range outside of the expanded parent column
The range address updates automatically in the add‑on.
Expanding will automatically fetch the range to refresh values for the newly added columns.
Collapse (Rows or Columns)
Select a single cell in the parent dimension header that currently shows expanded children.
Select Collapse from the Expand/Collapse dropdown.
Cube removes the displayed child members and returns the view to the parent/tag only.
The range address updates automatically in the add‑on.
Expand All (Rows or Columns)
Select a single cell in the parent/ dimension header within the range.
Select Expand All from the Expand/Collapse dropdown.
Cube inserts all descendant levels under that parent—
For rows: children are inserted above their parent at each level.
For columns: children are inserted to the left of their parent at each level.
Cube applies the same rules as Expand for number formatting, formulas, styles within the range (not copied), and styles/content outside the expanded dimension (copied).
The range address updates automatically in the add‑on.
Fetch the range to refresh values for the fully expanded view.
Key Items to Remember
Select one cell—expand/collapse works only with a single selection.
Work inside the selected range area.
Target a row or column dimension header, not a data cell.
Only parent dimensions can be expanded or collapsed.
With zero-suppression on (toggle in Analyze), expansion inserts only children with at least one non-zero value; if none exist, nothing is added.
Expand inserts children above the parent (rows) or to the left (columns); rows keep indentation, columns don’t.
Children inherit number formats and formulas; styles within the range aren’t copied (styles/content outside the range are).
The range address updates automatically—fetch after expanding to refresh values.