When it comes to visualizing complex flows, relationships, and transitions in data, the Sankey Chart excel in providing clear and concise representations. Sankey charts, also known as Sankey diagrams, offer a unique and effective way to showcase the distribution and movement of quantities over multiple categories or stages.
Join us as we explore the concept of Sankey charts in Excel and their applications, and provide answers to frequently asked questions to help you leverage this powerful visualization tool.
| Quick Answer |
| A Sankey chart in Excel is a visual representation that showcases the flow of data, energy, or resources between different categories or stages. Sankey charts excel in presenting complex flows and relationships in a clear and concise manner, making them useful in various applications such as energy management, financial analysis, customer journeys, and process mapping. |
Understanding Sankey Charts in Excel
Definition: The Sankey Chart depicts “a flow” of data from one set to the next. The two elements that are linked are referred to as “nodes.” The connections are denoted by the term “links.” Moreover, it is named after an Irishman, Capt. Matthew Sankey, who used them for the first time in a study on the energy efficiency of a steam engine in 1898.
Sankey charts in Excel are visual representations that showcase the flow of data, energy, resources, or any other quantity between different categories or stages. These charts use connected flow lines or ribbons to depict the proportionate quantities or values moving from one category to another.
The width of the lines or ribbons represents the magnitude of the flow, providing an intuitive and visually appealing display of the data.
Components of a Sankey Chart in Excel
A Sankey diagram is a simple diagram that consists of the following elements:
- Nodes: An element connected by “Flows.” It also represents the occurrences in each path.
- Flow: Flows connect the nodes. And the names of the source and target nodes in the “from” and “to” fields are used to identify each flow. Furthermore, the width field value defines the flow’s thickness.
- Drop-offs: A drop-off is a flow that does not have a target node.
Applications of Sankey Charts in Excel
Sankey charts find extensive use in various domains due to their ability to represent complex relationships and flows. Some common applications include:
a. Energy and Resource Management: Sankey charts can illustrate energy consumption, distribution, and losses in power systems, as well as the flow of resources in manufacturing processes.
b. Financial Analysis: Sankey charts can showcase cash flow, budget allocations, and investment portfolios, highlighting the movement of funds between different categories.
c. Customer Journeys: Sankey charts can demonstrate customer behavior, showing the progression of individuals through different stages such as website visits, conversions, and purchases.
d. Process Mapping: Sankey charts can visualize process flows, displaying the movement of inputs, outputs, and transformations in manufacturing, logistics, or project management.
How to Create Sankey Diagram in Excel
Excel provides several options for creating Sankey charts, including built-in templates and add-ins. Here are the general steps to create a Sankey chart in Excel:
a. Organize your data: Prepare a dataset with columns representing source, target, and flow values. Each row corresponds to a flow between two categories.
b. Insert a Sankey chart: Select the data range, go to the Insert tab, and choose the Sankey chart option. Excel will generate a basic Sankey chart based on your data.
c. Customize the chart: Format the chart by adjusting colors, line thickness, labels, and other visual elements to enhance clarity and aesthetics. Use Excel’s chart tools to modify the chart as per your requirements.
Energy Management Application
Assume you’ve been asked by a fictional country’s Energy Commission with analyzing their massive data. They wish to know the following information on domestic energy consumption:
- Each energy-generating source’s contribution.
- The total quantity of energy lost.
- The contribution of cleaner and greener energy sources.
- Energy usage patterns by consumer segment (commercial use versus domestic use).
The Energy Commission is looking for a data story to use in the upcoming announcement of its 10-year plan. The example data for the scenario above is shown in the table below.
Note: The table below is quite long to demonstrate how Sankey can present massive data sets without concealing key information.
Data Table
| Energy Type | Main Source | Source type | Energy Source | Usage | End-User | MegaWatt |
|---|---|---|---|---|---|---|
| Agricultural waste | Bio-conversion | Solid | Thermal generation | Losses in process | Lost | 5 |
| Agricultural waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Industry | 7.3 |
| Agricultural waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – commercial | 5.1 |
| Agricultural waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – homes | 3.7 |
| Agricultural waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – commercial | 4.9 |
| Agricultural waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – homes | 2 |
| Other waste | Bio-conversion | Solid | Thermal generation | Losses in process | Lost | 7.2 |
| Other waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Industry | 5.4 |
| Other waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – commercial | 6.7 |
| Other waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – homes | 4.8 |
| Other waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – commercial | 7.4 |
| Other waste | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – homes | 2.5 |
| Marina algae | Bio-conversion | Solid | Thermal generation | Losses in process | Lost | 0.7 |
| Marina algae | Bio-conversion | Solid | Thermal generation | Electricity grid | Industry | 0.5 |
| Marina algae | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – commercial | 0.9 |
| Marina algae | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – homes | 0.5 |
| Marina algae | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – commercial | 0.8 |
| Marina algae | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – homes | 0.6 |
| Land-based bioenergy | Bio-conversion | Solid | Thermal generation | Losses in process | Lost | 1.3 |
| Land-based bioenergy | Bio-conversion | Solid | Thermal generation | Electricity grid | Industry | 2.5 |
| Land-based bioenergy | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – commercial | 3.2 |
| Land-based bioenergy | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – homes | 0.7 |
| Land-based bioenergy | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – commercial | 1.4 |
| Land-based bioenergy | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – homes | 0.9 |
| Biomass import | Bio-conversion | Solid | Thermal generation | Losses in process | Lost | 0.4 |
| Biomass import | Bio-conversion | Solid | Thermal generation | Electricity grid | Industry | 0.7 |
| Biomass import | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – commercial | 0.8 |
| Biomass import | Bio-conversion | Solid | Thermal generation | Electricity grid | Heating and cooling – homes | 0.3 |
| Biomass import | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – commercial | 0.6 |
| Biomass import | Bio-conversion | Solid | Thermal generation | Electricity grid | Lighting & appliances – homes | 0.2 |
| Nuclear reserves | Nuclear Plant | Solid | Thermal generation | Losses in process | Lost | 50 |
| Nuclear reserves | Nuclear Plant | Solid | Thermal generation | Electricity grid | Industry | 13 |
| Nuclear reserves | Nuclear Plant | Solid | Thermal generation | Electricity grid | Heating and cooling – commercial | 8 |
| Nuclear reserves | Nuclear Plant | Solid | Thermal generation | Electricity grid | Heating and cooling – homes | 6 |
| Nuclear reserves | Nuclear Plant | Solid | Thermal generation | Electricity grid | Lighting & appliances – commercial | 11 |
| Nuclear reserves | Nuclear Plant | Solid | Thermal generation | Electricity grid | Lighting & appliances – homes | 4 |
| Coal reserves | Coal | Solid | Thermal generation | Losses in process | Lost | 4.7 |
| Coal reserves | Coal | Solid | Thermal generation | Electricity grid | Industry | 3.1 |
| Coal reserves | Coal | Solid | Thermal generation | Electricity grid | Heating and cooling – commercial | 4.2 |
| Coal reserves | Coal | Solid | Thermal generation | Electricity grid | Heating and cooling – homes | 0.7 |
| Coal reserves | Coal | Solid | Thermal generation | Electricity grid | Lighting & appliances – commercial | 4.8 |
| Coal reserves | Coal | Solid | Thermal generation | Electricity grid | Lighting & appliances – homes | 0.5 |
| Gas reserves | Natural Gas | Gas | Thermal generation | Losses in process | Lost | 5.1 |
| Gas reserves | Natural Gas | Gas | Thermal generation | Electricity grid | Industry | 8.4 |
| Gas reserves | Natural Gas | Gas | Thermal generation | Electricity grid | Heating and cooling – commercial | 7.9 |
| Gas reserves | Natural Gas | Gas | Thermal generation | Electricity grid | Heating and cooling – homes | 4.8 |
Steps to Visualize Data with Sankey Charts
Now let’s use ChartExpo Add-in for this data.
To visualize your data with Sankey Charts, simply follow the instructions outlined below.
- Click on Add-ins > ChartExpo > Insert
- Click on Sankey Chart.
- Choose the variables: Type, Main Source, Source type, Energy Source, Usage, End User, Mega Watt.
- Select Create Chart from Sheet Data. Now you should get a result on your screen.
Note: You have the option of editing the chart to highlight important information. Simply click the Edit Chart button.
Why You Should Use Sankey
- Sankey Charts for Excel spreadsheet allows you to visually depict complex processes while focusing on a specific component or resource that you want to highlight.
- Sankey also has the advantage of supporting numerous viewing levels.
Aside from that, viewers can acquire a high-level overview, view individual information, or build interactive views.
- These visualization charts highlight dominant contributors or consumers. As a result, your audience will be able to notice crucial insights such as relative magnitudes and regions with the greatest prospects.
Assume you have data from various but interconnected segments. You’ve discovered a point of leaking. And you want to bring this to your company’s management’s attention. How would you approach it?
You were completely correct. A data story is a great tool for convincing management to act on your recommendations. But how would you make this story compelling?
You must utilize clear and easy-to-understand graphics. You don’t want your viewers to become disoriented while attempting to decipher insights from a chart. This is a morale destroyer. A Sankey is strategically placed to show the data scenario described above. This is due to the fact that it might help construct a realistic picture of the leakage in the minds of the audience (in our case, company management). Yes, you read that correctly.
This chart will reveal the precise locations of the leakage. As previously stated, leakages, also known as drop-off zones, are flows that do not have a target node. Choosing relevant and simple charts makes it simple for your audience to grasp and interact with the data.
READ ALSO:
- How to Print Gridlines in Excel Online
- How to Import Data into Excel Online
- How to get Excel Certified Online
- How to Access Excel Online
Frequently Asked Questions
Sankey charts are not natively available in older versions of Excel. However, you can explore third-party add-ins or consider upgrading to newer versions that support Sankey chart creation.
Sankey charts can handle large datasets, but it’s crucial to ensure data accuracy and avoid clutter. Consider aggregating or summarizing data if there are too many categories or flows to maintain chart readability.
Yes, you can export Sankey charts from Excel to other applications as image files or copy and paste them into documents, presentations, or graphic design software.
While Excel provides basic functionality to create Sankey charts, it has certain limitations compared to dedicated data visualization tools. Some limitations include the lack of advanced customization options, limited interactivity, and difficulties in handling large or complex datasets.
Consider using specialized data visualization software or add-ins if you require more advanced features and flexibility.
Yes, you can add supplementary data to a Sankey chart in Excel. This can be achieved by adding additional columns to your data table and mapping them to specific visual elements within the chart. For example, you could include tooltips, labels, or additional flow values to provide more context to the chart.
The Wrap-Up
Sankey Charts for Excel are among the best charts you can use to make your data narrative irresistible to your audiences. This chart is incredibly easy to read and interpret, and you can rely on it to highlight the key insights supporting the thesis statement of your data story. You can draw this chart with very simple steps using Excel’s ChartExpo library.
