Waterfall Chart in Excel - Easiest method to build. (2022)

Technique #1: Line Series For the Deltas With Up/Down Bars

The delta bars are in fact created by drawing two line series and then activating a chart feature called Up/Down Bars. What Up/Down Bars do is they connect the first point/position on the first line series with the first point on the second line series. You need to have two line series to be able to activate these.

What are Up/Down Bars actually used for? For graphs that show candlestick formations – i.e. they have a low end / high. They are commonly used to visualize price movements for trading.

Our Waterfall has nothing to do with trading but we’re going to use Up/Down bars as our deltas. To get them to do what we want, we need to plot a point before the change occurred and a point after the change occurred. We’ll do that for every single of the delta categories. This means we have a chart that has two lines, “before the change” and “after the change”.

Before we get to our data table let’s consider this: For every series we’d like to add to the Waterfall, we need a separate column in our data table. This means:

  1. Before series is separate to After
  2. Start & End series can be together in one column for which we can use a standard column chart.
  3. Cumulative series can be added to check the cumulative value at each stage in time. It’s good to know and it can simplify some of our other formulas. Plus we’ll need it later for our connectors.

Let’s calculate:

Cumulative series is =SUM($B$8:B8) → written in C8 and pulled down till C13.

Start value (D8) =B8

End value (D14) =C13

Before series =C8 → written in E9 and pulled down to E13 (covers all the deltas)

(Video) Easiest Excel Waterfall Chart (Bridge graph) from Scratch - Works with minus values

After series = C9 → written in F9 and pulled down to F13 (covering all the deltas)

Now that we’ve set up the data, let’s start with our Waterfall.

Select A7 to A14, hold down the control key and highlight D7 to F14. Notice you’re including the headers and the empty cells below the deltas. The headers help Excel identify that you’re adding new series and by highlighting the empty cells below the deltas you remain consistent with your categories. As smart as Excel is, you need to be consistent with your ranges, otherwise it will assign the wrong value to your Deltas.

At this point, your Waterfall looks like this – no worries, we’re just a few clicks away.

Waterfall Chart in Excel - Easiest method to build. (2)

Select either the Before or After series, right-mouse click and Change series chart type….

Stay in this view and change the chart type of Before and After series to a Line.

Now click on either of the lines, go to the Plus button on the top right hand corner of your Waterfall chart and place a check-mark for Up/Down Bars.

You get two new chart elements added. One is called Up-Bars and the other Down-Bars. You can see them by clicking on the drop-down box to view all chart elements. In our Waterfall, the Up-Bars show positive change, whereas the Down-Bars show negative change. We can color them the way we want, so green for Up and in my case, orange for Down. They come with borders, but don’t worry about them at this stage.

To remove the lines, click on the Before series and select No line from the Fill Options. Do the same for the After series.

How do we get the Delta Bars Closer?

(Video) Build a Waterfall Chart in Excel in less than 5 Minutes

It’s easy to solve this for the Start & End series. Select this series from the chart element selection and reduce Gap Width to a number of your liking. I’ve used 100%. Now when you try to do this for the Up/Down bars, you will realize there is no Gap Width option. That’s because the option is sitting with the original series which was the line series. From the series options, select either the Before or After series and reduce the Gap Width to a 100%.

Now it’s starting to look like a Waterfall Chart.

Technique #2: Customizing Data Labels

What we’d like to have is to get the amount of the deltas to sit on top of the bars. This means we’d like to have a value of 500 sitting on top of the Start bar, then 100 on top of the green bar and -200 on top of the red bar. How can we achieve that?

The moment there is a discrepancy between the “position” of the data label and the “value” that label shows, we need to use a special technique. But first of all, let’s check if there are any discrepancies.

Start – Position: 500, Value: 500 →Match

Delta 1 – Position: 600, Value: 100 → No Match

Delta 2 – Position: 600, Value: -200 → No Match

….. No Matches until End

End – Position: 380, Value: 380 → Match

We have a match for Start & End series. This means we can activate the data labels for this series without problems.

What to do with the Deltas?

We need a series that plants points in the right positions, i.e. a series that has 600 for Delta 1, 600 for Delta 2, 460 for Delta 3 & Delta 4, and 380 for Delta 5.

That brings us back to our data table. There are of course different ways of writing this. We can use an IF() function and check if our Delta is positive, in which case we take the After value, otherwise the Before value. OR we take a shortcut and get the MAX() of Before and After.

(Video) How to create a waterfall chart in Excel

Formula for cell G9 would be = MAX(E9:F9) and pull this down to G13.No we have the correct position. Let s add this to our Waterfall Chart as a scatter plot. This means you need to:

  1. Right-mouse-click on your graph and Select Data.
  2. Click Add – pick cell G7 as your series label (always make sure your series have labels so you can easily identify them in the series options) and range G8:G14 as your Y range. Remember: Be consistent. Just because you have numbers for the Deltas, doesn’t mean you ONLY select the numbers. You need to be in sync with your categories, otherwise you place the Delta 1 number on the Start category.
  3. At this point it might look like you’ve ruined your Waterfall. Excel has added another line chart and is using that for the Up/Down bars. Don’t panic. Just right mouse click on any series and go to the Change Series Chart Type…
  4. From the Change Series Chart Type… options, find the Data Label Position Series and change it to a Scatter Plot.

Now things look better again

Click on the Data Label Position Series or select it from the Series Options. Activate data labels and position these on top. Now hide the markers. You can do this by selecting No Marker from the fill options or by selecting No Fill and No Outline from the formatting options (I usually have these two in my Quick Access Toolbar).

Are these the labels we want? No! They are the position of the series and not the value of the deltas.

We have two options now to replace the labels with our delta values

Option 1: Click on each data label and type a formula in the formula bar that references the value for the delta. So for Delta 1 data label, you click on the label, then go to the formula bar and type in =B9. Excel does the fixing for you.

Option 2: Select the data labels and from the data label options put a check-mark for Value From Cells. You get a pop-up at this stage and you can highlight B8 to B14. Remember be consistent. Even though we don’t need the value for Start and End, we need to respect the category order.

Which Option is better?

That depends on which version of Excel you have and which version of Excel your colleagues who might work will your file have. The formula referencing method is obviously more time-consuming but it’s compatible with older versions of Excel e.g. from Excel 2007 and Option 2, i.e. the Value From Cells method is compatible from Excel 2013.

Technique #3 – Error Bars as Connectors

I’m pretty sure economists didn’t think of error bars as cosmetic enhancements to charts, but it does a great job for our Waterfall.

(Video) How to Easily Create a Waterfall Chart in Excel

A scatter plot can have both X and Y error bars associated with each point – i.e. a range of possible error which is shown as capped lines in Excel. If we manage to plant a point at the “end “ of each of the bars and activate the right-hand side of the error bars only, we’ll get our connectors.

To “add” error bars to our chart, we first need to add a series for which we can activate the error bars for. This series should always sit at the “end” of the bars, which means we can use the cumulative series for this purpose. Follow these steps:

  1. Right-mouse-click on the chart and Select Data.
  2. Add a new series. Pick C6 as the label “for connectors” and select C8 to C14 as the Y values.
  3. The chart type is already a scatter plot. Why? Because that’s the series we had last. Remember – for the data labels.
  4. Now we just need to activate the error bars. Select the newly added series, click on the plus sign on the top right-hand corner of the Waterfall chart and put a check-mark for Error Bars.
  5. We don’t need the Y error bars. Click on one of them to select or select them from the series options and press the Delete button on your keyboard.

Double click on any of the horizontal error bars to bring up the options. Here is what we’ll see:

Now to make sure we make the right choice for Direction, always take a look at your marker and the point you see in the picture. Think about which direction you want the line to go. In this case, we need it to be a Plus.

For End Style, select No Cap to remove the tick mark at the end of the line.

Who decides how long the line is? It’s the Error Amount. Here you have a lot of options at your disposal, but we just need the first one and that’s to type a 1 for Fixed value. The reason this works is because the distance between each category is 1. That’s how Excel translates category spacing for the scatter plot – side note: remember scatter plot needs numbers for X and Y – if you leave X empty, Excel is nice enough to fill it with 1, 2, 3, etc for each category. – Doesn’t work for Y but it does for X).

The rest is cosmetic enhancements. Click on color options for the X error bars and change the line color to a subtle grey. We’re nearly there, but it’s still not fully “right”. Notice how the connector line “sits” on top of the other bars. It doesn’t flow well.

To improve the flow, do the following:

  1. Add the same color border to the Start/End series and the Up, then the Down bars.
  2. Remove the Y-axis. Just click on it and press Delete.
  3. Remove the legends on the bottom and the Gridlines if you haven’t done so by now.
  4. Add a Title.
  5. To make sure your category axis labels move down if your cumulative values become negative, go to the X-axis options and for Label Position, select Low.

Test it out by putting in a large negative change that pushes your cumulative into the negative.

Now we’re all set with our super flexible Waterfall chart!

(Video) How to Create an Excel Waterfall Chart

FAQs

How do I create a total waterfall chart in Excel? ›

To designate a data point as a total or subtotal, double-click on that data point (in this case, the ending balance). In the Format Data Point pane, check the "Set as total" box. Now the chart will display that value as a total or subtotal instead of adding it to the other values.

Can you do a combo waterfall chart in Excel? ›

As you mentioned, for the default Waterfall chart in Excel, it is not feasible to add multiple series in a waterfall chart. Sorry for the inconvenience that happens to you. As a workaround, you can create a column chart to display the waterfall chart, then you can add another series to display the baseline.

How do you create a dual waterfall chart in Excel? ›

You can create a dual waterfall chart by clicking on the Waterfall dropdown arrow, and clicking the Dual Waterfall item in the dropdown menu. Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel.

How do you do a simple waterfall in Excel? ›

Select your data. Click Insert > Insert Waterfall or Stock chart > Waterfall. You can also use the All Charts tab in Recommended Charts to create a waterfall chart.

How do I create an interactive flow chart in Excel? ›

Press the Insert a SmartArt Graphic button under the Illustrations group. The choose a SmartArt Graphic dialog box will appear. Pay attention to the items listed on the left, and select Process. Look through the available options and choose the flowchart template you want to use.

How do you make a waterfall step by step? ›

How to Build a Waterfall
  1. Dig a hole for the pond. ...
  2. Build a frame for your pond. ...
  3. Prepare your waterfall pump. ...
  4. Make the base for your waterfall. ...
  5. Assemble the waterfall. ...
  6. Attach your tubing to your waterfall. ...
  7. Test the pump. ...
  8. Decorate your waterfall.
23 Jul 2021

Can you stack in a waterfall chart? ›

Organizing your source data

UpSlide can also generate a Waterfall if your data is organized horizontally. For Stacked Waterfall charts, all data in the same row must be of the same sign.

Is waterfall and Gantt chart same? ›

The waterfall methodology is often visualized in the form of a flow chart or a Gantt chart. This methodology is called waterfall because each task cascades into the next step. In a Gantt chart, you can see the previous phase "fall" into the next phase.

How do you create a 2D chart in Excel? ›

Select any cell in the data range. On the Excel Ribbon, click Insert tab, then click Column Chart. In the 2-D Column section, click the first chart type -- 2D Clustered Column chart.

What are the 6 stages of waterfall method? ›

The waterfall model has six stages: requirements, analysis, design, coding, testing, and deployment. During the requirements stage, developers write down all the possible requirements of a system in a requirements document.

Is Excel or PowerPoint better for flowchart? ›

Excel is an easier to learn program and does have the peculiar manner as Word. From the perspective of flowchart mapping, Excel outperforms Word on several levels.

Can you make a waterfall without a pump? ›

Gravity-Fed Fountain

A continuous-flow water fountain moves water without a pump or mechanical parts. A gravity water fountain moves water through multiple chambers by using a combination of gravity and pressure science to tumble water into the air in a dancing, fluid flow.

How do waterfall charts work? ›

A waterfall chart is actually a special type of Excel column chart. It is normally used to demonstrate how the starting position either increases or decreases through a series of changes. The first and the last columns in a typical waterfall chart represent total values.

How do you draw rain falls? ›

Sketch an oblong shape, with the one end pointing down to the direction of the rainfall. Sketch the pointed tail of the raindrop pointing to the opposite end of the oblong. Draw an outline over the shape of the raindrop and erase the sketch marks. Fill and paint over the shape with white.

What are 3 types of charts that you can create use in Excel? ›

Available chart types in Office
  • Column chart. Data that's arranged in columns or rows on a worksheet can be plotted in a column chart. ...
  • Line chart. ...
  • Bar chart. ...
  • Area chart. ...
  • Stock chart. ...
  • Surface chart. ...
  • Radar charts. ...
  • Treemap chart (Office 2016 and newer versions only)

What are the 4 most common chart types in Excel? ›

Following are the most popular Excel charts and graphs:

Clustered column chart. Combination chart. Stacked column chart. 100% stacked column chart.

Can you split a bar on a waterfall chart? ›

There is a Split Bar Waterfall Chart in Peltier Tech Charts for Excel (not shown here) that takes this into account, splitting each bar vertically, to stack positive values in the left half of the bar and negative values in the right.

Can waterfall chart go negative? ›

In a waterfall chart, the first column is the starting value and the last column is the end value. The floating columns between them are the contributing positive or negative values.

How do you connect lines to a waterfall chart? ›

Double-click on any column in your waterfall chart so that the editing window appears on the right. Now you can check or uncheck the box "Show connector lines".

Is waterfall better for smaller projects? ›

Waterfall relies on teams following a sequence of steps and never moving forward until the previous phase has been completed. This structure is suited to smaller projects with deliverables that are easy to define from the start.

What are the 5 stages of waterfall model? ›

But generally, you can group the activities of the waterfall approach into five stages: planning, design, implementation, verification, and maintenance.

Is waterfall methodology still used? ›

Though many developers are moving to new and emerging approaches, Waterfall is still widely used in traditional organizational environments and processes.

How do you plot multiple data on one graph? ›

How to show two sets of data on one graph in Excel
  1. Enter data in the Excel spreadsheet you want on the graph. ...
  2. Select the data you want on the graph. ...
  3. Click the "Insert" tab and then look at the "Recommended Charts" in the charts group. ...
  4. Choose "All Charts" and click "Combo" as the chart type.

How do you create a subtotal in a waterfall chart? ›

To set a subtotal, right click the data point and select Set as Total from the list of menu options. We designed Waterfall charts so customers never have to make edits to their data in the Excel worksheet. Everything can be done in the chart.

Which type of Charts can show data from multiple series? ›

The most common, simplest, and classic type of chart graph is the line graph. This is the perfect solution for showing multiple series of closely related series of data.

How do I stack values in Excel graph? ›

Stacked area charts are created like other charts in Excel. First, enter and highlight the data. Most versions of Excel: Click on Chart. Click Insert, then click Stacked Area.

Can Excel make 3D graphs? ›

To create a 3-D line chart, click Line, and then under 3-D Line, click 3-D Line. To create a 3-D area chart, click Area, and then under 3-D Area, click 3-D Area. To create a 3-D surface chart, click Other Charts, and then under Surface, click 3-D Surface or Wireframe 3-D Surface.

Which chart type provides the best visual display in Excel? ›

Scatterplot. Scatter plots are useful for showing precise, data dense visualizations, correlations, and clusters between two numeric variables.

How do I create a 4d chart in Excel? ›

How to Create a 4 Axis Chart in Excel
  1. Create a new spreadsheet in Excel.
  2. Type the label names of your axes in each column, for example, Axis 01, Axis 02, Axis 03, and Axis 04 as headers in columns A, B, C, and D respectively.
  3. Type the corresponding data for each column and row.

How do I create a dynamic list in Excel? ›

Creating a Dynamic Drop Down List in Excel (Using OFFSET)
  1. Select a cell where you want to create the drop down list (cell C2 in this example).
  2. Go to Data –> Data Tools –> Data Validation.
  3. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.

How do I create a dynamic function in Excel? ›

Dynamic Formulas begin with &== and are followed by an Excel formula. Repeating Dynamic Formulas begin with &=&= and are followed by an Excel formula. You may use most of Excel's functions in a Dynamic Formula.

How do I create a dynamic data table in Excel? ›

The Pivot Table option can create dynamic Tables in Excel. For this, select the complete data to be included in Dynamic Table and then click on the Pivot Table option under the Insert menu tab or else press short cut key ALT + N + V simultaneously to apply it.

How do I create a drop-down list and autofill in Excel? ›

Re: drop down list and autofill
  1. From Developer TAB, hit Design.
  2. Left to it is Insert.
  3. From the menu find Active X controls.
  4. Select & draw Combo Box on sheet.
  5. Select the Combo Box & Right Click.
  6. Now from the menu find Linked Cells, assign the data range where you want to put selected locations.
30 Nov 2020

How do you autofill data in a drop-down list? ›

Press Alt + Q keys simultaneously to close the Microsoft Visual Basic Applications window. From now on, when click on a drop down list cell, the drop down list will prompt automatically. You can start to type in the letter to make the corresponding item complete automatically in selected cell.

What is the difference between a static list and a dynamic list? ›

A static list consists of prospects that you build once and edit manually to make changes. A dynamic list is rule-based and automatically updates when a prospect's data changes. You can use either list as a recipient list or suppression list for list emails and engagement programs.

Can Vlookup be dynamic? ›

While using the VLOOKUP function in Excel, sometimes we need to lookup multiple tables. In order to create the dynamic VLOOKUP formula, we will nest the INDIRECT function inside of a VLOOKUP function to dynamically reference multiple tables within the lookup formula.

What is dynamic modeling in Excel? ›

Layout of a spatial Excel model

The spatial models we consider are also dynamic models, meaning that the variables change over time. This means that in addition to tracking the value at different places, we also need to keep track of the model variables at different times. The result is data overload.

How do you get Excel to automatically update data? ›

Automatically refresh data at regular intervals

Click a cell in the external data range. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties. Click the Usage tab. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

How does Xlookup work in Excel? ›

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

Why do we spill in Excel? ›

If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!. This error value is generally associated with the use of RAND, RANDARRAY, and RANDBETWEEN functions.

Is pivot table in Excel dynamic? ›

You can base a pivot table on the dynamic range. Then, when you refresh the pivot table, it will include all of the data in the range, even if new rows or columns have been added.

Can you use Excel as a database? ›

As a spreadsheet program, Excel can store large amounts of data in workbooks that contain one or more worksheets. However, instead of serving as a database management system, such as Access, Excel is optimized for data analysis and calculation.

Videos

1. Excel Masterclass Waterfall Chart
(The Optimisation Game)
2. Excel - how to easily create a waterfall chart
(United Computers)
3. PowerPoint WATERFALL Chart With MULTIPLE SERIES | Step-by-Step TUTORIAL Incl. EXCEL Links
(Presentation Mastery)
4. Creating a Waterfall Chart in Excel 2016
(Chester Tugwell)
5. Excel Chart - Stacked Waterfall Chart for Annual Expenses Reporting
(Caripros HR Analytics)
6. Build 5 ADVANCED Excel Charts from Scratch
(Kenji Explains)

Top Articles

Latest Posts

Article information

Author: Fr. Dewey Fisher

Last Updated: 12/03/2022

Views: 6609

Rating: 4.1 / 5 (42 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Fr. Dewey Fisher

Birthday: 1993-03-26

Address: 917 Hyun Views, Rogahnmouth, KY 91013-8827

Phone: +5938540192553

Job: Administration Developer

Hobby: Embroidery, Horseback riding, Juggling, Urban exploration, Skiing, Cycling, Handball

Introduction: My name is Fr. Dewey Fisher, I am a powerful, open, faithful, combative, spotless, faithful, fair person who loves writing and wants to share my knowledge and understanding with you.