Default Keys: Ctrl + Shift + Alt + H
If you have never seen a Waterfall Chart before, I highly recommend you take a moment and look them up right now. I liken seeing one for my first time to seeing a unicorn. While I’ve never actually seen a unicorn, I imagine that if I did, the clouds would part, and a perfect beam of sunlight would shine down on it. I would immediately recognize its beauty, and while hearing angelic chanting sounds in my head, have the overwhelming sense that I was in the presence of greatness. OK, I possibly went too far, but Waterfall Charts are wonderful. They are one of the best ways to visualize a bridge between two amounts, showing those items which helped, or hurt along the way.
While Waterfall Charts are great once they are all setup, if you have ever tried to create one in Excel, I’m sure the experience scarred you, much like it scarred me. I quickly went from viewing them as a majestic unicorn, to a demon-spawn sent to torment me. The process is convoluted and confusing, and while I’ve done it many times, I still need a set of instructions and at least an hour to create new ones. This is where my Waterfall Chart Creator Nutility steps in. It simplifies and automates the process, so that it can be accomplished faster than you can stick a horn to a horses head and say “unicorn” — it can help you create a Waterfall Chart in less than a minute.
This Nutility requires you to have the Nutilities Excel add-in installed in order to use it. If you don’t know what Nutilities is, or where to get it, please start at my web-page here: http://iwishexcel.com . The add-in is completely free to use.
Instructions and Example:
Pictured below is an example Waterfall Chart created using the Waterfall Chart Creator Nutility. It bridges the overall year-over-year change in profit for a company from 2017 to 2018, by walking each of the company’s products’ y-o-y change in profit.
To begin, in the workbook you want to create the Waterfall Chart in, run the Nutility by pressing, at the same time, the default keys: Ctrl + Shift + Alt + H. You will be met with a popup asking if you want to create a Setup Sheet. You do. Click OK.
A new sheet will be created in your workbook called “Nut Waterfall Chart Setup” (pictured below). This sheet is used to collect the information you want presented on your chart. It is important that you make no changes to this sheet other than to fill in your values in the yellowed cells. All of the dark-yellow cells must be populated in order to create your Waterfall Chart. The light-yellow cells are optional, and there is an opportunity to update these after the chart has been created.
The picture below shows how I populated the setup sheet in order to create the example chart I presented earlier in this article. Once filled in, I clicked the “Click to Create Chart” button at the bottom of the setup sheet to generate my chart.
After clicking the button, a new sheet will be created in the workbook that contains the Waterfall Chart, and its source data (pictured below). At this point you are free to delete the setup sheet as it is no longer needed. The Nutilities Waterfall Chart sheet is broken up into two sections. The left section contains the source data for the chart, and the right section contains the chart itself. In the source data section, you are free to update any of the yellowed cells, and your changes will be reflected on the chart. However, it is important that you do not insert or delete any rows in this area, and that you do not change any of the blue cells — these hold the formulas that make the chart work. So if you wanted to change the description for Product A to Product Alpha, you could easily make that change in cell B5.
You may at this point have noticed that rows 9-19 have been grouped and hidden. These rows are where you can optionally fill in more categories if they are needed (these are related to the optional yellow cells on the setup sheet that I left blank). The Waterfall Chart has been setup to only display values in visible cells. So by grouping and hiding those rows, they are not displayed on the chart. However, as shown in the picture below, if you expand the grouped to rows to be visible, they will automatically be displayed on the chart.
So if after having already created your chart, you realize that you need to add a couple more categories, you can simply ungroup and show a couple of those hidden rows to add them to your chart. For my current example, I decided after the fact that I needed to display two more products: E and F. The picture below shows how I unhid two rows to add them to my chart.
That’s it! All done. At this point you have a Waterfall Chart ready for presentation. You are free to manually make further customizations to the chart, but all the hard-work of getting the basic chart setup has been done for you.
YouTube Video Demo:
If you prefer to watch a video demonstration, please use the YouTube link below.