Study guide

14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst

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

14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst

Key Takeaways Explain This Topic AI

Waterfall charts (bridge charts) illustrate how an initial baseline value increases or decreases through sequential positive and negative transactions to reach a final total.

In a Waterfall chart, intermediate subtotals and grand totals must be designated manually by selecting the specific data point and enabling 'Set as total' in the Format Data Point pane.

Funnel charts visualize sequential attrition and conversion stages across a linear process, centering horizontal bars proportionally without requiring secondary axes.

Treemaps display hierarchical data as space-efficient nested rectangular tiles, optimal for comparing relative proportions across 1–2 categorical tiers.

Sunburst charts illustrate multi-tiered hierarchical relationships as concentric radial rings, where slice angles represent proportions and outer rings represent deeper descendant branches.

Last updated: September 2026

13.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst

Executive reporting demands visual clarity when communicating complex financial movements, conversion pipelines, and nested categorical structures. Standard bar and pie charts fail when illustrating how intermediate debits and credits impact an operating balance or how revenue cascades across multi-tiered corporate divisions. To address these sophisticated reporting scenarios, Microsoft Excel incorporates specialized financial and hierarchy charting engines: Waterfall , Funnel , Treemap , and Sunburst charts. Mastery of these visualization types requires understanding their specific structural requirements, formatting controls, and axis behaviors.

Waterfall Charts: The Financial Variance Bridge

A Waterfall Chart (frequently referred to in corporate finance as a bridge chart ) visualizes the cumulative effect of sequentially introduced positive and negative values. Beginning with an initial starting baseline, individual floating columns depict intermediate increases and decreases, culminating in a grounded ending balance. Typical applications include corporate earnings walks (Gross Sales to Net Income), inventory reconciliations, and budget-versus-actual variance explanations. $100M ┼───███

  • $80M ┼───███ (+20M) ───███ (Net Total)
  • $60M ┼───███───███ ███
  • $40M ┼───███ ███───░░░ (-30M) ███
  • $20M ┼───███ ███ ░░░───░░░ (-15M) ███
  • $0 ┴──────────────────────────────────────────────────────███─────────
  • Gross New COGS OpEx Tax Misc ... Ending
  • Start Sales Profit
  • [Total] [+] [-] [-] [-] [+] [Total]
  • Step-by-Step Waterfall Construction & Total Pillar Setup

By default, Excel's Waterfall engine assumes every value in the selected data range is an incremental floating addition or deduction. Consequently, intermediate subtotals (such as Operating Income) and final summary figures (such as Net Income) will incorrectly float in mid-air unless the author explicitly configures them as anchor pillars.

Select the two-column table containing row descriptions and numerical values (including starting values, positive additions, negative deductions, and intermediate/final totals).

Click Insert > Charts > Insert Waterfall, Funnel, Stock, Surface, or Radar Chart > Waterfall .

Identify the summary columns that should ground to the horizontal zero baseline (e.g., Starting Capital , Gross Profit , Net Income ).

Isolate the specific column : Click once on the chart series to select all columns, then click a second time on the specific target column (selection handles must appear exclusively around that single bar).

Right-click the selected column and choose Format Data Point... (or press Ctrl+1 ).

In the Format Data Point task pane, check the Set as total box. +-------------------------------------------------------------------+

  • | Format Data Point |
  • +-------------------------------------------------------------------+
  • | Series Options |
  • | [x] Set as total |
  • | |
  • | [x] Show connector lines |
  • +-------------------------------------------------------------------+

Upon checking Set as total , three immediate visual transformations occur:

The bar anchors firmly to the horizontal axis ( $0 baseline).

The bar changes color from the variance palette (green/red) to the dedicated Total fill color (default dark blue/grey).

Connecting lines between adjacent columns automatically recalculate their origin to bridge from the top of the newly designated total pillar.

Funnel Charts: Visualizing Pipeline Velocity & Attrition

A Funnel Chart displays values across sequential stages in a linear business process. As items progress through each consecutive operational stage, values typically experience attrition, causing the bars to narrow progressively. Primary use cases include sales conversion pipelines (Website Visits ➔ Inquiries ➔ Qualified Leads ➔ Contract Proposals ➔ Closed Deals), HR recruitment cycles, and customer onboarding funnels. Stage 1: Website Visitors (100,000) [════════════════════════════════════]

  • Stage 2: Marketing Leads (42,000) [═════════════════════]
  • Stage 3: Sales Qualified (18,000) [═════════]
  • Stage 4: Proposals Sent (6,500) [═══]
  • Stage 5: Closed Deals (2,100) [═]
  • Implementation Rules for Funnel Charts

Pre-Sorting Dependency : Unlike Pareto charts, Funnel charts do not sort data automatically . If stages are entered out of sequential order, the visual funnel shape will break. Authors must order source records chronologically or logically according to the process flow prior to chart creation.

Symmetrical Horizontal Layout : Excel automatically centers each horizontal bar along a shared vertical midline, rendering proportional widths that make relative drop-offs between stages immediately apparent.

Data Structure : Requires a single column of stage labels and a single column of non-negative numerical values ( Insert > Charts > Waterfall, Funnel... > Funnel ).

Hierarchical Visualizations: Treemap vs. Sunburst

When data contains nested parent-child categories (such as Region > Country > City or Department > Cost Center > Expense Category ), standard charts cannot communicate relative proportions without creating confusing multi-level axis clutter. Excel provides two dedicated hierarchical charts to solve this problem: TREEMAP (Nested Rectangles) SUNBURST (Concentric Rings)

  • +-------------------+-------------+
  • | NORTH AMERICA | EUROPE | ( ( Root ) )
  • | +--------+------+ | +---------+ | ( Level 1 Level 1 )
  • | | USA | CAN | | | UK | DE| | ( L2 L2 L2 L2 )
  • | +--------+------+ | +---------+ |
  • +-------------------+-------------+
  • Treemap Charts (Rectangular Nested Space Fillers)

A Treemap displays hierarchical data as a collection of nested rectangles. The area of each rectangle is strictly proportional to its numerical value relative to the whole.

Visual Mechanics : Top-level parent categories form large outer bounding containers. Lower-level child subcategories are nested inside these containers as proportional tiles.

Label Options : Open Format Data Series > Series Options > Label Options to select between:

Overlapping : Displays parent labels directly inside child tiles (compact).

Banner (Recommended): Displays parent category names across a dedicated header banner spanning the top of each category cluster.

None : Omits category headers entirely.

Best Applied When : Space is limited, hierarchy depth is shallow (1 to 2 levels), and the primary objective is rapid visual comparison of relative volume.

Sunburst Charts (Radial Multi-Tier Decompositions)

A Sunburst Chart displays hierarchical data radially across concentric rings.

Visual Mechanics : The center circle represents the top-level parent categories (Root / Level 1). Each additional ring extending outward represents a deeper descendant level of the hierarchy. The angular arc length of each slice is proportional to its value.

Best Applied When : Analyzing deep, multi-tiered hierarchies (3 or more levels) where visualizing the exact structural branching between parent nodes and multiple child descendants is paramount.

Source Data Preparation for Hierarchical Charts

Both Treemaps and Sunburst charts enforce strict data architecture rules. Source data must be arranged in contiguous columns from highest hierarchy level to lowest, followed by the numerical metric column:

  • Column A (Level 1) Column B (Level 2) Column C (Level 3) Column D (Value)
  • North America United States Commercial 4,200,000
  • North America United States Enterprise 6,800,000
  • North America Canada Commercial 1,500,000
  • Europe United Kingdom Enterprise 3,100,000

Exam Trap & Data Integrity Rule: Never include pre-aggregated total or subtotal rows (such as "North America Total") within the selected source range. Excel's Treemap and Sunburst calculation engines compute parent sums dynamically from individual child values. If an analyst includes subtotal rows, Excel treats the subtotal as an additional child item, doubling the category's reported size.