A look at new chart types in Excel 2016: Waterfall

- [Instructor] If you'd like to depict the data in columns A and B by way of a chart, you certainly could go to the Insert tab and possibly consider a column chart. Here's an option, try that. And we see that there are positives and negatives, and there's no question that this does show the data in an accurate way. Here's some negatives, we see those being depicted in the chart. If it is a column chart, you might want to consider making the columns wider, an easy change there. Double-click any column. That activates the Format Data Series dialog box on the right, and if these three columns are not already selected, they are here, you will see a choice called Gap Width.

Make that narrower. This might make for a more interesting chart. I'll change this down to 25. I think this is a little bit easier to read, but do notice that the labels here do overlap. Possibly we could change these. If you click on the label area here below the chart, we can then go to the Home tab and use one of the orientation buttons, possibly choose Rotate Text Up. That's not necessarily better but I think perhaps it's a bit more readable. Anyway, this is certainly one option for displaying the data. There is a new chart type however, introduced in Excel 2016 that might depict this data in a more beneficial way and bring out the idea that we've got some negatives in a way that focuses on the idea of cumulative data.

Now, I've got formulas in Column D here that show the cumulative effect and these are in pure numbers. Column B is formatted so that we're seeing the numbers in thousands, nevertheless, in other words as we click on each of these we see the total so far year to date. So if I were to highlight these four numbers, for example, if you look in the status bar at the bottom of the screen you'll see off to the right some 83.4. That more or less reflects the number that we're seeing right here, 83.4 thousand in a rounded way.

So we don't necessarily need to have this data here, but I went there for reference because this new kind of chart, which is called a waterfall chart, could fit our needs perfectly here. Let's highlight this data, and go to the Insert tab. Now if you know where to find this, you'll know it's on the Option here, kind of looks like a waterfall, maybe that's pushing it a bit. Here's a drop arrow. Insert Waterfall or Stock Chart. As we slide over the option, description is, use this chart to show cumulative effect of a series of positive and negative values.

Use it when you have data representing inflows and outflows, such as financial data. So there's the description of it. Now if you didn't know about this new chart feature, or you're not made aware of it, you might happen to go to Recommended Charts, and explore some of the options here and there, what do we see? Third choice here: Waterfall Chart, and there's that same kind of description here, and let's look at this much larger by simply clicking OK. I'll make this bigger and move it around a little bit, and now we do see more clearly what's happening month to month.

We know that there are four negative months, and we can certainly see that in the data, but the chart brings it out even more clearly. Here too, we could possibly make an adjustment to make these columns wider but I don't think that's necessary here. However, the labels at the bottom might look a little funny. Let's close that dialog box, make these wider. Watch those labels across the bottom. They change this way. Now if we make them narrower, we could do this. Now unlike in the previous chart, where we could select the label area and go to the Home tab, we can't do this on a waterfall chart, nor can we tie our title to a given cell, so there are some limitations on some of the newer chart capabilities when it comes to traditional labeling that we might have done with other kinds of charts.

Nevertheless, the focus here is on the idea that, when the numbers are fluctuating, particularly if there are negatives here, we see the ebb and flow of the data. Think of each rectangle as representing the data that we see in column B, so there's a minus 15 for April, this is a relative size 15, but it's a different color, and we see where the legend is Decrease. Now, what might look a little surprising here is, we've got three items in the legend. The blues represent the positive values, and we can see how those are being displayed on the chart.

The orange color represents decrease, we see those, and then we see gray for total, but where's the gray? Well there's a gray right there. So as I'm pointing right here to the gray, it's really not giving me any information here, but as I look at the scaling off to the left, I see that's about 85 or so. How does that correspond with our cumulative total? Remember, it wasn't necessary to have this here, but it's the number right here. Or is it the number right here? So we can see on either side of May here, we've got one number that's more or less a low 80, and one just to the right of it, just a little bit bigger.

So we're talking about equivalent relationship of these two values, so, that's a little weak on the screen and there's no direct way to make that stronger. You could take out the grid lines. When you select a chart there's an option off to the right. Click the plus, you can remove the grid lines and then see those gray bars a little bit more clearly, but I think the focus really is on the overall visual here. I do think that's a little bit weak, that total entry there. Nevertheless, we see what the data is. I think it's helpful to also do this with the data although it's not a requirement.

We see the data in a different way than we might typically with other kinds of charts. You could, by way of different formulas, construct a chart somewhat like this using formulas and standard column chart, but you need to be using some manipulative techniques to hide parts of columns. You'd have to use something like a stacked column and then hide the portions you didn't want to see. This is much more direct and easy to work with, and even though you don't have control over all the display aspects of this chart, as you might in other charts, I think it gives you a good clear image.

Do experiment with the column width. I don't think it's necessary to change it here, but I'll double click one of the columns and off to the right, the gap width is currently 50. What if I make that be 10? See what's happening there, brings them closer together. It does hide that slight gray line that we can barely see here, reflecting total, but since that's barely readable anyway, you could make a case for saying, this is the appearance you want. And as always with a chart, do consider some of the chart styles. The example here, even though there's a drop arrow, there are only the styles that we see visible at the top, so as we slide across these, one or another of them might catch your fancy as being a better way to display this data.

But there's no question that it's a valuable addition to the arsenal of charts that we've got available. These were introduced in Excel 2016. You will not see this kind of a chart in 2013 or any earlier versions of Excel.