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
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
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
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
The resulting chart will show positive columns in green and negative columns in red (see Figure 5).
Fig. 5
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
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
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.