Conditional formatting is a great tool for automatically formatting data in Excel, based on the conditions present in a cell(s). For example, we can use conditional formatting to highlight, automatically, all unfavorable budget variances in red and all favorable budget variances in blue. Given the power of using conditional formatting on traditional Excel ranges, many Excel users would like to use this tool on charts and often ask the question, “Can we apply conditional formatting to charts?” The short answer is, “No, conditional formatting does not work on chart data.” The longer answer is, “With a little creativity, we can trick Excel into applying what appears to be conditional formatting.” In this tip, we will show you how, with a little creativity, you can create a chart in Excel with conditional formatting.
Consider the data presented in Figure 1; sales data is presented for each of thirteen salespersons. You have been charged with the responsibility of creating a chart containing this data and also having the top-performing salesperson’s data as well as the bottom-performing salesperson’s data formatted differently than the rest of the data on the chart.
Figure 1 - Raw Data for Conditional Formatting on a Chart
To accomplish this task, you will need to create three additional columns of data and plot those three columns of data – and not the original column of sales data – in a stacked column chart. The revised data should resemble that shown in Figure 2.
Figure 2 - Revised Data for Conditional Formatting on a Chart
In cell C3 (and the remainder of the cells in the range extending through cell C15), the formula shown below is used to identify the maximum sales value in the range and to populate that value into the appropriate cell in Column C.
The formula populates all other cells in Column C with zero.
In cell D3 (and the remainder of the cells in the range extending through cell D15), the formula below was used to identify the minimum sales value in the range and to populate that value into the appropriate cell in Column D.
The formula populates all other cells in Column D with zero.
Finally, in cell E3 (and the remainder of the cells extending through cell E15), the formula below was used to enter all values other than minimum and maximum values.
Once you have arranged the data, as shown in Figure 2, a simple stacked cylinder chart, similar to the one shown in Figure 3 can easily be created. This chart consists of three data series: one for column C, one for column D, and one for column E. Each of these series is formatted differently than the others; column C is formatted in blue, column D is formatted in red, and column E is formatted in light yellow. Notice, however, that the zero values are effectively hidden from the chart because they are, in fact, zero values; thus, the chart gives the impression of applying conditional formatting to maximum and minimum values in column B. As the data in column B changes, the results of the formulas in columns C, D, and E also change. As these data values change, the chart appears to apply conditional formatting, when in reality, it is not doing so.
Figure 3 - Chart with Conditional Formatting Applied
Charting is a terrific way of enhancing the readability and understandability of the data on which you report. Conditional formatting is also a tremendous mechanism for doing the same. Combining these two tools by tricking Excel into doing something it technically cannot do, allows you to create reports that clearly and effectively communicate your intended message.
For a video demonstration of this tip, please visit www.tinyurl.com/k2tips117.