Waterfall charts are not supported by Excel, thus making its construction time consuming and inefficient. The customized macro allows to create professional looking charts within seconds. Oct 19, 2016 The waterfall chart is a really nice addition to the family of charts offered in Excel 2016. As we have seen here, it provides a cascading visualization of data that includes increases and decreases while allowing us to see the cumulative effects on the running total in a very intuitive fashion. Customize Your Waterfall Chart. Your Excel waterfall chart’s a little drab, and you want to spice it up a bit. Maybe add some color, a few more details for context, and give it a title. Making Your Chart Stand Out. In our example above, we want to differentiate between our Up and Down columns. Excel Chart Template – 33+ Free Excel Documents Download Free. Chart Template – 41+ Free Printable Word, Excel, PDF, PPT, Google. Waterfall Chart Excel - 6+ Free Excel Documents Download Free. Graph Chart; Chore List Template – 10+ Free Sample, Example, Format. 41+ Simple Chart. Waterfall Chart Excel Template. Download this Excel Waterfall Chart template and type in your own labels and data. Try to backtrack to see how it’s setup. Let’s have a look at the techniques used to create the Waterfall chart and then let’s lay out the type of series and calculations necessary to create our chart.
How to create waterfall chart in Excel?
The waterfall chart is usually made for clearly viewing and understanding how a start value is affected by a series of intermediate positive or negative values and get the finally value, which is very useful. It can be used for the cash flow, the inventory audit and so on. Now, I talk about the steps on creating a waterfall chart in Excel.
Create and make a waterfall chart in Excel
For instance, here we make a waterfall chart about the cash flow during a year.
1. To create a waterfall chart, firstly, you need to arrange and type your data as below screenshot:
Tip: Base 1000 is a calculated amount for a series, you can change it as you need.
2. In the following cells, type these formula:
A2: =' '
F3: =G3, press Enter;
B4: =SUM(B3,E3:F3,-D4), press Enter, then drag the fill handle to fill the range B4:B15 (you can change the range as you need).
D4: =-MIN(G4,0), press Enter, then drag the fill handle to fill the range D4:D15 (you can change the range as you need).
E4: =MAX(G4,0), press Enter, then drag the fill handle to fill the range B4:B15 (you can change the range as you need).
Then you can see the data has been set as below:
3. Select the range A1: F17 (except the Flow column in my case), and click Insert > Column Chart > Stacked Column. See screenshot:
4. Click the Base column (the dark blue bricks) in the chart, and click Format > Shape Fill > No Fill. See screenshot:
5. Select the Start column in the chart, and click Format > Shape Fill, and select the same color as the color of the End column in the chart. See screenshot:
6. Select a column and click Format > Format Selection to open the Format Data Series dialog. See screenshot:
7. In the Format Data Series dialog, adjust the Gap Width axle to meet your need and click Close to close the dialog. See screenshot:
Tip: If you are using Microsoft Excel 2013, a Format Data Series pane will come out at right side after clicking the Format > Format Selection. You can adjust the gap width in this pane. See screenshot:
Then you can see the waterfall chart has been made as below:
Tip.If you want to quickly export images or shapes from workbook to a folder, please try to use the Kutools for Excel’s Export Graphics as shown in the following screenshot. It’s full function without limitation in 30 days, please download and have a free trial now.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.This is the best teching of waterfall chart I ever come across... thanks a lot and keep up the good work.
We’ve got everything you need to understand the basics of a waterfall chart—including why you might need it, when to use it, and how to create your own. And if you don’t have time to create your own, we’ve created a template for you!
Let’s start with the basics.
What is a Waterfall Chart?
You might have heard it referred to as a “waterfall graph” or maybe a “bridge graph,” but it’s all the same. The point of the waterfall chart is to show both positive (yes!) and negative (no!) values over a period of time, while pointing out the initial and end values as well.
Whether it’s company profit, inventory, or overall sales, the waterfall chart is a useful little tool to get a quick overview and make sense of all the numbers and how things are going.
Why Would I Need a Waterfall Chart?
Why wouldn’t you? Everybody needs a waterfall chart in their life, right?
Okay, that might be a stretch. But if you found this page, we’re guessing you probably are in hot pursuit of a waterfall chart that will make your life much easier.
Here are a few examples of when using a waterfall chart makes sense:
- Analyzing sales and inventory over time
- Evaluating profit
- Comparing product earnings
- Illustrating employee growth
- Showing budget changes over time
- Highlighting product value over time
So… How Do I Create a Waterfall Chart in Excel?
We’re glad you asked. And, actually, we’re here to help.
We’ve already created a free, downloadable Excel waterfall chart template for you. Unless you want to spend 48 hours (slight exaggeration) typing numbers into a spreadsheet, then we recommend you download this beautiful little template and blow the socks off your friend and co-workers. We’ll even let you take credit for all the work!
If you’re dead set on making the waterfall chart on your own, just because you’re nerdy like that, then we get it. We’re ready to help with that too.
Whether you have a PC or a Mac, these instructions will work for you.
Step 1: Grab Your Data Set in a Table Format
For our example, we started with something simple, monthly income. You’ll see varying numbers based on positive or negative income for each month.
- We already have one column—sales number. Now add three additional columns to our Excel waterfall chart (pictured below). We’ll name them “base,” “up,” and “down.” The base column shows the starting point for the up and down income flow throughout the chart.
- Type your negative income numbers into the “down” column and the positive income into the “up” column.
- Be sure to add a “start” and “end” row so you’ll see total numbers for the entire year.
Let’s move on.
Step 2: Add Formulas
Inputting formulas into Excel might look like Greek, but it’s not that hard. Add the formulas to the first cells in each column, then use the fill tool to copy them down throughout the column.
- For the Down column, select C7 and enter formula: =IF(E7
Drag the fill tool to the end of the column to copy the formula. Your chart should now look like this:
- Next, select D4 in the Up column and enter this: =IF(E4>0, E4,0)
Use the fill tool to drag the formula down to the end of the column again. Now your Excel waterfall chart should look like this. If so, well done.
- Select B4 in the chart and enter this formula: =B3+D3-C4.
Use the fill tool to drag the formula down to the bottom of the column again. And (boom!) your waterfall chart should now look like this.
Step 3: Make Your Data Table a Column Chart
Sounds fancy, yeah? It’s not.
But you’re going to need it.
- Highlight all the data you want to include in your chart. Be sure to include your headers and leave out the Income Flow column.
- Find the insert tab in your menu.
- Click “chart” then select “stacked chart” from the list.
Now you’ve got yourself a stacked chart.
Look at that! It’s got your numbers and everything. However, it’s not a waterfall chart. Not yet. Let’s make that happen, shall we?
Step 4: Turn Your Stacked Chart Into a Waterfall Chart
Everyone has a little room for growth, including our dear stacked chart. We’re ready to grow our stacked chart into a wonderful, well-respected waterfall chart.
- Click on the Base series, then right click and select “Format Data Series” from the dropdown.
When the “Format Data Series” pops up:
- Click on Fill in the left menu, then “No Fill” in the color dropdown box.
- Do the same thing with the “Line” section.
With the base section now excommunicated from our Excel waterfall chart, we can take it out of the legend.
- Right-click on “Base” in the legend, then select “Delete” from the dropdown.
You’ve now created a basic waterfall chart. But if you’re like us, you don’t go for basic. We’ll talk about how to make your chart pretty.
Customize Your Waterfall Chart
Your Excel waterfall chart’s a little drab, and you want to spice it up a bit. Maybe add some color, a few more details for context, and give it a title.
Making Your Chart Stand Out
In our example above, we want to differentiate between our Up and Down columns.
- Select the Down series, right click, then select “Format Data Series.”
- Select Fill, then pick the color you want. We went with blue to make it stand out more.
- Do the same with the Line option in the left menu.
- Repeat the process with your Up column. We chose green.
- Make sure to individually color code your start and end columns. Pick a separate color to make them stand out.
Here’s how your waterfall chart should look now.
Getting Rid of White Space
You can also get rid of that extra white space between your columns. This will make the chart “pop” a little more.
- Double click on a column, select “Format Data Series,” and click on “Options” in the left menu.
- Change the “Series Overlap” to 100% and the “Gap Width” to 15%.
Now you’ve got a pretty waterfall chart that should look something like this.
Adding Titles and Labels
To add a title to your chart:
- Click on your chart and look for “chart options” in the formatting palette.
- Click on the chart title box to name your chart.
If you want to add a data label to show specific numbers for each column, you can do that. Right click on one of your columns and select “Add Data Labels” from the dropdown. Your numbers should now magically appear.
To format those labels and make them a little prettier, select one, then right click and pick “Format Data Labels” from the dropdown list.
From within that same pane, you can change the position of the label and play around with the font color and size to make the numbers easier to see. After that, you can delete zero values and, optionally, the legend from your waterfall chart.
How to Analyze a Waterfall Report
One of the great things about the waterfall report is its ability to show a starting number, the positive or negative changes, and the ending number—all in one chart.
In our example, what jumps out at you?
Our negative income months were April and May, then July and August. What is it about those late spring and late summer months that caused a dip?
Are sales down because of spring and summer vacations? Is this a normal, expected trend or something that’s new this year? If this is an expected dip, has the company made the necessary budget accommodations? And if it looks like it’s just a one-off, how can you make sure it doesn’t become a trend next year?
It also never hurts to walk any relevant team members through your chart. Seeing the data in color might make more of an impact than just sending out an email. The chart could give your sales people the kick in the pants they need to make next year better than ever!
Excel For Mac Waterfall Chart Download Excel
Download Our Template
Remember, TeamGantt is here to help. We’ve created an easy-to-use waterfall template for you. Just plug and play with your own numbers and you’ll be ready to go.
Waterfall Chart Tableau
You can easily plan and communicate within your project budget by creating your own plan with TeamGantt. Get started!
- Project management template library: Use these simple templates in any industry to plan and manage projects, identify and mitigate risk, and communicate effectively at every project step.
- Gantt chart Excel template: Save time organizing your project plan with our premade Excel gantt chart template! Simply plug in your tasks and dates, and you'll have a presentation-quality Excel gantt chart.