Five chart types your Excel deck needs

Excel ships a vertical waterfall and a static scatter. That covers two of the five chart types analysts actually reach for. The other three — Sankey, decomposition tree, pictograph — usually mean a trip to Power BI, Tableau, or a separate Python session. This workbook builds them all inside one .xlsm: formulas where Excel does the job well, native charts where Excel already has the right primitive, VBA where I need a custom layout, and a single VBA button that shells out to a small matplotlib script when the visual deserves plotly-quality rendering. No Power BI, no Microsoft 365 dependency.

Nine tabs. Edit one cell in 02_Source_Data and the downstream tabs that drive native charts update immediately. The two image-based tabs (Sankey, Pictograph) carry a refresh affordance for re-rendering.

The first thing a P&L slide tries and fails to do is show flow. Five revenue segments funnel through one of two destinations: Cost of Revenue, or Gross Profit. Bar charts can’t show that attribution. Sankey does.

How it’s built:

• A formula-driven helper table at A5:D10 computes revenue, cost ($ = revenue × cost%), and gross profit per segment.
• A small Python script (matplotlib PathPatch with cubic Bezier control points) renders the Sankey PNG.
• A VBA RefreshSankey macro exports the helper-table cells to %TEMP%\sankey_refresh.csv, shells out to python refresh_sankey.py (hidden, synchronous), then swaps the image in place. Excel stays open the whole time.

Excel’s native waterfall is vertical only. Fine for six drivers with short labels; collapses at eleven drivers or when names like "Marketing reallocation" force rotated text. Rotated text is a clue you needed a horizontal layout.

The math, one row per driver:

• Invisible_Base is the running total before this driver lands. For an increase: previous running total. For a decrease: the new (lower) running total.
• Pos_Delta and Neg_Delta carry the bar length; the chart colors them differently.
• Total rows hold the full running total in their own series, zero base. Pos and Neg are zero on total rows — a gotcha I hit on the first build that double-stacked the first bar.

The Power BI decomp-tree maps cleanly onto how finance teams talk: "Total revenue, broken by segment, then within the biggest segment, broken by quarter." The same cascade renders in Excel with VBA-drawn FancyBboxPatch rectangles and connector lines.

Aggregations are formula cells (SUM and per-segment quarter totals). A Refresh decomp button on the tab redraws the shapes from those cells; Workbook_Open draws them once when the file opens.

Power BI’s Play Axis animates a scatter over time. Pretty in a live demo, useless in a printed deck. The Excel equivalent is a scroll bar form control bound to a cell, with INDEX formulas pulling the active quarter’s revenue and margin per segment. Drag the slider, the chart updates.

• SUMPRODUCT aggregates each segment’s revenue and margin per quarter into a 5×4 table.
• An "active view" range with =INDEX(...,$B$6) picks the column the scroll bar selects.
• A native scatter chart reads from the active view. No VBA needed; the form control rewires the formula chain.

Native Excel can fill a column chart with a picture, and stack the picture in fixed units instead of stretching. Each gas pump represents $200K; bars become stacks of pumps; the human eye reads them faster than a number above a bar.

How: 2D Clustered Column chart. Right-click a bar → Format Data Series → Fill → Picture or texture fill → enable Stack and Scale with → set units to 200000. Use the included gas-pump.png or your own icon.

On both the Sankey and Pictograph tabs, scroll below the chart. The full standalone Python script that produces the visual sits embedded in cells, one line per row, ready to copy into a .py file. Three steps: install matplotlib, save the script, run it. No dependency on anything that lives on my machine. Anyone with Python can reproduce the chart locally and edit the data arrays at the top.

• Sort by narrative, not by size. Volume first, price/mix second, cost drivers third, FX and one-times last. Size-sorting destroys the story.
• Round labels. "+$1.2M" communicates more than "+$1,184,231." The audit trail stays in the workbook.