How to Make a Waterfall Chart in Excel with ChatGPT

A waterfall chart is one of the best ways to tell a story with your financial data, showing exactly how you got from a starting value to an ending value. While Excel has a built-in function for this, there's an even faster way to get the job done. This guide will walk you through both the standard method for creating a waterfall chart in Excel and a more powerful, automated approach using ChatGPT.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Waterfall Chart, Anyway?

Think of a waterfall chart (sometimes called a bridge chart) as a visual financial statement. It breaks down a total into its individual positive and negative components. The chart starts with an initial value (like starting revenue), then shows a series of "floating" columns that add to or subtract from that value (like sales, expenses, and returns), and ends with a final total value (like net profit).

These charts are perfect for answering questions like:

• "What drove our change in revenue from last quarter to this quarter?"
• "How did we end up with this net profit after all our expenses?"
• "Where did our project budget actually go?"

Each floating column represents a change, helping stakeholders quickly see the biggest contributors - positive or negative - to the final outcome without getting lost in a spreadsheet of numbers.

Method 1: The Standard Way with Excel’s Built-In Chart

Modern versions of Excel (Office 2016 and newer) include a dedicated waterfall chart type, which simplifies a process that used to require complex workarounds. It's straightforward but involves manual steps for setup and customization.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Set Up Your Data Table

First, organize your data in two columns. The first column should contain your categories, and the second should have the corresponding values. Positive numbers represent increases (like revenue or sales), while negative numbers represent decreases (like costs or expenses).

Here's a sample dataset showing a simple profit and loss breakdown:

Step 2: Insert the Waterfall Chart

Once your data is ready, creating the chart is just a few clicks away.

1. Highlight your entire data table, including headers (in our example, a range like A1:B8).
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts group, click on the "Insert Waterfall, Funnel, Stock, Surface, or Radar Chart" icon.
4. Select Waterfall from the dropdown menu.

Excel will instantly generate a basic waterfall chart. You'll notice that positive values are shown as "Increase" and negative values as "Decrease", but the start and end values may not be properly grounded to the axis. That's what we'll fix next.

Step 3: Define Your "Total" Columns

Your chart now shows all values as changes. To make it a proper waterfall, you need to tell Excel which columns represent totals (the pillars) and which ones are the floating blocks in between. In our example, "Gross Revenue" is our starting total and "Net Profit" is our ending total.

1. Set the Starting Total: Double-click on the first column in your chart ("Gross Revenue"). The "Format Data Point" pane will appear on the right. Check the box that says Set as total. The column will change color and ground itself to the horizontal axis.
2. Set the Ending Total: Now, do the same for the "Net Profit" column. Double-click that bar, and in the "Format Data Point" pane, check Set as total.
3. (Optional) Set Subtotals: If you have any intermediate totals, like "Operating Profit" in our example, you can set them as totals too. Just double-click the column and check the "Set as total" box.

Step 4: Customize Your Chart for Clarity

With the structure correct, you can add cosmetic touches to make your chart easier to read. Right-click on various chart elements to format them.

• Colors: To change the default blue and orange, click on any "Increase" or "Decrease" bar, open the Format pane, and use the fill options to choose more intuitive colors like green for increases and red for decreases. You can set a separate color for total bars.
• Data Labels: Right-click on any bar and select Add Data Labels to show the numeric value of each change.
• Connector Lines: These are the faint lines connecting each column. You can turn them on or off by clicking on a series and going to the "Format Data Series" pane.
• Chart Title: Click the "Chart Title" box and give your chart a descriptive name.

This manual method works well, but if you create these charts often, the formatting and setup can become repetitive.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: The Faster Way with ChatGPT and VBA

What if you could skip all the manual formatting and have a perfect, presentation-ready chart in seconds? By using ChatGPT to write a simple Excel VBA macro, you can do just that. You don't need to know how to code - you only need to know how to copy and paste.

Step 1: Your Data Stays the Same

The beauty of this method is that your data setup is identical. Just have your simple two-column table ready in an Excel sheet.

Step 2: Craft a Good Prompt for ChatGPT

The key to getting what you want from AI is a clear, specific prompt. You're going to ask ChatGPT to write an Excel VBA script. Don't worry if you don't know what that means - ChatGPT does.

Here's a great prompt template:

Write an Excel VBA macro that creates a waterfall chart from the data in cells A1 through B8 on the active sheet. The data has headers in the first row. In the chart, set the first data point (Gross Revenue) and the last data point (Net Profit) as totals. Also, set the "Operating Profit" data point as a subtotal. Please customize the chart with the following properties:

This prompt is effective because it clearly defines:

• The action: "Write an Excel VBA macro."
• The data source: "cells A1 through B8 on the active sheet."
• The structure: "headers in the first row" and which columns are "totals."
• The customizations: Specific title, colors, and labels.

ChatGPT will generate a block of VBA code in response.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Run the Code in Excel

Now, let's bring that code into Excel. This might look intimidating if you've never used macros before, but it's just a few simple steps.

1. Open your Excel workbook.
2. Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor. A new window will pop up.
3. In the menu bar of the VBA editor, click Insert > Module. A blank white code window will appear.
4. Copy the entire block of code that ChatGPT generated.
5. Paste it into the blank module window.
6. To run the code, simply click the play button (a green triangle) in the toolbar or press F5 on your keyboard.

Switch back to your Excel sheet, and you'll find a perfectly formatted waterfall chart, custom-built to your exact specifications.

Why Automate Excel Charts with ChatGPT?

At first glance, this might seem like more work than just clicking a few buttons. But the benefits are huge, especially if you handle data regularly.

• Speed and Efficiency: A single prompt and a copy-paste action replace dozens of clicks through menus for creation, configuration, and formatting.
• Deep Customization, Instantly: Want a specific RGB color code? Need the chart placed in a particular spot on the sheet? Just add it to the prompt. This level of customization is far faster than digging through Excel's format panes.
• Consistency and Reusability: Once you have a macro, you can save it and reuse it with a single click. This ensures all your reports have a consistent look and feel without repetitive manual work.
• No Learning Curve for Code: VBA is incredibly powerful, but has a steep learning curve. ChatGPT acts as your personal developer, writing the code for you. You get the power without the pain.

Final Thoughts

Understanding how revenue becomes profit is fundamental to business, a waterfall chart quickly visualizes all of the steps in between. Excel's built-in tools are great for one-off charts, but leveraging ChatGPT to automate the process can turn a tedious reporting task into a highly efficient one.

While using macros is a huge step up, the data in your spreadsheet is still static. This means you have to repeat the process every time your numbers update. At Graphed, we've built a solution to connect to your live data sources and make this kind of analysis conversational. Instead of writing prompts for a macro, you just ask questions like, "Show me a waterfall chart of our Shopify revenue vs. ad spend this month," and receive a real-time, shareable dashboard that updates automatically. Tools like Graphed are designed to eliminate manual data steps entirely, so you can focus on the insights, not the setup.