What Is a Waterfall Chart and Why Would I Need One

Last month your revenue was $40,000 and this month it's only $30,000? What happened? In Excel, you could print a nice report that shows each revenue stream for last month and this month, so you can compare the amounts.
You could even create a bar chart to compare the different revenue streams.
The bar chart lets you see the differences for each stream, but maybe you'd like to see how each revenue stream contributed to the overall change in revenue. A waterfall chart will let you see the changes that occur between a starting point and an ending point. In Excel, you can create a waterfall chart by building a column chart, and making some changes to it.

Create a New Table

The first step in building a waterfall chart is to create a table that calculates the individual changes, and a running total. In the example below:

  • June and July revenues are at the far right.
  • F3 is the total for June.
  • C9 is the total for July.
  • Column D shows the difference, where amounts have gone down.
  • Column E shows the difference, where amounts have gone up.
  • Column B is the running total, from the June start, to the July end.


Add a Column Chart

To start the waterfall chart, select the range in the thick border (A1:F10), and insert a clustered column chart. Waterfall04

Hide the Running Total

To focus on the revenue stream changes, you can hide the series for the running total:

  • Click on a dark blue column, to select the Run Ttl series
  • On the Ribbon's Format tab, for Shape Fill, select No Fill

To remove the Run Ttl series from the legend:

  • Click on the legend to select it
  • Click on Run Ttl, then press the Delete key


Format the Waterfall Chart

Next, you can add a bit of formatting to make the column chart look more like a traditional waterfall chart. To widen the columns:

  • Right-click on any column, and click Format Data Series.
  • In the Series Options category, set the Gap Width to 0%.
  • Close the dialog box.

To lighten the gridlines:

  • Right-click on a gridline, and click Format Gridlines.
  • In the Line Color category, select Solid Line.
  • From the Color drop down list, select a light shade, such as the lightest grey.
  • Close the dialog box.

In this revenue chart, up is good, and down is bad, so you can change those series colors to red and green. If the End series is red, change it to a different color, to avoid any confusion. To change a series colors:

  • Click on a column, to select its series
  • On the Ribbon's Format tab, for Shape Fill, select the color you want

And here's the finished waterfall chart. I also added data labels with a custom number format, to show up and down arrows.

Waterfall Chart Utility

The example shown here is a very simple waterfall chart, with only a few changes. If you need to make lots of waterfall charts, or more complex charts, you should invest in Jon Peltier's Excel Chart Utility. To use the utility for the revenue stream data, you could set up a simple table that showed the start and end amounts, and the change in each revenue stream. These are just links to the original table, that had monthly revenue and the change amounts.
When you install the Chart utility, it adds a Waterfall command to the Ribbon. Select a cell in the table, then click the Waterfall Chart command, to open the dialog box.
You can change the sort order, and the option to use values as data labels, or go with the default settings. Then click OK, and the chart is created and formatted instantly. Much quicker and easier than doing everything manually!
So, to save yourself some time and headaches, take a look at Jon Peltier's Excel Chart Utility. Use it to create waterfall charts, and other custom charts. It's very reasonably priced, and will quickly pay for itself, in time saved.

Create Multiple Home Pages in Firefox

Do you have two or more web pages that you use frequently? I like Google as a home page, and use it many times during the day. I also refer to my own website several times, to make sure everything is working okay, or look up one of the Excel tips that are stored there.
I had Google set as my home page in Firefox, then opened Contextures.com later.
It's not a terribly painful process, but I've found a way to set multiple home pages, instead of one.

Set Multiple Home Pages

Here's how I set up the multiple pages:

  1. On the Firefox toolbar, click Tools
  2. Click Options
  3. Click  the Main heading, then click the Use Current Pages button
  4. The URLs will appear in the Home Page box, separated by the pipe separator.
  5. Click OK, to close the Options dialog box.

The next time you open Firefox, both home pages will automatically open.

Change Google RSS Folders

In the Google Reader, you can see a list of your RSS feeds, and group them into folders. Usually I assign a feed to a folder as soon as I subscribe to it. If I forget, then I can assign it later.
One way is to click the Manage Subscriptions link, at the bottom of the feeds list. Then, click the feed's Change folders button, and select a folder. This method works well if you have a few feeds to change.
You can also click on a feed name in the feed list, then, click the Feed Settings button.
A quick way to assign a folder is to drag the blog name in the feed list. In the screen shot below, I'm moving 10x Software Development from Software to Technology.

AutoFilter By Selection In Excel

In Excel 2003, you can add a couple of buttons to the toolbar to make it easy to filter a table. For example, in the table below, the East region is selected. With one click of a button, and no programming, you can add an AutoFilter and filter the table to show only the East region orders. Thanks to Roger Govier for sharing this tip.

Continue reading "AutoFilter By Selection In Excel"

Clear Dead Links From Your Bookmarks

I'm setting up my new laptop, and plan to copy my bookmarks from the old laptop. Before I copy them, I'll clear out the dead links, to tidy things up a bit. There's no point in copying useless data to the new machine.
If you'd like to do this on your machine, you can use the free utility AM-Deadlink. It finds dead links and duplicate links in your list of browser bookmarks.
It's easy to install and use, and takes only a couple of minutes to check my bookmarks (about 1700). Then I sort the list by error number, and delete any 404 pages.
You can also use AM-Deadlink to create backup files for the bookmarks.

Calculate a Ratio in Excel

In Excel, if you divide 2 by 8, the result is 0.25. if you format the cell as a fraction, the cell might show 1/4 as the result.

What if you want to show the result as a ratio? How can you get the cell to show 1:4 instead of 1/4? There may be other ways, but here's the formula that I used.

=B2/GCD(B2,C2) & ":" & C2/GCD(B2,C2) 

This formula requires that the Analysis ToolPak be installed, in Excel 2003 and earlier versions. It divides each cell by the greatest common divisor (GCD), and puts a colon between the two numbers. Would you use a different formula? __________________

Open a New Tab in Firefox

Well, it’s Christmas Eve, and you’re probably too busy to read a long blog post today. So, here’s a quick tip that I discovered this week.
I use Firefox as my browser, and when I need a new tab, I right-click in the tab area, then click on New Tab.
Now, I realize that isn’t a terrible hardship, but it’s much easier to create a new tab in other browsers, that shall remain nameless.
I know there’s a New Tab button, and I even added that to my toolbar, but never remember to use it.

Double-Click For a New Tab

This week, I accidentally double-clicked in the tab area, to the right of the last tab (or in the sliver of space above the tabs, if your aim is good), and presto! A new tab was instantly created.
It’s still two clicks, but for some reason I find this quicker and easier. It’s the small things. 😉
Happy Holidays!

Defensive Design

Recently I've been skimming through Defensive Design for the Web by 37signals and have found a few good tips for creating web forms, help and error messages. It's not the best book that I've seen on web design, but it's easy to read, and illustrates its common sense guidelines with good and bad examples from popular sites.
At the end of the book there's a design test that you can use on your own web site, to see how it rates.

When Things Go Horribly Wrong

As a customer, my worst web site experience was with the Westin Hotel, while booking accommodations for a conference. I selected my arrival and departure dates, then clicked a button to view room information, so I could decide which type I wanted. I closed the room info window, completed the booking, and got my confirmation, which I quickly read, then filed.
About a week before the conference, I checked the confirmation, and realized that it was for only one night, instead of the four that I needed. A few frantic phone calls later, I had the four nights booked, although one night was a a different hotel, a few doors down from the Westin.
What went wrong? I went back to the site and tested. While booking, when I opened that room info window, it wiped out my departure date! Now that's bad design. One of the book's 40 guidelines suggests saving customer data in incomplete forms.

Other Applications

Some of the book's ideas could also be applied to forms in Excel and Access. For example:

  • Highlight either required or optional fields.
  • If customers can't choose it, don't show it.
  • Make it fast, not cute.
  • Eliminate unnecessary navigation during multistep processes.