Discover The Secret Of How To Conditionally Format An Excel Chart

Have you ever wanted to conditionally format an Excel chart? Conditional formatting in Excel has always been possible when applied to cells, but you can also conditionally format an Excel chart using an 'IF' function and some additional rows or columns of data. This article describes how to do this using a simple example.

Suppose you have an Excel table consisting of two rows; a heading row and a data row. For example, say, the headings start in cell D1 and are January, February, March and April. The data under each heading in our example start in cell D2 and are sales figures 40, 50, 60 and 30. We want to create a column chart with the column colour conditional on the sales value. We want sales above or equal to fifty to show as a green column and sales less than fifty to show as red. To enable us to vary the threshold figure, we put 50 into a separate cell slightly away from the table, for example in cell B1.

If you've built column charts before in Excel based on a table with several rows of data you'll have found that Excel creates one set of columns for each row of data, with each set of columns a different colour. In our example above we're going to create a second table and separate the original data into two different rows, depending on value. This will allow us to create different column colours which depend on value.

So back in our example, copy the original headings and paste them several rows under the first table, say, starting in cell D5. Now under these lower headings we're going to create two extra rows of data using the IF function. Remember the original January data is in cell D2. So select cell D6. We're going to use an IF function to test whether the original January data in cell D2 is above or equal to 50, and if it is we want to put this same value into cell D6. If not we want to enter the value zero. Don't forget we put the value 50 in cell B1.

So in cell D6 we type =IF(D2>=B1,D2,0) and press the enter key. In our example, cell D2 actually contained 40, so the function puts a zero into cell D6. We need to make the B1 cell reference absolute in our formula we just typed in, so we edit it and add the dollar symbols immediately before the "B" and the "1" (or use the F4 key), giving us the formula =IF(D2>=$B$1,D2,0) . Then use the Fill handle to fill the formula in D6 across the other three cells in the lower table, so all three cells now contain the formula. You'll see that cells D6, D7, D8 and D9 now either contain the original value or a zero, depending on whether the value is above or equal to 50, or not. We're halfway there.

Now select cell D7, immediately below where you placed the first formula. This time we want to test if the original number is less than 50, and if it is we want the original number, if not we want a zero. So in cell D7 we type =IF(D2<$B$1,D2,0) and press enter. Note the dollar symbols to again make cell B1 an absolute reference. In our example, cell D2 actually contained 40, so the function puts 40 into cell D7. Then fill the formula across the other three columns. This second row should now contain the original value or a zero, depending on whether the original value is below 50 or not. That's our data organized, next we'll create the column chart.

To create our column chart, first select the lower table headings and two rows of data. Then create a column chart. However we don't want a regular column chart. Instead we want a stacked column chart. Why? Well when Excel creates columns from the two rows of data we want to combine the data into a single column. So we'll see a stacked column for the two values for January for example. However because one of these values is always a zero, the stack will only show one column for each pair of data under each Month heading. But because we actually see a different column depending on which row has the value, the column colour is different. So now you have a column chart with two colours of columns. Next we'll change the colours.

We want all columns representing values above or equal to 50 to be green. So select the new stacked column chart and click once on any of the columns showing the values above or equal to 50. All the relevant columns should highlight. Then right click and choose Format data series, and change the fill colour to green, and click Ok to finish. Now the cells with values above or equal to fifty show as green. Then do the same for the other values. So with the chart selected, click once on any column representing a value less than 50. All the relevant columns should highlight. Right click and choose Format Data Series, and this time set the colour fill to red, and click OK to finish. Now all values below 50 show as red columns.

You might like to drag the new chart over the extra table to hide it, just for visual neatness. Now try changing values in the original table. Try some above or equal to 50 and others below. Your column chart will now display column colours conditional on cell values.

Interested in learning more about Excel's many other really useful features? You might like to consider attending our training course. Then you can really boost your Excel learning.

You can reach us on any of the following;

Tel: 08062468296 | 08108054165

Email: [email protected]