Making a Sankey diagram from Excel
Excel offers no Sankey chart type, and no version ever has. The method that works is to prepare the data in the spreadsheet, in a very simple shape, then send it to a tool that knows how to draw it.
Free, in the browser, no account.
The shape your data must take
One row per flow, three columns: where it starts, where it ends, how much it carries. That is all it takes to produce a first diagram.
| Source | Target | Value |
|---|---|---|
| Harvest | Sawmill | 120 |
| Harvest | Export | 30 |
| Sawmill | Sawn timber | 72 |
| Sawmill | By-products | 48 |
Nodes do not have to be declared: they are derived from the names used in the Source and Target columns. Write them exactly the same way from one row to the next — “Sawmill” and “sawmills” will make two distinct nodes, and that is by far the most common error on a first import.
Three rules for a correct diagram
- One unit only. Tonnes or euros, not both in the same table: the widths would become incomparable.
- What comes in must go out. For every intermediate node, incoming values must sum to the outgoing ones. In the example above, the sawmill receives 120 and emits 72 + 48.
- No totals in the data. A “Total” row imported as a flow creates a ghost node that doubles the quantities. Totals are recomputed, they are not transported.
What if the table does not balance?
That is the normal case as soon as figures come from several sources. Two possible attitudes. The bad one: parking the gap in a “miscellaneous” or “other” item, which amounts to hiding the uncertainty. The good one: dealing with it.
Since a node’s inputs equal its outputs, a missing value surrounded by known ones is fully determined — the tool computes it. When several are missing or contradict each other, a reconciliation solver looks for the consistent set of values closest to your data, weighted by how much confidence you place in each. You keep track of what was measured and what was inferred.
What you will not get inside Excel
The workarounds that circulate — stacked bar charts, hand-drawn shapes, third-party add-ins — produce a frozen image. They give you neither automatic node placement, nor branch collapsing when the diagram gets dense, nor hover interactivity, nor recomputation when a figure changes. On a diagram of a few dozen flows, the difference in working time is measured in days.
Beyond the three columns
The minimal format is enough to start, but a workbook can carry more: several sets of values for the same diagram (years, scenarios, territories), grouping levels, units, uncertainties, colours. That is how the studies published in the gallery are built.