Spreadsheets Expert

SpreadSheets Expert

How to Create a Sankey Diagram in Excel?

There is no doubt Excel is one of the most used applications worldwide for creating charts and graphs. One of the most demanded charts is the Sankey Diagram. Sankey Chart illuminates the flow of information, energy, or materials through a complex system, allowing you to present data in a dynamic and easily digestible format.

With confidence, this guide will walk you through the process of creating a Sankey Diagram in Excel Using the Best Sankey Diagram Generators, transforming raw data into a compelling narrative that highlights key relationships and efficiencies. Whether you’re analyzing business processes, energy consumption, or financial transactions, mastering the Sankey Diagram in Excel will elevate your data analysis and presentation skills to the next level. Let’s dive in and make your data speak volumes.

Table of Contents:

  1. Sankey Diagram Definition
  2. Sankey Chart Components in Excel
  3. How to Create a Sankey Diagram in Excel Using the Best Sankey Diagram Generator?
    1. Creating Sankey Chart Using ChartExpo add-in for Excel
    2. Creating Sankey Chart Using Power-user add-in for Excel
    3. Creating a Simple Sankey Diagram in Excel for Free without using any Add-on
  4. ChartExpo vs. Power User
  5. FAQs

Let’s start by understanding the Sankey Chart and its components so we know how to create it easily in Excel.

What is the Sankey Diagram?

Definition: The Sankey Diagram is one of the most used types of flow diagrams, which shows/presents the flow between 2 or more dimensions. Researchers, analysts, and professionals like this diagram because it helps them to find the flaws in their system by presenting the flow of data in a complete process.

This diagram is named after a person who created this diagram to show the energy efficiency of a steam engine in his publication. The person’s name was an Irishman, Capt. Matthew Sankey. This guide will tell you how to create a Sankey Diagram in Excel.

Let’s first learn about the components of the Sankey Diagram.

Components of Sankey Chart

What makes the Sankey Diagram interesting is its components.

Nodes & Links: The connected dimensions are called nodes; the connection between these nodes is known as links. The width of the links is directly proportional to the values assigned to those connected nodes.

Now, let’s go to the main part of the blog, How to Create a Sankey Diagram in Excel.

How to Create a Sankey Diagram in Excel Using the Best Sankey Diagram Generator?

Due to the useability and popularity of the Sankey Chart, there are many tools online that you can use to create it. Excel does not have a Sankey Diagram in its charts, but there are ways that you can use to create a Sankey Chart in Excel.

The best possible ways include using third-party add-ins in Excel and customizing existing charts to meet the needs of Sankey visualization. The first way is straightforward, and it takes only a few steps to create a Sankey Chart using a third-party add-in in Excel.

Second, customizing existing charts to create this diagram in Excel is very time-consuming. Stay calm as we will teach you both ways and their advantages and disadvantages so you can choose easily.

Here are some tools that you can use as a Sankey Diagram Generator in Excel:

  1. ChartExpo add-in for Excel
  2. Power-user add-in for Excel

1. ChartExpo add-in for Excel:

Sankey Charts are straightforward to create in Excel once you have set up the proper environment. To make this chart, you first need the data in the appropriate format, and the second required thing is the ChartExpo add-in for Excel. Follow the steps below to create Sankey Visualization in Excel using ChartExpo.

  1. Open your Excel applications and prepare the data on which you want to draw the Sankey Diagram.
  2. Go to the “Insert” tab and Click on Store. Now click on the search box and type ChartExpo.
  3. You will see ChartExpoTM for Excel in the search results. Click on Add.
  4. Alternatively, you can use this link to install it directly from Microsoft Store.
  5. Once you have installed the ChartExpo add-in in the Excel application, follow the steps below to create a Sankey Diagram in Excel.
  6. Go to the Insert tab and click on My Apps. There, you will see ChartExpo for Excel in your installed applications. Click on it and then click on Insert.
  7. You will see the list of charts this add-on offers. Click on the search box and type “Sankey Chart”.
Find Sankey Chart in Sankey Diagram Generator
  • Click on the Sankey Chart, and a new window will open containing a sample Sankey Diagram.
Sample Sankey Chart in Excel
  • Now select your data in the Excel sheet and then click on the “Create chart from selection” button.
Select Data to Create Sankey Chart in Excel
  • Here you go. Here is the Sankey Chart in Excel plotted on your selected data. By default, it’s in 1 color. You can edit this chart to customize it. Edit the color nodes and their fonts to meet your requirements.
Simple Sankey Chart in Excel
  • Here is the final Sankey Diagram in Excel after customizing it which we created using our data.
How to Create Sankey Diagram in Excel

2. Power-user add-in for Excel:

The Second add-in that you can use to create a Sankey Chart in Excel is Power User. First, you have to install the Power User add-in for Excel, which you can do by following the steps below:

  • Visit their website at https://www.powerusersoftwares.com/.
  • Go to Free Download and download the file.
  • After downloading the file, install it.
  • Now open your Excel application. Go to “File” and then look for “Excel Options” in the list.
  • Now click on Add-ins and enable the “Power User” option.
  • Once the add-in is enabled, you can see Power User in the top toolbar of MS Excel.

Let’s discuss how to create a Sankey Graph in Excel using the Power User add-in.

  1. Open your MS Excel application.
  2. Enter the data on which you want to create Sankey Chart.
  3. Go to the Power User tab in the toolbar.
  4. Select the data in Sheet and then Click Create Sankey Chart in the Charts Section of Power User.
  5. Confirm the range of the selected data and press OK to continue.
  6. It will create a Sankey Diagram in Excel for you.

3. Creating a Simple Sankey Diagram in Excel for free without using any Add-on

Creating a Sankey diagram in Excel, though not a straightforward process, can be achieved with a combination of chart manipulation and creative techniques. Here’s a detailed guide on each step to create a Sankey diagram in Excel for free without using any 3rd party add-on:

Step 1: Prepare Your Data

    • Begin by organizing your data into three columns.
    • The first two columns should represent the source and target of each flow.
    • The third column will contain the numerical values indicating the flow magnitude.
    • Source: List down the starting points of your flows (e.g., Dept A, Dept B).
    • Target: Identify where each flow is directed towards (e.g., Project X, Project Y).
    • Value: Assign numerical values to each flow to represent its magnitude.

Step 2: Create a Stacked Bar Chart

    • Select your prepared data.
    • In Excel, go to the “Insert” tab and choose “Stacked Bar Chart” from the chart options.
    • You’ll need two separate stacked bar charts – one representing the sources and the other for the targets.

Step 3: Calculate Dummy Series for Spacing

    • To create the Sankey effect, you need to introduce a dummy series that will act as gaps in the chart.
    • This spacing is crucial for visually separating the different flows.
    • The dummy data should be calculated in such a way that it positions the real data appropriately on the chart.

Step 4: Customize the Chart

    • Adjust the gap width in the chart settings to 0%. This ensures that the bars in your stacked chart touch each other, forming a continuous flow.
    • Use distinct colors for different flows. This helps in differentiating the various flows and enhances the visual appeal of the diagram.

Step 5: Create Flow Paths

    • Using Excel’s shapes (like rectangles), manually draw paths that represent the flows between the bars.
    • Ensure that the width of each flow path corresponds to the value of the flow it represents.

Step 6: Fine-Tuning

    • Carefully align your manually drawn flow shapes with the corresponding bars in the stacked chart.
    • To improve visualization, adjust the transparency of the flow shapes.

Step 7: Add Labels and Final Touches

    • Use text boxes to add labels to your diagram, providing information like the names of sources, targets, and flow values.
    • Ensure that your Sankey diagram is clear, easy to read, and understand.

ChartExpo vs. Power User:

Let’s compare both tools so that you can select the one that suits you better.

  • Pricing: The ChartExpo subscription fee is $10/month while the cost is higher for Power User which is $18.75/month.
  • Easy to Use: Both tools are easy to use. You can create charts in a few clicks without any coding.
  • The Number of Advanced Visualizations: ChartExpo offers more advanced charts than Power User which you can use for data analysis purposes. But Power User also gives logos, diagrams, icons, and pictures.
  • Trial Period: You can create charts using ChartExpo and Power User in your trial period as the trial contains most of the premium features to test.

FAQs:

Can you create a Sankey diagram in Excel?

Yes, you can create a Sankey Diagram in Excel. Excel does not have a built-in Sankey Chart in its charts library. To create a Sankey chart in your Microsoft Excel application, install third-party add-ons like ChartExpo and Power User.

How do I create a Sankey chart in Excel?

Follow the steps below to create a Sankey Diagram in Excel.

  • Step 1: Open your Excel applications and prepare the data on which you want to draw a Sankey Diagram.
  • Step 2: Go to the Insert tab and click on My Apps. There, you will see ChartExpo for Excel in your installed applications. Click on it and then click on Insert.
  • Step 3: You will see the charts list; click “Sankey Chart”. A new window will open containing a sample Sankey Diagram.
  • Step 4: Select your data in the Excel sheet and click the “Create chart from selection” button.
  • Step 5: Here you go. Here is the Sankey Chart in Excel plotted on your selected data. By default, it’s in 1 color. You can edit this chart to customize it. Edit the color nodes and their fonts to meet your requirements.
  • Step 6: You will get the final Sankey Diagram in Excel, which we created using our data.

What is the best Excel add-in for the Sankey diagram?

There are a couple of add-ins that you can use in Excel to create a Sankey Chart. However, considering the ease of use, ChartExpo surely beats all the competition. You can use the ChartExpo add-in for Excel to create Sankey Visualization in a few clicks without coding in a few minutes.

What is an alternative to a Sankey diagram in Excel?

Suppose you cannot use Excel or do not want to use Excel to generate a Sankey Diagram. In that case, you can use online tools like Google Sheets to create a Sankey Diagram. Do not worry; we will publish an article on creating a Sankey Diagram in Google Sheets soon.

3 thoughts on “How to Create a Sankey Diagram in Excel?”

  1. Thanks, it helped me a lot. There is only 1 issue. I create Sankey chart in Excel using ChartExpo. It worked just as you shown in the steps but I got a watermark on it. Is there any way around to get this done without watermark.

    Reply

Leave a Comment