Tony sends this chart and asks if it can be done in Excel.
It sounded like a good challenge for a lazy Sunday morning. So here we go. (Posting it on Monday).
Now I could not get an oil rig photo or that data. So I made up few numbers and used a photo of Flinders street station I took when I was in Melbourne last year.
Step 1: Arrange the data.
Arrange the data like this.
Step 2: Create a column chart
Select the data, insert a stacked column chart (why not a regular column chart?, you will understand in a minute).
You will get this.
Step 3: Set up image as background for chart’s plot area
Select chart’s plot area. Press CTRL+1.
Choose picture or texture fill and select the file with image you want.
Step 4: Add dummy max-series
In your data, add a column which gives the difference between column values & axis maximum. For our test data, I choose 1,400 as axis maximum, so the dummy series values are,
Now add this series to chart.
Step 5: Format the chart
Now, we are almost done. Our chart looks like below. We just need to format it.
- Select the columns (any series) and press 1
- Adjust gap width to 0%
- Fill the dummy series with a chosen background color.
- Make the data series transparent (fill color = no color)
- Add borders to data series. Border color should be same as background color.
- Adjust the border thickness to 3pts.
- Adjust axis maximum to 1,400 (or any value you have selected in Step 4).
- Remove grid lines, legend and any un-necessary chart fluff.
Your column chart with background image is ready!
Note of caution: Go easy with images
The main purpose of a chart is to convey information. By adding a background images, sometimes your chart will be difficult to read. So I suggest you to go easy with background images.
Download Excel workbook with this chart
Click here to download Excel file with this chart and play with it. Examine the chart formatting settings to understand this technique better.
Do you use background images in your charts?
I confess. I rarely use this technique, because finding good background image that can reveal data is very tricky. The oil rig image is an excellent choice. Also, certain things like drop-shadow for data series are not possible with our technique (as gap width = 0%). But I enjoyed figuring out this puzzle.
What about you? Do you use such charts? Please share your tips and techniques using comments.
35 Responses to “How to create a column chart with background image in Excel ?”
I like to visualize routemaps with scattered XY charts with outlined county maps as the background images. They're nowhere near perfect but they'll do the trick.
It would be a blast if I knew how to use GeoCodes as the XY coordinates for the chart but I don't know how to transpose them to the flat surface so I have locate the coordinates for zip codes manually.
Also, sometimes Excel seems to display the chart background image differently (=the background image of the chart is usually displayed as solid grey/black background) so it is often times better to layer the chart with invisible background on top of the map on the workbook and then just group them together.
Great idea, Chandoo -- works well in PowerPoint too!
I like how you creatively solved this problem, which is what Excel development is all about.
But it's my sworn moral duty to tell people it's not a good idea to put a chart like this on a dashboard or report. just say no.
Just as an after thought: if anyone is wondering why you shouldn't put a chart like this on a dashboard, take a look at Chandoo's previous post on the ten rookie mistakes when making dashboards.
http://chandoo.org/wp/2013/04/25/10-rookie-mistakes-to-avoid-when-making-dashboards-video/
Very interesting.
🙂
Awesomeness personified!!
Thanks as always, Chandoo 🙂
I really like this thing. Thanks chandoo.
Awesome.. and Rocking ..
Wow, this submission has provoked a deep ambivalence within me; it illustrates brilliantly how pretty visuals override the meaning of data.
I accept that, when reporting on data, distraction will hide the meaning. However, this, and the 'Pie Chart' article before it, demonstrate the requirement to actually practice such techniques. In practicing, one develops a firm control over the information, appreciating how eye candy distorts and so honing clarity through avoidance.
The greatest properties of pretty graphics is that they do distract and attract the viewers attention. In context, this may be the main objective of customers within image centric organisations like PR and Advertising to name just two.
I don't think distracting graphics should be immedately vilified. Instead, I thank Chandoo for demonstrating how they can be created so that we can better understand them
Hmm...While I'd be wary of putting this in most of my sheets, I definitely could see it's place in an infographic or annual report or something in general with basic info.
Thanks!
this is an excellent Boss
Hats of to you...
Happy weekend and have a nice time Boss...
Nice.. very good idea.. keep it up 🙂
It is really nice and useful.
Thanks Chandoo. 😀
This puzzle solving is amazing.. If there is a will there is a way.. You proved it here.. HATS OF U...:)
Hello,
I have data which I updated daily so its dynamic, hence I have made a simple pivot to give me consolidated numbers. with these number is pivot table, I created a chart which I send it to my boss. so my question how to format the chart in above mentioned style. I tried but could not do it.
Someone please assist.
Moin,
I have daily updating data as well. Here's what I did:
In Step 4, no need to choose a specific max value. Where 1400 is, insert:
=MAX(B3:B11)
(assuming column B contains the sales data and North is in row 3)
Then change your chart type to 100% Stacked Column.
[...] How to create a column chart with background image in Excel ? [...]
It was really Awesome trick.
Thanks
Tried it. Loved it.
thanks for sharing
I absolutely love your blog and find a lot of your post’s to be what precisely I’m looking for. can you offer guest writers to write content to suit your needs? I wouldn’t mind creating a post or elaborating on most of the subjects you write about here. Again, awesome web site!
AWESOME.. thanks
i absolutely love the way you present your charts. i am new in the field of Analysis and i have found some of your work very helpful. i would love to know more though. Great work you are doing.
I tried this till 4th step but i can't proceed further for the next step. can you tel me how to do the 4th step in a easy way.
Chandoo... u rock!!! :):):)
Wow, This is what I was looking for. This one is awesome trick to make our presentation look Good in Excel. Thank You so much for giving this Information 🙂
Hi,
Awesome solution !
Can you please show us how to put the figure values in the bar with color contrasting to the background as the chart with the Oil RiG !
🙂
Sayan
@Sayan
Did you download the sample file:
Select the chart
then select the data series columns
Right Click, Add data Labels
Select the Data Label
Right Click, Format Data Label
Label Position, Inside End
Close the format Box
Now select the Data Labels again
and select a single Data Label by selecting it again
You can now use the Font Color and Bold buttons on the Home Tab to set the colors as you want
You can move back and forward through each Data Label using the Right/Left arrow keys
Thanks a ton , Hui 🙂
Dear Sir,
It is not working on pivot charts. Is there any way I can do it on pivot charts too?
Lovely step by step explanation. I landed on your blog some days ago and since then i am coming regularly.
Hi Chandoo,
this is a really cool idea. I would like to publish a German YouTube-tutorial demonstrating this technique. There will be a link to this site, of course.
Best regards from Germany
Andreas
@Andreas... Thanks for the feedback. Please go ahead with the video. I am sure (y)our German readers would love it.
It's online 😉
http://youtu.be/1A4hU9HJ-gc
Hi,
I also added some eye-annoying images behind some (not transparent) charts, with the goal of providing additional information about the criticality of the displayed trends.
Unfortunately, the images are not displayed on other sources, even though I uploaded them, into a similar relative directory structure. My fear is that they are referenced with absolute path.
Do you know somehow the reason, maybe even the solution (maybe in C#: I ma using chart.SetBackgroundPicture(backgroundImageFile);)?
Thank you in advance for any help.
Regards,
bigstefi
Awesome!...Thank you so much.