How to Make a Sankey Diagram in Excel with ChatGPT

A Sankey diagram is one of the most effective ways to visualize the flow of data, yet it's famously missing from Excel's standard chart library. While you could download unofficial add-ins or move your data to a BI tool, an elegant solution sits right inside your spreadsheet, powered by ChatGPT. This guide will walk you through creating a stunning Sankey diagram in Excel, showing you both the manual logic and how to use ChatGPT to automate the most time-consuming steps.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is a Sankey Diagram, Exactly?

A Sankey diagram is a type of flow chart where the width of the arrows or bands is proportional to the quantity of the flow. You might use one to map a customer journey, illustrate energy flow, or track marketing budget allocation. The powerful visual makes it instantly clear where the biggest contributions and drop-offs are occurring.

Common uses include:

• Website Analytics: Visualizing how users flow from traffic sources (e.g., Organic Search, Social Media) to different sections of your site (e.g., Home Page, Pricing Page, Blog).
• Marketing Funnel Analysis: Mapping how leads move from one stage to the next, like from "Marketing Qualified Lead" to "Sales Accepted Lead" to "Closed Won."
• Budget Allocation: Showing how a total budget is divided among departments and then further distributed to specific projects or campaigns.
• Supply Chain Movement: Tracking products from supplier to warehouse to final retail destination.

The core value is its ability to highlight relationships and magnitudes within a system's flow, making complex data intuitive and easy to grasp.

Why Create a Sankey Diagram in Excel?

Excel may not have a one-click Sankey chart option, but building one manually (or with an AI assistant) offers some distinct advantages:

• Accessibility: You don't need to purchase or learn a new piece of specialized software. Your data often starts in Excel, so it makes sense to finish your analysis there.
• Customization: The method we will use gives you complete control over the colors, formatting, and layout, allowing you to match your brand's style precisely.
• Impressive Storytelling: A well-made Sankey diagram stands out in a presentation, demonstrating a deeper level of analysis than a standard bar or pie chart.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Preparing Your Data for the Sankey Chart

The foundation of a good Sankey diagram is a well-structured data table. The process involves turning your raw data into a format that Excel can use to build the visual. This setup is the most critical part, and it's where an AI tool can save you an immense amount of time.

First, your raw data should be in a simple, three-column format: Source, Destination, and Value (or Weight).

For this tutorial, let's use a sample of marketing data representing website traffic:

Example Raw Data:

To turn this into a chart, we need to create several helper tables. These tables will calculate the sizes of the pillars (nodes), the gaps between them, and the curves of the flows that connect them. It sounds complex because it is - but breaking it down makes it manageable.

Using ChatGPT to Generate Your Calculation Table

Manually writing the formulas to restructure this data is tedious and prone to error. You'd need a combination of SUMIFS, VLOOKUP, and other complex functions to get everything just right. Instead, we can simply ask ChatGPT to do the heavy lifting.

You can use a detailed prompt to get the exact formulas you need. By providing the structure and logic, the AI acts as your personal data analyst, building the backend for you.

Step 1: Get the Helper Formulas from ChatGPT

In ChatGPT, use a prompt like this. Be sure to copy and paste your actual raw data table directly into the prompt so the AI can use your specific categories.

I want to create a Sankey diagram in Excel using a stacked bar chart and a scatter plot. I have the following data in a table starting in cell A1:

[Paste your raw data table here]

Please provide all the necessary Excel formulas to create three helper tables:

1. A "Node Calculations" table: This table should list all unique sources and destinations. For each one, it needs to calculate its total value, its cumulative starting position, and its ending position. Also include a 'gap' size between the nodes.

2. A "Source & Destination Bar Chart" table: This will feed the two main bar charts. Create columns for the Node Names, a visible bar value, and an invisible bar value for padding.

3. A "Flow Calculations" table for the scatter plot: This is the most important one. For each row in my original data (e.g., Organic Search to Home), calculate the (x, y) coordinates needed to draw a smooth bezier curve connecting the source bar to the destination bar. I'll need source X, source Y-start, source Y-end, destination X, dest Y-start, and dest Y-end.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Set Up Your Helper Tables in Excel

ChatGPT will return a series of formulas. Now, your job is to simply arrange them in your spreadsheet. Your sheet will look something like this, with three distinct sections:

Table 1: Nodes This table calculates the size and position of your Source and Destination bars (your nodes). It identifies unique items, sums their total values, and calculates where they should sit on the chart's Y-axis.

Table 2: Bar Chart Data This table structures the data for your two main bar charts. Each "bar" is actually a stack of three series: a blank (transparent) section, the colored node, and another blank section. This creates the visual effect of floating blocks with gaps between them.

Table 3: Flow Data (For the Scatter Plot) This is where the magic happens. For each individual flow in your original data, this ridiculously long table calculates the exact starting and ending points for the connecting curves. It determines where a flow should "leave" the source bar and "arrive" at the destination bar. Manually creating this for more than two flows is a recipe for a headache - ChatGPT turns it into a simple copy-and-paste task.

Building the Sankey Diagram in Excel

With your calculation tables automatically populated by ChatGPT's formulas, you're ready to build the chart.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Create the Source and Destination Bars

1. Select the data for your Source bar chart from your Bar Chart data table.
2. Go to Insert > Chart > 2-D Bar > Stacked Bar.
3. Do the same for your Destination data to create a second, separate bar chart.
4. Format the bars:

Step 2: Add the Flows Using a Scatter Plot

The "flows" connecting your two bar charts are actually drawn with a scatter plot with smoothed lines. You will overlay this on top of your bar charts. This is the most meticulous step.

1. Right-click on one of your bar charts and choose Select Data.
2. Click Add to create a new data series. Name the series after one of your flows (e.g., "Organic to Home").
3. For the series values, just select a single empty cell for now. Click OK.
4. Now, right-click the new series in the chart and choose Change Series Chart Type.
5. In the combo chart view, find your new series and change its chart type to Scatter with Smoothed Lines. Make sure the "Secondary Axis" box is unchecked.
6. Right-click the chart again and go back to Select Data. Select your scatter plot series and click Edit.
7. Now, select the X and Y values from your massive Flow Data table that ChatGPT helped create. Each flow (like "Organic to Home") has its own set of X and Y coordinates.
8. Repeat this process - adding a new scatter-plot series - for every single flow in your dataset. Yes, this is repetitive, but with the data already calculated for you, it's just a matter of pointing and clicking.

Step 3: Format and Polish The Flows

Once all your scatter plot series are added, they will look like lines connecting the nodes. To give them the classic Sankey appearance:

• Add Fill: You can't directly "fill" a line. The workaround is to create two series for each flow - one at the top edge and one at the bottom - and then use the "Fill" option between a series for special chart types that support it by creating an area chart instead of a scatter or a line. However, a simpler approach is to adjust the line width and transparency:
• Match the scale: Ensure both your bar chart and scatter plot are on a similar axis scale by manually setting the minimum and maximum bounds of their Y-axes.

After a bit of formatting and alignment, you will have a functional and visually compelling Sankey diagram built entirely within Excel.

Final Thoughts

This process proves that with a bit of workaround ingenuity, you can push Excel far beyond its out-of-the-box capabilities. While setting up a Sankey diagram is complex due to the data restructuring required, tools like ChatGPT can act as your co-pilot, handling the tedious formula creation and freeing you up to focus on visual formatting and interpreting the insights from the chart.

Of course, manually building charts comes with its own challenges, especially when data needs to be regularly updated. When you need automated, real-time insights from sources like Google Analytics, Shopify, and your various ad platforms, the manual process isn't scalable. To eliminate spreadsheet-wrangling from our workflow, we built Graphed. Instead of spending hours in setup, you can simply connect your data and ask in plain English: "Show me a flow of my website traffic from marketing channel to landing page," and receive a live, interactive visualization instantly.