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.

**Table of Contents**hide

## Download Practice Workbook

You can download our practice workbook from here for free!

## 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 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 two categories becomes the same, it gets very tough to differentiate between them anymore.

## Steps to Make Sankey Diagram in Excel

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.

### đź“Ś Step 1: Prepare Necessary Data to Make Sankey Diagram

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

- It would 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.

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

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

- Now, you need to specify the space between 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.

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

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

**Read More:** **Descriptive Statistics â€“ Input Range Contains Non-Numeric DataÂ **

### đź“Ś 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.

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

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

- At this time, add some new columns named
**End Position, A**and_{start}, A_{mid1}, A_{mid2}, A_{end}, V_{start}, V_{mid1}, V_{mid2}, V_{end}, B_{start}, B_{mid1}, B_{mid2},**B**for obtaining values to draw the charts._{end}

- At these created helper columns, for getting the values, insert the following formula and hit the
**Enter**button.

**A _{start} Column:**

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

**A _{mid1} Column:**

`=[@Astart]`

**A _{mid2} Column:**

`=[@Aend]`

**A _{end} Column:**

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

**V _{start} Column:**

`=[@Value]`

**V _{mid1} Column:**

`=[@Value]`

**V _{mid2} Column:**

`=[@Value]`

**V _{end} Column:**

`=[@Value]`

**B _{start} Column:**

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

**B _{mid1} Column:**

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

**B _{mid2} Column:**

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

**B _{end} Column:**

`=SUM([Value])-[@Aend]-[@Vend]`

- Besides, create another table for obtaining the source pillars.
- Create two columns named
**From**and**Value**in this table.

- Afterward, click on the
**C28**cell and insert the following formula for source pillars values.

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

- Following, press the
**Enter**button.

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

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

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

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

**Â Read More:** **How to Move Data from Row to Column in Excel (4 Easy Ways)**

**Similar Readings**

**How to Set Intervals on Excel Charts (2 Suitable Examples)****How to Remove Last Modified By in Excel (3 Ways)****Make a Dot Plot in Excel (3 Easy Ways)****How to Create Butterfly Chart in Excel (2 Easy Methods)**

### đź“Ś Step 3: Draw Individual Sankey Lines

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.

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

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

- Afterward, click on the
**Add**button.

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

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

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

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

As a result, your Sankey lines are drawn.

**Read More: How to Create an Organizational Chart in Excel (2 Suitable Ways)**

### đź“Ś Step 4: Draw Sanky Pillars and Complete Sanky 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.

- As a result, a stacked chart will appear.
- Now, go to the
**Chart Design**tab >>**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.

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

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

- As a result, you will get your final source pillar to make the 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.

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

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

**Read More:** **How to Make a Venn Diagram in Excel (3 Easy Ways)**

## Understanding a Sankey Diagram

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

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.

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

And, visit **ExcelDemy** for many more articles like this. Thank you!

**Related Articles**

**How to Add Trailing Zeros in Excel (2 Easy Ways)****Calculate WACC in Excel (with Easy Steps)****How to Make a Box Plot in Excel (With Easy Steps)****Make a Forest Plot in Excel (2 Suitable Examples)****How to Calculate Cost of Funds in Excel (with Easy Steps)****How to Calculate APR in Excel (3 Simple Methods)****If a Value Lies Between Two Numbers Then Return Expected Output in Excel****How to Move Up and Down in Excel (5 Easy Methods)**