How to create a column chart with background image in Excel ?

Posted on May 6th, 2013 in Charts and Graphs , Excel Howtos - 33 comments

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).

How to create a column chart with background image in Excel ?

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.

Arrange data like this - column chart with background image in Excel

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.

Create a simple column chart -  - column chart with background image in Excel

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.

Fill chart's plot area with an image -  - column chart with background image in Excel

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,

Dummy data series -  - column chart with background image in Excel

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.

Final chart before formatting -  - column chart with background image in Excel

  1. Select the columns (any series) and press 1
  2. Adjust gap width to 0%
  3. Fill the dummy series with a chosen background color.
  4. Make the data series transparent (fill color = no color)
  5. Add borders to data series. Border color should be same as background color.
  6. Adjust the border thickness to 3pts.
  7. Adjust axis maximum to 1,400 (or any value you have selected in Step 4).
  8. Remove grid lines, legend and any un-necessary chart fluff.

Your column chart with background image is ready!

Finalized column chart with background image made using Excel

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.

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

33 Responses to “How to create a column chart with background image in Excel ?”

  1. Misca says:

    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.

  2. Great idea, Chandoo -- works well in PowerPoint too!

  3. 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.

  4. Ricardo Vieira says:

    Very interesting.
    πŸ™‚

  5. Nikki says:

    Awesomeness personified!!

    Thanks as always, Chandoo πŸ™‚

  6. Ramu says:

    I really like this thing. Thanks chandoo.

  7. Bhushan says:

    Awesome.. and Rocking ..

  8. Leon-K says:

    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

  9. Jasmine says:

    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!

  10. Sathish Kumar Barla says:

    this is an excellent Boss
    Hats of to you...

    Happy weekend and have a nice time Boss...

  11. Abdelrahman Omer says:

    Nice.. very good idea.. keep it up πŸ™‚

  12. Eranda Karunathilaka says:

    It is really nice and useful.
    Thanks Chandoo. πŸ˜€

  13. Salih says:

    This puzzle solving is amazing.. If there is a will there is a way.. You proved it here.. HATS OF U...:)

  14. Moin says:

    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.

    • Chris says:

      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.

  15. Swapnil says:

    It was really Awesome trick.

    Thanks

  16. Yael Z. says:

    Tried it. Loved it.
    thanks for sharing

  17. Lashanda Takeda says:

    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!

  18. Carlos says:

    AWESOME.. thanks

  19. Butholenkosi Tallbert says:

    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.

  20. Game Developers says:

    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.

  21. Medha says:

    Chandoo... u rock!!! :):):)

  22. Gaurav says:

    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 πŸ™‚

  23. Sayan says:

    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

    • Hui... says:

      @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

  24. Arup says:

    Dear Sir,

    It is not working on pivot charts. Is there any way I can do it on pivot charts too?

  25. Ankit Bansal says:

    Lovely step by step explanation. I landed on your blog some days ago and since then i am coming regularly.

  26. 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

Leave a Reply