Remove Excel Chart Whitespace from Empty or Zero Columns (Part 1)

Remove White Space from an Excel Chart Part 1

Hi everyone.  In the last Friday challenge, we had a user post this question about an Excel Column Chart:

Chart: Remove whitespace from empty (non-existent) columns

I’m trying to generate a graph from this table.
Excel 2012

1 s 1 4 8
2 16k ref 328.28
3 8 330.33 173.6 168
4 16 329.35 128.79 104.87
5 32 359.85 109.87 75.37
6 64 376.94 108.65 67.72
7 256k ref 439.44
8 8 446.86 250.14 244.29
9 16 457.7 183.56 155.98
10 32 509.38 155.3 109.77
11 64 549.9 154.2 93.99
12 4m ref 553.6
13 8 566.84 340.23 334.43
14 16 585.2 237.53 209.97
15 32 658.24 199.3 140.48
16 64 720.2 198.69 120.3

It turns out like this. image

This is almost what I need however there is an unnecessary whitespace for every blank cell. It’s more obvious when you look at the ref bars. I suppose Excel is creating a column of height 0 for every blank cell. I really need to change this behavior and remove those empty columns.  Can somebody help me?


Well I received a few responses.  But one was the best.

Don showed me the easiest way to solve this problem.  To be frankly honest, I was way way over complicating this issue for the user by totally rearranging the data.  However, this particular data set is custom made to Don’s solution and what I was thinking of would work in more complicated situations.  So lets cover Don’s easy solution.  I will post a Part 2 with a more complicated data set in a future Friday Challenge.


The Breakdown

1) Create Excel 2D Clustered Column Chart

2) Move the “ref” column to the 2nd axis



1) Create Excel 2D Clustered Column Chart

Select the data range from A1:F16image


Select the 2-D Clustered Column Chart from the Insert Ribbon under the Charts GrouNyah-Nyahimage

2) Move the “ref” column to the 2nd axis

Select the chart and then select the “ref” column data series (the blue one if you are not color blind).  Then press CTRL+F1 keys to bring up the Format Data Series dialog box and menu.  Then choose the Secondary Axis radio button to move the series to the secondary axis. image

It will now be centered and will appear to remove the white space.  All Done.  Very simple, very easy!

I really thought it was going to be harder than that.  I guess I should have looked at the data and also gave it a try.  WAY TO GO DON!!


Video Tutorial

Free Excel Chart Template File Download: Remove-White-Space-Part-1.xlsx