Column Chart with Primary and Secondary Axes

Column Chart with Primary and Secondary Axes:
Use a Panel Chart Instead

It’s hard to make column charts with primary and secondary axes; special steps must be taken to prevent secondary columns from obscuring primary columns. Such charts are even harder to read than most two-axis charts, probably because the primary and secondary columns are interspersed. You can avoid these problems if you use a panel chart instead.

What People (Think They) Want:
Column Chart with Two Axes

Let’s use some simple data to illustrate the situation. Here the Secondary data is more than an order of magnitude greater than the Primary data.

Columns on Two Axes Data

When plotted together on a single axis, we can’t tell much about the Primary data, because it is overshadowed by the Secondary data.

Columns on One Axis

Aha! We’ll just plot the Secondary data on the Secondary axis, despite Jon’s earlier warnings about Secondary Axes in Charts.

Overlapping Columns on Two Axis

Now the Secondary data really obscures the Primary data. This is where people usually get stuck with their chart.

Of course, it’s possible to add dummy series to the chart to provide adequate spacing.

Fixed Columns on Two Axes

I’ve found that plotting data on two different axes makes it hard for the reader to determine and recall which axis goes with which data. It’s even more confusing with bars that alternate between axes. No matter how you try to clarify the chart, using axis labels that coordinate with the data (shown below), using arrows (not shown), people still mix them up.

Gussied Up Columns on Two Axes

This is added to the usual confusion with two axes. I’ve had people (smart people, mind you) look at the chart, and their takeaway was that Secondary started lower then Primary, but finished higher.

Ah, the perils of a chart with primary and secondary axes.

What People Really Should Use:
Panel Chart

It’s okay to use primary and secondary axes in the same chart, but to avoid confusion, it’s best to separate them into separate panels of the chart. This is the protocol for creating such a panel chart.

We’ll use the same data.

Columns on Two Axes Data

Start by making a plain old column chart.

Columns on Two Panels - Step 1

Format the Secondary series so it is plotted on the Secondary Axis.

Columns on Two Panels - Step 2

The Primary axis is scaled from 0 to 10, and the Secondary axis from 0 to 200. We need to adjust these scales so the Primary panel is in the bottom half of the chart, and the secondary panel in the top half. If the primary panel has to be 0 to 10 in the bottom half of the chart, we need another 10 units on top, so the total primary scale should be 0 to 20. If the secondary panel has to be 0 to 200 in the top half of the chart, we need another 200 units below this, so the secondary scale should be -200 to 200.

Format the primary and secondary vertical axes according to these computations. If you use a major unit of 2 for the primary axis and of 40 for the secondary axis, both sets of labels line up with the primary horizontal gridlines.

Columns on Two Panels - Step 3

We don’t want to display primary axis labels in the secondary panel or secondary labels in the primary panel. We can use custom number formats to display only the desired axis labels.

In general, number formats have four elements, separated by semicolons. The first element shows the format to be used for a particular numerical situation, the second for another particular numerical situation, the third for all other numerical values, and the fourth for alphanumeric labels:

[first condition]format;[second condition]format;format;alphanumeric

If conditions are not specified, the first condition is positive numbers and the second is negative number, leaving the third for zero values.

This makes the custom number format for the secondary axis very easy:

0;;0;@

Positive numbers (first element) and zero values (third element) will be displayed as numbers with no decimal digits (i.e., “0”), negative numbers (missing second element) will not be displayed, and alphanumerics (fourth element) will be displayed as themselves (“@”). You could leave off the “@”, and alphanumeric values will also not be displayed.

The custom number format for the primary axis is not much more difficult, now that you know the system:

[<=10]0;;;@

Values less than or equal to 10 (first element) will be displayed as numbers with no decimal digits (i.e., “0”), any other values (missing second and third elements) will not be displayed, and alphanumerics (fourth element) will be displayed as themselves (“@”), or omitted if desired (omit the “@”).

Format the primary and secondary axes in turn, and assign the applicable custom number format to each.

Columns on Two Panels - Step 4

We’ll want to visually separate the two panels. Excel only gave us the secondary vertical axis, but we’ll add the secondary horizontal axis, and position that between the panels (at Y=0 on the secondary vertical axis).

First, format the gridlines to use a lighter shade of gray, and the primary horizontal axis to use a darker shade of gray (but not too dark, no need to use harsh black lines).

Columns on Two Panels - Step 5

Using the plus icon (Excel 2013) or the Chart Tools > Layout tab > Axes control (Excel 2007/2010), add the secondary horizontal axis. Excel puts it at the top of the chart by default.

Columns on Two Panels - Step 6

Format the secondary horizontal axis so it uses the same gray line color as the primary horizontal axis. Also format it so it has no labels and no tickmarks.

Columns on Two Panels - Step 7

Format the secondary vertical axis (right side of chart) so the horizontal axis crosses at the automatic position (zero).

Columns on Two Panels - Step 8

Nobody says you need to keep the original aspect ratio of your chart. You can make it narrower (more so for fewer bars) and taller. You can also adjust the size of the plot area to minimize the white margins around the plotted data.

Columns on Two Panels - Step 9

You probably want to label your vertical axes. Using the plus icon (Excel 2013) or the Chart Tools > Layout tab > Axis Titles control (Excel 2007/2010), add axis titles to the two vertical axes.

Columns on Two Panels - Step 10

Excel centers these axis titles along the sides of the chart. You can drag them so they are centered on their respective panels. You’ll probably also have to readjust the plot area.

Columns on Two Panels - Step 11

Adding Data

Making the panel chart seemed like a lot of steps, but it’s really not more steps than making any chart with primary and secondary axes, and it’s fewer steps than the gymnastics needed to put columns on two axes with appropriate spacing.

Adding data is also easier for the panel chart. If I add a series to the column chart with two axes, it throws off the spacing provided by the dummy series, and I’ll need to adjust the number and arrangement of blank series in the chart.

Columns on Two Axes with Added Series

But I’m not going to bother. Instead I’ll add the data to my panel chart.

So copy the new data, select the chart, click on the Paste button of the Home tab (the Copy button in 2007!), and add the data as a new series.

The data is added to the secondary axis.

Columns on Two Panels with Added Series

If the data belongs on the primary axis, simply format the new series and assign it to the primary axis.

Columns on Two Panels with Added Series

If the new series belongs on the secondary axis, you’re already done.

Columns on Two Panels with Added Series

If you add multiple series, and they belong on both axes, well, just move the appropriate ones to the primary axis.

Columns on Two Panels with Added Series

If you need primary and secondary axes, it’s easy to use panel charts to produce charts that avoid the confusion that overlapping axis scales can cause.

 

Peltier Tech Charts for Excel

Alternatives to a 3D Bar Chart

3D Bar Chart

A reader asked if I had an add-in to create this kind of chart. He canceled his request a short time later when he realized that Excel can actually make these charts.

3D Bar Chart

It’s a 3D column chart, and the reader pasted it from another graphing package. This is my reconstruction of the chart in Excel, which is somewhat cleaner than the original. It clearly illustrates how 3D column charts make reading the data difficult to impossible. This chart is in some ways even worse than a regular Excel chart, because of the wider margins between the plotted columns and the back walls with the gridlines. I had to include extra rows and columns in the source data to simulate the wide gaps in the original chart.

First, the columns in front block our view of those in back. The first two yellow columns are completely hidden, as is the first blue column. If you look very closely, you can just make out the top corner of the second blue column.

Second, it’s hard to judge the values of the bars that are not hidden. For example, the tallest yellow bar has a value of 6, yet it seems to be shorter than the gridline for 4. The second green column has a value of 8, yet it barely reaches to the gridline for 4. The first green column has a value of 5, but only reaches to about 3 on the gridline scale. These discrepancies were less pronounced in the regular Excel 3D chart, because the back walls were closer to the plotted data, but it was still necessary to extrapolate to estimate the values.

I have not developed, nor do I plan to develop, any add-in that makes 3D charts.

Alternative Charts

Here is the data from the original 3D chart, based on my best extrapolation from the 3D columns to the gridlines, and made-up data for the columns I couldn’t see at all.

Chart Data

The clustered column chart is the simplest way to show the data. If you haven’t changed Excel’s defaults, this is the chart type that Excel would automatically use. If you don’t have too many series, this shows the data clearly. No extrapolation is needed to a distant set of gridlines; at most you have to interpolate between gridlines. If there are lots of series, it becomes difficult to interpret individual series.

Clustered Column Chart

You could also use a line chart. If you have more series, this might be more effective, because markers at small values stand out. In a bar chart, shorter bars may be overshadowed by taller ones.

Line Chart

Of course, some purists warn that line charts are evil for categorical axes, because the connecting lines may lead to inadvertent interpolation of values and trends between plotted points. Blah blah blah. If the categories are sorted into some kind of logical order (e.g., by date, by increasing or decreasing values, or by categories that fall into a natural sequence), and if markers are used to signify individual data points, this argument becomes pedantic.

A third option is a panel chart, which separates each series into its own portion of the plot area of the chart. There is no longer any concern about too many series as there would be in a column chart, nor about interpolation between actual data points as in a line chart. On the other hand, it’s not immediately obvious that the fourth bar of each series share the exact same value.

Panel Chart (Separated Stacked Column Chart)

Excel doesn’t have built-in panel charts like this, but they are not hard to make if you follow a simple protocol.

Building the Panel (Separated Stacked) Chart

Add Spacer Data

The panel chart shown above is basically a stacked column chart, with invisible columns between visible data. The built-in vertical axis labels are hidden, and dummy XY series provide the desired labels. The invisible columns make sure each set of columns is positioned in its own panel. Since each of our panels is 10 units high, the data plus the spacer must sum to 10, so our blank data is simply the difference between 10 and the preceding value. You need a blank series for each series of columns except the last.

Blank Series Data

Starting from the clustered column chart shown above as the first alternative to a 3D chart, copy the data, select the chart, and use Paste Special to add the blank data to the chart.

Clustered Column Chart with Blank Data Added

The new data is added after the existing data. Reorder the data, by rearranging the items in the list of series in the Select Data Source dialog…

Reorder Series in the Select Data Source Dialog

…or by changing the last argument in the SERIES formula in the formula bar.

Reorder Series in the Formula Bar

Now the “real” data is interspersed with the blank columns.

Clustered Column Chart with Blanks Between Data Columns

Change the chart type to Stacked Column. This could also have been done in the first step, by choosing Stacked Column in the Insert Chart dialog.

Short Stacked Column Chart

Starting from scratch (often a better approach), intersperse the “real” data with the blank data in the worksheet (below), and create the same stacked column chart.

Chart Data with Blank Data

That chart is a bit squat, so stretch it vertically.

Tall Stacked Column Chart

Format Real and Dummy Axes

Change the scale of the vertical axis: make the minimum zero, the maximum 30, and the major unit 10, and make sure all of these are not automatic settings, so resizing the chart doesn’t mess everything up. Hide the vertical axis labels.

Format the horizontal axis so it uses no line.

Use a darker gray for the major horizontal gridlines, and insert light gray minor horizontal gridlines.

Format Axes and Gridlines

Now we need some data for our dummy axes. The explanation below shows how we derived the values in the table below.

In a column chart, the categories are at X values equivalent to 1, 2, 3, etc. Each category uses the space from 1/2 category to the left to 1/2 category to the right of its equivalent X value, so the axis minimum is 1 minus 1/2 or 0.5, and the axis maximum is 5 plus 1/2, or 5.5. The X values of the dummy series will be 0.5 for the left-hand axis and 5.5 for the right-hand axis.

Axis 1 (the first dummy axis) has labels to the left of the bottom panel showing 0, 2, 4, 6, 8, and 10 at the actual Y values of 0, 2, 4, 6, 8, and 10. Axis 2 has the same labels to the right of the second panel but at actual Y values of 10, 12, 14, 16, 18, and 20. Axis 3 has these labels to the left of the top panel at actual Y values of 20, 22, 24, 26, 28, and 30. These actual Y values are used to plot the dummy points that will show the desired labels.

Data for XY Dummy Axes

Copy the Axis 1 data, select the chart, and from the Home tab, choose Paste Special from the Paste button dropdown (left). Use the Paste Special options shown at right.

Paste Special Command and Dialog

The series is pasted as another column, stacked upon the rest.

Paste Data for First Dummy Axis

Right click on this new series, choose Change Series Chart Type, and select the XY Scatter type. Excel adds secondary X and Y axes for this new series.

Change Dummy Series Chart Type to XY

Format the XY series so it’s plotted on the primary axis. In Excel 2013 you can do this right from the Change Series Chart Type dialog.

Plot Dummy XY Series on Primary Axis

Copy the Axis 2 data, and use Paste Special to add it to the chart (left). New series are added using the same chart type and axis group as the most recent added series was changed to, which saves a lot of time. Repeat the Copy-Paste Special sequence with the Axis 3 data (right)

Paste Data for Second And Third Dummy Axes

Select Axis 1 and add data labels. Use the Plus icon in Excel 2013, or use Chart Tools > Layout tab > Data Labels in Excel 2007-2010. Use the Left position and the Show Y Values display option. Don’t worry that the labels are smooshed on top of the data points; we’ll fix that shortly.

Add Data Labels to First Dummy Series

Select Axis 2 and add data labels (left). In Excel 2013, use the Plus icon to add the labels, then choose the Value From Cells option, and select the range containing the Axis 1 Y values (0 through 10). In Excel 2007-2010, add any old labels since there is no Value From Cells option, then manually edit the individual labels to display 0 through 10. Or you could use Rob Bovey’s (free) Chart Labeler add-in to assign data labels from worksheet cells. The labels for Axis 2 must be positioned to the right of the data points.

Select Axis 3 and add data labels (right) using the same steps as for the Axis 2 labels, except that the Axis 3 labels must be positioned to the left of the data points.

Add Data Labels to Second and Third Dummy Axes

Clean Up

Now we just need to tidy up a bit. First, decrease the width of the Plot Area so there is more room for the data labels that label the vertical axes.

Widen the Plot Area to Make Room for the Axis Labels

Format Axis 1, Axis 2, and Axis 3 so they show no markers (left).

Format Blank 1 and Blank 2 so they use no fill color (right).

Hide Markers for Dummy XY Series and Fill for Blank Columns

Lighter colored bars seem to hide the major horizontal gridlines that serve as horizontal axes for the panels. If you give the columns a border of any color and a transparency of 100%, the gridlines are somewhat more visible.

If this isn’t good enough, you could muck around with horizontal error bars, or make the colored bars slightly shorter and the transparent spacing bars slightly taller, so the bottoms of the colored bars are slightly higher than the gridlines.

Finished Panel Chart (Separated Stacked Column Chart)

You aren’t limited to separated stacked columns. You could make the original stacked chart using either stacked area (left) or stacked line (right) chart type.

Stacked Area and Line Versions of the Panel Charts

You don’t need to format the plotted series with different colors, because they are located in separate panels. You can avoid the pitfalls of color and simply use the same gray for all plotted data, and use text labels for identification.

Single-Color Versions of the Panel Charts

 

Peltier Tech Charts for Excel

Series Lines: Useful or Chart Junk?

Stacked column charts in Excel offer a feature called “Series Lines”. These are lines between the stacks, connecting the tops and bottoms of corresponding blocks in adjacent stacks.

Stacked Column Chart with Series Lines

At first glance, these lines seem useful, because the lines will converge or diverge depending on changes in value of a series from stack to stack.

Unfortunately, the differences in slope is not really observable unless it is substantial. There are better ways to display this data.

The Data

Here is the data used in this example.

Data for this example

Standard Charts

Let’s start with the simple stacked column chart below. We can readily see how the total varies from year to year: a steady increase from 2005 through 2008, a plateau between 2008 and 2009, and a drop in 2010. We can also judge variation in series “alpha”, because this series starts at the base of the chart.

Stacked Column Chart

Series “beta” is harder to judge, because its blocks are added to the staggered baseline formed by the tops of the alpha blocks, and the baseline is staggered further. Comparisons between points in different series is also made difficult by the lack of a single baseline for the bars.

We can add the series lines to connect endpoints of corresponding points. These lines may help judge changes in a series from year to year if the changes are large enough, but they don’t reset any staggered baseline to a straight line. They also do not help at all with comparisons of different series. In fact, the lines may guide your eyes away from the comparisons you are interested in.

Stacked Column Chart with Series Lines

Series lines add useless elements to the chart. This is the definition of chart junk.

Feeble Improvement Attempts

Let’s give the data a common baseline by unstacking the points. Now we’ll have an easier time comparing lengths of the bars, because the different lengths only move the tops, not the bottoms, of the bars.

Clustered (Grouped) Column Chart

There is a problem with this display: it is too cluttered to read easily. We can easily compare the bars within a given year, but when we compare from year to year, the bars in between interfere with the comparison. This is especially problematic with the shorter bars.

If we convert the bar chart to a line chart, we’ll remove the vertical rectangles of ink that interfere with our comparisons. Now there is a different problem. The overlapping of points makes it difficult to trace a series from left to right. Even the different colors don’t help a lot, and if you are looking at a photocopy, you’re lost.

Line Chart

A Better Approach

Well, we don’t need to overlap all of the data. We can plot each series in its own stretch of horizontal space, so each series occupies its own panel. Yes, it’s a panel chart, even if we don’t have lines between all of the panels. It’s also similar to a type of chart known as a cycle chart.

Multiple Series Panel Style Chart

We can see immediately that gamma has an overall downward trend and epsilon a similar upward trend. This was not at all visible in the earlier charts. We can also clearly see that the general amplitudes of alpha and beta are similar, as are those of gamma, delta, and epsilon. We could have seen this in the stacked charts, if we’d squinted long and hard enough. That’s too much work; the panel chart is so much easier.

The panel chart is not too hard to create. It’s really a simple line chart. What’s complicated is the data layout, shown below. The data is spread out vertically so the blocks of data do not overlap. Each blue shaded cell is completely blank (not a formula returning a null string, but a completely empty cell). The tan shaded cells contain space characters.

The two blank cells in the top left tell Excel that the top row should be used for series names and the left two columns should be used for category axis labels. The blank cells between series names in the first column tell Excel to center the labels across the blank ranges. The space characters keep the year labels from moving out of place. I hid all but the first and last year labels, since these two were sufficient to describe the data.

Expanded Data for Multiple Series Panel Style Chart

We actually don’t need different formatting for the different series, since they are separated in the chart. We can use a single column to define our data range (keeping the same category axis data, with its blank cells and space-containing cells).

Expanded Data for Single Series Panel Style Chart

The resulting chart shows our data as clearly as the previous one. Adhering to a single format makes the chart a bit cleaner.

Single Series Panel Style Chart

Obviously this chart does not include the totals for each year. We could include them in this chart, but that has the undesirable effect of shrinking the individual series, reducing the resolution.

Single Series Panel Style Chart With Totlas

There’s a radical approach to showing both the individual series and the totals. It doesn’t involve mucking around with dual axes, which cause more confusion than anything else. It doesn’t involve any kind of tricks to force all the data into the same chart. What is the radical approach?

Two charts.

Use Two Charts to Show Two Bits of Data

The first chart shows the individual values, in the panel style chart above. The second chart shows the total values, I used a stacked bar, but you could have used a line chart of the totals without its constituents. I’ve revived the colors for the line chart on the left, so the lines can serve as a legend for the colored blocks on the right.

What do you think of this approach?

 

Peltier Tech Charts for Excel

Broken Y Axis in an Excel Chart

If you’re looking for a tutorial on breaking an axis scale, you won’t find it here. Instead you’ll read why breaking an axis is a bad idea, and you’ll get a tutorial in Panel Charts, which are a more effective (and easier) means to show your data.

Chart with Broken Y Axis

The Problem

People frequently ask how to show vastly different values in a single chart. Usually they ask because a few very large values (for instance, Paris in June or Madrid in May in the chart below) overwhelm the other, relatively much smaller, values.

Chart with Unbroken Y Axis

Logarithmic Scale

One suggestion is to use a logarithmic scale. For scientific data presented to scientific audiences, this is often an excellent suggestion. For the general public, and for general data, this may not be so useful. Especially in a bar chart, where the length of bars is important to comprehension, not some mathematical abstraction of length.

Chart with Logarithmic Y Axis

Broken Axis

Another suggestion is to “break” the axis, so that part of the axis shows the small values, then another part of the axis shows the large values, with a section of the axis scale removed. Sounds good, but you’ve lost any correlation between the large and small values. Also our eyes are likely to see the two broken bars in the chart below as only about twice the value of the tallest of the unbroken values (despite our conscious brains “knowing” that the axis has been cut).

Chart with Broken Y Axis

Another problem with this approach is that it’s cumbersome to create and nearly impossible to maintain charts like this.

Panel Chart

A better suggestion than either a log scale or a broken axis is to plot the data in a panel chart. This chart has two panels, one with an axis that shows all the data, the other with an axis that focuses on the small values. I generally advise strongly against using any kind of gradient in a chart, because the gradients are pretty much meaningless. In this chart, the gradient at the tops of the (truncated) large values are not meaningless, but are intended to show the large values extending high up into the clouds.

Chart with Panels Having Distinct Y Axis Scales

Making the Panel Chart (It’s Easy!)

If you want to play along at home, the data is located in BrokenYData.csv.

Here is the data for the chart. Columns E, F, and G have the same data as columns B, C, and D, except the two very large values (>30 million) have been replaced by cut-off values of 7,500,000 (shaded cells).

Data for Panel Chart

The first step is to plot all of the data in one chart. By default, all series are plotted on the primary axis.

Panel Chart Step 1

The second step is to move the three extra series to the secondary axis. They block the primary axis data…

Panel Chart Step 2

… but if I format the secondary axis series with outlines and no fills, you can see the primary axis data.

Panel Chart Step 3

Back to solid fill colors. I have rescaled the vertical axes. The primary (left) axis now has a minimum of -40 million and a maximum of +40 million; the secondary (right) axis now has a minimum of 0 and a maximum of 16 million.

Panel Chart Step 4

Add the secondary horizontal axis. Excel by default puts it at the top of the chart, and the bars hang from the axis down to the values they represent. Pretty strange, but we’ll fix that in a moment.

Panel Chart Step 5

Format the secondary vertical axis (right of chart), and change the Crosses At setting to Automatic. This makes the added axis cross at zero, at the bottom of the chart.

(The primary horizontal axis also crosses at zero, but that’s in the middle of the chart, since the primary vertical axis scale goes from negative to positive.)

Panel Chart Step 6

Now we need to apply custom number formats to the vertical axes.

The primary (left) axis gets a format of 0,,"M"; (zero, comma, comma, and capital M within double quotes). Each comma knocks a set of three zeros off the displayed value, making for example 1,000,000 appear as 1. The M will be shown after the number of millions. The semicolon indicates that this format is for positive values, and nothing after the semicolon indicates that negative values are not to be shown. Since no special format is indicated for zero (which would be after a second semicolon), it is shown with the same format as a positive number.

The secondary (right) axis gets the trickier format of [<8000000]0,,"M"; (less than eight million enclosed in square brackets, zero, comma, comma, and capital M within double quotes). The first format in the string is normally for positive numbers, but square brackets indicate a non-default condition for the first string. This means that any values less than 8 million will appear as the number of millions folloewd by capital M. The semicolon with nothing following means that any other numbers will not be displayed.

Panel Chart Step 7

Now I’ve cleaned up a bit. I’ve used a medium gray line for the plot area border, and for both horizontal axis lines. I’ve also set the labels of the primary horizontal axis (center of the chart) to No Labels, because they are redundant and clutter up the chart. The primary and secondary axis scales conveniently have the right spacing so that the primary horizontal gridlines work for the secondary axis as well.

Panel Chart Step 8

Now I’ve applied the same fill colors to the secondary axis columns as are used for the primary axis columns.

Panel Chart Step 9

Finally I’ve formatted the two large values separately. To format just one point in a series, click once to select the series, then click again to select the particular point (column) to format.

I used a gradient that had white fill at 0%, and column’s regular fill color at 15% and at 100%. This gradient makes the bars extend upward, and fade as they reached into the clouds.

Panel Chart Step 10

Finally I deleted the duplicate legend entries. To delete an unwanted legend entry, click once to select the legend, then click again to select the particular legend entry, then press the Delete key.

Panel Chart Step 11

This is the finished panel chart. The top panel shows that the two outlying values are drastically larger than the others, while the bottom panel allows comparison between the smaller values.

 

The Final Word

I know everybody’s case is special, and everybody knows better than I do about why using improper techniques is correct in their particular situation. Your boss needs it this way, or it’s a specialized scientific chart, or you don’t see how anybody could be confused, or it’s really really important. However, I am under no obligation to share something that I do not want to share. I do not even have the old tutorial, so I cannot send it to you, nor will I recreate a new version of the tutorial. 

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0