Grid lines provide great help in understanding values in a chart. Here is a handy trick you can use in the next bar chart to spice it up.
Here is how you can get this type of chart (we will call it a brick chart)
- First we will make a regular bar chart
- Now, let us assume we want each brick to be of 5 units width. So we take another column in the worksheet and enter the value 1 twenty times. This will be a dummy series that we will add to the chart. Just copy these 20 cells and paste them in to the chart. (just press ctrl+c after selecting the dummy value cells, and then select the chart you made in step 1 and press ctrl+v)
- Now we will change the dummy series’ chart type from bar chart to column chart. Just select the newly inserted series in the chart and right click and select chart type. In the chart type dialog, change the type.
- Now the new chart will look like this. We will adjust the secondary axis parameters so that the columns will span the entire height of the chart. Just use the format axis dialog for this.
- Once the columns are of sufficient height, we will adjust their fill color to transparent (none) and line color to white. This will produce the following effect.
- Finally, remove the unnecessary chart junk like axes and you have a neat looking brick chart.
Download the excel brick chart template and play with it.
PS: making this type of charts is slightly difficult compared to normal charts. What we have done here is, we mixed two types of charts. These are called combination charts. We will explore more about this type of charts later.
6 Responses to “Brick Charts in Excel – an Alternative to Gridlines”
Now that's a neat trick!
Thank you so much, I will definitively use this 😀
best regards
I'm not sure adding the vertical grid lines adds value to the chart... I think you can get the same, if not better, effect by having solid bars or columns.
Maybe if the lines were minimized instead of being so obvious, it might be better.
This might fall into my category of chartjunk.
I agree with Tony. juiceanalytics.com has an example with more modest grid lines:
http://media.juiceanalytics.com/images/Columnwithoverlay.gif
I like the repeating shape that makes it easy to see values.
I prefer to paste a shape into data range, scaling the shape to a value.
@Peder: You are welcome 🙂
@Tony: They need not be vertical alone. For column charts you can have them horizontally. Anyways, These are merely an alternative to gridlines and can be used to get the brick effect. Personally I prefer thin grayish white gridlines for my charts and try to avoid them if I can.
@nixnut: thanks for the link. There are several alternatives to gridlines.
@WebGenii: welcome to PHD. Repeating shapes is another neat technique.
Please chandoo also show.example.of horizontal grid.lines for brick chart