Within the 2010 version, Microsoft Excel still does not have a feature to selectively highlight specific regions of your chart backgrounds. For example, in the chart below, let’s say that I want to highlight ONLY the third quarter periods of the year (July, August, and September).
There are basically two ways to perform this task manually. One involves using wide data bars on a secondary axis to simulate a highlighted background. I generally don’t recommend this method because it is very complicated and it adds another axis to your graph, which you must use significant formatting to hide. The other method involves using an embedded picture and is much easier to implement. I’ll describe the process below:
1. Count the number of intervals in your chart
In my example, there are 12 intervals.
2. Select a set of uniform width cells that match the number of intervals in your chart
The easiest way to do this is to move over to the right of your Excel spreadsheet where you haven’t edited anything yet. These cells will already have a uniform width. To make things easier, you can label each of these blank cells with a number above.
3. Highlight the cells within the set that correspond with the region of the chart you want highlighted
Since I want highlight the third quarter of the year, this would correspond with cell numbers 7, 8, and 9.
4. Remove gridlines
Click “View” within the Excel Ribbon and uncheck the box for gridlines.
5. Copy the set
6. Open MS Paint and paste what you copied
There are other programs you can use, but MS Paint is probably the simplest
7. Within MS Paint, cut off the white space at the top and bottom of the image
This step is important because this image will be stretched out in your background. Therefore, if you leave the white space in, it will cover a large portion of the background.
8. Save the image file as a jpeg
9. Back in Excel, Right click the chart and select “Format Plot Area”
10. Within the “Fill” section, select “Picture or texture fill”, click the “File…” button, and select the jpeg file you created
Voila. The background should still work regardless of how you resize the chart. However, the background created is static. Therefore, if you need to adjust to highlight the fourth quarter, for example, you’d need to repeat the steps above.
There is another way to do this: put the chart over the highlighted cells and set the chart background to transparent.
It may be difficult to get everything to line up properly (you’ll need cells that line up with the plot area), but this way, you can more easily change the cells that are highlighted, or even automate the process using conditional formatting.
Thanks for all your tips. Your way to explain is clear and makes everything very simple.
Good luck with everything.
Draw a box. Send to back.
That doesn’t work…..
It will work as long as the background of your chart is blank
You can also make the box transparent.
Thank you so much, works very well 🙂
Thanks, this is really simple and super usefull
It is in point of fact a great and helpful piece of information. I’m happy that you just
shared this useful information with us. Please keep us up to date like this.
Thanks for sharing.
Awesome! I didn’t have MS paint, so I just used another excel sheet to create the image as a picture copy/paste, then saved it as JPG. It worked PERFECTLY. Thank you!
I think a very quick way to do this is use a Combo Chart and add a new Series as a Clustered Column (adjusting transparency and spacing)
I never did such thing in my professional life of 12 years but suddenly from my boss i got such requirement to highlight all the quarters of the year separately. Trust me I was worried if i will be able to achieve this and had the old ways to just use the drawing tool to create borders. Thank you very much for sharing this great piece of work. It was wonderful creating it using your step by step instructions. The funniest thing was even my boss was confused how I did it. 🙂 Sometime boss demand things which even they are not aware of if possible. :D:D:D
Hey outstanding blog! Does running a blog similar
to this take a massive amount work? I have very little knowledge of computer programming but I was hoping to start my
own blog in the near future. Anyway, should you have any recommendations or tips for new blog owners please share.
I understand this is off subject but I simply needed to ask.
Thanks!