Multiple Series in One Excel Chart

A common question in online forums is “How can I show multiple series in one Excel chart?” It’s really not too hard to do, but for someone unfamiliar with charts in Excel, it isn’t totally obvious. I’m going to show a couple ways to handle this. I’ll show how to add series to XY scatter charts first, then how to add data to line and other chart types; the process is similar but the effects are different.

Displaying Multiple Series in One Excel Chart

Displaying Multiple Series in an XY Scatter Chart

Single Block of Data

This is a trivial case, and probably not what people are asking about. But I’ll cover it just for completeness.

If I have a single block of data, I can select the block of data, or just a single cell within it, and Excel will build a chart using all of the data. The first column (if series are plotted by column) is used for X values, the rest of the columns become the Y values, and the first row is used for series names.

Multiple Series from One Data Block - XY Scatter Chart

Select Series Data: If I somehow have a chart that uses only part of the data, I can right click on the chart and choose Select Data, or I can click Select Data on the ribbon, and the Select Data Source dialog pops up. I can then edit the Chart Data Range, either by manually editing the address, or by selecting a different range, to update the chart.

Select Data Dialog for One Data Block - XY Scatter Chart

Highlighted Chart Data: But it’s even easier to do without the dialog. If I select the chart, I can see the chart’s data highlighted in the worksheet.

XY Scatter Chart with One Series

I can click on any of the handles on the corners of the highlighted ranges to stretch the amount of data used in the chart.

XY Scatter Chart with Three Series

Easy peasy, right? I’ve written about this simple yet powerful technique for controlling chart data in Chart Source Data HighlightingChart Series Data Highlighting, and Highlighted Chart Source Data.

Multiple Blocks of Data

It’s not as easy to manipulate your chart’s data when the data resides in separate blocks of data, such as this:

Multiple Data Blocks - XY Scatter Chart

You have to start by selecting one of the blocks of data and creating the chart.

XY Scatter Chart with 1 Series

Select Series Data: Right click the chart and choose Select Data, or click on Select Data in the ribbon, to bring up the Select Data Source dialog. You can’t edit the Chart Data Range to include multiple blocks of data. However, you can add data by clicking the Add button above the list of series (which includes just the first series).

Select Data Source Dialog

The Select Data Source dialog disappears, while a smaller Edit Series dialog pops up, with spaces for series name, X values, and Y values.

Edit Series Dialog

Select ranges for each of these…

Edit Series Dialog

… then click OK and the new data appears as a new series in the list.

Select Data Source Dialog

The chart now has two series. Note that in an XY Scatter chart, each series can have its own X values, independent of the other series in the chart.

XY Scatter Chart with 2 Series

Repeat as needed to fully populate the chart.

Not too bad, but I’m not a huge fan of the Select Data Source dialog. It just seems like too much work. And like the expansion of data within a single range that I started this article with, there’s a faster and easier way to add data to a chart from different ranges.

Copy – Paste Special: Select and copy the data you want to add to the chart, then select the chart, and from the Home tab of the ribbon, click the Paste dropdown, and select Paste Special. You will be greeted with the Paste Special dialog.

Paste Special - Don't Replace Existing Categories

Make sure that the settings in the dialog are correct: Values (Y) in rows or columns, series names in first row, categories (X labels) in first column.

The Replace Existing Categories setting would replace existing X values with those being pasted, which makes little sense for an XY chart that already has X values defined. We’ll talk about this setting when we discuss Line charts. For XY Scatter charts, I never ever check this box.

Click OK and the chart has a new series.

XY Scatter Chart with 2 Series

Copy the next range, select the chart, Paste Special.

XY Scatter Chart with 3 Series

Again, in an XY Scatter chart, each series can have its own X values, plotted along the same X axis scale, independent of the other series in the chart.

This is pretty easy. It’s even easier to use Paste instead of Paste Special, but sometimes Excel guesses incorrectly on those row/column, first row, first column settings, and you’ll have to undo the Paste and do Paste Special.

Displaying Multiple Series in a Line (Column/Area/Bar) Chart

I’m using Line charts here, but the behavior of the X axis is the same in Column and Area charts, and in Bar charts, but you have to remember that the Bar chart’s X axis is the vertical axis, and it starts at the bottom and extends upwards.

Single Block of Data

When your data is in a single block, a Line chart works just like the XY scatter chart. The first column (if the series data is plotted in columns) is used as X values, or more accurately, X labels; the rest of the columns are used as Y values. The first row is used for series names.

Multiple Series from One Data Block - Line Chart

Multiple Blocks of Data

When there are multiple blocks of data, Line charts still work mostly the same as XY Scatter charts. Let’s look at this simple data.

Separate Data Blocks - Line Chart

Start by creating a Line chart from the first block of data.

Line Chart with 1 Series

Select Series Data: Right click the chart and choose Select Data from the pop-up menu, or click Select Data on the ribbon.

Select Data Dialog

As before, click Add, and the Edit Series dialog pops up. There are spaces for series name and Y values.

Edit Series Dialog

Fill in entries for series name and Y values, and the chart shows two series. The original X labels remain on the chart.

Line Chart with 2 Series and Original Categories

This dialog differs from the one seen when adding data to an XY Scatter chart, because there is no place for X values (or X labels). To change the X labels, click the Edit button above the list of X labels in the chart. The Axis Labels dialog appears.

Edit Series Dialog

The reason for this is that Line charts (plus Column, Area, and Bar charts) treat X values differently than XY Scatter charts. XY Scatter charts treat X values as numerical values, and each series can have its own independent X values. Line charts and their ilk treat X values as non-numeric labels, and all series in the chart use the same X labels.

Change the range in the Axis Labels dialog, and all series in the chart now use the new X labels.

Line Chart with 2 Series and New Categories

The differences between Line and XY Scatter charts can be confusing. What is important is that the data can be formatted the same (markers or no markers, lines or no lines), while the X values are treated differently (numerical values in XY Scatter charts, non-numeric labels in Line charts).

Copy – Paste Special: As in XY Scatter charts, adding data to Line charts can be faster and easier with Copy and Paste Special than with the Select Data dialog.

Paste Special - Don't Replace Existing Categories

Check the settings in the dialo: Values (Y) in rows or columns, series names in first row, categories (X labels) in first column. If Replace Existing Categories is unchecked, the original X labels will remain in the chart. Click OK to update the chart.

Line Chart with 2 Series and Original Categories

Although both series are plotted against the original X labels, if we examine the series formulas, we see that the original series formula contains the original X labels range ($B$3:$B$8), while the new series formula references the new range ($E$3:$E$8).

    =SERIES(Sheet4!$C$2,Sheet4!$B$3:$B$8,Sheet4!$C$3:$C$8,1)
    =SERIES(Sheet4!$F$2,Sheet4!$E$3:$E$8,Sheet4!$F$3:$F$8,2)

The X labels specified in the first series formula is what Excel uses for the chart. If we had selected only the new Y values, ignoring any new X values, and kept Categories in First Column unchecked, both series formulas would reference the same X label range.

Here is what happens when we check Replace Existing Categories.

Paste Special - Replace Existing Categories

When we click OK to update the chart, the new X labels appear along the axis. In addition, both series formulas include the new X label range.

Line Chart with 2 Series and New Categories

This usually isn’t what I want, so I almost never check Replace Existing Categories for any chart type.

The behavior even becomes stranger when we use mismatched data ranges. The second range below has many more rows than the first.

Unmatched Data Blocks

Here is the chart if we paste special with Replace Existing Categories unchecked. Both series use the same X labels, so the axis has enough spaces for the longest series. Since the first labels are being used, these fill the first part of the axis, overlapping excessively, while the rest of the axis remains unlabeled. The first series is pushed to the left of the chart along with the axis labels, since it only uses a fraction of the X axis labels.

Line Chart with 2 Series and Original Categories

Here is the same chart if we paste special with Replace Existing Categories checked. Both series use the new X labels, which fill the entire length of the axis, and they don’t overlap excessively since I wisely used one-character labels. The first series is again pushed to the left of the chart, since it has many fewer points than the second series.

Line Chart with 2 Series and New Categories

You can assign one series to the primary axis and the other to the secondary axis, and each axis will be long enough for its labels. Below, the first series is plotted on the primary axis (bottom and left edges of the chart), while the second series is plotted on the secondary axis (top and right edges).

It is easier to assign the new series to the secondary axis because I kept the Replace Existing Categories unchecked. This kept the new X label range in the series formula even though the series was initially plotted against the original labels. When I switched the series to the secondary axis, it used the new X labels from the series formula. If I had used Replace Existing Categories, the original categories would have been removed from the original series formula, and I would have had to restore them.

I oversimplified when I stated earlier that all series in a Line (Column, Area, Bar) chart use the same X labels. It’s more accurate to say that all primary axis series in a Line chart use the primary axis labels, while all secondary axis series use the secondary axis labels.

Line Chart with 2 Series on 2 Axes

You can try to do a little rescaling of axes to make the chart look better. Here I set the same maximum and minimum values for primary and secondary Y axes. I also formatted the Axis Position to On Tick Marks for both primary and secondary X axes: “One” lines up with “a” and “Six” lines up with “u”, and fortunately there are the right number of categories that each category on the primary scale lines up with a category on the secondary scale (“Two” with “e”, “Three” with “i”, etc.). This alignment was a happy accident.

Line Chart with 2 Series on 2 Semi-Aligned Axes

I hardly ever have a secondary X axis on a line chart, since there is usually no relationship between the two sets of labels, but our eye insists on seeing such a relationship. I’ve written about this confusion caused by Secondary Axes in Charts, even when applied in a well-meaning way.

 

Peltier Tech Chart Utility

Diverging Stacked Bar Charts

Often stacked bar charts are used to show the frequency of responses in surveys, where each stack represents the frequencies of replies to a given question (replies often sorted, such as Strongly Disagree, Disagree, Agree, Strongly Agree). A variation on stacked bar charts are Diverging Stacked Bar Charts, which position the replies horizontally so positive responses are stacked to the right of a vertical baseline and negative responses are stacked to the left of this baseline. Below is a diverging stacked bar chart that represents the relative truthfulness of politicians’ statements, with truthful and mostly truthful statements stacked to the right, and false statements to the left.

Diverging Stacked Bar Chart

In case you’ve been stuck in a cave for the past 16 months or so, here in the US we’re in the middle of an interminable presidential election campaign. During a campaign, there are a lot of statements made by the candidates, some truthful, some less so.

An organization known as PolitiFact has been fact-checking politicians’ claims, and various charts of their results have circulated the internet in recent weeks. Most are in the form of stacked bar charts, since those are easier to produce in Excel and in other graphing packages. The one below, for example, was found on Andy Kirk’s Visualizing Data web site, as part of his series The Little of Visualisation Design; he got it from Michael Sandberg’s Data Visualization Blog. Andy noted that had he drawn the chart, he would have “pivoted” the bars so that the positive bars (true statements) extended to the right and the negative bars (lies) to the left.

Simple Stacked Bar Chart

RJ Andrews claims he made the following variation in 3 minutes in PhotoShop. He didn’t actually create the chart, he simply split the above chart into horizontal strips and offset the strips laterally to align the baseline. It obviously was done in a quick and dirty fashion: the gridlines don’t line up, since they were laterally offset with the adjacent bars. But it shows how such a chart would look.

Quick and Dirty Diverging Stacked Bar Chart

So how do we go about drawing such a chart?

Stacked Bar Chart

The data from the above charts is too complicated to show the process clearly, so I’ve dummied up the following data set.

Building a Stacked Bar Chart

Select the data and insert a 100% stacked bar chart.

Building a Stacked Bar Chart

Since there were more columns in the data set than rows, Excel didn’t plot the data the way we wanted it. No matter, it’s easy enough to click the Switch Rows and Columns button on the ribbon.

Building a Stacked Bar Chart

Now let’s format the data. We’ll format the bar colors to match the charts I’ve scavenged from the internet, and apply a gap width of 75% to shrink the white space between stacks. (You only need to apply the gap width to one series, and the rest will use the same value.)

Building a Stacked Bar Chart

Finally, as usual, Excel laid out the stacks from bottom to top, although the data was listed from top to bottom. This is a common issue, and is actually consistent with charting in general, but it annoys many people. I’ve written about it in at least two posts on this blog, Excel Plotted My Bar Chart Upside-Down and Why Are My Excel Bar Chart Categories Backwards? The solution is an easy two-step process: Format the vertical axis, check the Categories in Reverse Order box, and select Horizontal Axis Crosses at Maximum Category.

Building a Stacked Bar Chart

Diverging Stacked Bar Chart – First Attempt

When you are exploring a new chart type in Excel, it often takes a few attempts to get it right. I’ll step through these attempts so you can see my thought process and perhaps learn more about Excel’s charting logic at the same time.

Since we want the lies plotted to the left of the baseline, we’ll make those numbers negative, and the vertical axis will become the baseline. Here is how the simple data set changes.

Building a Diverging Stacked Bar Chart - First Attempt

We select the data and insert a 100% stacked bar chart.

Building a Diverging Stacked Bar Chart - First Attempt

Oh yeah, then we switch rows and columns. You’ll get good at that before this tutorial is finished.

Building a Diverging Stacked Bar Chart - First Attempt

Apply the desired colors, and set gap width to 75%.

Building a Diverging Stacked Bar Chart - First Attempt

Finally reverse the order of categories and cross the horizontal axis at the maximum category.

Building a Diverging Stacked Bar Chart - First Attempt

So what’s right with our chart, and what’s wrong? The false statements go to the left and the truths to the right, that’s good. But while the truths are plotted from the axis starting with the least true, the lies are plotted from the axis starting with the most false. We need to reverse the order of the negative data columns, so they are stacked in the order we want them.

Diverging Stacked Bar Chart – Second Attempt

Here is the rearranged data.

Building a Diverging Stacked Bar Chart - Second Attempt

Select the data, insert a 100% stacked bar chart.

Building a Diverging Stacked Bar Chart - Second Attempt

Switch rows and columns, blah, blah.

Building a Diverging Stacked Bar Chart - Second Attempt

Apply fill colors and gap width.

Building a Diverging Stacked Bar Chart - Second Attempt

Reverse categories and cross the axis at the maximum.

Building a Diverging Stacked Bar Chart - Second Attempt

So how are we doing? Well, the bars are all stacked in the appropriate order, that’s good. But the legend has the negative entries in the reverse order. How do we get the bars stacked in the right order, while also listing the legend entries in the right order, since they seem to be working against each other?

Diverging Stacked Bar Chart – Third Attempt

In hindsight, the way to get the appearance we want is easy. But when I first worked on this chart type a few years ago, it took me a couple days before I figured it out. What we need are a couple dummy series. The following adjusted data range will help clarify it.

Notice that there are duplicate column headings for False and Mostly False, but the cells contain no values. We will plot these columns with the others, so we will have duplicate series for False and Mostly False. The duplicate series will not appear, since they have no values. Then we will have duplicate legend entries, and we can delete the ones that are out of order.

Building a Diverging Stacked Bar Chart - Third Attempt

Here’s another view of the data, showing which series will have visible legend entries and which legend entries will be deleted.

Building a Diverging Stacked Bar Chart - Third Attempt

Select the data and insert yet another 100% stacked bar chart.

Building a Diverging Stacked Bar Chart - Third Attempt

Switch rows and columns. This should be second nature by now.

I’ve widened the chart so the legend entries appear in a single row. Note the duplicate entries.

Building a Diverging Stacked Bar Chart - Third Attempt

Apply the fill colors and gap width as before. Note that both False series have the same formats, as do both Mostly False series. Even if a series doesn’t appear in the chart, you can select it by selecting a visible series and using the arrow keys to cycle through the other series; once the hidden series is selected, you format it just like any other.

Building a Diverging Stacked Bar Chart - Third Attempt

Delete the excess legend entries: click once to select the legend, then click again to select the legend entry then click Delete. If you delete the wrong one, simply delete the whole legend and reinstate it. When it appears with all legend entries, delete the ones you don’t want, only more carefully this time.

Building a Diverging Stacked Bar Chart - Third Attempt

Finally, do the axis switcheroo: categories in reverse order, cross at maximum.

Building a Diverging Stacked Bar Chart - Third Attempt

And that’s just what we wanted.

One final fix. You may not want to show any negative percentages along the horizontal axis. Select the axis, press Ctrl+1 to open the Format Axis dialog, and change the number format from 0% (which applies a percentage format with no decimal percentages and a minus sign for negative percentages) to 0%;0%;0% (which applies a percentage format with no decimal percentages and no minus signs for any numbers: the three items separated by semicolons are for positive, negative, and zero values).

Building a Diverging Stacked Bar Chart - Third Attempt

Now that’s really just what we wanted.

Politician Data – Stacked Bar Chart

Here’s the data I manually digitized from the chart I got from Andy Kirk’s chart. This article is a tutorial on the making of a chart with this data, and I will not entertain any discussion on the data itself, its derivation, or its accuracy. There is plenty of that discussion in the various social media.

Stacked Bar Chart - Liars

Select data, insert 100% stacked bar chart. Hey look, with more rows than columns, Excel plotted it the way we wanted, so no need to switch rows and columns.

Stacked Bar Chart - Liars

Apply fill colors and gap width.

Stacked Bar Chart - Liars

Format that vertical axis: Categories in reverse order, horizontal axis crosses at maximum category. While there, specify a label interval of 1; with so many labels, Excel automatically shows only every second label.

Stacked Bar Chart - Liars

Finally, stretch the chart vertically, so it doesn’t look so crowded.

Stacked Bar Chart - Liars

That’s a decent enough stacked bar chart.

Politician Data – Diverging Stacked Bar Chart

Here is the politician data adjusted to make a diverging chart. Note the negative values and columns with blank values and duplicate labels.

Diverging Stacked Bar Chart - Liars

Select the data, insert a 100% stacked bar chart.

Diverging Stacked Bar Chart - Liars

Apply fill colors and gap width.

Diverging Stacked Bar Chart - Liars

Delete the unneeded legend entries.

Diverging Stacked Bar Chart - Liars

Reverse the categories on the vertical axis, move the horizontal axis to the maximum category, and show all labels.

Diverging Stacked Bar Chart - Liars

Finally, stretch the chart.

Diverging Stacked Bar Chart - Liars

Finally, if you don’t want to display negative percentages, you can adjust the number format of the horizontal axis. Follow the protocol introduced above: select the axis, press Ctrl+1 to open the Format Axis dialog, and change the number format from 0% to 0%;0%;0%.

Diverging Stacked Bar Chart - Liars

And there’s our Diverging Stacked Bar Chart in Excel.

Peltier Tech Charts for Excel 3.0 – Diverging Stacked Bar Chart

This technique for making diverging stacked bar charts is a bit tricky, especially with the repeated and deleted legend entries. I’ve built this chart type into the Advanced Edition of my Excel charting software, Peltier Tech Charts for Excel 3.0, so the extra series are added and the superfluous legend entries removed automatically.

Starting with the stacked bar chart data set, you can click on the Diverging Bars button on the Peltier Tech Advanced ribbon tab. A dialog pops up with a few typical settings.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

The color scheme options have been taken from Cynthia Brewer’s Color Brewer. You can select your color scheme, and set the order of the colors (i.e., left to right or right to left), and the program will use as many colors along the scale as you need.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

A new worksheet is inserted, with negative values, duplicate series headers, and blank columns as needed, and a chart is generated.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

As with any Excel chart, it’s easy to stretch the chart and modify its formats.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

Many surveys have a neutral response. A diverging stacked bar chart would plot the neutral responses so that it spans the vertical baseline.

The data set below joins Pants on Fire with False into a new False category, and the Half True becomes a neutral category. The dialog works just the same.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

To achieve a neutral bar spanning the vertical axis, the program splits Half True into a negative half and a positive half, both formatted the same using the central color for the selected color scheme.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

Again, the chart is easily manipulated.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

For more information about this Excel charting add-in, go to Peltier Tech Charts for Excel 3.0. Note that Diverging Stacked Bar Charts are included only in the Advanced Edition of the software.

 

Peltier Tech Chart Utility

Copy Chart to New Sheet and Link to Data on New Sheet

This is a quick post to answer this rather common question from the forums:

I have a worksheet that has data in a particular arrangement and a chart that plots this data. I have another worksheet with the identical data arrangement. How can I copy the chart to the new worksheet and have the chart link to the data on the new worksheet?

Here is my simulation of the problem. Worksheet “Original Data” has a particular data arrangement with a “nice” chart that plots this data.

Original worksheet's data and chart

If I click on a series in the chart and peek at the formula bar, I can see that it plots data from “Original Sheet”. Here are the two series formulas:

=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)

Great, just what I expect.

I went to a lot of trouble drawing this chart exactly to the boss’ specifications, and I don’t want to spend another hour and a half duplicating the chart for the data on the worksheet named “New Data”. So I copy the chart, and paste it on “New Data”. But that looks like it plots the numbers from “Original Data”.

New worksheet's data but pasted chart shows original worksheet's data

I can verify this behavior by checking the series formulas in the chart:

=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)

Most people don’t expect this behavior (though it does make sense, given the history and nature of charts).

In this simple case it wouldn’t take too long to actually edit the series formulas to make the new chart match the new data. But in real life, the chart contains 37 series, and you need to put the chart onto 47 different worksheets. (It’s not too hard to write some VBA to help you edit series formulas, but that’s not today’s lesson.)

So I’m going to show you an easier way to make sure the chart links to the data on the new sheet.

The original chart links to the data on the original sheet, right? If we make a copy of that original sheet, call it “Original Data (2)”, we can verify that the chart on this copied sheet links to the data on the copied sheet. See, series formulas:

=SERIES('Original Data (2)'!$C$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$C$3:$C$8,1)
=SERIES('Original Data (2)'!$D$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$D$3:$D$8,2)

I didn’t even have to touch the chart. Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter.

Look, the chart now is showing the data we just pasted on top of the copied data:

New worksheet's data and chart that shows new worksheet's data

All we need to do is remove the current “New Data” sheet, and rename this copied sheet to “New Data”.

It’s a small amount of work, but isn’t copying and pasting data a lot easier than futzing with a chart’s series formulas and source data?

 

Peltier Tech Chart Utility

Paired Waterfall Chart

The Paired Waterfall Chart

I came across a new (for me) style or waterfall chart last month, which I call the paired waterfall chart. It is designed to show how an investment fund’s total holdings, comprised of stocks or other vehicles and cash, changes during a time period, based on changes in value of its stocks and on proceeds from the sale of some stocks.

Here is an example of such a chart. The holdings of the fund totaled 1350 in 2015: 1100 in stock plus 250 in cash. The holdings of the same fund in 2016 totaled 1450: 675 in stock and 775 in cash. Stock A didn’t change in value. Stock B appreciated by 50 in value. Proceeds from the sale of some Stock C were 275, while the fund’s total value of Stock C declined by 250; the difference of 25 must have been additional appreciation of Stock C. Sales of some Stock D resulted in proceeds of 250 and corresponding loss in value of 250. Stock E went down 25 in value, while Stock F went up 50.

Paired Waterfall Chart

There is no provision for taking cash and buying more of a stock already in the portfolio or a new stock, so the upper set of floating bars are green only, that is, you can only gain cash. The lower bars can be red or green, but the value of your stock holdings can never drop below zero, because at worst the stock is worth nothing. These constraints make the chart slightly easier to build than a standard waterfall, though the horizontal and slanted lines are trickier than in the standard one.

For want of a better name, I call this the “Paired Waterfall Chart”. In hundreds of images of waterfall charts that Google found for me, none had this same structure. I tried to crowd source the chart’s name on Twitter, but the only suggestions that weren’t lame attempts at humor were “stacked waterfall” and “cascade”, and these refer to other chart types. If you know what this chart is really called, or if you have some examples you can share, please let me know in the comments below this article.

Stacked Waterfall and Cascade Charts

How To…?

Since I’ve never seen this kind of waterfall, and since nobody asked me how to make one, I have not prepared a tutorial. But someone did ask whether my charting software, Peltier Tech Charts for Excel 3.0, could make these charts. The answer was, not yet, but with the thought of perhaps selling a few licenses, I took on the task.

The Advanced Edition of Peltier Tech Charts for Excel 3.0 now includes a Paired Waterfall Chart feature, and that’s what I used to make the chart at the top of this article. The data consists of three columns of data, with labels for the horizontal axis, entries for value of the holdings, and entries for proceeds due to any sales. The top row consists of labels, and the totals in the bottom row are omitted, because the program can add better than you can.

If you are a licensee of the Advanced Edition of Peltier Tech Charts for Excel 3.0, shoot me an email and I’ll send you the link to download the latest build, which includes the Paired Waterfall Chart. If you are a licensee of the Standard Edition, let me know and I’ll send a coupon code so you can upgrade to the Advanced Edition for the difference in price. If you are a licensee of an older Peltier Tech utility, let me know and I’ll send a discount coupon code so you can upgrade. And you should upgrade, because Peltier Tech Charts for Excel 3.0 works in Excel 2016, and the same add-in file works on both Windows and Mac.

Like all of my utility’s charts, it is easy to make the Paired Waterfall Chart. Here I’ve selected the data, clicked the Waterfall dropdown, then clicked the Paired Waterfall Chart button.

Data and Ribbon Button for Paired Waterfall Chart

This very simple dialog came up.

Paired Waterfall Chart Dialog

I clicked OK, and the program generated a new worksheet with a bunch of formulas in a nice Excel Table, and the Paired Waterfall Chart (click on the image to see it full-sized).

Paired Waterfall Chart Output

I’m gradually transitioning as many of my program’s chart output sheets to use Excel Tables, because they make it easier for users to adjust the chart’s data after they’ve already built the chart. Tables will also make it easier when I finally come out with Peltier Tech Charts for PowerPoint and Word. The structured referencing in the tables can be a challenge, but Zack and Kevin’s book about Excel Tables helped a lot.

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.