(613) 818-2848

Excel Tricks for Data Viz #2

Again, I don’t know why Excel’s default setting for the width of a bar chart’s bars compared to the spaces between is the way it is. Suffice to say it isn’t ideal. The overview of what is going on in the chart is much easier to grasp if the bars are a bit wider, at least equaling the width of the gaps between, or perhaps slightly more. It’s also just more aesthetically pleasing.

Skinny bar chart

fatter bars


To do this:

Control-click on any bar;

Select “Format Data Series” from the pop-up menu;

Under “Series Options”, adjust the Gap Width slider to about 75%.

OK, that’s mildly interesting.

But hold on; the gap width adjustment and the secondary axis option that we looked at last time are the key to several of the important visual data charts.

In this case, instead of representing two measures over a  series of categories with a clustered bar chart, as usual, we’d like something better. In particular, we’d like a bar-in-bar chart, that makes it easier to see the change in both variables simultaneously (and can save space).


clustered bars


To do this:

Instead of selecting the clustered bar chart to insert, select the stacked bar chart. At first this will look odd, as one variable is stacked on top of the other;

Control-click on one of the series, select “Format Data Series” from the menu and put the series on the secondary axis;

Still not quite there. Now select one of the series, and using the formatting menu as in the first bar chart example, change it’s gap width, but this time to about 300%, in order to narrow the bar;

Choose colours, using “Fill” in the formatting options that are easily differentiated but neither bright nor clashing. If you use a faint shade, as the grey is in this example, use the series formatting options to add a slightly darker line around the bar.


bar in bar


Well, now, isn’t that better?