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.

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.

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.

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: NodesThis 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 DataThis 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.

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:

   • Right-click on the "Gap" or "Padding" series in your chart and set the Fill to No Fill. This makes them invisible, creating blank space around your visible bars.

   • In the Format Axis options for the Y-axis, check the box for Categories in reverse order. This puts the first item at the top.

   • Delete the chart title, legend, and X-axis to clean it up.

   • Place the two charts side by side on your canvas. This creates the left and right pillars of your Sankey diagram.

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:

  • Select a flow line. Go to Format Data Series.

  • Increase the line Width significantly (e.g., 20pt, 30pt, or more), depending on the magnitude of the flow. This is a manual approximation but visually effective.

  • Set the line color to a semi-transparent gray or light color. This allows overlapping flows to be visible.

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