Limitations of Waterfall charts

The first limitation that I want to show you is in the chart title and in chart labels. What we are unable to do is reference a cell. So let’s say we wanted to go into our chart title, and from the formula bar, click the formula bar. We can’t, which means we cannot create a dynamic chart title using a waterfall chart in Excel. Now, there is a way that you can overcome this. You can create a text box. And with the text box, you can add in your chart title.

So for example, if I go to insert and I am going to insert a text box and this will allow us then to draw a particular text box in such a place. And this is the chart title. What we can then do is this can then be moved around. The only problem is if you take your chart and you move your chart, the title doesn’t move with it. Now, what you can do is take the two of these together and then you can actually group them by selecting group and group. Now, when you group these, still unfortunately when you move one, the other is not going to move with it. And it’s not just the chart title.

It’s also chart labels. You can’t reference other cells in these. The next problem is when identifying your up bars and your down bars, and it would be nice to

be able to give these their own colors: up bars green, and maybe down bars red. But unfortunately, with the standard chart, you cannot do it. When you pick a data series, you pick the entire data series. So let’s say we just picked gross profit. We go into fill for our gross profit. Select a solid fill of let’s just say yellow and only that one has changed. Now all of the totals have changed so you can change all of the up bars and all of the down bars and all of the totals the same. Now it is the same with the connector bars in between. Each one, we are unable to change the color of these particular connector bars.

So, if I want to change, it just wouldn’t. You see, all of the bar has changed to red there when I was trying to change the connector bar only. So, again, as you see in when I select the whole data series, all of the up bars and down bars and totals actually changed. Now the data label position is also a restriction and there is another restriction as well in the readability of this chart. And that restriction is when you have negative values. So, I want to just go back to changing all them to kind of a gray color or something. And in my operating expenses, I’m going to increase the operating expenses by a million.

And we see what happens here is that we now have a negative bar on the chart. But the negative bar, the line, the line that goes across the negative bar should be kind of at zero, making it more easy to read values are negative and it doesn’t. It puts this line at the negative value. And this can be a little bit misleading to people when they’re actually reading the chart. So the standard waterfall charts in Excel, they have serious limitations. They are very useful and they can be used to create visualizations to tell the story of your data from opening to closing balances.

But it is important that you understand that they do have limitations over and above other charts that are available in Excel. And most of these limitations stem around the formatting options within in the actual chart. [END]