Ever wondered how to visualize complex flow data in Excel? A Sankey diagram could be the perfect tool for the job. Sankey diagrams, also known as Sankey flows, are a type of flow diagram in which the width of the bands between the nodes is proportional to the flow quantity. Here's a step-by-step guide on how to make a Sankey chart in Excel.
Before we dive into the process, ensure you have the latest version of Excel with Office 365 subscription. The-charting capabilities in Excel 365 are extensive and continuously evolving, making it easier to create complex diagrams like Sankey charts.
Preparing Your Data for the Sankey Chart
The first step in creating a Sankey chart is to structure your data effectively.
Your data should ideally be in a list or table format, with one column for the starting nodes, one column for the ending nodes, and at least one column for the flow values. Here's an example:
Aligning Your Data
Make sure your data is organized neatly in a table to prevent errors later on.
As a best practice, exclude any blank rows or columns, as these can cause errors in the charting process.
Uniquely Identify Nodes
Each node in your Sankey diagram represents a different data category.
To ensure the chart connects the right nodes, make sure each starting and ending node is unique. For example, if you're visualizing energy flow, ensure each resource (like 'Coal' or 'Wind') is not repeated under different names.
Creating the Sankey Chart in Excel
Now that your data is adequately prepared, it's time to create your Sankey chart.
This process involves creating a scatter plot with smooth series and adjusting the markers to represent the flow quantities.
Convert Your Data to a Scatter Plot
Select your data, then click 'Insert' on the toolbar. Choose 'Scatter' and then 'Smooth'.
Excel will create a scatter plot, with the starting nodes on the x-axis, the ending nodes on the y-axis, and the flow values represented by the scatter points.
Adjust the Marker Sizes
Right-click the plot area and select 'Format Selection'. On the 'Marker' tab, adjust the 'Marker size' to be proportional to your flow values.
This way, as your flow values change, the size of the scatter points will also change, reflecting the flow quantities in your Sankey chart.
Add Texture to Your Node Circles
To make your chart more visually appealing, you can add a background texture to your node circles.
On the 'Marker' tab, set the 'Marker fill' to a suitable color and adjust the 'Transparency' level for a better effect.
There you have it! You've successfully created a Sankey chart in Excel. This chart type can enhance data visualization and provide a clearer understanding of data flow in your reports. If you haven't already, why not experiment with different data sets to test out your new skill?