Excel Conditional Formatting

Excel Conditional Formatting

In this segment, you will learn how to use Excel Conditional Formatting  to set up the criteria to change the formats of a cell.

The easiest way to show how to use Conditional Formatting is to use an example.
For simple Conditional Formatting we have got this following example. A couple of ratios based on various departments in the company, and what would be nice is, if we could somehow change the format of these cells to show which departments and divisions are doing well, which are doing less well, and which are doing poorly. Using Conditional Formatting this can be done.

So for example

• you highlight the cells you want to apply the conditional formatting to
• you go to Format,
• Conditional Formatting , and this is available in the more recent versions of Excel, from about 1997,
• this box will pop up, and what it first says is this is Condition number 1,
• here you can choose whether the cell value is specific or as a formula. For this simplified version we’re going to use the cell value
• and then you can choose what it must look at in the criteria, so must the numbers in the cells be
  • between 2 numbers,
  • not between,
  • equal to,
  • not equal to,
  • greater than ,
  • less than,
  • greater than or equal to,
  • less than or equal to

So in this situation what were going to say is

• if the cell value is less than or equal to 10%,
• then you click on the Format and you’ve got the choice to format your cells, you can either format the characters or you can change the border in some way, or the contents of the cell, and in this case if its less than 10%
• we’re going to make it red
• click ok

We then want to add another criteria and here we want to say again the cell value, this time what we want to say is

• if its between this 10% and 20% that’s in the middle ground, so we’ll leave it on between
• and well say between 10% and 20%
• and in this case were going to format it
• and we’ll make it yellow,
• push ok
• and you can add one more condition with Conditional Formatting, and in this case what we are going to say is
• if the cell value is greater than or equal to 20% then what we’re…….????? is quite a good result
• and well make it green
• now you’ve set up all your conditions, as long as you’ve highlighted the cells your looking at
• and you push ok
• you’ll see that automatically the cells are change the colour so you can quickly look and see that JHB and CT did well, BLM and DBN are so so, and PE is bad.
• if it turns out that DBN actually had a percentage of 31, when you input the number you’ll see it automatically changes to adapt for the new input.

You can use Conditional Formatting for more sophisticated criteria
For example here we have a Net Profit After Tax figure and we have some benchmarks here, so the company has decided that 8% is the benchmark and what we’d like is for the colours, the cell colours to change depending on whether the net profit after tax is above, or below this percentage. Obviously we can go and use simple conditional formatting, and type in the 8%. The problem is that if these benchmarks change, you are going to have to work through the entire spreadsheet and make changes to all these percentages. What you can do however,

• is you highlight the cells you want to apply the conditional formatting to
• Click Format
• Conditional Formatting
• Because we’re using the more sophisticated methodology, we are not going to say the cell value is a number, we’re now going to say the formula is,
• And what you do is you then say equals, and you be specific, you want to say if this cell is bigger than that cell over there

Now what’s important to realize here, you can see absolute referencing has been put in here, which means that this criteria is going to be anchored, because you’ve highlighted a selection of cells, if you chose a format and then pushed ok. What would happen is Excel would say this exact formula with the absolute referencing must be included in all these cells, so if it is left as is,

• for example this cell won’t use itself, it’ll actually refer to B14, because that is what the formula says
• so what you need to do is you need to say ok that is the cell I have chosen for this number for the rest of them I want them to use their own cells so it needs to be a relative referencing,
• so we’ll come here and we’ll remove the absolute referencing
• we’re asking it to be, is it bigger than B7
  in this case, all these cells need to refer to the absolute reference, they need to refer to this cell here, so in this case the absolute reference is correct.
• You can now set up the format,
• And we’ll make it green and bold,
• click ok,
• you can add other criteria but we’re going to say ok,
• what happens now is Excel has gone and looked at this cell, compared it to B7 said its bigger, made it green and bold,
• gone to this cell, looked at this cell , compared it , made it green and bolded it,
• so now, if for example you change this to 6%
• what will happen is, it automatically correct itself. But more importantly if the benchmarks change, so let’s say this goes up to 11%, that also directly effects the spreadsheet.
• another good way this sophisticated Conditional Formatting is to actually find what criteria -where the criteria is met
  so for example I’m just going to make this a bit smaller,
• lets say we want to know who the worst is, and in this case in order for the Operating Expenses to revenue, the highest is worst,
• so we put a max function which will tell us the Division with the highest percentage,
• now this is a particular small spreadsheet, but on bigger ones it might be useful if the cell that has that number, is highlighted so you can easily get it.

With Conditional Formatting this is as simple as

• highlighting the cells,
• click Format,
• Conditional Format
• Ok and this is not a number issue this is a referencing issue so it’s a formula,
• we click equals
  we click in the white cell of the highlighted cells,
• because we know it needs to be applied all the way across, we know that this cannot be an absolute reference, it needs to be a relative reference,
• ok we get rid of that,
• and we say if that is equal to this cell, and again even though its moving across we now know it needs to be anchored there, so that’s an absolute reference, so were happy with the dollar signs
• the format we want is red, to indicate the problem department,
• click ok,
• we click ok,
• what happens is it points out, which one is the worst based on this formula.
• So if it turns out that we actually have a 27% there, it’ll move across and highlight the department that has that highest value.

Using Conditional Formatting, your spreadsheet can become quite colourful, and you may decided that you want to remove one or all of the conditional formats, the way to do this is

• you highlight the cells that have conditional formatting on them
• you go Format,
• Conditional Formatting
• and there’s a delete button here, you click the delete button
• and it’ll ask you what conditions do you want to do delete,
• now we’ve highlighted the whole area, and we want to delete all three conditions, but you can maybe highlight a cell and just remove one ore 2 conditions,
• we click ok ,
• Ok again, and you’ll see that all conditional formats have been removed.

Sometimes its difficult to find the conditional format,s and for that I recommend you use the GO TO SPECIAL command and there is some training available on this site