How to Make a Sankey Diagram Excel Dashboard? A Step-by-Step Guide

This tutorial on how to make a Sankey diagram Excel dashboard is suitable for all Excel versions including Office 365.

Sankey diagrams are powerful data visualization tools that help you visualize the flow of resources from the root source to the destination.

Let’s say, for example, you want to find out how a country’s natural resources are utilised. You can use a Sankey diagram to find out where these resources are coming from and connect to where they are being used.

*A Sankey Diagram Excel Dashboard in action*

Unfortunately, unlike Power BI, there is no built-in option in Excel that allows you to insert a Sankey diagram directly into a spreadsheet.

But, there is still a way to make a Sankey diagram Excel dashboard using nothing more than just bar charts.

In this tutorial, I’ll walk you through how to do this in a step-by-step manner.

Table Of Contents

How to Make a Sankey Diagram Excel Dashboard?
FAQs
Closing Thoughts

I suggest you download the sample Excel sheet and use it to follow along with this guide.

Creating a Dynamic Pivot Chart Title Based on Slicer(6 Easy Steps)

How to Make a Line Graph in Excel? 4 Best Line Graph Examples

How to Make a Sankey Diagram Excel Dashboard?

Creating a Sankey diagram in Excel is very easy if you break the process into these three steps:

Generate data for all individual Sankey lines.
Plot each individual Sankey line seperately.
Assemble all individual Sankey lines together into a Sankey diagram.

I’ll show you how each one of these steps work in greater detail.

Step 1: Get Your Data Ready for the Sankey Chart

Get your data source ready in the form of a two-dimensional table like shown below:

*Source Data for the Sankey Diagram in Excel*

Here, the rows represent the sources and the columns represent their destinations. Now, rename the table to ‘Data’ in the Table Design Tab.

Set a specific named range called ‘Blank‘ and assign a suitable value. To do this go to the Formula tab and click on the Define Name option. This is going to be the width of the blank space inside the Sankey diagram.

Set the width of the blank space in your Sankey Diagram and name it 'Blank' as a named range — *Set the width of the blank space in your Sankey Diagram and name it ‘Blank’ as a named range*

Now, in a separate table, list all combinations of flows in order, along with their corresponding values. You can use the following formula to retrieve the source values correctly:

=IF(LEFT([@From],5)=”Blank”,Blank,INDEX(Data,MATCH([@From],Data[From/To],0),MATCH([@To],Data[#Headers],0)))

Rename the table to ‘Lines’ in the Table Design tab. Every row in this table represents a Sankey line.

Now, In the same table, for every Sankey line, you need to calculate three things: The space above the Sankey line, the width of the Sankey line and the space below the Sankey line.

This means that you have to calculate three sets of data points for each row as shown below:

Calculate the Sankey Line parameters for each row in the 'Lines' table — *Calculate the Sankey Line parameters for each row in the ‘Lines’ table*

Use the following formulas to calculate the thickness of each Sankey line i.e each row in the ‘Lines’ table. Entering these formulas in the first row of the table is enough and fills the entire table with the relevant data.

Series 1 (Space Above the Sankey Line)

For AStart use: =SUM(Lines[[#Headers],[Value]]:[@Value])-[@Value]

For AMid1 use: =[@Astart]

For AMid2 use: =[@AEnd]

For AEnd use: =SUM([Value])-SUMIFS([Value],[End Position],”>=”&[@[End Position]])

Series 2 (Width of the Sankey Line)

For Value Start, Value Mid 1, Value Mid 2, Value End, use: =[@Value]

Series 3 (Space Below the Sankey Line)

For BStart use: =SUM([Value])-[@Astart]-[@Vstart]

For BMid1 use: =SUM([Value])-[@AMid1]-[@VMid1]

For BMid2 use: =SUM([Value])-[@AMid2]-[@VMid2]

Fir BEnd use: =SUM([Value])-[@deb-ashbyAEnd]-[@VEnd]

Finally, you need to set up two data tables for the Sankey pillars.

*Create Tables for both the Source and Destination Pillars*

For Source pillars use =SUMIFS(Lines[Value],Lines[From],[@From]) in the value column.

For Destination pillars use =SUMIFS(Lines[Value],Lines[To],[@To]) in the value column.

Also, don’t forget to create a separate named range for Spacing, which is going to be used as the horizontal axis.

*Create a sample Spacing Range for the X-Axis of the Sankey diagram*

Also Read:

Bar Graph in Excel — All 4 Types Explained Easily (Excel Sheet Included)

How to Make a Scatter Plot in Excel? 4 Easy Steps

How to Add Error Bars in Excel? 7 Best Methods

Step 2: Plot Individual Sankey Lines

Now, all you have to do is plot a 100% Stacked area chart (Insert tab >Recommended Charts > All Charts > Area) with 3 data series in the Y-axis vs the Spacing range in the X-axis. Series 1 should contain the range AStart, AMid1, AMid2, and AEnd of a particular row.

Similarly, Series 2 should contain the range VStart, VMid1, VMid2, and VEnd of a particular row. In the same fashion, Series 3 should contain the range BStart, BMid1, BMid2, and BEnd.

Use the 100% Stacked Area chart for Sankey Diagram Excel — Use the 100% Stacked Area chart to create individual lines of the Sankey Diagram Excel Dashboard

Plot each individual Sankey line using Series1, Series2 & Series3 inside a 100% Stacked Area Chart — Plot each individual Sankey line using Series1, Series2 & Series3 inside a **100% Stacked Area Chart**

Next, double-click on the Y-axis and plot it in reverse order.

*Plot the Y-xis in reverse order using the Format Axis Pane*

Now, double-click on each plot area to open the Format Data Series pane and set the transparency for the upper and lower area to 100%. Similarly, set the transparency of the middle area to 50%.

Switch off the gridlines of the plot area, set the fill to no-fill for the plot area, and remove the chart’s borders, axes, legends etc.

Switch off Gridlines using Chart Elements — Switch off Gridlines using **Chart Elements**

No Fill and No Border — *Double-Click anywhere on the empty space inside the chart and set the Fill to **No Fill** and Border to **No Line** in the Format Chart Area Options*

Congratulations, you have successfully created a single Sankey line. Follow steps 1-4 and create Sankey lines for all rows of data in the lines table.

Create similar Sankey Lines for all the rows in the 'Lines' table using steps 1 to 4 — *Create similar Sankey Lines for all the rows in the ‘Lines’ table using steps 1 to 4*

Step 3: Assemble All the Individual Sankey Lines

Now that you have all the individual Sankey lines ready, all you have to do is assemble all of them together into a single Sankey diagram Excel dashboard.

Assemble all individual Sankey lines together following the End Position order in the Table 'Lines' — Assemble all individual Sankey lines together following the **End Position** order in the Table **‘Lines’**

Next, create two separate Sankey pillar charts using a 100% stacked column chart using the Sankey pillar tables as shown below and format them as shown below.

Create a 100% Stacked Column Chart using the SPillar Table data — *Create a **100% Stacked Column Chart** using the **SPillar** Table data*

*Delete all Axes, legends & Titles from the Chart*

Switch off the Gridlines and Set Fill to No Fill, Border to No Border in the Format Plot Area pane — *Switch off the Gridlines and Set Fill to **No Fill**, **Border to No Border** in the Format Plot Area pane*

*Set the Fill Colour to No Fill for all Blank 1, Blank 2 and Blank 3 ranges in the Stacked Column*

Repeat the same formatting process with the 100% Stacked Column chart created with the EPillars table.

Set suitable Fill Colours to the Sankey pillars and drag them near the created Sankey diagram

*Assemble the Pillars with the Sankey Lines to complete the Sankey Diagram Excel Dashboard*

Congratulations, you have successfully created your own Sankey Diagram Excel dashboard. This dashboard is dynamic i.e any change in the source data will immediately reflect in the Sankey diagram.

How to Remove Hyperlinks in Excel? 3 Easy Methods

How to Use the Format Painter Excel Feature? — 3 Bonus Tips

FAQs

How do Sankey diagrams work?

Sankey diagrams visually represent the flow of resources taking place in a process or phenomenon. The thicker lines signify greater volume of flow and the thinner lines represent the lower volume flow of resources. In a nutshell, it helps you intuitively see complex processes visually.

Why is it called a Sankey Diagram?

Sankey diagrams are named after the Irish engineer Matthew Sankey who used them first to visualise the energy efficiency of a steam engine.

Closing Thoughts

In this tutorial, we saw how to create a custom Sankey diagram Excel dashboard using three simple steps. I recommend you try this one out using the sample Excel sheet to get a better understanding. If you have any questions or doubts about this, please let us know in the comments section.

Need more high-quality Excel guides? Check out our free Excel resources centre.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses by seasoned professionals. Click here for advanced Excel courses with in-depth training modules.

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.

How to Make a Sankey Diagram Excel Dashboard? A Step-by-Step Guide

How to Make a Sankey Diagram Excel Dashboard?