Locked lesson.
About this lesson
Waterfall charts help break "net change" into increases and decreases, giving more information about the business cycle.
Exercise files
Download this lesson’s related exercise files.
Waterfall Template.xlsx24.3 KB Waterfall Charts.xlsx
76.1 KB Waterfall Charts - Completed.xlsx
95.9 KB
Quick reference
Waterfall Charts
Adding Waterfall charts to break down net change.
When to use
These charts are useful in breaking down net change values to show both increases and decreases. They are very useful for showing things like stock changes or membership changes.
Instructions
Excel 2016
- Ensure your data is arranged in a tall and narrow table, not a pivoted setup
- Select your Data --> Insert --> Waterfall or Stock Charts --> Waterfall chart
- Locate data points that represents a total --> Left click it (twice) then right click --> Set as Total
Excel 2013 and earlier
Adding the Waterfall Template to your system
- Open your target workbook
- Open the Waterfall Template.xlsx file
- In the Waterfall Template file, right click the Waterfall Chart tab --> Move or Copy
- Check the box to make a copy, then select your target workbook from the list
- Close the Waterfall Template file without saving changes
Updating the Waterfall chart for your data
- Update the chart title (cell B10) to something that makes sense for your data
- Update the legend names (cells B14:B16) to something that makes sense for your data
- Update cells B26 and B19 to the names you want on the start and end columns
- Link C26 to your opening value
- Insert rows between rows 27 and 28 for your data points
- Copy the formulas from D27:H27 down to cover the inserted rows
- For each row, link column B to the description you’d like, and column C to the change value
Deploying the chart
- Copy the desired chart to your dashboard
- Paste it on your dashboard
Hints & tips
- For the manual Waterfall chart, increases use positive values, decreases use negative values
- On the manual Waterfall chart, compare the ending value generated by the chart against your original data set. (The chart calculates this value automatically, and it should balance to your data)
- 00:05 So our dashboard is coming along quite nicely.
- 00:07 And now what we'd like to do is we'd like to add a chart to show stock movement.
- 00:12 The ideal chart for this particular problem is what we call a waterfall chart.
- 00:16 So let's go take a look at this page here with the waterfall data and
- 00:19 I wanna call your attention to something kind of important.
- 00:22 Oftentimes, we track our data in a table that looks like this,
- 00:25 where we have our months across the top, and we have our categories down the left,
- 00:28 and the categories in the middle.
- 00:30 In order to drive a waterfall chart,
- 00:32 you need to have your data unpivoted into a format that looks like this.
- 00:36 Where we have our Opening Units, January, 50.
- 00:38 Units purchased, January, 50.
- 00:39 Units sold, January, -10, negative number is important for
- 00:43 the actual sold piece here.
- 00:45 And then we start again with units purchased, we need to unwind our table.
- 00:48 You can link it with formulas too we need to do, but it needs to be in this format.
- 00:52 Now, the next question is, how do we create a waterfall chart?
- 00:55 And the answer is, it depends on the version of Excel you have.
- 00:59 If you have Excel 2016 on subscription, you can go to Insert tab,
- 01:04 click inside our data here, and we can go and click on the Waterfall chart.
- 01:09 That will create us a nicely chart that looks like this.
- 01:13 And before we get started there is two things we have to change here.
- 01:16 We need to go and select our very first data point, right-click on it and
- 01:21 say set as total, and
- 01:23 select our very last data point, and right-click on it and set as total.
- 01:28 If we have any totals in between, we would want to do the same thing.
- 01:32 What this does it creates what we call a waterfall chart.
- 01:35 And the way you read a waterfall chart is like this.
- 01:38 If we just open this one up a little bit more.
- 01:40 You can see that in January, we start with 50 units.
- 01:43 We draw a line from the top of that across, and then we say,
- 01:47 how many units did we purchase?
- 01:48 And that will show us an increase, in this case, there's 0.
- 01:52 From here, what we'll do is we'll draw a line from the top of the blue and
- 01:56 then we decrease.
- 01:57 So we say, we sold 10 units.
- 01:59 We draw a line from the bottom of the orange and we increase by 100 units,
- 02:03 we decrease by 26, we increase by 0, we decrease 16.
- 02:07 So it gives us the ability to show movement here.
- 02:10 It's a little bit better than net change.
- 02:12 Now, this works great if you're on Excel 2016 subscription,
- 02:15 but what if you're on a different version of Excel?
- 02:18 Can we still create a waterfall chart?
- 02:20 The answer to this is absolutely yes.
- 02:22 As a matter of fact,
- 02:22 I've got a nice little template that you can take a look at right here.
- 02:26 This is the waterfall template file that's in your downloads.
- 02:29 You can actually use this, copy it right into Workbook and
- 02:31 then link it up to make your own waterfall chart.
- 02:34 So let's take a look at how to do that.
- 02:35 Let's say Move or Copy, right click that tab.
- 02:39 Create a Copy and move this into the Waterfall Charts Workbook.
- 02:43 I'm gonna throw it right to the end.
- 02:46 I'm now quickly going to go back to the waterfall template file.
- 02:50 We'll close it without saving changes to make sure that it's ready for next time.
- 02:54 First thing I need to do is give my chart a new title here, so
- 02:57 what we'll call this on is inventory movement.
- 03:02 The next thing I need to do is modify my legend names here.
- 03:06 So we can end up going through on this one here saying,
- 03:11 let's call this one actually units on hand.
- 03:15 We've got units purchased, And units sold.
- 03:23 So this gives us the ability to actually control what we want for
- 03:26 our legend to show what the stock movement is doing.
- 03:29 The next thing we wanna do is change the names of the actual bars themselves.
- 03:34 And the way we're gonna do that is we're gonna come over and
- 03:36 we'll say, let's have an opening count.
- 03:39 We might as well go and
- 03:41 link this immediately to our waterfall data of 50 units, so enter.
- 03:48 Let's go to the end and we'll say closing count.
- 03:51 And you can see that these have now updated on the chart.
- 03:55 We've got an opening count of 50 and closing count of 150 because
- 04:00 the data is automatically calculating its closing based on what's actually here.
- 04:04 Now, the next trick is knowing how many rows of data we actually need.
- 04:09 So if we go back to the waterfall chart, what we really want to do is go and
- 04:12 just run a little quick summary down here.
- 04:14 I'm not gonna grab the opening units line, but I'm just gonna go and
- 04:17 grab all this guy here and say, all right, we've got it looks like 24 rows, but
- 04:22 the one column that I selected.
- 04:23 So that's good to know.
- 04:25 That means that with the two lines that I have here, I need 22 more rows.
- 04:29 So I'm just gonna go and quickly scroll down and insert another 22 rows
- 04:34 into my solution here, right-click, insert.
- 04:39 And now, I need to copy this row, 27, down to make sure
- 04:44 that the formulas are extended properly, there we are.
- 04:49 And you'll notice that I copied all the way over the last row,
- 04:52 which is marked Total Payroll Deductions, but I did not replace the very last row.
- 04:57 The last piece of making this particular chart work is that we're gonna go through
- 05:01 and we're gonna input all of the information that we need for
- 05:05 the increases and decreases.
- 05:07 And the easiest way to do this, if the data's in the right form
- 05:09 on the other side, is to select from the top left all the way down,
- 05:13 every one of these rows till we get to the very last one above Closing Count.
- 05:17 We'll say equals, go back to our waterfall data.
- 05:21 We'll find our first item here for units purchased.
- 05:25 Because we selected all those cells, we can now hit control enter and
- 05:30 it links all of the data in nicely.
- 05:33 And it fills out this awfully horrendous-looking table,
- 05:37 which builds us a beautiful little waterfall chart.
- 05:41 Now, we can expand this a little bit more to see.
- 05:43 Sometimes if your lines aren't working perfectly or things are a little bit
- 05:46 short, some of the numbers can end up looking a little bit squished.
- 05:49 So it's not quite as elegant as the default built in one, but it also
- 05:53 does have more granularity of control that you can play around with as well.
- 05:57 At this point, I can now copy this guy.
- 06:00 We can take him over to our original dashboard.
- 06:05 Paste him right here.
- 06:06 Hold down my alt key, and move it, and resize it to the format and
- 06:11 the shape that I actually want to show on my dashboard.
- 06:14 And job done,
- 06:16 we now have a little water flow chart that will work in any version of Excel.
Lesson notes are only available for subscribers.