fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

35 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

  27. bigstefi says:

    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

  28. Witchny says:

    Awesome!...Thank you so much.

Leave a Reply