6.1 Data Grouping, Ungrouping, & Multi-Level Outlining

Microsoft Excel Expert Microsoft 365 Apps

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Exam Overview & MO-211 Fundamentals

1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture

2 Chapter 2: Managing Workbooks & File Options

2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution

3 Chapter 3: Workbook Protection & Collaboration Settings

3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations

4 Chapter 4: Advanced Data Entry & Series

4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)

5 Chapter 5: Custom Formatting & Data Validation

5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing

6 Chapter 6: Data Grouping, Summarization, & Cleansing

6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing

7 Chapter 7: Advanced Conditional Formatting & Filtering

7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges

8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions

8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS

9 Chapter 9: Advanced Lookup Functions & Variable Optimization

9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET

10 Chapter 10: Date, Time, Text, & Auditing Functions

10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools

11 Chapter 11: Dynamic Array Functions & Spill Operations

11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping

12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions

12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic

13 Chapter 13: Macro Automation & VBA Fundamentals

13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor

14 Chapter 14: Advanced & Specialized Visualizations

14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts

15 Chapter 15: PivotTable Construction & Field Configuration

15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences

16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts

16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts

6.1 Data Grouping, Ungrouping, & Multi-Level Outlining

Key Takeaways Explain This Topic AI

Excel outlines allow hierarchical collapsing and expanding of structured row and column data up to 8 nesting levels without altering underlying cell formulas or deleting records.

Data grouping can be executed manually via Data > Group (Shift+Alt+Right Arrow) and ungrouped via Data > Ungroup (Shift+Alt+Left Arrow), selecting entire rows, columns, or cell ranges.

The Auto Outline tool automatically scans formulas (such as SUM or AVERAGE) and establishes multi-level hierarchy provided formula ranges consistently point to contiguous detail cells in a uniform direction.

Outline settings control whether summary rows appear below or above detail rows and whether summary columns appear to the right or left of detail, customizable via the Outline dialog launcher.

When copying collapsed outline views, pressing Alt+; (Home > Find & Select > Go To Special > Visible cells only) is mandatory to prevent Excel from copying hidden underlying detail records.

Last updated: September 2026

6.1 Data Grouping, Ungrouping, & Multi-Level Outlining

Managing complex analytical workbooks requires presenting high-level executive summaries while preserving access to underlying transactional data. Rather than manually hiding and unhiding dozens of rows or columns, Excel's grouping and outlining architecture provides a dynamic, collapsible framework. On the MO-211 exam, candidates must demonstrate complete proficiency in manual grouping, automated hierarchy detection, outline level navigation, directional settings adjustment, and the critical protocol of isolating visible cells when extracting summary reports.

Manual Grouping & Ungrouping Mechanics

Excel supports hierarchical grouping along two axes: rows (horizontal detail blocks) and columns (vertical time periods or departmental breakdowns). Grouping adds an interactive outline margin adjacent to row numbers or above column letters, featuring bracket bars and expand/collapse toggles.

Step-by-Step Manual Grouping Procedure

Select the Detail Range : Highlight the contiguous rows or columns that represent detail records. When grouping rows, select only the detail rows—do not include the summary or total row.

Execute the Group Command :

Ribbon : Navigate to Data > Outline group > click Group (or click the lower half of the Group split button and choose Group... ).

Keyboard Shortcut : Press Shift+Alt+Right Arrow .

Resolve Orientation Prompt (if applicable) : If you select a block of cells (such as B5:E12 ) rather than entire row or column headers, Excel displays the Group dialog box asking whether to group by Rows or Columns . Select the intended orientation and click OK . Select Detail Rows (e.g., Rows 5:8)

│
▼
Data Tab ──► Outline Group ──► Group (Shift+Alt+Right Arrow)
│
▼
Outline margin bar renders with [-] collapse toggle
Ungrouping and Clearing Outlines

To dismantle an existing group:

Select the rows or columns currently assigned to the group.

Navigate to Data > Outline > Ungroup , or press Shift+Alt+Left Arrow .

To remove all outline structures across an entire worksheet simultaneously without manually selecting individual levels, click the dropdown arrow beneath Ungroup and select Clear Outline .

Excel allows up to 8 nested outline levels for both rows and columns. Applying Shift+Alt+Right Arrow repeatedly on progressively smaller subsets within an existing group increments the nesting depth.

Auto Outline Prerequisites & Detection Mechanics

Building multi-level outlines manually across large financial models with hundreds of line items can be tedious. Excel includes an automated structuring engine: Auto Outline . Data ⟶ Outline ⟶ Group dropdown ⟶ Auto Outline \text{Data} \longrightarrow \text{Outline} \longrightarrow \text{Group dropdown} \longrightarrow \text{Auto Outline} Data ⟶ Outline ⟶ Group dropdown ⟶ Auto Outline

Architectural Requirements for Auto Outline

Auto Outline does not guess; it analyzes worksheet formulas to determine parent-child relationships. For Auto Outline to succeed, the dataset must satisfy three rigid conditions:

Consistent Formula Patterns : Detail cells must contain raw values or primary calculations, while summary rows must contain consistent mathematical formulas (such as SUM , AVERAGE , COUNT , or SUBTOTAL ) referencing those detail cells.

Directional Uniformity : Summary formulas must reference contiguous blocks pointing in the same direction throughout the table. If row 10 sums rows 5:9, row 16 must sum rows 11:15. Mixing formulas where some sum downward and others sum upward causes Auto Outline to fail.

No Reference Gaps : Summary formulas must directly abut their precedent ranges without empty intersecting rows that break mathematical continuity.

Exam Pitfall: If an exam task directs you to apply Auto Outline and Excel returns the error dialog "Cannot create an outline" , verify whether the data consists of raw text or values without formulas. Auto Outline cannot structure flat data devoid of formula references.

Outline Hierarchy, Margin Symbols, & Level Buttons

Once an outline is established, Excel renders an outline bar in the margin (to the left of row numbers or above column letters).

Navigation Controls & Symbols

Level Buttons ( 1 , 2 , 3 , ...) : Located at the top-left intersection of the worksheet headers. Clicking button 1 collapses the entire outline to the highest summary level (typically Grand Totals). Clicking button 2 reveals intermediate category totals. Clicking the highest number button expands all detail rows.

Collapse Toggle ( - ) : A minus symbol positioned at the end of a bracket line. Clicking - hides the detail rows spanned by that bracket and flips the icon to a plus symbol.

Expand Toggle ( + ) : A plus symbol indicating hidden detail records underneath or adjacent to the summary cell. Clicking + expands the group.

Display Toggle Shortcut : Press Ctrl+8 to toggle the visibility of the outline margin symbols on and off without removing the underlying groups.

Outline Element Visual Indicator Functional Behavior
Level 1 Button Box labeled 1 Collapses entire outline; displays only top-tier grand summaries
Level 2 Button Box labeled 2 Displays category/regional subtotals; hides item-level detail
Level 3+ Button Box labeled 3 – 8 Progressively reveals deeper nested subcategories or raw records
Minus Symbol ( - ) Bracket ending in - Collapses the active group bracket; conceals detail cells
Plus Symbol ( + ) Isolated box with + Expands the concealed group bracket; reveals detail cells
Outline Symbols Toggle Ctrl+8 Shows or hides margin outline bars without altering grouping
Configuring Outline Settings & Directional Options

Standard accounting conventions position summary rows below detail rows and summary columns to the right of monthly or quarterly columns. However, executive dashboards and financial planning models frequently position totals at the top or to the left .

To align Excel's outline brackets with non-standard models, configure the Outline Settings :

On the Data tab, locate the Outline group.

Click the small Dialog Box Launcher arrow in the bottom-right corner of the Outline group.

In the Settings dialog box, configure the required layout parameters:

Summary rows below detail : Checked by default. Uncheck this box if your summary formulas reside in a header row above the transactional items.

Summary columns to right of detail : Checked by default. Uncheck this box if summary calculations reside to the left of detailed time-series columns.

Automatic styles : When checked, Excel applies pre-configured cell styles (such as RowLevel_1 , RowLevel_2 , ColLevel_1 ) to format summary tiers automatically based on their hierarchy.

Click Apply Styles to format existing groups, or click OK to save the directional rules.

Copying Grouped Data: The Visible Cells Only Protocol

A frequent operational requirement on the MO-211 exam involves collapsing an outline to show only high-level summary rows, copying that summary, and pasting it into an executive presentation sheet.

The Hidden Data Copy Trap

When you select a cell range across a collapsed outline using standard mouse drag or Ctrl+A , Excel selects all cells within the bounding rectangle , including the hidden rows. If you press Ctrl+C and then Ctrl+V into another worksheet, Excel pastes the hidden detail rows alongside the summary rows, corrupting your executive report.

The Visible Cells Only Solution

To extract strictly the visible summary values:

Collapse the outline to the desired summary level (e.g., click Level Button 2 ).

Highlight the summary table range (e.g., A1:E50 ).

Select visible cells only using either method:

Keyboard Shortcut : Press Alt+; (Alt + Semicolon).

Ribbon Route : On the Home tab, in the Editing group, click Find & Select > Go To Special... > select the Visible cells only radio button > click OK .

Visual confirmation appears as thin white demarcation lines spanning the selected rows.

Press Ctrl+C to copy, navigate to the target sheet, and press Ctrl+V to paste. Only the visible summary rows are transferred.