Waterfall Charts

Quick reference

• 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.