How to Create a Waterfall Chart in Excel: A Practical Guide

Creating a waterfall chart in Excel is a straightforward process once you understand the logic. First, you need to structure your data to show a starting value, followed by a series of positive and negative changes. From there, you can use Excel's built-in Waterfall chart feature. The most crucial step is to set your starting and ending values as "totals" within the chart's formatting options. This simple action anchors the chart to the baseline and ensures the cumulative story your data tells is clear and accurate.

Why Waterfall Charts Tell a Better Data Story

A person points to a data chart on a whiteboard during a meeting, with another person watching.

A waterfall chart does more than just display numbers; it walks you through the story of how a starting value changes over time. Imagine you're explaining your company's monthly cash flow. Instead of just presenting the final balance, a waterfall chart shows the entire journey—the revenue gains and expense deductions that occurred along the way.

This visualization transforms a dry spreadsheet of positive and negative values into a clear, compelling narrative. It highlights the sequence of events and the impact of each one, making complex financial data easy for any stakeholder to grasp.

The Power of Visual Storytelling

The waterfall chart gained significant traction after being popularized by the strategic consulting firm McKinsey & Company as a powerful tool for explaining financial changes step-by-step. Its genius lies in showing cumulative effects intuitively. Today, it’s a staple in finance and business for illustrating changes in revenue, profit, or costs. To learn more about the history of these charts, visit FusionCharts.

At its core, a waterfall chart answers the question "why." It doesn't just show you the final profit; it shows you precisely which revenues pushed it up and which costs pulled it down, providing a clear visual explanation for the final result.

When to Use a Waterfall Chart Over Other Charts

Choosing the right chart is critical for effective communication. A bar chart is excellent for comparing distinct categories, while a line chart is ideal for showing trends over time. The waterfall chart, however, excels in a specific scenario: when you need to explain the sequential changes that contribute to a final result. It's the perfect tool for visualizing a story with a beginning, a middle, and an end.

This table helps you decide if a waterfall chart is the right tool by comparing its ideal use cases against other common chart types in Excel.

Chart Type	Best for Visualizing	Common Excel Use Case
Waterfall Chart	The cumulative effect of sequential positive and negative values.	Explaining monthly profit changes (revenue, costs, etc.).
Bar Chart	Comparing distinct categories against each other.	Comparing sales figures across different regions.
Line Chart	Showing trends and changes over a continuous period.	Tracking stock prices or website traffic over a year.
Pie Chart	Illustrating the proportion of parts to a whole.	Showing market share distribution among competitors.

Ultimately, by choosing a waterfall chart, you’re doing more than just presenting data. You're building a narrative that guides your audience through the story behind the numbers, a crucial skill for anyone communicating with data in a business context.

Using Excel’s Built-In Waterfall Chart

If you're using Excel 2016 or a newer version (including Microsoft 365), you have access to a native waterfall chart feature that simplifies the process immensely. The key to success isn't the chart creation itself, but rather preparing your data correctly beforehand.

Get the data structure right, and the rest is easy. All you need is a simple, two-column table. The first column holds your labels or categories (e.g., "Starting Revenue," "New Sales," "Operating Costs"), and the second contains the corresponding numerical values. Positive numbers represent gains, and negative numbers represent losses.

Getting Your Data Ready

Before clicking the "Insert" tab, ensure your data is arranged in a logical sequence. Your table must tell a story. It should begin with a starting value, list all the subsequent changes (both positive and negative), and conclude with a final value.

Here’s a practical example of a data table for tracking monthly cash flow:

Starting Cash: 50,000
Product Sales: +30,000
Service Revenue: +15,000
Operating Costs: -12,000
Marketing Spend: -8,000
Final Cash: 75,000

This clean, sequential layout gives Excel a perfect roadmap to follow, ensuring the chart accurately visualizes how you got from your starting cash to the final balance.

A common mistake is forgetting to define the start and end points as "totals." These bars act as the anchors for your entire visual story. If you miss this step, your chart won't be grounded on the baseline and will be difficult to interpret correctly.

Creating and Fine-Tuning the Chart in Excel

Once your data is prepared, creating the chart takes just a few clicks.

First, select your entire data table, including the headers. Navigate to the Insert tab on the Excel ribbon. In the "Charts" group, click the icon for Insert Waterfall, Funnel, Stock, Surface, or Radar Chart. A dropdown menu will appear—simply select Waterfall.

Excel will instantly generate the chart. However, you must complete one last critical step: telling Excel which bars are totals.

Double-click on the first bar ("Starting Cash" in our example) to open the "Format Data Point" pane. In this pane, check the box that says Set as total. Now, repeat the process for the last bar, "Final Cash."

You will see these two bars immediately drop to the baseline, correctly bookending your chart. Now you can customize colors, edit the title, and add data labels to prepare it for your report or presentation. This built-in Excel tool makes creating a once-complex chart a task you can complete in under a minute.

If you want to explore more ways to accelerate your charting workflow, check out our guide on how to create charts in Excel with AI.

Building a Waterfall Chart in Older Excel Versions

If you're working with an Excel version prior to 2016, you won't find a built-in waterfall chart option. However, you can still create one using a clever workaround with a stacked bar chart.

This manual method requires more data preparation but offers complete control over the chart's appearance. The technique involves creating a stacked bar chart where the bottom segment of each bar is made invisible. This "invisible base" pushes the visible data—your increases and decreases—to the correct starting point, creating the classic "floating bar" effect of a waterfall chart.

Preparing Your Data with Helper Columns

The real work for this method happens in your worksheet, not the chart wizard. You'll need to add a few "helper" columns next to your original data. Instead of a single column for your values, you'll create four new ones to structure the data for the stacked bar chart.

Let's use a project budget as an example. Your initial data might have two columns: "Category" and "Amount." To prepare it for our waterfall chart, we will add these four helper columns:

Base: This column holds the running total before the change in the current row is applied. This will become the invisible portion of our stacked bars.
Rise (Positive): This column contains only the positive values (increases). If a value is negative, this cell will be zero.
Fall (Negative): This column contains the absolute value of any decreases. If a value is positive, this cell will be zero.
End: This column is reserved for your start and end totals, which will be displayed as solid bars extending from the baseline.

This process relies on segmenting your data and tracking the cumulative value. The key is to label everything clearly to avoid confusion. For more insights on this topic, there are excellent discussions around effective data storytelling that delve deeper.

The Formulas You Need in Excel

Let's get practical with our budget example. Assume your categories are in column A and amounts are in column B, with data starting in row 2. Here’s how to set up the formulas for your helper columns.

Base Column (C): In cell C3, enter the formula =C2+D2-E2. This calculates the new base by taking the previous base, adding any rise, and subtracting any fall. Drag this formula down the column.
Rise Column (D): Use the formula =IF(B2>0, B2, 0). This checks if the amount in B2 is positive. If it is, the formula populates the cell with that value; otherwise, it enters 0.
Fall Column (E): Use the formula =-IF(B2<0, B2, 0). This formula checks for negative values and converts them to positive numbers for charting purposes.

The entire workflow—from raw data to a finished visual—is designed to transform numbers into a clear story.

A three-step diagram showing a data document, a bar chart, and a 'Total' trophy representing a data analysis process.

As the diagram illustrates, the goal of this exercise is to turn raw data into a chart that clearly shows how you arrived at the final total.

Assembling the Stacked Bar Chart

Once your helper columns are set up, it's time to build the chart.

Select your category labels along with the "Base," "Rise," and "Fall" columns. Navigate to Insert > Chart and choose the Stacked Bar Chart option.

The initial chart won't look like a waterfall chart yet—that's expected. You'll see a series of bars at the bottom representing your "Base" values. To create the waterfall effect, you need to make these invisible. Right-click on any of the "Base" bars, select Format Data Series, and change the Fill to No Fill.

And there you have it! The base bars will disappear, leaving your "Rise" and "Fall" bars floating perfectly to illustrate your data's story. You can now customize the colors—traditionally green for increases and red for decreases—to finalize your chart.

Letting AI Do the Heavy Lifting for Your Charts in Excel

Building a waterfall chart from scratch, especially the manual stacked-bar method for older Excel versions, can be time-consuming and prone to error. It requires careful data preparation and multiple steps.

Now, imagine creating that same professional-looking waterfall chart without writing formulas or spending time on manual formatting. This is where an AI assistant for Excel, such as Elyx.AI, can fundamentally change your workflow. By leveraging AI within Excel, you can automate complex tasks and focus on interpreting the data rather than manipulating it.

AI understands requests in simple, everyday language. You no longer need to memorize formulas for helper columns or recall the precise sequence of clicks. Instead, you can focus on the insights your data provides.

Automate Data Preparation with a Simple AI Request

The most tedious part of creating a manual waterfall chart is the data setup. Structuring your data with "base," "rise," and "fall" columns is meticulous work. An AI tool can eliminate this entire process.

You can provide your raw sales and expense data and ask the AI to 'Prepare this data and create a waterfall chart to show the change in profit from start to finish.'

With a single command, the AI can:

Automatically calculate the running totals needed for the "base" of each bar.
Generate all the necessary helper columns for the stacked-bar method.
Create the final, formatted chart with appropriate labels.

This is more than just a time-saver; it eliminates the risk of a formula error derailing your entire chart. By automating these repetitive tasks, you make powerful data visualization accessible to everyone on your team, regardless of their Excel expertise.

When an AI tool handles the technical setup, you can proceed directly to the most valuable step: analyzing the story behind the numbers instead of just building the graphic.

Go Beyond Charting with AI-Powered Analysis

The benefits of AI in Excel extend beyond chart creation. Once the waterfall chart is built, you can use AI to dig deeper into the data.

Try asking follow-up questions like, "What was the single biggest contributor to our profit decrease?" or "Summarize the key drivers of change shown in this chart."

This transforms a static visual into an interactive analytical tool. You can have a real conversation with your data, making the analysis process more dynamic and insightful. As AI becomes more integrated into spreadsheet software, these workflows are becoming the new standard. To learn more about this trend, you can explore the evolving role of AI in Excel.

AI's utility in data analysis is broad. For a wider look at how these tools accelerate data-related work, check out this list of the best AI tools for academic research; many of the principles are directly applicable to business analysis. Using AI for the mechanics lets you produce better visuals in less time, freeing you up to focus on what truly matters: making smart, data-driven decisions.

Designing Your Chart for Maximum Impact

A close-up of a desk with a laptop, a pen, and a document displaying colorful bar charts.

Preparing the data correctly for your waterfall chart is only half the battle. To ensure it effectively communicates its message—whether to drive a decision or clarify a complex situation—it must be clear, persuasive, and easy to understand at a glance. Your design choices are what elevate a simple graphic into a powerful storytelling tool.

The strength of a waterfall chart lies in its natural narrative flow. The viewer's eye automatically travels from the starting point, follows the positive and negative changes, and lands on the final result. The largest bars, whether gains or losses, represent the most significant events in the story. The ability to spot these instantly is what makes the analysis so effective.

This clarity is why waterfall charts are so prevalent in business. According to some findings on data visualization effectiveness, teams that use such charts can analyze financial performance more quickly compared to reviewing raw data tables.

Customizing Your Chart for Clarity

Consider Excel's default formatting a starting point, not the final product. To create a professional and impactful chart, you need to make deliberate design choices that guide your audience's attention. A few minor adjustments can make a significant difference.

Here are some actionable tips for refining your waterfall chart in Excel:

Use Consistent Colors: While the default green for increases and red for decreases is intuitive, consider using your company's brand colors for a more polished and professional appearance, especially for external presentations.
Adjust the Gap Width: Right-click a bar and select "Format Data Series." Reduce the gap width between columns to approximately 50-75%. This makes the chart look more substantial and visually connects the data points.
Refine Connector Lines: The faint lines connecting the bars are essential for showing the flow. Ensure they are a subtle, neutral color like light gray. They should guide the eye without being distracting.

Your primary goal is to eliminate any visual element that does not contribute to the story. Every color, line, and label should enhance understanding, not create clutter.

Telling a Story with Titles and Annotations

Your chart needs a narrative to complement the visuals. The title should be a headline, not just a label. Instead of a generic title like "Q3 Financials," try something more descriptive that summarizes the key insight, such as "Q3 Profit Grew by $50K, Driven by Strong Product Sales."

Annotations are another powerful tool for adding context. If there's a significant drop in revenue, don't leave your audience wondering why. Add a small text box next to the corresponding bar to provide a brief explanation, such as: "Caused by unexpected supply chain disruption." This immediate context is invaluable for clear communication.

Once your chart is polished and tells a clear story, it's ready to be shared. If you need to incorporate your work into a presentation, our guide on how to link Excel charts to PowerPoint presentations will show you how to do it seamlessly.

Answering Your Top Waterfall Chart Questions

Even after you've created a few waterfall charts in Excel, you may encounter specific challenges. Mastering these details will help you produce polished, professional charts that accurately tell your data's story every time. Here are answers to some of the most common questions.

How Do I Add Subtotals to My Waterfall Chart?

Yes, and you should! Subtotals are excellent for adding important checkpoints to your chart, such as showing gross profit before subtracting operating expenses.

The process is simple. After creating your chart, identify the bar that represents your subtotal. Right-click on that specific bar and select "Set as Total" from the context menu. Excel will immediately anchor that bar to the baseline, clearly marking it as a cumulative point before the next series of changes begins.

Can I Change the Bar Colors?

Absolutely. While the default green and red are standard, customizing colors can align the chart with your brand or specific reporting needs. To change them, click once on any bar to select the entire data series. Then, click a second time on one of the "Increase" bars. This will select only the "Increase" bars.

From there, use the "Format Data Point" pane to choose a new fill color. You can repeat this process for the "Decrease" and "Total" bars, giving you full control over the chart's appearance.

If you remember only one tip, let it be this: Always set your start and end values as totals. Forgetting this step is the most common error and causes the entire chart to "float" above the baseline, which breaks the visual narrative and makes the chart difficult to interpret. Double-check this setting every time.

What Are Those Lines Between the Columns?

Those faint lines connecting the top of one bar to the next are called connector lines. Their purpose is to guide the viewer's eye along the data's path, showing the flow from one value to the next and reinforcing the chart's story.

If you don't like their appearance, you can customize them. Right-click on any connector line and choose "Format Connector Lines." This will open a panel where you can adjust their color, thickness, and style (e.g., solid or dashed). This gives you full control to make them a more subtle or a more prominent element of your chart's design.

Ready to stop wrestling with formulas and let AI do the heavy lifting in Excel? With Elyx.AI, you can generate complex charts, clean your data, and get instant insights with simple text commands. Transform your Excel workflow today.