# Create a Sankey diagram in Excel

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.

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.

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.

Contents

## The secret

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.

## Initial data

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.

## 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.

### SankeyLines table

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:

Value

The formula in the Value column is:

```=IF(LEFT([@From],5)="Blank",Blank,
INDEX(SankeyData,MATCH([@From],SankeyData[From / To],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).

End Position

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:

Above Start

`=SUM(SankeyLines[[#Headers],[Value]]:[@Value])-[@Value]`

This calculates the amount of space required above the Sankey line at the start point.

Above Mid 1

`=[@[Above Start]]`

This is sent to equal the Above Start column.

Above Mid 2

`=[@[Above End]]`

This is set to equal the Above End column (see below)

Above End

`=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

`=[@Value]`

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.

Below Start:

`=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]]`

Below End:

`=SUM([Value])-[@[Above End]]-[@[Value End]]`

### SankeyStartPillars table

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:

`=SUMIFS(SankeyLines[Value],SankeyLines[From],[@From])`

### End pillars

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:

`=SUMIFS(SankeyLines[Value],SankeyLines[To],[@To])`

### 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.

## Chart layering

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
• 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.

## Conclusion

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.

Don’t forget:

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:

1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
3. 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.
4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

## 17 thoughts on “Create a Sankey diagram in Excel”

1. A. Rich Suchoski says:

I have been looking for this for years, thank you so very much! I can’t wait to dive in!

• Excel Off The Grid says:

I was also looking for it for years. Which is what drove me to find a solution. It’s not the most elegant, and it’s quite time-consuming to set-up, but once it’s working you can use it over and over 🙂

2. nico says:

Great example. i’ve been struggling on how to solve it and here it is!
thanks

• Excel Off The Grid says:

Thanks Nico – It’s a bit of a hack method, but it seems to work OK 🙂

3. Jeremy says:

This is great! I found this article while searching for a way to monitor my stock portfolio’s diversification across Sectors and Industries and show the Percentage and My Cost towards each Sector and Industry. I’m hoping this is possible and that as I Buy or Sell stocks, I would be able to quickly modify the values and then view the results.

Thank you so much for sharing!

Thanks for Sharing really good.

4. Augustina Arora says:

Thanks for sharing. This is really good but lengthy.

Here I find the video to Create Sankey Diagram in Excel or Google Sheets in minutes. No expertise or coding required. Very straightforward.

• Excel Off The Grid says:

Agree that it is straightforward, but you need to (a) use Google Sheets (b) install an add-in to use it (c) pay per user per month. 3 big negatives in my opinion.

My solution is lengthy, but (a) uses Excel (b) uses native charts which all users have already (c) once set up, it’s free. 3 big positives in my opinion.

5. Mr. Paul says:

This is very complex and updates just fine… as long as you don’t add or remove a row/column. I removed the last column and added two rows, then updated the tables. Now it doesn’t line up and I don’t understand why not. All of the data and references check out. The only difference is the number of columns and rows.

• Excel Off The Grid says:

Hi Paul – I think you may need to read the article to understand how it is created. This isn’t one single chart, it is lots of charts stacked on top of each other. Adding and deleting rows does not change the number of charts.

If you create your own template with enough spaces within each category then you never need to add or remove lines. As the unused lines can be set to a value of zero with no description.

• Mr. Paul says:

• Excel Off The Grid says:

Hi Paul,

There isn’t a reason why you couldn’t do as you want. The numbering represents the final position within the chart, so if you’ve got 23 items including blanks then there should be numbers 1 to 23.

Currently, within the template, there is a line that goes from every start to every end. So if there are 4 end points, then there are 4 lines that begin within a specific start point.

If you want more start or end pillars, it may be better to have an entirely new section that contains lines that go to/from each start/end point (you’ll also need to add lines into the existing start/end points to). As stated in the conclusion, it does take a long time to create the chart.

6. sarah says:

The example file is amazing! Can you provide any guidance on changing the colors of the flow lines? I tried to pull one out like you did in the video, but I can’t seem to get to the right ones to change the color.

• Excel Off The Grid says:

Hi Sarah – Good question.

Go to Home > Find & Select > Selection pane. This will list all the charts.
Hide the ones you don’t want to change
Adjust the one chart you want to change
Unhide all the charts again

7. Gisel says:

Hi there, is it possible to create a Sankey with four ‘From’ values and 55 ‘To’ values? I am having trouble with my lines adjusting and am wondering if this is even possible to do. Thank you!

• Excel Off The Grid says:

Yes, it’s possible. But try think of it as individual lines. Basically you’ve got 55 lines, with an additional 54 blanks.

Then it’s just a matter of arranging those 109 (lines and blanks) into the correct order.