Skip to content

Conditional Formatting

September 9, 2010

Welcome back !!! Its hard to believe that I’m writing my second blog. However I am going to try and  do a one blog a month. Its not too much to commit to and it gives me a whole month to think about the next one !!!

In this blog I am going to talk about conditional formatting.  As many of you will know formatting a cell in Excel is just changing its colour e.g making certain cells green etc.

What is Conditional Formatting ?

Conditional formatting means that the cell changes colour when certain criteria are met.  The cells change colour automatically. This may seem strange but there are valid uses for such behaviour :-

  • May be you have a spreadsheet where you store all your invoices and need to know when they are overdue. You can have a field for the due date – and apply a rule so that if the invoice is over due the cell colour changes red automatically.
  • Perhaps you have a list of customers – and their addresses. You can have rules to automatically show which customers are from say England, Scotland, Wales or Northern Ireland
  • Or you have a list of sales staff who participate in a bonus scheme. Condtional formatting will allow you to highlight those staff that are eligible for a bonus based on the criteria that you use.

Probably the best example of where I have used Conditional Formatting on projects is at the Department of Health. They required to validate a large amount of data on a monthly basis. They needed to see that the data contained the correct prices and codes for a number of operations. I built a tool that highlighted the errors in red – so that they could be seen at a glance.

You can find the full case study on my website at :-

http://www.simplyspreadsheets.co.uk/case-study.php/The_Department_of_Health

The model contained over 40 rules.

Incidentally in Excel 2007 and later Microsoft considerably expanded the capability of conditional formatting. You can find more information in the video links below.

Video Tips

The following is a link to show you some examples of how to use conditional formatting in Excel.

If you are using Excel 2007 or later then the following video will be useful


http://www.youtube.com/watch?v=PpQ9d4cHVdk

For those of you using Excel 2003 or earlier then the following link is appropriate:-

http://www.youtube.com/watch?v=3p0fkljYwPY&feature=related

Advertisements
One Comment leave one →
  1. December 8, 2010 10:36 am

    Great Blog! Very informative and useful! Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: