Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

Combine Clustered Columns with Clustered Lines.


 

Line-Column Combination Chart

When you create a combination chart with clustered columns and lines, the lines do not cluster the way the columns do. The markers for the line series all line up over the middle of the cluster of columns they represent. For example, start with this data.

  A B C D E F G
1   Columns Lines
2   Red Green Blue Red Green Blue
3 Alpha 4 5 9 4 5 3
4 Beta 5 6 8 6 4 4
5 Gamma 4 9 8 5 8 5

Using the first column (A) as category labels, we can create a combo chart with three column and three line series. First select A2:G5 and create a column chart of all six series.

Right-click on one series you want to switch, choose Chart Type from the popup menu, and select a Line series with markers and lines.

Repeat for the other two series you want to switch. If you don't do anything else after converting one series, you can select the next series and press the F4 key to repeat the last action.

But wouldn't it be a better chart if the Red markers of the line series lined up with the red columns, and the blue markers with the blue columns? The greens line up, but only because there are an odd number of each type of series.

Scatter-Column Combination Chart

My first thought when I attacked this puzzle many years ago was to replace the line series by my favorite XY Scatter type series. So I changed the chart type of the lines to XY. But there was no change in the appearance of the chart.

I realized that the markers of the scatter series lined up the way the markers of the line series had, because I had not changed the data used for the category labels. In fact, using text labels like these, Excel assumes the simple array {1, 2, 3}. So I gave each series its own X values, as shown in the table below. For the Green series, the array of {1, 2, 3} is fine, but I need to offset the Red XY series left and the Blue XY series right. I could subtract or add 0.25 to effect this offset. A different offset is required for a different number of clustered column series, or for a different gap width.

  A B C D E F G
7   Scatter
8     Red   Green   Blue
9 Alpha 0.75 4 1.00 5 1.25 3
10 Beta 1.75 6 2.00 4 2.25 4
11 Gamma 2.75 5 3.00 8 3.25 5

Start by making a clustered column chart using the first three series of data.

One by one, copy the data for each XY Scatter series; for the red series, copy the range B8:C11. Select the chart, and from the Edit menu, select Paste Special, and select the Add New Series, Series Names in First Row, and Category (X) Labels in First Column options. Do NOT select Replace Existing Categories. Repeat for Green and Blue, copying in turn ranges D8:E11 and F8:G11, and using Paste Special as above.

Right-click on one series you want to switch, choose Chart Type from the popup menu, and select an XY Scatter series with markers and lines. Don't worry about the secondary axes that Excel adds to the chart for the Scatter series, we'll fix that in a minute.

Repeat for the other two series you want to switch. If you don't do anything else after converting one series, you can select the next series and press the F4 key to repeat the last action.

Right click on the chart, and choose Chart Options from the popup menu. Click on the Axes tab, and uncheck both secondary axis checkboxes. Excel figures out how to replot the scatter series points. You might need to adjust the offset for different combinations of gap width and number of column series. For my combination of three column series and a gap width of 100, the offset should be 0.25. For three columns and the default gap width of 150, the offset should be 0.2222 (i.e., 29). A little arithmetic should help you determine the offset for your specific case.

Line-Column Combination Chart with Sparse Data Range

There's an alternate way to produce this chart appearance, by combining line chart series and stacked column series, using a sparsely populated range in Excel. The range is shown below. The blank rows produce gaps between clusters of stacked columns. The spacebar characters in the first column of data help Excel figure out where to start the category (X axis) label range. The top left cells should be completely blank (indicated by [blank]) the other apparently blank cells in column A should contain spaces (indicated by [space]).

  A B C D E F G
1 [blank] Columns Lines
2 [blank] Red Green Blue Red Green Blue
3 [space]            
4 [space] 4     4    
5 Alpha   5     5  
6 [space]     9     3
7 [space]            
8 [space] 5     6    
9 Beta   6     4  
10 [space]     8     4
11 [space]            
12 [space] 4     5    
13 Gamma   9     8  
14 [space]     8     5
15 [space]            

Select this entire range (A1:G15), and create a Line chart with all of the series. (If you start with a Column chart, you will lose the ability to interpolate over the gaps in a later step.)

With the chart selected, pick Options from the Tools menu. Click on the Chart tab, select the Interpolated option for Plot Empty Cells As. This connects the points with lines, even though there are gaps in the data.

Right-click one series you want to switch, and select Chart Type from the popup menu. Choose the Stacked Column type.

Repeat for the other two series you want to switch. Remember, choose Stacked Columns rather than Clustered Columns. (The F4 key, repeat last operation, doesn't work here; after changing the first series to a stacked column, the F4 key converts the second--and the first--to clustered columns. After there are two stacked column series, the F4 key will correctly convert additional series to stacked columns.)

Finally, double-click one of the column series. On the Options tab, change the Gap Width to 0. This widens the adjacent columns so they touch one another.

 

Page copy protected against web site content infringement by Copyscape

Peltier Tech Chart Utility

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


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2013. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile