How to Make a Waterfall Chart in Excel with AI

A waterfall chart tells a clear story about how a starting value increases or decreases through a series of changes to reach a final value. It’s perfect for visualizing things like monthly revenue, project budgets, or inventory changes. This guide shows you two ways to create one in Excel: the classic, manual method and the newer, faster method using AI.

What is a Waterfall Chart and Why Use One?

Imagine you're trying to explain your company's monthly profit. You started with an opening balance, added revenue from sales, then subtracted costs for materials, marketing, and salaries to arrive at your end-of-month profit. A waterfall chart visualizes this exact journey, showing each positive and negative contribution floating between the start and end points.

Think of it as a visual financial statement. The bars hang in mid-air, showing how you got from point A to point B. It’s an incredibly intuitive way to spot the biggest drivers of change in your data.

Here are a few common use cases where a waterfall chart shines:

• Analyzing Profit and Loss: Start with net revenue, subtract different expense categories (COGS, SG&A, etc.), and end with net profit.

• Tracking Website Traffic: Show an initial user count, add traffic from various channels (Organic, Paid, Social), subtract bounces or drop-offs, and show the final engaged user count.

• Managing Project Budgets: Start with the initial budget, show expenses for each phase, and visualize the remaining funds.

• Sales Performance: Start with the sales quota, and show each deal closed as a positive contribution throughout the month.

Method 1: The Traditional Way to Create a Waterfall Chart in Excel

For years, creating a waterfall chart in Excel required some clever workarounds with stacked bar charts and invisible bars. Thankfully, Excel now has a built-in Waterfall chart type that automates a lot of the process. While much easier than the old-school way, it still requires a few manual steps to get it right.

Step 1: Set Up Your Data

The key to a successful waterfall chart is structuring your data correctly. You need at least two columns: one for a text label (Category) and one for the corresponding numeric value (Amount). Positive values represent increases (like revenue or gains), and negative values represent decreases (like costs or losses).

Your first and last rows should represent your total start and end values. For this example, let's track a company’s monthly profit:

Your data layout in Excel should look something like this:

Important Note: You need to manually calculate the final total. In this case, 20,000 + 50,000 + 15,000 - 25,000 - 7,500 - 12,500 = 40,000.

Step 2: Insert the Waterfall Chart

Once your data is ready, creating the chart is simple:

1. Highlight the entire data range, including the headers (e.g., A1:B8).

2. Go to the Insert tab on the Excel ribbon.

3. In the "Charts" group, click a dropdown that looks like a blue histogram, which is the "Insert Waterfall, Funnel, Stock, Surface, or Radar Chart" button.

4. Select Waterfall from the options.

Excel will instantly generate a chart. It will look close, but not quite right. You’ll notice the "Opening Cash" and "Closing Cash" bars are floating like the other values instead of being 'grounded' on the baseline.

Step 3: Format Your Start and End Totals

This is the most important - and most commonly missed - step. You need to tell Excel which bars are totals and which are changes.

1. Click once on the first bar (the "Opening Cash" bar) to select only that bar.

2. Right-click on the selected bar and choose "Set as Total" from the context menu. You’ll see the bar change color and drop down to the horizontal axis.

3. Repeat this process for the last bar ("Closing Cash"). Click it once to select it, right-click, and choose "Set as Total".

Now your chart correctly shows the flow from the starting total to the ending total. From here, you can customize things like the chart title, colors, and data labels to make it presentation-ready.

Method 2: The Faster, Smarter Way Using AI

The rise of AI tools, including Microsoft's own Copilot built into Office, is completely changing how we interact with spreadsheets. Instead of clicking through menus and manually formatting chart elements, you can now do a lot of the work by simply describing what you want in plain English. This eliminates the learning curve and speeds up the entire process.

The traditional method isn't terribly difficult, but it has friction. You have to remember where the chart type is located and how to set the totals. If you forget, you're off to Google to search for tutorials. AI removes that friction by letting you stay focused on your goal, not the process.

Creating a Waterfall Chart with AI: A Step-by-Step Guide

The AI approach is conversational. You start with clear data and then give the AI assistant a series of prompts to build and refine your chart.

Step 1: Get Your Data Ready

Just like the manual method, AI works best when it has clean, well-structured data. Start with the same table format we used before: a column for categories and a column for values. The advantage here is you don't need to be as strict with the calculations beforehand, often, the AI can figure them out if prompted correctly.

Step 2: Use a Natural Language Prompt

With an AI tool like Copilot for Excel open, you select your data and give it a simple instruction. Your prompts could look like this:

• "Create a waterfall chart from the data in A1:B8."

• "Build a waterfall chart for this data. Make sure to set the 'Opening Cash' and 'Closing Cash' rows as totals."

• "Visualize this data as a waterfall chart titled 'Monthly Cash Flow Analysis.' Set the first and last values as totals, color increases green and decreases red."

The AI will interpret your request and generate the chart, including setting the totals - all without you ever needing to right-click or hunt through formatting menus.

Step 3: Refine and Edit Instantly

This is where AI truly outshines the manual method. After the initial chart is created, you don’t need to fiddle with formatting panes to make adjustments. You just keep talking to the AI. This turns analysis into a quick, iterative conversation.

Here are some examples of follow-up prompts:

• "Change the chart title to 'Q1 Profit & Loss Breakdown'."

• "Remove the data labels from the negative bars."

• "Can you reformat 'Cost of Goods Sold' to be bright orange?"

• "Add connector lines between the bars to make the flow clearer."

Each command makes an immediate change, allowing you to ask follow-up questions and explore your data in a way that feels like a natural conversation. You can experiment and tweak your visuals in seconds instead of minutes.

Beyond Excel: When to Use a Dedicated Analytics Tool

Excel's built-in AI is a massive step forward for anyone working with spreadsheets. It democratizes data visualization and gets rid of a lot of tedious, manual work. But it still operates within the confines of a static Excel file.

The process, even with AI, usually starts with exporting a CSV file from another platform like Shopify, Google Analytics, or your CRM. You import that static data into Excel and then build your chart. By the next day, that data is already stale.

For marketing and sales teams who need up-to-the-minute insights, this manual export-and-analyze workflow becomes a bottleneck. The core problem remains: the chart is disconnected from the live data source. This is where dedicated, AI-powered analytics platforms offer a more powerful solution. Instead of bringing the data to the visualization tool (Excel), they connect the visualization tool directly to the data streams.

Final Thoughts

Waterfall charts are a powerful tool for explaining the story behind your numbers, showing the contributions that bridge a start and an end point. Whether you use Excel's manual built-in chart type or its new time-saving AI features, you can now create compelling financial and operational visuals in just a few clicks or prompts.

For us, cutting out the pain of manual reporting is why we built Graphed in the first place. Instead of spending hours exporting CSVs and fighting with spreadsheets to build something that's obsolete by tomorrow, you can connect your data sources - like Google Analytics, Shopify, and your ad platforms - directly. Then, just like with Excel's AI, you can use plain English to build real-time, interactive dashboards that are always up-to-date.