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

A B C D E F
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

 

Step-by-Step





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





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





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





 

Steve=True





2 COMMENTS

  1. Nicely done Don. I was making it more complicated than it had to be. My only concern with your method, depending on the data and how often it changes, is that you will have to manually match both of the vertical axis scales manually. The only way that I have found to do it automatically is with VBA.

  • Hi Pete, I noticed that after I created the video. Although, we could simply delete the right vertical axis and then they should line up correctly. Steve

LEAVE A REPLY

Please enter your comment!
Please enter your name here