Sankey Regions
A sankey region draws a flow diagram over a rectangle of the sheet: nodes as bars, flows between them as ribbons whose thickness follows the amount. It is the picture of where the money goes — revenue lines flowing into revenue, revenue splitting into cost of goods sold and gross profit, gross profit into the operating expense lines and the result. Cash from its sources to its uses, quantities from vendors through locations to customers, headcount from departments to cost centres: anything that is a quantity moving from one bucket into others.
Like charts and KPI cards, the region fills its whole rectangle and is drawn after everything else has calculated, so its flows can come from formulas, feeder regions, pivots and SQL regions alike. It shows in the editor, in preview, in print and in template snapshots.
Unlike a chart, the diagram is cached: laying it out means dozens of passes over every node and ribbon, too much to repeat on every keystroke. The drawing is kept until a setting, the title or the rectangle changes, and the refresh button beside the region badge redraws it from the current cell values. Change a slicer or the flows, then press refresh to see the new picture. The agent's refresh_region tool does the same.
Setup
| Field | Meaning |
|---|---|
| Range | The rectangle the diagram is drawn over, for example B9:I22. |
| Title | Drawn above the diagram. Accepts [Address] tokens: Cash flow for [B1]. |
| Flows Range | The cell range holding the flows, one row per flow: the first column is the from node, the second the to node, the third the amount. See below. |
| Node Alignment | Where the nodes sit horizontally — see Layout. Default Justify. |
| Ribbon Color | How the ribbons are coloured — see Colours. Default Gradient. |
| Node Order | The vertical order of the nodes within a column — see Layout. Default Auto. |
| Show Values | Prints each node's amount under its name. On by default. |
| Value Decimals | Decimals in the node amounts (0–5). |
| Color / Color Scheme | The node palette — see Colours. |
The Flows Range
The diagram reads the flows range row by row after the sheet has calculated:
| Column | Holds |
|---|---|
| 1 | The from node — any text; the same text always means the same node. |
| 2 | The to node. |
| 3 | The amount, a positive number. |
| 4 (optional) | A colour #RRGGBB (or #RGB) for the to node. |
| 5 (optional) | A colour for the from node. |
Rules the renderer applies:
- Rows with a blank node or a non-numeric amount are skipped, so a flows range that is longer than the data (a shrinking feeder, trailing empty rows) does not draw ghost flows.
- Flows between the same two nodes add up into one ribbon.
- A node's size is the larger of what flows into it and what flows out of it. A node that receives 1,225 and passes on 1,225 is drawn as 1,225; a node that receives 1,225 and passes on 1,000 shows the 225 gap as an unfilled part of its bar.
- A sankey is a directed, acyclic picture: a flow from a node to itself is ignored, and a flow that would close a cycle (A → B → C → A) is dropped. If something must flow back, give the destination another name ("Retained — reinvested").
- Amounts must be positive. A negative amount is a flow in the other direction — flip the sign in the formula and swap the nodes.
Building the flows with formulas
Put the flows range in the support area and compute the amounts from the slicers, so the picture follows the period the user picks:
| AB | AC | AD |
|---|---|---|
| Product sales | Revenue | =GL.GLSum('40000..40999', C2) |
| Services | Revenue | =GL.GLSum('41000..41999', C2) |
| Revenue | Cost of goods sold | =GL.GLSum('50000..59999', C2) |
| Revenue | Gross profit | =AD3+AD4-AD5 |
| Gross profit | Operating expenses | =GL.GLSum('60000..69999', C2) |
| Gross profit | Operating income | =AD6-AD7 |
| Operating expenses | Sales & marketing | =GL.GLSum('60000..62999', C2) |
| Operating expenses | Administration | =GL.GLSum('63000..69999', C2) |
| Operating income | Tax | =GL.GLSum('80000..80999', C2) |
| Operating income | Net income | =AD8-AD11 |
Revenue accounts carry credit balances in the G/L, so wrap them in -GL.GLSum(...) (or Abs) where needed to keep every amount positive. A fourth column with #DC2626 on the cost rows and #16A34A on the result rows colours those nodes without touching the palette.
Layout
The layout is the classic sankey algorithm (the one behind d3, Google Charts and Plotly): each node's column is its distance from the sources along the longest path, and within a column the nodes are placed by repeated relaxation so that ribbons leave and enter nodes with as few crossings as possible.
| Node Alignment | Columns |
|---|---|
| Justify | By distance from the sources, but every final node (one nothing flows out of) moves to the last column — the picture reads left to right from sources to results. |
| Left | By distance from the sources only; final nodes stay where their depth puts them. |
| Right | By distance to the final nodes — sources that feed a late stage sit late. |
| Center | A source sits right before its first target. |
| Node Order | Within a column |
|---|---|
| Auto | The layout decides, minimising ribbon crossings. |
| Data Order | The order the flows range first mentions each node — useful when the reading order matters (an income statement's lines). |
| Descending / Ascending | By amount, largest or smallest first. |
Labels are automatic: the first column's labels sit to the left of the diagram and the last column's to the right, in margins reserved for them; the middle columns' labels sit beside their nodes over the ribbons with a light halo. Long names wrap onto two lines, amounts go on their own line when the column has room and inline when it does not, labels of neighbouring nodes are pushed apart rather than printed over each other, and when a column has more nodes than lines of text fit, the smallest nodes lose their label first. Give a dense diagram more rows.
Colours
Nodes take their colours from the region's Color and Color Scheme exactly as a chart's series do — the classic multi-hue palette by default, or shades and related hues derived from the region colour. The optional colour columns of the flows range override the palette for single nodes.
| Ribbon Color | Ribbons are |
|---|---|
| Gradient | Blended from the from-node's colour into the to-node's — the eye follows the flow. |
| Source / Target | Painted flat in the from- or the to-node's colour. |
| Neutral | Grey, so only the nodes carry colour — the quiet choice for a crowded diagram or a report with one accent colour. |
Ribbons are translucent, so where they overlap, both stay visible.
Worked Examples
Income statement as a flow
A dashboard with a ?RANGE period slicer in C2, the flows range AB3:AD12 built as shown above, and a sankey region over B9:I22 with the title Where the money went — [C2]. Change the period and the whole picture re-flows.
Cash sources and uses
Sources (operating cash flow, new borrowing, asset sales) each flow into one Cash node; Cash flows out into uses (capital expenditure, debt repayment, dividends, change in cash). Use Neutral ribbons and colour Cash with the house accent.
Quantities through the supply chain
Three feeder regions aggregate item ledger entries by vendor → location and location → customer; two flows ranges stacked under each other feed one sankey. Node Order Descending puts the biggest partners on top.
Export
A sankey has no native Excel chart type, so the Excel export leaves the rectangle empty; its source cells export like any other cells. Preview and print draw the diagram.
For the Spreadsheet Agent
The agent creates and updates sankey regions with upsert_sankey_region (range, data_range, title, alignment, link_color, node_sort, show_values, scheme, value_decimals) and is told to build the flows with formulas on the slicer cells. AI reports have the matching aireport_sankey tool, which takes the flows directly — see AI Reports.