Sankey diagrams are used to show flow between two or more categories, where the width of each individual element is proportional to the flow rate. These chart types are available in Power BI, but are not natively available in Excel. However, today I want to show you that it is possible to create Sankey diagrams in Excel with the right mix of simple techniques.
While Sankey diagrams are often used to show energy flow through a process, being a finance guy, I’ve decided to show cashflow. The simple Sankey diagram above shows four income streams and how that cash then flows into expenditure or savings.
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 0050 Sankey diagrams in Excel.xlsx
Watch the video
This blog post will be slightly different from others; rather than show you how to do each step, I will demonstrate the overall approach. There are additional details covered in the YouTube video.
While I should wait to reveal the secret until the end of the post, I think it will help to explain how it all fits together if I reveal it upfront.
Here is the secret: the Sankey diagram you see in the example file is not one chart, far from it. There are actually 21 charts all stacked on top of each other.
The following image shows the 21 individual charts that could make up a Sankey diagram.
By setting the backgrounds to be fully transparent, and the shaded lines as partially transparent; overlaying the charts on top of each other creates the Sankey effect. The pillars at each end are 100% stacked column charts, which are also overlaid on top.
Now that I’ve revealed how simple this secret is, let’s look at each stage in a bit more detail. Then you will be able to create your own Sankey diagram templates.
Our initial data is a two-dimensional table. The rows are the start point for our Sankey diagram, while the columns are the endpoint. The value at the intersection of these is the flow rate.
Within the template, we also have a named range called Blank. The value in here represents the spacing to be applied between each category.
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
Interim calculation tables
To create the Sankey effect from our initial data, we need to create interim calculations.
- SankeyLines table
- SankeyStartPillar table
- SankeyEndPillar table
- Spacing named range
Let’s look at each in turn.
From the source data, we create a table with a row for each possible combination of rows and columns. This table is called SankeyLines table in the example file. Each row category is separated by an additional line which creates the blank spaces we see in the charts.
Our initial SankeyLines table needs to contain the following base information:
The formula in the Value column is:
=IF(LEFT([@From],5)="Blank",Blank, INDEX(SankeyData,MATCH([@From],SankeyData[From / To],0), MATCH([@To],SankeyData[#Headers],0)))
This formula retrieves either:
- Value from the source table
- Value of the Blank named range (where it is a row used for spacing between categories).
The End Position column determines the order of the lines at the end of the Sankey diagram. Where 1 is finishes at the top, 2 is the second item from the top, etc.
SankeyLines table additional data points
To create the data for the 100% stacked area chart, we need to calculate some additional data points:
- Space above the shaded Sankey line
- Value of the shaded Sankey line
- Space below the shaded Sankey line
Our SankeyLines table needs to expand with the following calculations.
The formulas in each column are as follows:
This calculates the amount of space required above the Sankey line at the start point.
Above Mid 1
This is sent to equal the Above Start column.
Above Mid 2
This is set to equal the Above End column (see below)
=SUM([Value])-SUMIFS([Value],[End Position],">="&[@[End Position]])
This calculates the amount of space required above the Sankey line at the end point.
Value Start, Value Mid 1, Value Mid 2, Value End
The height of the shaded Sankey line never changes. Therefore, we can set the value of all 4 columns to equal the Value column.
Below Start, Below Mid 1, Below Mid 2, Below End
The columns calculate the amount of space required below the shaded Sankey line. Each calculation is constructed the same way. It takes the SUM([Value]) then takes away the value from the equivalent Start, Mid 1, Mid 2 or End columns.
=SUM([Value])-[@[Above Start]]-[@[Value Start]]
Below Mid 1:
=SUM([Value])-[@[Above Mid 1]]-[@[Value Mid 1]]
Below Mid 2:
=SUM([Value])-[@[Above Mid 2]]-[@[Value Mid 2]]
=SUM([Value])-[@[Above End]]-[@[Value End]]
The SankeyStartPillar table is reasonably easy to understand. It just needs each row category from the source data listed with a “Blank” item in between.
The formula for the Value column is:
The SankeyEndPillar table is similar to the SankeyStartPillar table. It just needs each column category from the source data listed with a “Blank” item in between.
The formula for the Value is:
Spacing named range
The final part of the interim calculations is a named range called Spacing. This is used as the Category (horizontal) Axis for the chart. It determines at what point the slope of the chart starts and finishes.
Once all the interim calculations are ready, the chart creation can begin.
Create the individual shaded Sankey lines
Each row of the SankeyLines table needs to be a separate 100% stacked area chart with 3 data series:
Once the chart has the correct series data, it then all comes down to formatting.
After all the formatting has been completed the chart will become a single grey line.
If the row in the SankeyLines table starts with “Blank”, then technically it can be deleted, however for completeness, I prefer to keep it there, but set it to No Fill. It means that if I ever need to use it, I can just change the fill color and it can be used as a standard shaded Sankey line.
Once all the shaded Sankey lines have been created, they can be overlaid on top of each other.
Create the Sankey pillars
Most of the hard work has now been completed. We just need to create two 100% stacked column charts—one for the start pillar and one for the end pillar.
The pillars are set to be formatted as follows:
- Plot series in reverse order
- Your preferred fill colors
- Set the “Blank” sections with No Fill
- Add data labels to the relevant points.
After all of that, we overly the pillars on top of our chart. Finally, our Sankey diagram is complete.
That’s it. Individually there is nothing too difficult with any of the techniques involved. But they need to be combined in the right way to create the Sankey effect. Initially, this takes quite a long time to create, but once set-up, it can be used over and over with different categories. It’s definitely the case of invest the time once, to create a template big enough then and reuse it.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: