How to Add Selective Highlighting to Your Excel Chart Background

by Matthew Kuo on January 5, 2013

in Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

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

ExcelBackground0

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.

ExcelBackground2

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.

ExcelBackground3

4.  Remove gridlines

Click “View” within the Excel Ribbon and uncheck the box for gridlines.

ExcelBackground4

5.  Copy the set

ExcelBackground5

6.  Open MS Paint and paste what you copied

There are other programs you can use, but MS Paint is probably the simplest

ExcelBackground6

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.

ExcelBackground7

8.  Save the image file as a jpeg

9.  Back in Excel, Right click the chart and select “Format Plot Area”

ExcelBackground9

10.  Within the “Fill” section, select “Picture or texture fill”, click the “File…” button, and select the jpeg file you created

ExcelBackground10

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.

ExcelBackground0

{ 11 comments… read them below or add one }

James Nylen July 9, 2013 at 7:20 am

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.

Reply

Sandra March 30, 2014 at 9:50 pm

Thanks for all your tips. Your way to explain is clear and makes everything very simple.

Good luck with everything.

Reply

May 25, 2014 at 10:50 pm

Draw a box. Send to back.

Reply

Shan November 11, 2015 at 9:07 am

That doesn’t work…..

Reply

Raj April 1, 2016 at 1:38 am

It will work as long as the background of your chart is blank

Reply

Cara January 25, 2017 at 11:29 am

You can also make the box transparent.

Reply

AB March 28, 2016 at 4:11 am

Thank you so much, works very well 🙂

Reply

jorge April 27, 2016 at 11:26 am

Thanks, this is really simple and super usefull

Reply

June 4, 2016 at 2:22 pm

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.

Reply

July 14, 2016 at 4:31 pm

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!

Reply

Eric August 23, 2016 at 9:06 am

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)

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post:


Related pages


formula match excelexcel indexingexcel formulas if statementsexcel diary templateerror bar excel 2007excel formula for numberingsites with most trafficexcel vlookup example different sheethow to use excel to compare two lists of datawhat is hlookup in excelhow to write a vlookup formula in excelhlookup vbagoals excel templatehow to draw normal distribution curve in excel 2007bin range excelexcel homeschoolexcel adding numbers in a columnexcel 2010 compare two listsvlookup multiple worksheetswww anderson ucla eduspark notes the goalhow do i subtract columns in exceldelete duplicate rows in excel 2010powerpoint checkmarkexcel symbol for does not equalucla course listingvlookup and hlookup tutorialhow to remove duplicate rows in excelexcel comparing cellstick in wingdingsexcel vlookup maxvlookup training videofuzzy vlookupvlookup and hlookup in excel 2007using if statements in excelexcel trend chartexcel randomisersticker format in exceldefine array excelcountif formula in excelremove duplicate cells in excelhow to draw a column graphcreate column chart in excelexcel randomizerexcel vlookup rowhow to create a countif formula in excelexcel find duplicates in two columnswhy does vlookup return 0excel sum columnscreating bar charts in excelbar graph in excel 2007match rows in excelhow to put cells together in excelifcount excelexcel pivot table tutorialsexcel vertical lookupcollege library uclasum columns excelinventory management xls filesif statements excelvlookup duplicate valueswhat is v lookup in excelexcel spreadsheets formulas and functionsmyucla turnitinexcel formulas if cell contains thenexcel 2003 iferrorexcel linked cellsformulas in excel sheetvlookup and isnagreater than or equal to in excel formulavlookup to the lefttest normality excelexcel formulas valueexcel spreadsheet for inventoryvba excel remove duplicatesfind duplicate records in excel
\n