How to Make Sankey Diagram in Excel (With Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

When working with flow analysis, the Sankey diagram is a fantastic tool to use. This diagram portrays the flow characteristics, orientations, and trends of the whole dataset easily and effectively. Now, if you are looking forward to the ways of creating a Sankey diagram, you have landed in the perfect place. In this article, I will show you step-by-step guidelines to make a Sankey diagram in Excel.


What Is Sankey Diagram?

Sankey diagram is mainly a flow diagram which is very handy and effective when working with flow analysis. The width of the arrows is maintained by the quantities and values of the categories.

Any kind of flow analysis such as material flow, energy flow, cash flow, etc. can be visualized and analyzed easily through this diagram.

Advantages:

  • The most crucial advantage of the Sankey diagram is you can visualize the trend of multiple categories of your data.
  • You can understand the relative weights of every category by the width of the arrows in the Sankey diagram.
  • You can portray many complex categories through the Sankey diagram.

Disadvantages:

  • It’s hard to draw and understand sometimes due to its complex features.
  • If the arrow width of the two categories becomes the same, it gets very tough to differentiate between them anymore.

Say, we have a dataset of a person’s income source and expense destinations. Now, we can make a Sankey diagram based on his varying income sources fulfilling the expense at varying destinations. You can follow the steps below to achieve this target.

Sample dataset to Make Sankey Diagram


📌 Step 1: Preparing Necessary Data to Make Sankey Diagram in Excel

First and foremost, you need to prepare your sample dataset properly to make a Sankey diagram.

  • It will be better if you make your data range into a table.
  • To do this, select your data range (B4:F8 cells here) >> click on the Insert tab >> Table tool.

Convert the Dataset into Table to Make Sankey Diagram

  • As a result, the Create Table window will appear. Subsequently, click on the OK button.

Confirm the Creation of Table

  • At this time, it would be better to name your table.
  • To do this, click inside the created table >> go to the Table Design tab >> write Dataset inside the Table Name: toolbox.

Name the Table

  • Now, you need to specify the space between the two categories of the Sankey diagram.
  • To do this properly, write the value inside the D10 cell >> go to the Formulas tab >> Defined Names group >> Define Name option.

Access the Define Name Option

  • At this time, the New Name window will appear.
  • Following, write Space inside the Name: text box and click on the OK button.

Create a Named Range to Make Sankey Diagram

Thus, your sample dataset is prepared to complete further calculations to make a Sankey diagram in Excel.


📌 Step 2: Prepare Sankey Lines Table

After preparing the dataset properly, it is time to do further calculations and prepare the Sankey Lines table.

  • To do this, at the very beginning, create a table named Lines containing From, To, and Value columns.

Sankey Lines Table

  • Afterward, click on the D5 cell and insert the following formula.
=IF(LEFT([@From],5)="Space",Space,INDEX(Dataset,MATCH([@From],Dataset[From / To],0),MATCH([@To],Dataset[#Headers],0)))
  • Subsequently, hit the Enter button.

Find Values of Sankey Lines from Data

  • As this is a table, all the cells below this column will follow the same formula and will be automatically filled.

All Values Extracted from Dataset

  • At this time, add some new columns named End Position, Astart, Amid1, Amid2, Aend, Vstart, Vmid1, Vmid2, Vend, Bstart, Bmid1, Bmid2, and Bend for obtaining values to draw the charts.

Create New Columns of Sankey Table

  • The values in the End Position column will be given according to column To and insert a space number after each category ends.
  • The first category is Rent in the To column. So, numbering will continue as 1,2,3,4 downwards for Rent and when all Rent is counted, Space 1 will appear and contain the next digit (5 here).

Finding End Position Value for Food Category

  • Similarly, Food is the second category. It will contain end position numbers from 6. The numbering will increase as we go downward in the To column and find the Food category. When all Food is counted, Space 2 will appear and contain the next digit (10 here). This will go on until all the values of the To column are counted.

Finding End Position Value for Rent Category

  • For the other created helper columns, to get the values, insert the following formula and hit the Enter button.

Astart Column:

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

Amid1 Column:

=[@Astart]

Amid2 Column:

=[@Aend]

Aend Column:

=SUM([Value])-SUMIFS([Value],[End Position],">="&[@[End Position]])

Vstart Column:

=[@Value]

Vmid1 Column:

=[@Value]

Vmid2 Column:

=[@Value]

Vend Column:

=[@Value]

Bstart Column:

=SUM([Value])-[@Astart]-[@Vstart]

Bmid1 Column:

=SUM([Value])-[@Amid1]-[@Vmid1]

Bmid2 Column:

=SUM([Value])-[@Amid2]-[@Vmid2]

Bend Column:

=SUM([Value])-[@Aend]-[@Vend]
  • Besides, create another table for obtaining the source pillars.
  • Create two columns named From and Value in this table.

Source pillars Table

  • Afterward, click on the C28 cell and insert the following formula for source pillar values.
=SUMIFS(Lines[Value],Lines[From],[@From])
  • Following, press the Enter button.

Source Pillars values Extraction Formula

  • Similarly, create another table for destination pillars containing two columns named To and Value.

Destination Pillars Table

  • Following, click on the C38 cell and insert the following formula.
=SUMIFS(Lines[Value],Lines[To],[@To])
  • Subsequently, hit the Enter button to get all the destination pillars’ values.

Destination Pillars Values

  • Last but not least, you will need spacing values of the X-axis to draw the diagram properly.
  • Write the spacing values as 0,10,90 and 100 at C46, D46, E46, and F46 cells.

Spacing Values

Thus, now you have all the values that you need to draw the Sankey diagram of your dataset.


📌 Step 3: Drawing Individual Sankey Lines in Excel

Now, after getting all these values, you need to draw individual Sankey lines.

  • To do this, first and foremost, click on the Insert tab >> Insert Line or Area Chart tool >> 100% Stacked Area option.

Insert Chart to Make Sankey Diagram

  • Consequently, a 100% stacked area chart will appear.
  • Now, right-click on the chart area and choose the option Select Data… from the context menu.

Stacked Area Chart

  • As a result, the Select Data Source window will appear.
  • Following, at the Legend Entries (Series) pane, click on the Remove button to delete all the initial entries.

Remove Initial Entries

  • Afterward, click on the Add button.

Add Series to Make Sankey Diagram

  • As a result, the Edit Series window will appear.
  • Subsequently, write 1 at the Series Name: text box >> choose the F5:I5 cells reference in the Series values: text box.
  • Finally, click on the OK button.

Edit Series Window

  • Now, at the Horizontal (Category) Axis Labels pane, click on the Edit button.

Edit Horizontal Labels to Make Sankey Diagram

  • At this time, the Axis Labels window will appear.
  • Refer to the F46:I46 cells in the Axis label range: text box.
  • Following, click on the OK button.

Finalize Horizontal Axis values

  • Now, double-click on the Y-axis >> tick the option Values in reverse order from the Format Axis pane on the right side.

Reverse Order to Make Sankey Diagram

As a result, your Sankey lines are drawn.

Read More: How to Draw Lines in Excel


📌 Step 4: Adding Sankey Pillars to the Diagram

Now, you need to draw the Sankey pillars to complete the diagram.

  • To do this, select the B28:C34 cells >> click on the Insert tab >> Insert Column or Bar Chart tool >> 100% Stacked Column option.

Insert Bar Chart to Make Sankey Diagram

  • As a result, a stacked chart will appear.
  • Now, go to the Chart Design tab >> Change Chart Type tool.

Access the Change Chart Type tool

  • At this time, the Change Chart Type window will appear.
  • Now, choose the second option and click on the OK button.

Finalize your Chart Type

  • At this time, you will see your desired pillar which will look like the following.

Source Pillar to Make Sankey Diagram

  • Now, you don’t need the space to be shown.
  • So, click on the space area and choose the option No fill from the Format Chart Area pane on the right side.

Remove Fill from Space

  • As a result, you will get your final source pillar to make the Sankey diagram.

Final Source Pillars to Make Sankey Diagram

  • Similarly, you can make pillars for destination sources and change their colors for better understanding by choosing the Fill Color option from the Format Data Series pane on the right side.
  • Finally, you have everything to make the Sanky diagram.

Destination Pillars to Make Sankey Diagram

  • Now, combine these Sankey lines with the Sankey pillars to complete the Sankey diagram.

Thus, you have made a Sankey diagram successfully. And, the final diagram would look like this.

Sankey Diagram in Excel

Read More: How to Draw to Scale in Excel


Understanding a Sankey Diagram

Sankey diagrams can easily portray the sources, destinations, and contributing pathways from sources to destinations.

Understanding the Sankey Diagram

Generally, the sources are situated on the left side and the destinations are on the right. From the sources to destinations, several pathways are drawn to depict the tradition and contribution of each source and destination. Besides, the width of these pathways helps to visualize the greater and lesser contribution of pathways.

Read More: How to Make Fishbone Diagram in Excel


Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

So, I have shown you all the steps to make a Sankey diagram in Excel. Go through the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me.


Related Articles


<< Go Back to Drawing in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

5 Comments
  1. D5 formula does not work. The syntax of the name “@To” isn’t correct. Verify the name: -starts with a letter or underscore, – Doesn’t include a space or character that isn’t allowed. – Doesn’t conflict with an existing name in the workbook.

    • Hello James,
      Thank you for your query.
      Regarding your problem, I can assure you that the formula in cell D5 is correct. The syntax “@To” is not a common syntax, this is right. It is actually a dynamic table formula syntax. When you work with dynamic table formula, then to look up certain column values, you have to use this syntax. Even, if you select a cell of a table column inside a table, you will see the formula would automatically pick this syntax. I hope this answers your question.
      Again, to assure you, the formula in cell D5 is working correctly in my file. But, if you keep facing issues regarding this formula, I would really appreciate it if you send me your Excel file. Maybe I can help you further then!
      Thank you!

      Regards,
      Md. Tanjim Reza Tanim
      Team Leader, Exceldemy.

  2. I have completed all the other steps but am confused by the ‘end position’ column in the lines table. I do not see a step which tells me what to enter into this column, which is an issue for the other formulas.

  3. I have completed all the other steps but am confused by the ‘end position’ column in the lines table. I do not see a step which tells me what to enter into this column, which is an issue for the other formulas.

    • Dear FRANCESCA BATHE,
      End Position is given according to column To. Where there is RENT in To column, the number starts 1,2,3,4 and ends with SPACE1 numbered 5. Then next is Food starting 6,7,8, 9 and ends with SPACE2 numbered 10. Likewise, numbering is done for every cell data in End Position.
      Numbering End position
      Regards,
      Joyanta Mitra

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo