Squeeze Columns Together (Squeeze Out Blank Columns).

If a clustered column chart is created from a range that has some blank values, the blanks will result in gaps between columns in the chart. Excel doesn't allow the existing columns to be squeezed together, but the data can be rearranged to eliminate these blanks, and dummy series can be added to fix the category axis.

Let's use the following data for this example:

            Category1    Category2    Category3
Series A        1            2            3
Series B        1                         5
Series C        5            2
Series D        6
Series E                                  4

A clustered column chart can be constructed from this data, but the blanks cells in the data range produce unsightly blanks in the chart.

Rearrange the data as shown in the table below, which eliminates the unwanted blanks, while adding blanks that produce the desired spacing between category groups.

             Series A    Series B    Series C    Series D    Series E	
blank
Category1        1
Category1                    1
Category1                                5
Category1                                            6
blank
blank
Category2        2
Category2                                2
blank
blank
Category3        3
Category3                    5
Category3                                                        4
blank

Rather than a Clustered Column chart, construct a Stacked Column chart, as shown below.

Fix up the chart a little bit. Double click the Category (X) Axis to format it, and on the Patterns tab, select None for Major and Minor Tick Marks and for Tick Labels. Double click any series to format it, and on the Options tab, change the Gap Width to zero.

Add a dummy series to simulate the desired ticks along the Category (X) Axis. Put the following data into an unused range of your worksheet. The X values in the first column were derived to place markers between groups of columns and at the beginning and end of the axis; the Y values of zero place the markers on the axis.

      Fake Ticks	
 0        0	
 6        0	
10        0	
15        0

Copy this range (including the blank cell to the left of 'Fake Ticks'), select the chart, and from the Edit menu, select Paste Special. For the options, select As New Series, Series Names in First Row, and Categories in First Column.

Select this new series; it's not visible, so select Series E and use the up arrow key, or select it from the Chart Objects drop down on the Chart command bar. On the Chart menu, select Chart Type, and select the XY Scatter type, with markers and no lines. A cross marker style with a size of about 7 makes a good fake axis tick mark. These are shown in the chart below in red for emphasis.

Add another dummy series to simulate the desired tick mark labels along the Category (X) Axis. Put the following data into an unused range of your worksheet. The X values in the first column were derived to center labels below groups of columns; the Y values of zero place the markers on the axis; the labels in the third column will become the new Category Axis labels.

        Fake Labels
 3          0          Category1
 8          0          Category2
12.5        0          Category3

Copy the first two columns of this range (including the blank cell to the left of 'Fake Labels'), select the chart, and from the Edit menu, select Paste Special. For the options, select As New Series, Series Names in First Row, and Categories in First Column. Since you have just added a series above and changed it to an XY Scatter type series, this new series also picks up the XY Scatter type.

Apply the labels to the Fake Labels series using the XY Chart Labeler, a free add-in written by Rob Bovey and available at http://www.appspro.com. When you install the Labeler, a new item 'XY Chart Labels' is added to the Tools menu. Select the chart, go to the Tools menu > XY Chart Labels > Add Labels. Choose the Fake Labels series, select the worksheet range containing the category labels, and pick 'Below' for Alignment.

Select the plot area of the chart, and drag the bottom edge upward to make more room for the labels. The labels and markers for this series are shown in blue in the chart below.

Now clean up the chart. Format the Fake Ticks as black crosses (not red), format the labels in black (not blue), use no markers for the Fake Labels series. Select the Secondary Category (X) Axis (top of chart), and press Ctrl-1 (numeral one) to format it. On the Patterns tab, select None for Major & Minor Ticks and None for Labels. Remove the excess Legend entries: single click twice on the text for 'Fake Labels', then press the Del key; repeat for the 'Fake Ticks' series.

SqueezeOutBlankColumns.zip contains a sample file and these instructions for squeezing spaces out of your column chart.

Peltier Technical Services, Inc.

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

Peltier Technical Services, Inc., Copyright © 2010. 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

My MVP Profile