Once again, I am posting a challenge I was sent while on holiday last week. This one was sent from a colleague, who like myself is a bug fan of www.chandoo.org. Apparently they had seen this on the chandoo site and wondered if I could do it, (without cheating!)…
Here’s the picture I was sent:
It’s a lovely photo of Boston, being used as background fill for the chart series. Since I had just come back from there, it seemed appropriate and a fun challenge, so off I went! I have to admit, my first attempt was rather feeble, but then I thought about it some more and managed to work it out. See what you think. You can download the example chart here.
First I set up some dummy data and created a normal column chart:
Then I added a background fill to the chart by clicking on the white background and pressing Ctrl+1. Then I chose picture fill, and added my picture as the chart background.
Imagine if you will a stacked bar chart, the current blue series changed to a transparent fill (so you can see the background) and a series above the blue one (using a second set of data) with a white fill to mask the background picture.
First then, I needed to calculate the size of the top series. My chart currently has a maximum value of 200 on the Y axis, so I needed to calculate the difference between the current value and 200 for each row of the table. So in cell C2, I wrote the calculation “= 200 – B2.” Then I copied and pasted this to fill each row of the table
Then I needed to change the chart type to a stacked column chart.
Next I needed to add the white series to the chart and adjust the Y axis to a fixed maximum of 200. By default, Excel made it red, but we will change this later.
Almost there! The rest really is just formatting. First I clicked on series 1 (blue) and pressed Ctrl+1 again. I adjusted the gap width to 0. Then I added a white 6pt border to the series. You’ll notice you can no longer see the background photo (at the moment) in the picture below. Eventually the white border will create the illusion of space between the columns.
Then I changed the fill to “No fill”.
Finally, I changed the fill of the red series to white. This then, became my initial draft…
However, I wasn’t quite done! I added data labels, got rid of the axes and here’s the finished product below. What do you think?
You can of course download the sample chart and data here. Beware though, adding pictures that are lively could detract from your data! You can see in the case above that sometimes the data is difficult to read!