Column Charts

Column Chart with Category Axis Labels Between Columns

Friday, August 21, 2015 by Jon Peltier 7 Comments

Friday, August 21, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

In Stacked column chart in Excel with the label of x-axis between the bars, a SuperUser user posted this chart, asking how to get the labels between the columns, instead of under the columns, where Excel puts them. This is a great tutorial, so I’m repeating my answer here.

We’re going to use a dummy (hidden) XY series on the chart, whose data labels will become the labels we want our axis to display.

We’ll start with two sets of data. The first (below left) is the data for the stacked columns, using simple counting numbers for the categories. The second (below right) is XY data where X is the list of values where we want our labels, and Y is zero.

Select the first set of data and insert a stacked column chart.

Copy the second range, select the chart, and use Paste Special (Home tab of the ribbon > Paste button dropdown > Paste Special), and select the following options: Add data as series, series in columns, series names in first row, categories in first column.

The new series is added as a third set of stacked bars, which don’t show up because their height is zero.

Select the added series by selecting the green bars and clicking the up arrow key. Click the menu key (between the right Alt and Ctrl buttons on most Windows keyboards) or hold Shift and click the F10 function key to pop up the context menu. Click Change Series Chart Type, and choose XY Scatter. This adds a set of markers along the bottom of the chart (I used blue circles in the chart below) and it adds secondary X and Y axes.

Format the scale of the secondary horizontal axis (top of chart) so it fits the data: min = -115, max = -50. Note that the blue circles are now aligned between the bars, where the labels will go.

Hide the secondary X and Y axes by formatting their label position as No Label, and their line color as No Line. This hides the labels and reduces the margin around the chart that previously held the labels.

Hide the primary horizontal labels by using a custom number format of ” ” (that’s right, a space surrounded by double quotes). This hides the labels but keeps the margin in place for the other labels we’re going to add.

Right-click the series of blue dots, and choose Add Data Labels. Excel adds the default Y values (zeros) to the right of the markers.

Format the labels so they are in the Below position, and so they show the X values instead of the Y values.

Finally format the series of dots so they use no markers. And we’re done.

This technique can be used to customize axis labels and add many other labels to your charts.

Posted: Friday, August 21st, 2015 under Chart Axes.
Tags: Axis Labels, Column Charts, Combination Charts, dummy series.
Comments: 7

Multiple Width Overlapping Column Chart

Thursday, December 12, 2013 by Jon Peltier 32 Comments

Thursday, December 12, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

I read a post entitled Calling All Graph Wizards – Overlapping/Stacking Graphs w/o secondary axis on the Mr Excel forum today, and decided the question was broad enough and the answer quick and elegant enough that it was worth sharing.

The problem was that the user wanted to show projected and actual values of one variable as columns on the primary axis and of another variable as lines on the secondary axis.

If you only have the one variable, you can plot projected on the primary axis and actual on the secondary axis, then use a smaller gap width (wider bars) on the primary axis and a larger gap width (thinner bars) on the secondary.

Dual Column Width Chart Using Primary and Secondary Axis

But you can’t use two gap width settings if the columns must be plotted on the same axis. When you plot them, the taller bars in the front obscure the shorter bars in the back, so you can’t compare the values.

Mono Column Width Chart Using Only Primary Axis

But you can still make Excel do what you want. (In fact, you can almost always make Excel do what you want, if you know how.) I’ll describe two ways to accomplish this.

Option 1: Fill Bars with Rectangular Shapes

This approach was the original topic of this tutorial.

Draw two rectangles, pretty tall. The taller the better for visual quality of the chart. Make the thinner rectangle the color you want, and make the thicker one transparent. Make the widths of the two rectangles in the same proportion as the widths you want for the bars in the chart. Center the two rectangles horizontally.

Rectangles for Dual Column Width Chart

Select both rectangles, and copy (Ctrl+C). Select the series in the chart, and paste (Ctrl+V). The chart now uses the copied shapes as the fill for the selected series.

Dual Column Width Chart Using Only Primary Axis

Pretty easy, once you know how.

This comes in handy too if you need more than two widths. Without this trick, you couldn’t make the following chart even if you could use the primary and secondary axes:

Triple Column Width Chart Using Only Primary Axis

This requires two pairs of rectangles, a clear one and a relatively wide filled one for the second series, and a clear one and a relatively narrow filled one for the third. These are shown below:
Pairs of rectangles for bar chart fills

The advantage of this approach over the next is that the narrower bars keep their relative width, which is fixed by the ratio of filled rectangle to transparent rectangle used to fill the bars. The disadvantage is that if you want to adjust the width of the narrower bars, you need to adjust the width of the rectangles, then copy and paste onto the chart series.

Option 2: Error Bars with Multiple Widths

My colleague Andy Pope has pointed out in the comments that another approach for this effect is to use error bars for the narrower bars. In Excel 2003 and earlier, you had few options for line width, but since Excel 2007, you can make lines of seemingly any arbitrary thickness.

I’ll show Andy’s technique for three sets of bars. It’s even easier for two sets of bars.

I’ll start with the original column chart, setting overlap temporarily to zero so the different sets of error bars don’t obscure each other. The first thing to do is hide the bars you want to display narrower, that is, use no fill color for them. I’ve kept a colored outline to show what’s going on.

Using Error Bars for Multiple Width Chart Series Bars

Add error bars to the series you want to show as narrower bars.

Customize the error bars using the Minus Only, No Caps, and 100% Percentage value options.

Using Error Bars for Multiple Width Chart Series Bars

Now comes the magic. Apply the desired line colors to the error bars, and make the error bar lines thicker. Here I’ve used 11.25 pt for the orange bars and 5.75 pt for the blue bars.

Then I’ve hidden the outlines of the original bars.

Using Error Bars for Multiple Width Chart Series Bars

Change the overlap back to 100 so the bars are all centered on the categories (the month labels).

This has made the unchanged bars for Plan much wider, so we need to adjust the line widths of our error bars. I’ve settled on 30 pt for the orange and 12 pt for the blue.

Using Error Bars for Multiple Width Chart Series Bars

I used a little trick to reapply the bar colors to the legend. When I made the bars transparent, I started with the entire series formatted with the desired color. Then I selected one bar at a time instead of the entire series, and used no fill for the bar. Working point-by-point in this way leads Excel to believe that the series as a whole has not been changed, so it leaves the legend entries alone.

The disadvantage of the error bar approach is that any reformatting that changes the widths of the original bars (changing overlap, adding points to each series, stretching the chart) will distort the relative widths of the error bars and the original bars.

The advantage is that these widths can be adjusted very easily by formatting the error bar lines, without having to fiddle with the widths of the rectangles which must then be copied and pasted onto the series bars.

Posted: Thursday, December 12th, 2013 under Formatting.
Tags: Column Charts, Combination Charts.
Comments: 32

Microsoft MVP Logo

Column Chart with Primary and Secondary Axes

Monday, October 28, 2013 by Jon Peltier 32 Comments

Monday, October 28, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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.

Posted: Monday, October 28th, 2013 under Chart Types.
Tags: Column Charts, Panel Charts, Secondary Axis.
Comments: 32

Microsoft MVP Logo

Histogram Using XY and/or Area Charts

Thursday, October 24, 2013 by Jon Peltier 1 Comment

Thursday, October 24, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

The default category labels in Excel column charts produce histograms that don’t look very good.

Typical Excel Histogram with Ugly Labels

In Histogram with Actual Bin Labels Between Bars I showed a column-line combination chart that centered bin edge values between the bars of a histogram.

In Histogram on a Value X Axis Scale I showed a column-XY combination chart that simulates a horizontal value axis with markers that resemble tick marks and data labels.

In this tutorial I will go one further and show how to plot your histogram on a value-type horizontal axis.

XY Chart Histogram

The table below summarizes fifty random values between 4.95 and 6.45. The gold shaded cells contain the list of bin values, which are the values at the top of each bin. The blue shaded cells contain the results of Excel’s FREQUENCY function, which tells us the number of values below the first bin value, the number of values between each successive pair of bin values, and the number of values above the top bin.

We can expand the range of bin values to show the overall upper and lower limits of the data.

We can stretch the range by inserting blank rows in the frequency table.

Finally we fill in the blank cells with the coordinates of the outlines of each histogram bar. For example, the first bar is drawn by starting at {4.95, 0}, drawing a line up to {4.95, 4}, across to {5.10, 4}, and down to {5.10, 0}. The second bar starts at {5.10, 0}, goes up to {5.10, 5}, across to {5.25, 5}, and back down to {5.25, 0}.

Expanded Histogram Data Populated for XY Chart

Select the range above and insert an XY Scatter Chart, using the lines without markers option.

Rescale the horizontal axis, and you’ve got your XY chart histogram.

XY Chart Histogram with Adjusted X-Axis Scale

This is a pretty easy chart to generate, if you don’t mind the duplicated data, and if you don’t need filled bars in your histogram.

Area Chart Histogram

The XY Chart Histogram is simple, and probably suitable for most tasks. If you want the bars to be filled, the XY chart won’t do it, but you can use an area chart.

To get vertical edges of the histogram bars, you need to use an area chart with a date axis. A date axis has a resolution of 1 day, and our scale requires decimals, so I took the XY chart data range above, and multiplied the X values by 1000. I chose 1000 instead of just 10 because I can use a number format of “0.00,” to display 4950 as 4.95.

Here is the data for our Area Chart Histogram.

Expanded Histogram Data Populated for Area Chart

Select this range and insert an area chart.

This has no border and a saturated fill. Lighten up the fill and apply a border using the original fill.

Ah, less intense on our eyeballs. (My daughter, a PhD student in the field of vision and cognition, insists that the word “eyeball” is much funnier than simply “eye”.)

Those truncated pyramids are reminiscent of those awful 3D charts. Make them into proper bars by formatting the horizontal axis, and choosing the Date Axis option.

If the axis labels are converted to dates, you can change them back by changing the number format to “0”.

To get a similar axis range to that in our XY Chart Histogram, change the minimum and maximum of the horizontal axis scale. In former versions of Excel, you could enter whole number values like 4500 and 7000, but that was broken at some point along the way. You need to enter the dates that these values represent. To determine these, enter them into two cells, and format the cells as dates. So enter 4/26/1912 and 3/1/1919 as the axis minimum and maximum.

Format the horizontal axis and change the number format to “0.0,” (without the quotes). The comma divides the value by 1000, and the result is displayed with one decimal digit.

You can add light gray vertical gridlines if desired.

Notice how the area chart histogram originally used the smallest and largest X values as the horizontal axis limits? We can make use of this automatic scaling behavior by extending our input data by one row in each direction, and inserting the desired minimum and maximum axis scale. This avoids the need of manually scaling the axis, and of determining the corresponding dates for the scale.

As above, select this range and insert an area chart.

Lighten up the fill and apply a border with a darker color.

Format the horizontal axis, and choose the Date Axis option.

There’s no need to rescale the axis, so change the number format to “0.0,” (without the quotes) and add vertical gridlines if desired.

The only problem with extending the data sideways is that there is a thin line that extends to the ends of the axis. You can fix that by reducing the border width from the 1.5 default to 0.75.

If you need the thicker border, use the first approach. Otherwise using the data range that includes horizontal axis endpoints is easier.

Area-XY Combination Histogram

You can make a filled histogram using an XY-Area Combination Chart. The combination part makes it more complicated, but an XY chart has more descriptive chart tooltips.

The area chart above displays the series name and point label when the cursor passes over a point. The XY chart below also displays the actual X and Y values.

Start with the XY Chart Histogram from the first section of this article.

Copy the expanded data range for an area chart; you can use either range, but the range with added first and last points that match the intended axis scale limits will be easier. Select the chart, go to the Home tab of the Ribbon, click the down arrow on the Paste button (the Copy button in older Excel ribbons), and choose Paste Special. Add the data as a new series with rows in columns and with category (X) labels in the first column.

Format this added series, and assign it to the secondary axis.

Use the plus icon in Excel 2013 or the Chart Tools > Layout tab > Axes dropdown to add the secondary horizontal axis.

Right click on the new series, choose Change Series Chart Type from the pop-up menu, and select an area chart. Lighten up the fill color.

Format the secondary (upper) horizontal axis, and change the axis type to Date Axis. Depending on which data range you used, you may have to enter appropriate axis scale limits.

Select the secondary (upper) horizontal axis. Format it so it has no line, no markers, and no labels.

Select the secondary (right) vertical axis, and press Delete.

This chart has the filled appearance of an area chart with the informative chart tooltips of the XY chart.

More About Histograms on the Peltier Tech Blog

Posted: Thursday, October 24th, 2013 under Chart Types.
Tags: Area Charts, Column Charts, Combination Charts, Histograms, Statistics, XY Charts.
Comments: 1

Microsoft MVP Logo

Histogram on a Value X Axis

Tuesday, October 22, 2013 by Jon Peltier 2 Comments

Most histograms made in Excel don’t look very good, because the category labels describing the bins are centered below the bars.

In Histogram with Actual Bin Labels Between Bars I showed technique that centered bin edge values between the bars of a histogram.

Much nicer.

In this tutorial I will go one further and show how to plot your histogram on a value-type horizontal axis.

Data for Histogram and Labels

Fifty random values between 4.95 and 6.45 are summarized in the table below. The gold shaded range is the list of bin values, corresponding to the value at the top of each bin. The blue shaded range is the result of the FREQUENCY function, which tells us the number of values below the first bin value, the number of values between each successive pair of bin values, and the number of values above the top bin.

We can expand the column of bin values so we know the overall upper and lower limit of the data.

By inspection of these upper and lower limits, coupled with decades of experience in labeling chart axes, I will use the following values for my histogram chart axis labels. The first column are the values, the second column is a bunch of zeros, because I will plot these points along the bottom of the chart, and hang the axis labels on them.

Constructing the Histogram

Start by selecting the blue range in the data range above. Ignore any of the other data.

Histogram with Value X Axis Scale - Step 1

Those are pretty spindly bars for a histogram. Change the gap width to a small number (10-15% works well). Purists will make their gap widths zero, but then you need a border around the bars. We’ll let the thin white gap serve as our border.

Histogram with Value X Axis Scale - Step 2

Copy the labels data (the last data range above), select the chart, go to the Home tab of the Ribbon, click the down arrow on the Paste button (the Copy button in older Excel ribbons), and choose Paste Special. Add the data as a new series with rows in columns and with category (X) labels in the first column.

Histogram with Value X Axis Scale - Step 3

Doesn’t look like much, but the values were zero, so the bars have no height. They did push the histogram bars aside to make room, though.

Select the added series (select the histogram bars and press the up arrow key), click the menu button (between the Alt and Ctrl buttons to the right of the space bar) to pop up the context menu, and choose Change Series Chart Type. Select XY Scatter with markers and no lines.

Histogram with Value X Axis Scale - Step 4

Excel automatically puts this XY series onto the secondary axes, and draws the axes for us. Select the secondary (right) vertical axis, and choose Horizontal Axis Crosses at Automatic Position (zero).

Histogram with Value X Axis Scale - Step 5

Select the primary (left) vertical axis, and choose Horizontal Axis Crosses at Maximum Axis Value.

Histogram with Value X Axis Scale - Step 6

Select the primary (left) vertical axis and press the Delete key, to turn those histogram stalactites into stalagmites.

Histogram with Value X Axis Scale - Step 7

Format the secondary (bottom) horizontal axis, and choose Vertical Axis Crosses at Automatic Position (zero).

Histogram with Value X Axis Scale - Step 8

Format the primary (upper) horizontal axis so it uses no labels, no tickmarks, and no line.

Histogram with Value X Axis Scale - Step 9

Format the secondary (horizontal) axis scale so its minimum and maximum are the same as the outermost bin limits, that is, 4.95 and 6.45. If the axis has visible tickmarks, change the tickmark settings to None.

Histogram with Value X Axis Scale - Step 10

If you hide the secondary (horizontal) axis tick labels but choosing None for labels, Excel will remove the space below the chart for these labels. Instead, change the number format of the secondary (horizontal) axis to ” ” (three spaces within double quotes) to preserve this spacing.

Histogram with Value X Axis Scale - Step 11

Add data labels to the XY series. Use the Below option for position and X Value option for what the label contains.

Histogram with Value X Axis Scale - Step 12

Finally, change the marker of this XY series to small plus symbols, and use a gray color that matches the axis line. These plus symbols now mimic axis tickmarks. Unfortunately there is no easy way to add tickmarks outside the plot area, which is my preference and should be yours, but we’ll put up with tickmarks that cross the axis just this once.

Histogram with Value X Axis Scale - Step 13

And our new histogram is finished.

More About Histograms on the Peltier Tech Blog

Posted: Tuesday, October 22nd, 2013 under Chart Axes.
Tags: Column Charts, Combination Charts, Histograms, Statistics, XY Charts.
Comments: 2

Microsoft MVP Logo

Column Charts

Option 1: Fill Bars with Rectangular Shapes

Option 2: Error Bars with Multiple Widths

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

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

What People Really Should Use: Panel Chart

Adding Data

XY Chart Histogram

Area Chart Histogram

Area-XY Combination Histogram

More About Histograms on the Peltier Tech Blog

Data for Histogram and Labels

Constructing the Histogram

More About Histograms on the Peltier Tech Blog

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

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

What People Really Should Use:
Panel Chart