Conditional Formatting - An Introduction

Conditional formatting

What is conditional formatting?

In literal terms it is getting the format of a cell to change depending upon the content of the cell linked to a rule of some sort.

In layman s terms or to the person on the street it is a helpful way to point out a specific thing in your table of information. Examples might include entries on an arrivals board at an airport that are late in arriving, examination results of the class that are above and below average and the lists of competitors in triathlon by country or team.

Wouldn t it be really handy when scanning those departure boards at the airport if the flights to Newcastle had a black background and white lettering? You would be able to pick out the flights pretty quickly if that was the case. Usually red lettering, numbers or background are the only indications we are given about flight status.

So why use conditional formatting?

Well, speaking from my own experiences, I use conditional formatting as a method of highlighting errors, seeking out certain values and cross checking with other data. We are now talking about potentially hundreds of thousands of lines of data as opposed to a couple of hundred rows of flights. It is an efficient way to cut down the amount of data you are dealing with if you can assign a conditional rule and then to filter the data based on that rule. It is starting to get a bit technical, so I will step back a bit and set out an example.

Patient Screening

Suppose you want to check to see which patients are due a screening for a medical test that is due on their 50^th Birthday? The local health trust may well have several hundred thousand rows of data to search for those reaching their 50^th birthday in the coming year. You can set up a condition that will highlight all rows where the age of the patient has turned 50, let us say a light blue background and black bold text. Such a set of data would then show at a glance who should have their tests carried out, this allows the data to be filtered and saves the staff time in reading individual rows with the possibility of error.

A further condition could be set on a column indicating whether a test has been carried out to indicate where they are overdue, this filters the data further and allows more focus to be applied to priority cases.

So the idea of conditional formatting is to be more efficient in putting attention and energy into those areas that require work, a means of prioritising.

The rules can be based on actual cell content (dates, numbers, text, etc) or they can be based on the content of other cells. Examples of this are: if the cell equals Overdue then make the background red and text bold black, if the cell contains Pass then make the background green and text black. There are so many combinations and conditions or rules that it is incredibly flexible for the user to set their own conditions.

When you start combining functions into the conditions you can really drive out some efficiency and smart ways of working.

One of my favourite things to do is to have a cell which contains the TODAY function =TODAY(). What this will do is insert the current date into the cell and this will refresh each time the file is opened. You can then set a conditional format referring to the cell with the TODAY function to say if the cell content is greater than TODAY, then turn the background red. That way you can see when something is overdue and it will keep current because the condition is based on a changing date rather than fixed.

The conditional format utility sits under the Home tab of the ribbon. It is worth just going in and doing some playing to see the results that can be generated. You will see that there are lots of different conditions that can be set out.

If this has been useful then do let me know, alternatively if it is not particularly clear then I am happy to go through and update the article based on feedback.