How to Make a Waterfall Chart in Google Sheets with ChatGPT

A waterfall chart can perfectly tell the financial story of a month, quarter, or project by showing how a starting value is affected by positive and negative changes. While Google Sheets doesn't offer a one-click waterfall chart, you can build one yourself with a clever workaround. This guide will walk you through setting up the data, creating the chart, and even show you how to use ChatGPT to do the heavy lifting for you.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Waterfall Chart?

A waterfall chart (sometimes called a bridge chart) is a data visualization that shows a running total as values are added and subtracted. It’s ideal for visualizing financial statements, tracking budget variance, or illustrating changes between two points in time.

Here’s what makes it so effective:

• Starting and End Points: The chart begins with a full column representing the initial value (like starting cash) and ends with a full column for the final value (ending cash).
• Floating Blocks: The bars in between "float," starting where the last one left off. Positive values (like revenue) are typically colored green and go up, while negative values (expenses) are red and go down.

This layout makes it incredibly easy to see exactly which factors had the biggest positive or negative impact on your bottom line. You can use them to answer questions like:

• "What did our monthly profit & loss statement actually look like?"
• "How did our initial sales forecast compare to the final numbers?"
• "What contributing factors led to the change in our website traffic from Q1 to Q2?"

The Trick to a Waterfall Chart in Google Sheets

Google Sheets is an incredible tool, but it lacks a dedicated, built-in waterfall chart type. The secret to creating one is to use a stacked column chart and arrange your data in a very specific way.

Essentially, you create an invisible "base" column that props up the visible positive and negative bars, making them appear to float. This data preparation is usually the most time-consuming part, but it's where we can bring in a tool like ChatGPT to speed things up immensely.

How to Manually Set Up Your Data

Before we ask ChatGPT for help, it’s useful to understand the data structure we need. Let's say you have a simple list of monthly financial transactions and you want to visualize them.

Your raw data might look like this:

To turn this into a waterfall chart, we need to create a new table with five columns:

1. Category: Your labels for each bar (e.g., Starting Cash, Revenue, Rent).
2. Base: An invisible pillar that props up the real data columns. We'll hide this later.
3. Increase: A column for all your positive numbers (income).
4. Decrease: A column for all your negative numbers (expenses).
5. Start/End: A column to hold the first and last total value bars.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Data Setup

Let’s use this example data for a small business's month:

• Opening Balance: $20,000
• Product Sales: +$15,000
• Ad Spend: -$3,000
• Salaries: -$7,000
• Software Fees: -$1,000

Step 1: Create Your Table Structure

In a new tab or next to your raw data, set up headers for your five new columns: Category, Base, Increase, Decrease, and Start/End. Then list your categories, adding an "Ending Balance" category at the bottom.

Step 2: Fill in the Start/End Column

In the "Start/End" column, put the opening balance ($20,000) in the first row. Then, in the last row for your Ending Balance, write a simple SUM formula to calculate the final total. For our example, the formula would be the sum of all transactions including the start value.

=SUM(B2:B6)

Assuming your initial raw values are in cells B2 through B6

Step 3: Fill in the Increase and Decrease Columns

This part is straightforward. You’ll use an IF formula to sort your positive and negative values from the raw data into the correct columns.

For the "Increase" column (let's say we're starting in C3), the formula would be:

=IF(B3>0, B3, 0)

This means: if the value in B3 is greater than zero, put it here. Otherwise, put 0.

For the "Decrease" column (starting in D3), the formula would be:

=IF(B3<0, ABS(B3), 0)

This means: if the value in B3 is less than zero, take its absolute value (to make it a positive number for the chart) and put it here. Otherwise, put 0. Drag these formulas down for all your transaction rows.

Step 4: Calculate the "Base" Column (The Tricky Part)

The "Base" column holds the values that make your chart work. It calculates the running total cumulatively, but does so in a way that provides a starting point for each subsequent bar. This is the hardest part to get right manually.

Here's the logic:

• The first row (Starting Cash) should have a base of 0. It starts from the bottom.
• For the next row, the formula will be Previous Row's Base + Previous Row's Increase + Current Row's Decrease.

Here's a sample formula to put into the cell for your first transaction's Base value (e.g., for "Product Sales") and then dragging it down:

=F2+G2-H3

...where F2 is the prior Base value, G2 is the prior Increase, and H3 is the current Decrease.

Feeling confused? This is exactly why using ChatGPT is such a game-changer.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Use ChatGPT to Generate the Formulas Instantly

Instead of wrestling with those complex, relative cell formulas, you can simply ask ChatGPT to generate them for you. This turns a frustrating 15-minute task into a 30-second copy-paste job.

Step 1: Organize Your Initial Data

Make sure you have your simple, raw data in two columns. For example, Column A (Category Name) and Column B (Value). Note your starting balance separately. It could be the first item in your list, or in a separate cell.

Step 2: Write a Clear Prompt for ChatGPT

The key to getting good output from any AI is a clear, detailed prompt. You need to tell it what your data looks like, what output you need, and what the logic should be.

Here’s a great prompt template you can use. Just copy it and replace the details with your own.

I want to create a waterfall chart in Google Sheets. My raw data starts in cell A2 ("Category") and B2 ("Amount"). The initial starting balance is in B2. Please provide the dynamic formulas for a new table that has these columns: "Base", "Increase", "Decrease", and "Start/End Value". This new table will start in Column D. The formulas should reference the raw data in Columns A and B and automatically populate the entire table. The logic is:

Step 3: Copy and Paste the Formulas from ChatGPT

ChatGPT will process your request and spit out the exact formulas you need to paste into your Google Sheet. It will understand the complex logic required for the "Base" column without you having to puzzle it out.

Simply copy the formula for each column header it provides and paste it into the corresponding cell in your sheet (e.g., paste the "Base" formula into the first cell of your base column). Use the fill handle (the little blue square in the corner of the cell) to drag the formulas down for all your data rows. Your data table for the waterfall chart should now be populated automatically!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Creating and Customizing the Waterfall Chart

With your data perfectly structured, creating the chart is the easy part.

Step 1: Select Your Data Range

Highlight the entire newly created table, from the "Category" header to the last cell of the "Start/End" column. Do not include your original raw data columns.

Step 2: Insert Chart

Go to the menu and click Insert > Chart. Google Sheets will likely default to a different chart type, so you'll need to change it.

Step 3: Choose 'Stacked Column Chart'

In the Chart editor pane on the right, under the 'Setup' tab, click the 'Chart type' dropdown and find 'Stacked column chart'. Select it.

Step 4: Customize the Series to Create the Waterfall Effect

This is the final and most important step to get the 'floating' effect. Go to the 'Customize' tab in your Chart Editor.

• Make the Base Transparent: Expand the 'Series' section. Click the dropdown that applies formatting 'To all series' and select 'Base'. Change its color to None. This makes the supporting bars disappear, leaving the others floating.
• Color Your Bars: Still in the 'Series' section, select your 'Increase' series and change its color to green. Then select the 'Decrease' series and change its color to red. Finally, give your 'Start/End' series a neutral color like blue or gray.
• Add Connector Lines (Optional): For some data, it helps to show the flow. Still under the "Series" dropdown on the 'Customize' tab, select the "Total" series, and tick the "Data Labels" checkbox. Then choose a 'Data labels' series and set a gray line color for the 'Connector lines'. Note: Functionality may vary in different Google Sheets versions.

At this point, you should have a beautiful, easy-to-read waterfall chart. Add a descriptive title, and you're ready to share your visualization.

Final Thoughts

Creating a waterfall chart in Google Sheets is a fantastic skill for anyone presenting financial or operational data. Knowing how to structure your data for a stacked column chart is the key, and using ChatGPT to produce the complex formulas can save you a huge amount of time and potential errors.

Of course, manually prepping data for reports, even with AI assistance, is just one step in the analytics process. We designed Graphed to automate this work completely. Instead of building tables and wrestling with chart settings, we let you connect your data sources directly and simply ask for what you need - like, "Build a waterfall chart showing last month's P&L statement from our QuickBooks data" - and get a real-time, interactive dashboard in seconds.