I saw this seemingly simple question from an Excel user but I spent a long time trying to figure it out without much success.
Can you figure out if it is possible to make this column chart in Excel? Give it a try and see if you can do it. (To be totally honest, I think I have seen a solution for this type of problem before, but haven’t worked this one out yet. So I hope it can be done)
Here is the Excel Challenge question:
Chart: Remove whitespace from empty (non-existent) columns
I’m trying to generate a graph from this table.
| 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.
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 behaviour and remove those empty columns.
Can somebody help me?
You can copy and paste the data table above into your sample Excel spreadsheet. Let me know in the comments below how you can solve this question.
If you want to submit your response, send me a comment and I will get your email address so that I can send you my email for your response.
Good Luck Excel Dashboard and Excel Chart Fans!
Steve=True
I imagine this can be possible through a secondary chart type/ axis but I’ll check. Fairly new to this 🙂
Thanks for trying it out. Good luck!
‘Format Data series’ – Select then right mouse on the ‘Ref’ data series.
Select ‘Plot Series On’ radio button ‘Secondary Axis.
Change the ‘Gap Width’ to approximately 85% or what ever looks good.
I forgot in addition…
Right mouse on Secondary Axis, and delete it.
The Columns will then align with the Primary Axis, but the ‘Ref’ data set will remain the same size without the large white space.
I forgot too. Thanks. Steve=True