Category: Excel charts

Did you know that you can create waterfalls in Excel — Waterfall Charts?  We have a very famous waterfall here in Canada, which you can see in the photo below from our fall vacation, a couple of years ago.

Continue reading “Create a Waterfall Chart in Excel”

Earlier this month, I had the pleasure of flying out of Chicago’s O’Hare airport. I was checking in at the ungodly hour of 6 AM on a Sunday, and hoped that would be a quiet time at the airport.

Continue reading “Excel Box Plot Chart-Airport Security Times”

“In the back of my mind, I always knew that charts would help me,” says the market researcher in this promotional video from 1982. Wow, did you really have to wait weeks for your data processing department to make your charts, back in the old days?

Continue reading “Early Charts – Business Graphics 1982”

Rumours say that the late night TV schedule on NBC will change. Jay Leno will leave his 10 PM spot, and return to 11:35 PM. How long will the revised Leno show be, and what effect will it have on the rest of the schedule?

I’ll bet the NBC programming executives have set up an Excel worksheet to test the possible scenarios, and they made a nice Gantt chart to show the results.

Continue reading “Leno and Conan Excel Gantt Chart”

Have you ever made a Polar Chart in Excel? Or are you like me, and only have a vague idea of what a Polar Chart is (or no idea at all)?

I’ve made thousands of Excel charts over the years, but never made a Polar Chart.

Excel Add-In for Polar Charts

Excel MVP, Andy Pope, has just released a new version of his free Polar Chart add-in, and here’s an example of the charts it can create.

Continue reading “What Is Polar Chart in Excel”

This week I was travelling, and signs are really important for helping you figure out where you are, and where you should be headed.

Whether you’re posting street signs, retail signs, or worksheet instructions, try to make them clear and helpful.
Continue reading “Grocery Store Charts and Other Signs”

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.

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.
________________