SubscribeExcel Conditional Formatting Basics
Today is the first anniversary of Excel Conditional Formatting post (Don’t worry, I am not going to make anniversary posts for all the 150 odd excel articles here). This is the most popular post on PHD. The post has 100 comments and bookmarked on delicious more than 700 times. It is truly a rock star post on PHD.
To celebrate the 1 year of teaching conditional formatting to you all, we have a series of posts, the first of which is “What is excel conditional formatting & How to use it?”
What is excel conditional formatting ?
Conditional formatting is your way of telling excel to format all the cells that meet a criteria in a certain way. For eg. you can use conditional formatting to change the font color of all cells with negative values or change background color of cells with duplicate values.
Why use conditional formatting?
Of course, you can manually change the formats of cells that meet a criteria. But this a cumbersome and repetitive process. Especially if you have large set of values or your values change often. That is why we use conditional formatting. To automatically change formatting when a cell meets certain criteria.
Few Examples of Conditional Formatting
Here are 3 examples of conditional formatting.
So How do I Apply Conditional Formatting?
This is very simple. First select the cells you want to format conditionally. Click on menu > format > conditional formatting or the big conditional formatting button in Excel 2007.
(we have used excel 2003 in this tutorial, but conditional formatting is similar in excel 2007 with lots of additional features)
You will see a dialog like this:
There are 2 types of conditions:
- Cell value based conditions: These conditions are tested on the cell value itself. So if you select a bunch of cells, and mention the condition as between 15 and 25, all the cells with values between 15 and 25 are formatted as you specify.
- Formula based conditions: Sometimes you need more flexibility than a few simple conditions. That is when formulas come handy. Conditional Formatting Formulas are slightly complicated and can be difficult to learn or use if you are new to excel. But they are very useful and intuitive and if you use them once you get a hang of it.
What are the limitations of Conditional Formatting?
In earlier versions of Excel you can only define max. of 3 conditions. This is no longer true if you are using Excel 2007 (read our review of excel 2007)
However, you can overcome the conditional formatting limitation using VBA macros (again, if you are new to excel, you may want to wait few weeks before plunging in to VBA)
Also, you can only use conditional formatting with cells and not with other objects like charts.
Ok, Enough Theory, Time for your First Conditional Formatting
Go ahead, open a new workbook and try few conditional formats yourself. See how easy and intuitive it is. Use it in your day to day work and impress your colleagues. Learn 5 impressive tricks about conditional formatting.
If you have trouble getting started, download the conditional formatting examples workbook.
Tell us how YOU use Conditional Formatting
Share with us how you use CF in your work. I am sucker for conditional formatting and use it wherever I can. What about you?
This post is part of our Spreadcheats series, a 30 day online excel training program for office goers and spreadsheet users. Join today.
Trackbacks & Pingbacks
- Pingback by Search a Spreadsheet Full of Data using Conditional Formatting | Learn Excel | Pointy Haired Dilbert - Chandoo.org on March 31, 2009 @ 4:44 pm
- Pingback by VBA and Conditional Formatting in Excel | Corality Blog on April 1, 2009 @ 4:31 am
- Pingback by Conditional Formatting in Excel | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on August 21, 2009 @ 3:34 pm
- Pingback by Excel Timesheet Templates, Resource Management Templates - Project Management using Excel Spreadsheets | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on September 8, 2009 @ 12:24 pm
- Pingback by Conditionally Formatting Dates in Excel - How to format dates in excel based on a condition? | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on January 5, 2010 @ 9:50 am
- Pingback by Highlighting Repeat Customers using Conditional Formatting [Part 2 of 2] | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on January 6, 2010 @ 9:56 am
- Pingback by Become an Excel Guru in 31 Days | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on March 11, 2010 @ 9:54 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
My favorite use of conditional formatting is dynamic color banding of rows. I use the formula =ISEVEN(ROW()) as the condition and pick light color for the fill. The result is alternating bands of color, but what is nicest feature is when a row is moved (or inserted), the color adjusts dyanmically so I do not have to reformat the banding manually.
@Steve – Yes, the popular zebra stripes.
I use conditional formatting often to show outliers or potential numbers that are a potential issue. For financial numbers, a negative would be formatted to be highlighted in yellow or bold.
In the past I setup a control total sheet that would alert the reader to a potential issue if a calculated value was outside of a given tolerance. This is one of the less popular but highly effective functions in Excel, in my opinion.
I use conditional formatting in almost each worksheet just to compare which value in two columns is bigger (as shown above). But one thought, more than any other, keeps me awake most nights… Why is not possible (in Excel 2003) to change the font size using conditional formatting? Every time I want to do so (change the font size) I am forced to use macros.
@Steve: I like the zebra stripes very much. However with the new excel 2007 table layouts makes it much more easy to stripe tables of data in whatever format we want.
@Tony: Another very popular use, highlighting outliers, specific set of values…
Once I set up an excel sheet that would play a sound when a particular condition is met (see: http://chandoo.org/wp/2008/08/04/play-sound-when-cell-value-changes/)
@Struzak: yes, that would have been fantastic… but alas, for font sizing you have to rely some script like excel tag cloud…
Hi,
This is a good introduction to conditional formatting in Excel. I was inspired to write something similar after a recent course I presented. Check it our here to learn a more advanced application of conditional formatting and VBA.
http://blog.corality.com/2009/03/vba-and-conditional-formatting-in-excel/
@CHandoo: If you like Zebra stribes, have a look at this cross-cursor application:
http://www.navigatorpf.com/training/tutorials/dynamic-cross-cursor-in-excel-vba
Cheers,
Rickard
I am trying to create a time sheet in Excel that will calculate several different elements of time and cumulate them along the way.
I have figured out the formulas for the DISPLAY, but it still requires me to enter hours even though these are not displayed. I want to be able to ENTER only minutes and seconds, but have it display cumulative time.
If you have a formula or other helps, it would be greatly appreciated.
NEED HELP:
How to do this in excel.
If A1 = 1 then display B2=(20 to 100), if A1=2 then display B2=(50 to 100).
@Kumar: Use a simple CHOOSE formula, like this,
=CHOOSE(A1,”(20 to 100)”,”(50 to 100)”)
It throws an error if the A1 has more than 2.
Hello Fellow PHD’ers,
I write to ask for help with an, hopefully, easy problem to solve. The how to has beaten me I am afraid.
I am using excel to create a project Management tool. I would like our CF friend to highlight any date that has past ( the deadline has been and gone – and its now into overtime!). The colours I can do (pink, much to the distain of the managers); the date I cannot fathom.
I have tried =>Date() -1, >= dateadd(”d”, date(), -1), <Date(), <Now() and nothing seems to work.
I hope what I am trying to do is not unreasonable.
Any help, advice, chocolate will be more than appreciated.
@Marcie… Thanks for visiting PHD and asking a question.
Assuming the project deadline date is in cell C1, select the cell you want to highlight, go to “conditional formating” and select “formula is”, now type =today()>c1 and set the pink color.
Thank you ever so for the response Chandoo
)
I have tried this in varying ways now
But alas!! It dosn’t seem to work for me.
I have done the following
Conditional Formatting>>New Rule>>Use formula – and entered the info as above.
Although I have more pink than before (which is achievement in itself!), the pink highlight is a weenie bit random. It has highlighted dates such as 15/01/10 and 20/03/10 along with historic dates.
Col D is my date col, and the data rows are 6 to 96. Date format is dd/mm/yy, the dates range from 30/10/09 (ddmmyy) to 31/06/10.
Oh, and I am using Excel 97 if that helps.
Again, any and all help it is welcome – thank you for reading
Kind regards
Marcie
@Marcie.. did you remove the previous conditional formatting rules?
Yes, as this is the only CF I need after each attempt I remove it and start again to avoid any troubles.
Hello again!
I think I was having a “senior” moment – its all working fabulously now Chandoo, thank you.
However…..
I now have another brain teaser….
Can you set conditional formatting based on another cell referance.
Eg:
I would like Col A to highlight IF Col B has “Task Complete” in it.
I have had a quick scout on the forums – if this already exists my sinecest aoplogies.
Wishing EVERYONE a VERY Merry Christmas – and a lucky 2010!
Marcy xx
Hi there,
I’m attempting to create a conditional formatting time tracker. I would like to track the number of times an event happens at different parts of the day. My team will gather data at various times and enter the times when they were gathering. I want to create a simple means of viewing the data to see what “holes” are left in a 24 hour day. So for example, my column A would be Begin time. Column B is End Time. At the bottom of my sheet I’d like a row labeled 12:00pm, 1:00pm, 2:00pm, etc. for a 24 hour period. I want that row to be conditionally formatted so that as my team enters begin and end times for when they were gathering data, the appropriate cells would be shaded to show that there was data for that time. This way, i could easily locate which time periods in a 24 hour day had data gathered and which didn’t. how should I go about doing this? thanks!
@Marcy.. you can do that using conditional formatting – formulas. Here is a post showing 5 good examples to get started. http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/
@Kevin: You can use the gantt chart technique shown in http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ to achieve this. However, you may need to have an additional helper row to calculate some intermediate values.