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 50th Birthday?  The local health trust may well have several hundred thousand rows of data to search for those reaching their 50th 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.


*Data used is fictitious.


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.


In the case above column E has had the condition applied to highlight any cell equal to or greater than 50.

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.

Comments

Post a Comment

Popular posts from this blog

NKW6 Pt 3 INDEX and MATCH

Image
 Part 3 INDEX and MATCH The last in a set of three looking this time at INDEX and MATCH as a way to search a table and return the contents of a cell.  This combination is a more versatile and perhaps more robust method of looking up data in a table than VLOOKUP. When I look at these two functions I almost always use them together.   It looks somewhat complex but thinking through and breaking it down it becomes clear and there are significant advantages.   First of all indexing is a method of pointing to something so if you imagine a table and you tell the programme which row to go to and which column to align to, you end up and an intersection and the contents of the cell are returned.   This may have it’s uses but it is rather inflexible on its own. By introducing MATCH to replace a specified row or column number you are now making the function so much more flexible and powerful.  The MATCH function looks at a range (either a column or row) and then sees...
Read more

Power Query: Merge and Append

Image
  Power Query: Merge and Append Discover the efficiencies and benefits of connecting and combining your data sources to drive your governance and performance measurement! Don`t be put off by the terminology, Power Query is simply sorting and organising data that you wish to import into your Excel spreadsheet and in doing so you can save a lot of time and reduce the scope for errors. ; The merge and append bit is talking about taking different data sources and adding them together to form a single source from which you can then do all sorts of interesting things! First of all we need to define what we mean by  merge  and  append . ; To  merge  a data set in this case means to add one data set to another by adding additional fields. For example: If we have a table of data that contains details of employee departments we might want to add it to another data table that contains details of their salaries. ; In effect we will create a single table ...
Read more

NKW6 PT1 - IF

Image
Introduction I recently pulled together an Excel session to go through the fundamentals of the IF, VLOOKUP, INDEX and MATCH functions.  To do this I started out by putting together a bit of an explanation and then recording, with sample data, how these functions can be used and common pitfalls.  This is the first session. Part 1 IF The IF function can allow you to interrogate the information in a cell or cells and then instruct the programme to return a response, which can be text a number, a cell reference, another function or a calculation.  It sounds rather complex, another way to think about it might be to set yourself a problem for example If the total cost of a project exceeds £3m then I should abandon it otherwise I will go ahead.  How does this look when written out?   ·          =IF ( A1 > 3000000 , ”Abandon” , ”Proceed”) ·          The =IF tells the programme wh...
Read more