Spreadsheets: Conditional Format a Chart

If you want to change the chart color for positive or negative, there is a built-in solution.


Editor’s Note:

To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Reader John P. wins a copy of Charts and Graphs: Microsoft Excel 2010 from the MrExcel Library for his question: “Is there any way to Conditional Format a Chart Range? For example, I have a series of numbers in a Balance Sheet Model (Cash line) that are currently negative, thus when I chart, I make the bars red. If I put in Additional Paid in Capital certain months, the Cash line may turn positive. [As a result], I would want those columns representing the positive cash months to be green.”

 

If you want to change the chart color for positive or negative, there is a built-in solution. First, create a default chart as shown in Figure 1.

Fig. 1

MrExcel 1-5-11 Fig01

 

Click on one column in the chart. This should select the entire series of columns. Press Ctrl+1 to display the Format dialog box for Series 1.

In the left navigation of the Format Data Series dialog box, choose the Fill category. It looks like the answer is to choose the Invert if negative checkbox as shown in Figure 2.

Fig. 2

MrExcel 1-5-11 Fig02

 

However, this is an unsatisfying result, as the positive columns are blue and the negative columns are white, as shown in Figure 3.

Fig. 3

MrExcel 1-5-11 Fig03

 

If you have Excel 2010,  the trick is to choose Solid Fill instead of Automatic. This step will cause Excel to display a Fill Color section with two color dropdowns. The first dropdown is for positive values and the second dropdown is for negative markers. Choose green and red or any colors desired (see Figure 4).

Fig. 4

MrExcel 1-5-11 Fig04

 

The resulting chart will show positive columns in green and negative columns in red (see Figure 5).

Fig. 5

MrExcel 1-5-11 Fig05

 

This easy method is fine if the dividing line between red and green is the zero axis. However, what if you want some other point to be the dividing line or what if you are using an older version of Excel? In Figure 6, any quality values below 97% are to be highlighted in red.

Fig. 6

MrExcel 1-5-11 Fig06

In this case, the chart is based on a secret range of calculated cells as shown in Figure 7. Rows 29 and 30 break the quality values from row 26 into either a “Good” or “Bad” series. The #N/A values prevent those columns from being drawn or labeled. The result is either a green or red column for each data point.

Fig. 7

MrExcel 1-5-11 Fig07

 

Bill Jelen is a CFO contributing editor and author of 32 books about Excel, including Charts and Graphs: Microsoft Excel 2007. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.

 

Discuss

Your email address will not be published. Required fields are marked *