Create a column chart using a background picture

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  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:

background photo chart

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:

dummy data

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.

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

second series

Then I needed to change the chart type to a stacked column chart.

stacked column

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.

2 series

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.

white border

Then I changed the fill to “No fill”.

no fill

Finally, I changed the fill of the red series to white.  This then, became my initial draft…

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?

final draft

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!


Alesandra Blakeston

8 thoughts on “Create a column chart using a background picture

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s