The New Waterfall Chart in Excel 2016

This article about Microsoft’s new Waterfall Chart in Excel 2016 was written by Kasper Langmann, co-founder with Mikkel Sciegienny of Spreadsheeto, a relative newcomer to the Excel blogosphere. Kasper and Mikkel are very enthusiastic about Excel, they have written a number of tutorials on the Spreadsheeto Blog, and they offer a comprehensive seven-part free training course on Excel. Mikkel approached me to write about the new charts that Microsoft introduced in Excel 2016, and he agreed that we should write some posts together.

In the first half of this post, Spreadsheeto will specifically talk about Microsoft’s new Waterfall Chart. I will follow up with a discussion of the extensive and flexible waterfall charts in Peltier Tech’s software.

The Waterfall Chart in Excel 2016

In 2015, Microsoft released six new charts in an update for Excel 2016. This release was a direct response to user feedback and one of those new charts was the waterfall chart. The waterfall chart is a bar chart in which the bars are placed along the vertical axis at different levels according to whether they are an increase or decrease.

Then totals are shown as bars of height from zero as they are affected by the increases and decreases.

This is often useful for visualizing things like financial data where revenue can be shown as bars that shift upward vertically and expenses can be shown as bars that shift downward by contrast.

As we dive into the details of how to create a waterfall chart, note that we will work with an example scenario in Excel 2016 for Windows.

Getting Started with a Waterfall Chart – Get the Data Right

The first and foremost objective when setting out to create a waterfall chart is to make sure our data is in the correct format. Let’s take a look at a simple example of income statement data.

Waterfall Chart Data

It is important that our data is in this form where increases (income) and decreases (expenses) will be shown in the waterfall chart from left to right according to the data points from top to bottom in our table. Also, any totals (Total Revenue, Operating Income, and Net Income) will be shown in the chart as they appear in sequence in our data table.

Note that Total Revenue, Operating Income, and Net Income are subtotals along the way that will be shown as cumulative totals in our waterfall chart. This is set up to be a very intuitive progression as the chart presents revenue and expenses in the same logical fashion as our data table.

This is an instrumental point for setting up our data. You get out of the waterfall chart what you put into it and this means knowing how to set up our data before creating the chart.

Inserting the Waterfall Chart

Once our data table fits this layout, simply highlight the entire table (or one cell within the table), click on the ‘Waterfall and Stock Charts’ dropdown button…

Waterfall Chart on the Excel Ribbon

Then select ‘Waterfall’ in the menu.

Waterfall Chart on the Excel Ribbon

Alternatively, click on the ‘See All Charts’ button at the bottom right of the Insert > Charts group…

Find All Charts on the Ribbon

Then find ‘Waterfall’ in the list of available charts and click OK.

Waterfall Chart Excel's All Charts Dialog

Identifying the Totals

Initially, our new waterfall chart will be a bit unorganized and will need some specific tweaks from before it will begin to take shape and be a better visual representation of our data. Note in the legend that there are three classifications of the data bars: ‘Increase’, ‘Decrease’, and ‘Total’.

Waterfall Chart, Not Ready Yet
Notice how ‘Increase’ bars rise in cascading fashion and ‘Decrease’ bars fall in the same way along the vertical axis according to the cumulative effect each has on the running total. Right now our Total Revenue, Operating Income, and Net Income totals are each shown as an ‘Increase’. We need to change them to each to a ‘Total’.

To do this, let’s first click on the Total Revenue bar and then right click.

Waterfall Chart, Setting Subtotals

Select ‘Set as Total’ and two things will happen. First, the color of the bar will change to match the legend as a ‘Total’ and second, the bottom of the bar will be anchored at zero. That way, the bar will be a visualization of the cumulative total income after increases due to Sales and Service income. Notice now how the top of the ‘Total Revenue’ bar is the exact same height as both the ‘Sales’ and ‘Service’ bars put together.

Waterfall Chart, Setting Subtotals

Now we just need to repeat this step for ‘Operating Income’ and ‘Net Income’. This results in the chart we ultimately want. Increases (income) are show as rising bars in blue while decreases (expenses) are shown as falling orange bars.  Note the cascading visual effect of the rising and falling bars that give the waterfall chart its name.

Waterfall Chart, Setting Subtotals

Now we can make any formatting changes we want just as we would with any other chart in Excel. Then it’s ready to disseminate across your organization, to your stakeholders, or for a presentation.


The waterfall chart is a really nice addition to the family of charts offered in Excel 2016. As we have seen here, it provides a cascading visualization of data that includes increases and decreases while allowing us to see the cumulative effects on the running total in a very intuitive fashion. For the right kind of data, the waterfall chart can really bring things to life by providing an at-a-glance representation that anyone can appreciate.

The new charts in Excel 2016 can be used in a variety of scenarios. To learn more about all of them check out our guide here.

Before you begin your charting adventure, you should definitely read this comprehensive piece on what to do and what not to do when creating charts.

This article is written by Kasper Langmann from Spreadsheeto. If you like it, you should check out his free Excel training.

Peltier Tech Charts for Excel Waterfall Charts

Many years ago, Peltier Tech introduced a tutorial that showed you how to make your own waterfall charts in Excel, the slow hard way. You can even read the latest edition of the Excel Waterfall Charts (Bridge Charts), which is only a few years old.

Peltier Tech came out with a demo Waterfall Chart utility a decade ago, because even though it’s possible to build your own waterfall chart by hand, it’s much easier to select your data and click a button than to follow a long protocol with many easy-to-omit steps.

Peltier Tech has had a commercial Waterfall Chart utility on the market since 2008. Over the years it has been upgraded to run in newer versions of Excel, and to run in Excel for Mac. It has been continually enhanced, several varieties of waterfall charts have been introduced beside it, and thousands of users now depend on it. The newest edition, released to coincide with Excel 2016 for Windows and Mac, is called Peltier Tech Charts for Excel 3.0.

When Microsoft finally came out with their own Waterfall Chart, Peltier Tech said, “Nice going, that’s a pretty good start.”

Regular Waterfall

The data for the Peltier Tech Waterfall Chart is like that for Microsoft’s version, except the totals are left blank. When the program encounters a blank in the data column, it knows to calculate a total for that category. (If the totals in your input data are wrong, the chart Microsoft creates is also wrong.)

Data for Peltier Tech Waterfall Chart

Select the data, then click the Waterfall dropdown button on the Peltier Tech tab > Custom Charts group of the ribbon, and click Waterfall.

Peltier Tech Waterfall Chart Dropdown

You get a dialog that lets you select from a number of options.

Peltier Tech Waterfall Chart Dialog

When you click OK, the program inserts a worksheet with links to the data as well as columns of formulas that make sure the plot comes out right, plus a chart. Alternatively, you could have the program construct these formulas in the original worksheet. There is a checkbox and list box next to the output range, so that you can modify some of the options selected in the dialog.

Peltier Tech Waterfall Chart Output


The waterfall chart looks like on of the charts below. The waterfall treats the first value in the input data as a subtotal, for example, as a starting value that you are tracing through a set of intermediate operations to get a final value.

Peltier Tech Waterfall Chart - First Category is Subtotal

The second waterfall chart treats the first value in the input data as a change in value, in this case, as the first increase in revenue, coming from sales.

Peltier Tech Waterfall Chart - First Category is Change in Value

These charts are regular Excel charts, so you can reformat them, resize them, copy and paste them wherever you want. You need to be careful, however, because if you format away some of the hidden magic that makes them work as waterfall charts, you may break them and have to rebuild them.

The Microsoft waterfall charts are still not 100% user editable, depending on which build of Excel 2016 you use. On my main laptop I have the non-Office-365 version of Excel 2016, so it isn’t updated monthly with new features. I can format the colors of the Microsoft waterfall chart, and I can change the maximum and minimum of the vertical axis, but I am unable to modify the major tick spacing of the vertical axis. I also cannot modify the orientation of the horizontal axis labels. I’m sure that before long Microsoft will enable formatting of all of the elements of its waterfall charts, but for now, as I said, it’s still just a “good start”.

Rotated Waterfall

So what are some of Peltier Tech’s other waterfall charts? One of them is a Rotated Waterfall Chart. It uses the same data as the “Regular” Waterfall Chart, but it uses horizontal bars instead of vertical ones. Most people prefer the regular orientation, but some customers asked for a rotated version, so here it is. Here is the “First Item is a Starting Total” version…

Peltier Tech Rotated Waterfall Chart - First Category is Subtotal

And here is the “First Item is a Change in Value” version.

Peltier Tech Rotated Waterfall Chart - First Category is Change in Value

Stacked Waterfall

There is also a Stacked Waterfall chart. which breaks down each bar into smaller components. For example, you may want to see how several divisions contribute to your company’s performance.

The data is the same as for the “Regular” Waterfall Chart, except that there are two or more columns of values, one for each division.

Data for Peltier Tech Stacked Waterfall Chart

Each division appears in the chart with its own color, so you can track it across the chart.

Peltier Tech Stacked Waterfall Chart

Note that it’s impossible to stack negative and positive values in a stacked bar chart. The Stacked Waterfall handles this situation by replacing a multicolored stacked bar with a single bar that merely shows the net for that category. There is a Split Bar Waterfall Chart in Peltier Tech Charts for Excel (not shown here) that takes this into account, splitting each bar vertically, to stack positive values in the left half of the bar and negative values in the right.

Dual Waterfall

Another type of waterfall is the Dual Waterfall Chart, which allows you to compare two sets of data. The data set below shows the same data for two years.

Data for Peltier Tech Dual Waterfall Chart

The Dual Waterfall shows one set of data as filled bars, the other as transparent bars with a thick outline, so you can compare performance item by item across two sets of data.

Peltier Tech Dual Waterfall Chart

Paired Waterfall Chart

I wrote about the Paired Waterfall Chart recently in the Peltier Tech Blog. This is yet another way to track the cumulative effects of intermediate factors on a value or set of values.

Paired Waterfall Chart

I’m sure there are numerous additional Waterfall Charts that people could show you, but this extensive set is all that I’ve got.

Peltier Tech Charts for Excel 3.0

All of these Waterfall Charts, and several other handy chart types, are included in the Advanced Edition of Peltier Tech Charts for Excel 3.0. The “Regular” and Stacked Waterfall Charts are included in the Standard Edition.


Peltier Tech Charts for Excel

Axis Labels on Small Charts

My colleague Patrica McCarthy, the Excel Diva, wrote in 12 Months Data – 13 Months Data in a Chart last week about a problem with Excel charts. If you make a 12-month chart, as you shrink the chart, for example to fit it into a dashboard, Excel drops off the last month’s axis label. She mentions that plotting 13 months of data will avoid this problem.

That doesn’t make sense, I thought. Excel doesn’t drop off the last category axis label on a chart. But then I thought about it. When you shrink the chart, and the labels get close together, Excel does little things to prevent axis labels from overlapping.

I’ll illustrate by showing larger charts and a really simple data set.

The default chart in US versions of Excel is 5 inches wide and 3 inches tall. The figure below shows three charts, each 1.5 inches tall, at widths of 5, 4, and 3 inches. The month labels all appear in these charts. It helps that I’ve used the three-letter abbreviations for the month names, and I’ve shrunk the axis label font size from 9 to 8 points (and I’ve also shrunk the chart title from a ridiculous default of 14 points down to 9 points).

Larger Excel Charts with Normal Monthly Category Axes

When I shrink the chart a bit further to 2.5 inches in width, Excel realizes that the axis labels won’t all fit, and it uses one of its favorite tricks: Excel has rotated the axis labels 90° (see chart below). They fit fine, but they are harder to read than horizontal labels.

Smaller Excel Charts with Potentially Overlapping Vertical Labels

You can format the labels so they are horizontal, but Excel uses another of its favorite tricks: it only shows alternating labels, starting with the first (left chart below). This is what Patricia was talking about, but when she said Excel hides the last label, I didn’t realize she meant Excel was hiding alternating axis labels. You can format the axis so that it shows every label (in other words, it uses a label interval unit of 1), but now we see that the labels are beginning to overlap (right chart below).

Smaller Excel Charts with Potentially Overlapping Horizontal Labels

How about a 45° degree tilt to the labels? Starting with the first chart, if you change to 45° labels, Excel still decides to show only every second label (left chart below). If you show all of the axis labels, they really don’t overlap (right chart below). This isn’t terrible, but I find partially rotated text as distracting as fully rotated text, and hardly easier to read.

Smaller Excel Charts with Potentially Overlapping Inclined Labels

Patricia mentioned in her article that you can show 13 months of data to make the last month’s label visible. I’ve added the prior December’s data below, and now that we start the alternating axis labels with last December, this December also appears. We still miss every second label, but seeing the last one anchors the visual more effectively.

Plot 13 Months to Show Last Month's Label

Showing both December’s gives insight into a year-over-year comparison, at least for December.

In addition to using Patricia’s workaround above, and using smaller font sizes and shorter axis labels as I’ve done from the start, there are a few other tricks you can try.

Here I’ve used one-letter abbreviations for the month names. Sure, there are 3 J’s, 2 M’s and 2 A’s, but within the context of the entire year, there’s little confusion about this, and it is done frequently.

Shorten Labels to Make Them All Fit

Quick Number Format Tip #1: If you have a date in a cell, you can display just the three-letter abbreviation if you use a custom number format of MMM, and you can show the one-letter abbreviation if you use a custom number format of MMMMM. (A single M shows a one- or two-digit numerical month, 1 for January and 12 for December; a double MM shows a two-digit numerical month, 01 for January and 12 for December; and a quadruple MMMM spells out the entire name of the month.)

You can also show alternating month labels, ending with December, as follows. Make your chart as before with the full set of month abbreviations, set it to show every horizontal label, then delete every other month starting with January. Now at least the last month’s label is visible, even if you’re only showing half of the axis labels.

Hide Alternate Labels in Worksheet to Show the Labels You Want to Show

Finally, you can show all axis labels, but prevent overlapping them, if you stagger them, showing some higher and others lower. To do this, put the cursor at the beginning of some of the cells, and hold Alt while pressing the Enter key. This inserts a carriage return (or line feed, whichever) at that point in the cell’s text. I’ve set the cell’s Word Wrap to true, so you can see the taller two-line cells in every second row below, and these labels appear one line lower along the axis. This needs more space to display, so Excel shrinks the plot area slightly, but you get the benefit of seeing all of the axis labels.

Stagger Labels with Creative Use of Carriage Returns

Quick Number Format Tip #2: To get the carriage return in a cell containing a date, use a custom number format of MMM, but precede the MMM with Ctrl+J. This undocumented feature inserts a carriage return right in the number format. This undocumented feature also only works in the number format dialog for worksheet cells, not for chart elements, which is a total bummer, because they’re at least as useful in charts as in the worksheet.

Quick Formula Tip #1: If the labels are in a column of cells, say A2:A13, you can easily get a carriage return into alternate cells. Enter the formula =A2 in cell B2, enter =CHAR(10)&A3 in cell B3, then select B2:B3, and drag the square on the bottom right of the selection down to B13 to fill the range with these alternating formulas.

Quick Formula Tip #2: If you have dates in A2:A13 instead of labels, enter =TEXT(A2,"MMM") into B2, enter =CHAR(10)&TEXT(A3,"MMM") into B3, select B2:B3, and fill down as above to B13.


Peltier Tech Charts for Excel

Microsoft Excel Charting Survey

Microsoft is conducting a survey to help plan new charting features for future versions of Excel. This is your chance to provide your thoughts and feedback. Here is the announcement from the Excel Team:

This survey is being conducted by the Microsoft Excel team. In the future, new charting features will be added to Excel. We want to better understand how you expect charts, that have new features, to be displayed and behave in older versions of Excel that don’t have these new features.

This survey will take approximately 10 minutes. Feedback from this survey will be used to improve the user experience in Excel. Responses to this survey will not be associated with any personal information. Please see the Microsoft Privacy Statement for further privacy details on all Microsoft products.

Thank you for participating,
Excel Team

Click for the Microsoft Excel Chart Survey

[NOTE: The survey has been closed]


Peltier Tech Charts for Excel

VB Password Prompt when Closing Excel

The VB Password Prompt Problem

The VB Password Prompt is a (usually) rare problem that occurs when you are using a workbook or add-in with a password protected Visual Basic project. When you quit Excel, you are asked for a password to open the VB project.

VB Password Prompt

You may not know the password, so you click Cancel once, twice, seventeen times before the prompt is dismissed.

Note: Peltier Tech software does have such a password, but users do not need it to run the software.

This may affect Peltier Tech add-ins, or some other add-in. I’ve written about the VB Password Prompt as part of my documentation for Peltier Tech Charts for Excel.

It is not the fault of the add-in, but of another third-party software application that apparently does not properly release resources when it finishes with them. Windows or Excel or VBA gets confused, decides a given add-in is at fault, and asks for the password so it can open the code and clean up the mess. If you do know the password and enter it at the prompt, your computer checks the project, decides the problem is elsewhere, and proceeds to close Excel.

Debugging the problem is difficult, because it is not consistent across even nominally identical computers. You have to disable any software that could possibly have an effect, and then turn them on one by one until you find the one that causes the problem. Normally the “solution” is either to disable the culprit, or live with the problem.

Implicated Software

The kinds of software that may cause a spurious VB password prompt include applications that control printing, either to a physical printer or to pdf files, enterprise document management programs, database applications, and others. Adobe Acrobat has been implicated, as has Acrobat PDFMaker, though this was reported fixed in December 2013. Other third-party programs that have been blamed include ProjectWise, Palo, Hyperion SmartView, Tabs for Excel, Microstrategy, OmniJoin, CapitalIQ, and ManicTime.

Et tu, Dropbox?

A recent culprit in the VB Password Prompt bug is Dropbox. This is disturbing to me because I like Dropbox; among the various file syncing programs, I feel that Dropbox runs most smoothly and reliably. I use Google Drive more extensively than Dropbox, and it seems to work pretty well, with occasional hangs and closes. I used to have problems with Microsoft’s OneDrive, but I’ve heard they’ve made some reliability improvements, so I’ll be trying them out again. Nice to have options.

First reported in September 2015, the problem affects VB workbooks and add-ins that contain at least one UserForm. If the UserForm is displayed during operation of the workbook or add-in, then all open workbooks are closed, and then Excel is closed, the insidious password prompt appears. In some cases, clicking Cancel a number of times lets Excel close properly, but sometimes….

Microsoft Excel has stopped working

That’s no fun.

The Dropbox Solution

If you use Dropbox, there is a workaround. Dropbox includes a feature called Dropbox Badge, which is closely integrated into Microsoft Office, and allows advanced interaction among users working on the same Office files. Sounds good, except this feature seems to be causing the password prompt to appear. It’s easy enough to turn off Dropbox Badge:

  • Click on the Dropbox icon in the Taskbar
  • Click on the settings gear icon in the top right of the dialog
  • Choose Preferences
  • Under Dropbox Badge, choose ‘Never show’
  • Click OK

Now you can continue using all your favorite Excel workbooks and icons, and let Dropbox sync your files.


Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0