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.
Waterfall01
You could even create a bar chart to compare the different revenue streams.
Waterfall02
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.

Waterfall03

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

Waterfall05

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.

Waterfall06
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.
Waterfall07

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.
Waterfall08
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.
Waterfall09
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!
Waterfall10
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.
MultiTabsFireFox
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
    FirefoxOptionsMain
  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.
RSSFolder
You can also click on a feed name in the feed list, then, click the Feed Settings button.
RSSSettings
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.
GoogleRSSDrag
_____________________________

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.
[Update: For Excel 2007 instructions, see Easy Filtering in Excel 2007]
AutoFilterBtns02

Add Buttons to the Toolbar

You can add two buttons to the toolbar, to make filtering easy. One button will filter the table for the selected item, and the other button will show all the records.
To add the buttons:

  1. On the menu bar, click Tools, then click Customize.
  2. In the Customize dialog box, click the Commands tab.
  3. Click the Data category, then drag the AutoFilter command to an existing toolbar.
  4. Drag the Show All command to an existing toolbar.
  5. Close the Customize dialog box.

AutoFilterBtns01

Apply the AutoFilter

Now you can use the new buttons to filter the table, or to show all the records. The table doesn't need to have an AutoFilter currently applied.

  1. In a table in Excel, select a cell that contains the criterion you'd like to use. For example, to filter for the East region records, select an East cell in the Region column.
  2. On the toolbar, click the AutoFilter button

The table is filtered, and shows only the East region records.

Remove the Filter

To remove the filter, and show all the records again:

  1. Select any cell on the worksheet
  2. On the toolbar, click the Show All button.

The filter is removed, but the AutoFilter feature is still turned on, and all the records are visible.
For more information on Excel AutoFilters, visit the pages on Excel AutoFilter Basics and Excel AutoFilter Tips on my website.

Watch the Video

To see the steps, you can watch the short video below.

_________________________

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.
AMDead01
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.
AMDead02
___________________________

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.
ClickNewTab
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.
NewTabButton

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.