FICPAYou Are Here:
Spreadsheets: Conditional Format a Chart
By Bill Jelen (www.cfo.com)
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.
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.
However, this is an unsatisfying result, as the positive columns are blue and the negative columns are white, as shown in Figure 3.
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).
The resulting chart will show positive columns in green and negative columns in red (see Figure 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? In Figure 6, any quality values below 97% are to be highlighted in red.
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.
This Tech Tip is brought to you by the Business and Technology Section ... IT solutions for today's CPAs. For more information and to view an archive of previous Tech Tips, please visit us here.
Do you have specific topics you would like to see covered in Tech Tips? Email any suggestions to firstname.lastname@example.org.
LAST UPDATED 1/7/2011