Conditional Formatting in Excel – 5 Tips to make you a Rockstar

Posted on March 13th, 2008 in All Time Hits , Analytics , Charts and Graphs , Featured , Learn Excel - 184 comments

How to be excel conditional formatting rockstarExcel conditional formatting is a hidden and powerful gem that when used well, can change the outlook of your project report / sales budget / project plan or analytical outputs from bunch of raw data in default fonts to something truly professional and good looking. Better still, you dont even need to be a guru or excel pro to achieve dramatic results. All you need is some coffee and this post to learn some cool conditional formatting tricks.

So you got your coffee mug? well, lets start!

The 5 tricks we are going to learn are,
1. Highlighting alternative rows / columns in tables
2. No-nonsense project plans / gantt charts
3. Extreme Incell graphs
4. Highlight mistakes, errors, omissions, repetitions
5. Create intuitive dashboards

If you are new to Excel Conditional Formatting, please read the Conditional Formatting Basics article before proceeding.

I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star

1. Highlighting alternative rows / columns in tables:

Using MS Excel conditional formatting to change background color of alternative rows or columns
Often when you present data in a large table it looks monotonous and is difficult to read. This is because your eyes start interpreting the data as grid instead of some important numbers. To break this you try highlighting or changing the background color of alternative rows / columns. But how would you do this if you have rather large table and it keeps changing. The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature, but we all know how the default settings of various Microsoft products are like).

  • First select data part of the table you want to format.
  • Go to Conditional formatting dialog (Menu > Format > Conditional Formatting)
  • Change the “cell value is” to “formula is” (YES, you can base your formatting outcome on formulas instead of cell values)
  • Now, if you want to highlight alternative rows, the formula can go something like this,
    =MOD(ROW(),2)=0
    which means, whenever row() of the current cell is even, to change the coloring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
    Also, if you want to highlight alternative columns instead of rows you can use the column() formula.
    What if you want to change background color of every 3rd row instead, just use =MOD(ROW(),3)=0 instead. Just use your imagination.
  • Set the format as you like, in my case I have used yellow color. When you are done, the dialog should look something like this:
    Excel Conditional Formatting dialog box, entering formulas to set the format
  • Click OK.
  • Congratulations, you have mastered a conditional formatting trick now :)

2. Creating a quick project plan / gantt chart using conditional formatting:

How to create Microsoft excel based gantt chart / project plan
Project plans / gantt charts are everyday activity in most of our lives. Creating a simple and snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of formulas you can do it no time.

  • First create a table structure like shown above, with columns like Activity, start and end day, day 1, 2,3, etc…
  • Now, whenever a day falls between start and end day for a corresponding activity, we need to highlight that row. For that we need to identify whether a day falls between start and end. We can do that with the below formulas,
    =IF(AND(F$8>=$D9, F$8<=$E9),"1","")
    Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell.
  • Next, whenever the cell value is 1, we will just fill the cell with a favorite color and change the font to same color, so that we dont see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. This will be done by conditional formatting like below:
    Excel Conditional Formatting Dailog, highlight a cell
  • Congratulations, you have mastered the art of creating excel gantt charts now

3. Extreme In-cell Graphs:

Incell graphing is a nifty trick that basically uses REPT() function (used to repeat a string, character given number of times) to generate bar-charts with in a cell. You can apply conditional formatting on top of them to give the charts a good effect. Here is a sample:
Excel Condtional Formatting along with In-cell Graphs

The above is a table of visits to Pointy Haried Dilbert ;) in the month of January 2008. As you can see I have highlighted (by changing the font color to red and making it bold) for the cells that have more than average number of visits in the month. I am not going to tell you how to do it, it is your home work :)

4. Highlight mistakes / errors / omissions / repetitions using conditional formatting:

Conditional formatting errors
Often we will do highly monotonous job like typing data in a sheet. Since the work is monotonous you tend to make mistakes, omit a few or repeat something etc. This can be avoided by conditional formatting. I use this trick whenever I am typing something or pasting a formula over a rather large range of cells (for eg. vlookup on annual revenue data of all your accounts, could run in to thousands of rows across multiple states /regions etc.).

Lets see how you can highlight a cell when it has an error:

  • First select the cells that you want to search for errors
  • Next go to menu > format > conditional formatting and mention the formula as: =iserror() (see below)
    Microsoft Excel conditional formatting dialog box
  • In the same way you track repetitions, a simple countif() would do the magic for you, or Omissions (again a countif())
  • Thats it, you have learned how to save tons of time by letting excel do the job for you. Sit back and sip that coffee before it gets cold.

5. Creating dash boards using excel conditional formatting:

As I said before you can use conditional formatting to create intuitive sales reports or analytics outputs. Like the one shown here,
dash board how to using excel

Here is how you can do it:

  • Copy your data table to a new table.
  • Empty the data part and replace it with formula that can go like this (I am using the above table format to write these formulas, may change for your data)
    =ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever the cell value is more than its predecessor in the data table we are appending the symbol â–² (go to menu > insert > symbols and look for the above one) etc.
  • Next, conditionally change the color of cell to red / green / blue or pink (if you want ;) ) and you are done
  • Show it to your boss, bask in the glory :)

I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star.

More DIY Excel Training:

Learn Excel Formulas in Plain English | Executive Dashboards in Excel – 6 Part Tutorial | 15 Excel Fun Tips

More...
Subscribe to Chandoo.org Email updates and get a free excel e-book with 95 tips & tricks


Trackbacks & Pingbacks

Comments
talljoanne March 14, 2008

Hi Chandoo, got here via Lifehacker and just wanted to say what a lifesaver this is! I don’t know who in the world picked the default colors for Excel charts, but they are truly hideous. (You’d think Microsoft can afford to hire someone with better sense aesthetics.) Thanks for bringing in some beauty into the corporate world indeed.

Krishna March 14, 2008

Hi chandoo…nice tips and i would use it in my work. So where are you now? based out chennai or moved to
US for a long term. I think you are now still with TCS. rite?

Berry Zito March 14, 2008

Thanks Chandoo!! I’ve never seen these features and will use them daily :O)

RKB March 14, 2008

In the Gantt chart example, instead of matching the font with the cell color, you can also simply apply a custom formatting of “;;;” which hides any text in the cell. That way you can feel free to mix and match colors (or even apply some conditional formatting on colors of the Gantt chart) without worrying about changing the font, too.

Wayne Elgin March 14, 2008

For the Gantt Chart:

You could ignore the contents of the cell altogether if you put =AND(F$8>=$D9, F$8<=$E9) directly into the conditional formatting “Formula Is” section and filled that out into the rest of the cells.

Neater, no?

magenie March 18, 2008

Okay I need help here, O Excel Master.
Let’s say I have a task that needs to be done by a colleague in 3 hours. How do I make a cell change colour when that specific time is reached? I tried many ways using conditional formatting but the cell just won’t change colour when the time comes!

Chandoo March 18, 2008

Wow, I am overwhelmed by the comments and pingbacks, thanks everyone.

@talljoanne: thanks, welcome to PHD :)
@Krishna: welcome back, I with tcs, moved to us though
@Berry Zito: thanks, welcome to PHD :)
@RKB: that is a cool trick, I shall use it going forward, thanks for sharing your tips with PHD readers.
@Wayne Elgin: Pretty neat trick indeed, reduces the need to paste the formula over a large range, thanks for sharing it, welcome to PHD :)

Chandoo March 18, 2008

@magenie: Thanks for dropping by. Hmm, that could be little tricky, but I am sure we can do this. I remember doing this for dates onces (when a deadline reaches that column would highlight), lets see how we can do this :

1. let me assume that we have task desc in cell a1
2. task target time (3:30 PM for eg.) in cell a2
3. We aim to change the color of cell a1 and a2 to red (or whatever) when the time is 3:30
4. The conditional formula could be something like :
=$a$2=now()
5. Now() would tell me what the date and time now is. It may not work correctly since now() would return date also, you can avoid this by converting date-time to only time using hour(), minutes() functions I guess.
6. Once you are done you can press “f9″ every now and then. This will force excel to recalculate formulas or if you close your workbook and reopen excel would do the same ideally (unless you explicitly changed the options)

let me know if this helps you, welcome to PHD :)

magenie March 18, 2008

Thanks for the speedy response, Chandoo!
I’ve actually tried that method earlier, but couldn’t set the NOW() function in the conditional formatting field to only calculate the time. This are some of the other things I attempted (without success):

1) Fill in two cells, one (A1) with NOW(), formatted to display current time only, and another cell (A2) with target time. Then set conditional formatting so that A1 changes color when cell value =A2.

2) Fill a cell (B1) with target time including date so that I could use set conditional formatting for B1 to change color when cell value equals to =NOW()=B1.

I can’t remember what other methods I’ve tried despite knowing it wasn’t gonna work. Sigh..

I’ve used Excel 2003 and 2007 as well. Both failed me. Or maybe I failed them..

Yelp…

Chandoo March 18, 2008

@magenie : hmm
first let me respond to your comment -
1) fill in two cells, one (A1)….
the value a cell displays is different from the underlying value of the cell, display value is based on the format you select, but when you access the cell through formulas, excel will pick up the background value which could be diff from display value, may be this is why you are having trouble doing the conditional check.
also, excel now() function returns time in seconds (and probably in milli seconds as well, not sure) so your conditional formatting may turn off even before you notice it.

Now, let me give you another solution…

in A1- enter time in “10:00 AM” format, just type in the value, excel will know that you have entered time and format the cell accordingly.
in A2 – enter the following formula “=TIME(HOUR(NOW()),MINUTE(NOW()),0)”, essentially I am extracting current hour and minute and manually setting seconds to zero to create a time.
now change color of A1/A2 when A1=A2 (make sure you put the correct cell address i.e. $a$1=$a$2 if needed)

I have tried this in a sheet and it seems to be working fine. Let me know if this works for you. I can send you the excel if you want.

magenie March 19, 2008

It works, it works! :)

At first it didn’t coz I used “Cell value”.. I’m always unsure when to use cell value and when to use formula, since I entered the target time (a value) into the first cell, and not a formula.

But no matter, as long as it works. You ze best, Chandoo. :)

TCL March 19, 2008

Great ideas and many thanks.

Can u please give examples for conditional formatting in open office Calc instead of using Excel?

Chandoo March 19, 2008

@magenie .. I am happy it is working for you :)
@TCL : welcome to PHD, thanks for your comments.
Hmm, Open office calc is not yet installed on my laptop, may be this weekend I will spend sometime with it and start writing about it as well.

Mike March 22, 2008

How do you insert the up and down arrows in the formulas on the dashboard (item 5)?

Chandoo March 22, 2008

@Mike .. thanks for visiting PHD, welcome…
you can insert up /down arrows from menu > insert > symbols (then you have to look up the symbol you want), the list will have all the symbols your current font has (you can even change font form that dialog)
Let me know if this is not working for you..

Cheryl March 29, 2008

This is great stuff! I was trying to find out how to fix a problem I was having with a conditional formatting. I have a worksheet where I enter 1 or 0. For 1, I want to highlight the cell green (that part works fine) for 0, I highlight red. The problem is that Excel thinks all blank cells are 0’s and turns the whole thing red! Do you have any suggestions?
Also, do you use pivot tables at all? Thanks!

Chandoo March 31, 2008

@Cheryl – Thanks for visiting PHD and commenting :)
coming to your question : “…The problem is that Excel thinks all blank cells are 0’s and turns the whole thing red!…”

you can try the conditional formatting with functions (instead of cell values) and give a formula that goes like,
=and(isnumber(cell),cell=0)

that is if you must use conditional formatting on ZERO, easier option is to replace zeros with some other value and excel would not mistake them for empty cells. Another option would be to replace empty cells in your table with some default value (like a hyphen – or something)

Yeah, I do use pivot tables, may be sometime I will write about those here :)

Cheryl April 1, 2008

Thanks Chandoo. Of course it would have been easier to change the zero to a different value… but my boss was insistent on 1’s and zero’s.
I look forward to reading about pivot tables.. I use them all the time. I tried to get your charts to work in a pivot chart but didn’t get it to work. : (

manish May 20, 2008

hi

how do you convert numbers to figures eg 1207 to one thousand two hundred & seven

Chandoo May 20, 2008

@ Manish .. thanks for the comments, welcome to PHD…

try this UDF http://www.ozgrid.com/VBA/ValueToWords.htm to convert a value to words, not sure if that works for all the values… let me know if this helps :)

Gaetan May 23, 2008

Chandoo, I love this page!

I have a question, I don’t think it’s achievable though.

Let’s say I have a ‘pass value’ of 10. Anything above 10 will be green (it’s above the pass value) and anything below 10 will be red.

This is easy to do with conditional formatting as per your example.

What I’d like to do is that in a cell where the value is 12 (hence green), and where its predecessor was higher, then I’d like to have the down arrow next to the green 12 to be red.

So the 12 will be green Still above the ‘pass value’ but the arrow will be red (since the value is lower than its predecessor).

Let me know what you think, I looked around but could not get that to work.

Thanks again :)

GWBE June 10, 2008

Hey Chandoo,

Stumbled here while reading one of your more recent entries.

Here’s my excel headache of the day – There seems to be a limit of 3 conditional formatting rules you can set up on a set of data. You can make it 4, if you take not having any rules set up as one of the rules.
What if I need to highlight 5 sets of data with 5 different colours? Is it possible through conditional formatting?

Chandoo June 11, 2008

@Gaetan .. thanks for visiting PHD, I couldnt respond any earlier. Somehow I thought I had answered your query, my mistake. Well, As far as I know, its not possible to color different parts of a cell with different formats using conditional formatting or formulas. You can do this with VBA though.
A simple workaround could be to have another column next to the one with values solely to display arrows and use conditional formatting on it based on the values in the data column. I hope you are getting what I mean here… well, drop me another comment if you dont, I can write a post on how to use cond. formatting with data…

@GWBE .. thanks, well, you answered your question :) if you need 5 or more conditional formats, you can either do it in google docs or write some vba. Now, vba option would require running the macro / sub every time you change your data which can be painful. I am thinking of writing a generic macro that can take formats from predefined cells (range) and apply it to data range based on the condition outcomes, but not sure its worth the pain.

One more ridiculously simple option you can try is using find/replace dialog to replace with your own format for cells meeting the find criteria. This way, first you need to define format for a cell in another column (that would purely contain values like 1,2,3,4,5,6,7,8,…n, each one defining one condition) and the search for cells matching 1 and replace them with format. This can be useful incase you need to format a table with more than 4-5 colors and need it urgent and for one time… otherwise vba is the best option…

@everyone.. let me know if you still need a soln. for more than 3 cond. formats, I can work on that generic vba soln. in my spare time :)

GWBE June 13, 2008

Thanks man!

Sixfive June 17, 2008

I have columns of numbers (a stat sheet). I’d like to highlight the max and min numbers (different colors) in each column to make the statistical leaders stand out. Is this possible using conditional formatting? I’ve used CF before but not to the extent you show. I appreciate any help/guidance.

Sixfive June 17, 2008

Sorry…figured it out. =B1=max(b1…b9). This would determine if B1 was the max value from b1 to b9. Great site…

Chandoo June 17, 2008

@Sixfive … Its amazing you found the solution in 10 mins, I am happy my little site could help you in becoming more productive .. thanks for visiting PHD … :)

hamo June 21, 2008

hi I need to know how work in excel environment and to be professional in using excel

Chandoo June 23, 2008

@hamo.. you are at the right place… just keep visiting phd, may be you want to start with posts in excel category – http://chandoo.org/wp/category/excel/

Love Excel June 24, 2008

Love the tips you provided. It definitely helps, but it seems your information may be a bit advanced for first time users.

xRITE July 5, 2008

test

colebro July 13, 2008

Chandoo, I have spreadsheet architectural diagram/map of hardware in our datacenter (SAN Directors and Storage Arrays). Each SAN Director has multiple storage array relationships (Directors ISL’d to multiple Arrays). I am looking for a way to highlight an Array or Director and all it’s relationships. Example: Enter in a serial # of an array and highlight that array box (serial# in cell), and the the SAN Director(s) and ISL’s that are related to the Array I am doing the search on. Conditional formatting is limitted to 3 conditions and I need many more than that. Any help would be appreciated.

Chandoo July 14, 2008

@Colebro: thanks for visiting PHD blog…

I am not able to visualize the problem you are having.. Do you mind sending some sample sheet to my email id… I can take a look at it during my spare time and get back to you :) Otherwise, drop in another comment with a google docs link or images of the sheet…. so that I can answer your query…

Sands August 3, 2008

Marked you in Delecious .

Thanks Chandoo for the excellent excel tips. And i thought i was an expert myself in excel !!
Alt +W+n has changed my life….. Just the thing i was looking for.

I have gone through almost all your post in category excel but couldn’t find out the answer to this :
I receive data from different customers in a sheet. 25 sheets from 25 customers. This sheet is designed by me. Consolidation in a new workbook with 17 sheets is easy.
The problem arises when the customer changes the format of the sheet

Is there a way to allow access to certain cells only.?
Something better than Tools => Protection => Allow users to edit ranges.

Thanks

eric August 5, 2008

Conditional formating is nice, but I wonder if there is something “more powerful?” To explain, one limitation seems to be that conditional formating appears to be capped at three conditions. What if I wanted to have a sheet that tracked projects for 5 separate clients and each client have it’s own color. I would love to be able to put in a specific client value to a particular cell and then that automatically put the entire project row in the corresponding color, but conditional formating (at least in the 2003 version) seems unable to do this.

So yeah, it is a great tool, but it’s kind of frustrating because it makes me want to do more than it’s capable of.

Roughim Redd August 11, 2008

Hi the moment i saw this site, just thought had i been u. well thanks for whatever posted i learnt a lot yet atiny drop in the vast sea.
Actually i have been scearching for a portfolio, and i have got that but my problem is that all of those are single users and i want multiple investor with multiple agents. My work is to buy/sell shares through different agents for various investors and i am paid monthly for that. Can u please help me in this matter. Thanks– Have a nice day.

Chandoo August 11, 2008

@Sands: Welcome to PHD…

your query… I receive data from different customers in a sheet. 25 sheets from 25 customers. This sheet is designed by me. Consolidation in a new workbook with 17 sheets is easy.
The problem arises when the customer changes the format of the sheet

Is there a way to allow access to certain cells only.?
Something better than Tools => Protection => Allow users to edit ranges.

well, I use google docs spreadsheet forms [more: http://chandoo.org/wp/2008/07/14/use-google-docs-to-track-your-bucks-spreadsheet-forms-idea/ ] to get data from my colleagues in India, this works well since they dont have to understand the spreadsheet format to enter data, even though this is a good idea when you want to get data from multiple sources, not suitable for all occasions. If the consolidation is something that you do everyday, I would advice you to create a vba form to get the data, as it is one time effort and saves you a ton of time in ensuring the format is right. Also, is it possible that all the people who enter data can access a shared network location, if that is the case, you can just create a shared workbook and ask them to enter there.. that way .. you dont have to go through 25 sheets to fetch everything you need.

But I guess, none of this may help you.. so let me plea our readers for help.. if you can solve Sands’ problem, a donut will be waiting for you :)

Chandoo August 11, 2008

@Eric: hmm.. you seem to be stuck with the same problem our earlier commenter GWBE had. hmm, builds a case for writing a small vba macro to do conditional formatting if there are more than 3 cases. I am working on it and will share with you all soon.

Welcome to PHD :)

@Roughim: I am sorry, but I could not understand your query, can you rephrase it …? Thanks for visiting my site…

Saim Baig September 2, 2008

Never thought it could do such amazing things.Good job.

samsul September 22, 2008

how if i make a link with your site ?
thanks
http://www.excel4share.blogspot.com

Chandoo September 23, 2008

@Saim Baig .. Welcome to PHD, thanks for the wonderful words. Subscribe to this blog to receive many more good tips

@Samsul… feel free to link to articles here along with a brief summary (if needed). Just copy the URL shown in the address bar and use it. You can also include a widget of this blog on your site if you wish… visit: http://chandoo.org/wp/subscribe/

CastleSteps September 23, 2008

This is a great help. I work at a hotel in Prague, and this will be a huge help for us as we use Excel constantly and this gives us a new way to organize the info so that the most important things can be read easily. Thanks for the tip.

Uma September 26, 2008

Hi – I find the blog useful. I have a query.
I have a Column A with some data & in column B I need to enter some figures. If the figure entered in column B is not equal to column A, it has to be highlighted (I use Conditional formatting here). This is something I can do for 1 cell. However if I need to have this conditional formatting done across 100 cells, I am not able to C&P using Paste spl – format route as the figures in column A are not uniform & change.
How do I Copy paste the format wherein the format remains intact but allows the cell value to change?

Robert September 26, 2008

@Uma,

change references in the formula used for conditional formatting in B1 from absolute to relative (e.g. =A1=B1 instead of =$A$1=$B$1) and you can simply copy down B1 using paste special/format.

Melody October 24, 2008

How do I copy and paste a conditional formatting formula but to auto update info.

For example:
Persons Name date
Persons Name date
Persons Name date
Persons Name date
Persons Name date

So, i have a large list of names. If there is no date next to the name, it highlight the name. (Basically telling me they didnt complete something). So I want to just be able to drag that formatting down. A1 now is looking at B1. But when i drag it, A2 looks at B1. I want it to look at B2. Do i seriously have to go in and format every cell?

Chandoo October 24, 2008

@Melody: Welcome to PHD and thanks for your comments.

Ofcourse you dont have to reformat every cell separately.

First, you can define conditional format for cell A1 using some condition like this, =b1="" make sure you write b1 and not $b$1. Then save the format by clicking ok.

Now copy A1 and then select cells A2: A100 (or An) and paste just formats (menu > edit > paste special > formats only). You can use format painter as well. This copies conditional formatting from A1 to all the cells in the list and ensures that the references remain relative, thus from b2:b100.

Let me know if this doesnt help :)

Waseem Nawaz November 5, 2008

This is simply exotic… Excellent use of excel.. Am impressed man…
I would be obliged if any one tells me about some more similar tips…my email is waseem_nawaz@hotmail.com… Carry on great work

Chandoo November 5, 2008

@Waseem : I am happy you liked this. I have sent an invite to your email ID for joining daily PHD news letter. Please follow the instructions to get more tips everyday :)

iip November 12, 2008

I found this awesome blog,
I’ll visit it again and again.

nice to know you,

greeting from indonesia

Chandoo November 12, 2008

@IIP: Welcome … :)

Steven JP November 12, 2008

Hi Chandoo

Stumbled across your site twice in the last two days for two different excel reasons – thanks for sharing such useful excel tips :)

Quick question relating to conditional formatting – I have a sheet that compares a value with two others, shading only occurs when the value is higher than both of the other two. However, there are some occasions when the comparitive values are dashes and the conditional format doesn’t get applied. It’s like excel thinks a dash is ‘higher’ in value. I’ve tried changing the dash to a word, still no joy. Only way I can see to make it work is to make the dash a 0 but strictly speaking this isn’t correct!

Any thoughts would be very much appreciated :)

Cheers

Chandoo November 12, 2008

@Steven JP: Welcome to PHD blog. I am happy you liked the site :)

you can use isnumber() function to check if a cell has valid numbers or not.

for eg. instead of writing =IF(AND(D2>B2,D2>C2),”shade”,”leave”)
you can write =IF(AND(OR(ISNUMBER(B3)=FALSE,D3>B3),OR(ISNUMBER(C3)=FALSE,D3>C3)),”shade”,”leave”)

that way, you will see “shade” even if the cells B3 and C3 have non-numeric values like “-”

Let me know if this doesnt help. :)

Steven JP November 14, 2008

@Chandoo – thanks for the additional advice :)

Swapnil December 11, 2008

Hi Chandoo,
this is Swapnil from mumbai. I like this blog very much + the newsletter too. Thanks for it.

Just would like to suggest you to include some tips about pivot tables. ie apart from basic layout,formulae tips,pls suggest some new tricks/method if you have any..
Thanks man..

Chandoo December 17, 2008

@Sawpnil: Thank you very much visiting the site and giving your suggestions. I will try to cover pivot tables in a post as and when possible.

karthick February 18, 2009

Plz look into this formula

Chandoo February 18, 2009

@Karthick, I dont see any formula. Can you type it once again ?

Michael February 24, 2009

Hi,

I am trying to write a conditional formula under the Formula Is category of Condition1. I want to compare 2 sets of cells. For example: =(c4>mb) AND (c4>D4). When I put the AND in, it gives me an error. Please help. Thanks.

Ilir February 25, 2009

Hi Chandoo,
Thank you very much for such a good site and useful tips and templates.
Please can tell how to create a calendar such the one you’ve made, I am talking about the mini one but I want to have the months vertically and the days horizontally. I tried to use the formula’s you have provided, but end it in a mess. :(

best regards
Ilir
P.S By the way the info about the camera tool was so great. Well done for that. I didnot know it even existed there!

Chandoo February 25, 2009

@Michael: Welcome and thanks for asking your question.

There is no AND operator in Excel. You should instead use AND formula. Like this: =AND(c4>mb,c4>D4)
now it will work. Let me know if you still face some problems.

Btw, if you are having difficulty with formulas, try our online formula help tool : http://chandoo.org/wp/excel-formula-helper-index-cards/

Cyril Z. March 3, 2009

Hi Chandoo,

This is simply sooo useful for me I can’t thank you the right way. BTW, I’ve noted that “bolding” incell graph detroy the scale.

OK, on one hand it makes them out like a a part of pie chart, but on the other hand, the scale is not respected at all…

I prefer the “normal” font for the graph part.

Cyril

Chandoo March 4, 2009

@Cyril: You are the 99th commenter on this post… and that makes me the 100th .. in a way you gave me the privilege to be 100th commenter on my all time favorite post. I agree with you that using bold on in-cell charts can be misleading. This is partially due to how excel 2003 (and windows in general) handles the bolding of text. For eg. when you bold a certain portion of text in office 2007, you wont see such drastic increase in text length. That said, I completely agree with you that applying bold distorts the scale and can be avoided (or used very rarely).

Once again thank you so much for commenting here and sharing your views. Welcome :)

Cyril Z. March 6, 2009

Happy my comment helps you in some way :-D

Simon P March 13, 2009

Great stuff Chandoo, I’ve been using some of these tricks for a while now – thought I’d give a little something back…

So here’s an adaptation that lets you alternate the row colours to group data quickly – good when there’s more than 4 identifiers.
Say this is my (sorted) data in column A:
Alice
Alice
Alice
Bob
Bob
Charlie
etc

I create a new column B, with -1 as the first value. Fill it down from B2 with this formula:
=IF(A2=A1,B1,B1*-1)
This fills the column with 1s and -1s, switching each time the value changes in A.
Conditionally format based on this column, and you’ll get (eg.) all of Alices rows in blue, Bob’s in green, Charlie’s in blue, Dora’s in green etc.
Better illustrated with pix of course…

Karen March 13, 2009

Hi

I need Help!
Can Conditional Formatting be transposed into Graph Form? for example using a traffic light system.

Red 0 to 50
Amber 51 to 70
Green 71 to 100

I can do this in a table format, but it is required in Graph Form using a Bar Chart so it is automatically Green, Amber or Red

Chandoo March 15, 2009

@Karen: this is not possible using conditional formatting, but you need to use multiple series to achieve this effect. I am planning to write a tutorial very soon on this… so please wait.. :)

Karen March 15, 2009

Hi Chandoo

Thank you for the Info, I await the tutorial and thanks for coming back to me.

Loves March 18, 2009

Hi Chandoo,

I’m new here and even if this is my first time reading through your tips I am very amazed and thankful of your wonderful excel usage.

By the way, I have a query. I have a worksheet that contains product names and their sales amount. What if I want to highlight an entire row where in the product name includes the word “TOTAL”, there are a lot of rows with the word “TOTAL” plus a different product names per row. What formula can I use in conditional formatting for this?

Thank you in advance and I love your Site (^_^) More Power!

Rachael March 19, 2009

Hi Chandoo,

Fantastic blog, It’s been really useful. I have a query though… I’ve got a column (B) containing different text data, some of which is in bold, some in normal font. What I want to do is create another column (A) where (for example) if B3 is in bold font, then A3 reads ‘T’, but if B3 is in normal font, then A3 would read ‘C’.

Do I really have to add in the data manually to every cell in column A? Or is there an easier way with conditional formatting?

Any ideas? Thanks :-)

david March 26, 2009

Wow – you are such a badass Chandoo! Thanks for sharing with the community!

Rob March 26, 2009

Hi Chandoo, well-written info – great tricks and tips. Question: do you know of a way to provide greater transparency into the Conditional Formats currently in effect in a worksheet? I use Conditional Formats a lot, and it would be nice to have a relatively easy way of viewing them, without having to go into each cell…

Thanks again for your great posts!

Amit Arora March 31, 2009

Hi Chandoo,

I have been a regular visitor of your great site in 2008 and lost track somewhere in 2009..am back again. I remember seeing a post where you told how to put in sybols like check mark or a cross mark using conditional formatting..I cannot find it now. Can you direct me? I want that instead of having Yes and No in cells, I shall get check mark and cross mark

Rickard Warnelid April 1, 2009

Great article on conditional formatting. This is even more useful than the more basic page which I liked too.

I have tried to extend the conditional formatting using circular high-lights around the selected cell, and it’s pretty neat even though I can’t think of any commercial applications…

http://blog.corality.com/2009/03/vba-and-conditional-formatting-in-excel/

What do you think? Can you think of an application?

Chandoo April 6, 2009

@Loves: I am sorry you had to wait almost 3 weeks to see an answer, but yes, you can do this using conditional formatting. There is a formula called FIND() which will tell you if a certain text is found in another text.

You can use it in CF to highlight the row. For eg. =iserror(find(”total”,$c1))=false would return true whenever the cells in column C contain the word “total”

@Rachael: Welcome. Unfortunately, there is no built in way to findout if a cell’s content is bold or normal. You can use VBA to find that out. But it would be cumbersome. A better solution would be to use some other way to determine (for eg. if you are bolding all the cells with above average sales, then we can use formulas to find that)

@Rob: In excel 2007 they have introduced a conditional formatting manager which provides you a lot of information on all the CFs in a workbook. In earlier versions, you still need to do a lot of guess work.

@Amit: hmm.. I dont remember such post. But I have written a few on using symbols, you can see them here: http://chandoo.org/wp/2008/08/07/display-smileys-excel-dashboards/
http://chandoo.org/wp/2008/08/21/display-symbols-excel-chart/

@Rickard: Welcome. I like the first use, generating cursor pointers using CF. I think it will be useful when you work a lot with spreadsheets and need to know where cursor is…

Btw: you have a very good blog…

Rickard Warnelid April 6, 2009

@Rachael If you were to implement a solution based on the bold then maybe populate a new column with the info based on the text formatting (bold/normal => 0/1 ) and then use the new column for the conditional formatting. Ideally I would recommend not using bold to drive any mechanics but rather use the 1/0 as an input and then use that to feed the conditional formatting and the bold/normal.

Michael April 8, 2009

Hi Chandoo!
Great stuff Chandoo, I’ve been using some of these tricks for a while now. Using symbols triggered by conditional formatting is petty nice to structure dashboards. I use this very often combined with an adapted color palette.
But the problem is, that using visual signals from the windings font or other font families may look neat on the screen,…. but printed….. ??? – Different printers replace these signs by their own font substitution.
Any ideas?

Have a nice day, and keep rocking ;-)
Michael

Francois April 9, 2009

Great blog.
Here is my issue with conditional formatting:
in a row (in this case row 10) I have a column with a value to determine the color of the cell (=” =$C10=”"x”"”); this is the first condition, the second condition is =($B$5-D10)>$C$4 which turns the cell to bold if true.
Problem is if the first condition is true it never evaluates the second condition; is there a way to make it so two (or three) conditions can be applied to one cell?
Thank you

Chandoo April 9, 2009

@Francois: Welcome to PHD. Conditional formatting is evaluated sequentially and stops at first match. so if you want RED+Bold, you need to define a combined formula for condition 1, and only red as second formula. Then it works. You can use the excel function AND() to evaluate 2 conditions.

JT April 17, 2009

Chandoo,

AWESOME TIPS!!!!!!!!!

I need some help (been told that before) … I’m trying to use your tip of insert/symbol in order to get up, down, and left/right arrows in a cell, but I can’t get it to work the same. When I do it, it inserts the symbol but it only shows up as an arrow if I format the cell as Wingdings 3. otherwise it’s a character like P. However I downloaded the sample s/s and in those formulas (on my machine anyway) there are actually graphical arrows and a dot or bullet in the formula surrounded by quotes like this: =IF(D2=”G”,”?”, IF(D2=”Y”,”?”,IF(D2=”R”,”?”,”"))) and those arrows and dot remain regardless of the font for the cell. Can you shed any light on how I can actually get the arrows in there instead of alpha characters and formatting it Wingdingst??? Please???

THANKS IN ADVANCE!!! And thanks for a WHOOP@$$ SITE!!!!!

JT April 17, 2009

Well @#$%@#%$ … that formula sample I pasted above converted to characters too … sorry … didn’t know it’d do that … looked ok when I pasted it.

Chandoo April 19, 2009

@JT: the default font (arial or calibari etc.) have few arrow symbols. You can find them from insert > symbols dialog. I use them as far as possible. But in some cases you need special symbols that are only available in wingdings etc. In that case you need to change the font.

cybpsych April 29, 2009

hi chandoo, i have another challenge for u ;)

consider that i have 4 rows x 5 columns of numbers:

Line1: 1 3 5 7 8
Line2: 32 54 22 55 86
Line3: 0.1 0.2 1.2 0.8 0.9
Line4: 9 5 4 1 1

for the time being, i’ve been applying conditional formmating to EACH line; highlighting the MAX value of each row. I used format value based on rank (Top 1).

For example above, numbers that are highlight are: Line1 is 8, Line2 is 86, Line3 is 1.2 and Line4 is 9.

how do i simplify the process so that I only select a range of numbers (above) but for formatting each line instead?

REason that i’m asking because i have tons of similar data to format and it’s not feasible/practical to set rules to each line (just to highlight the MAX value in it)

thanks!

Rickard Warnelid April 29, 2009

@cybsych,

I think I have a solution for your problem. To explain it properly I have entered your numbers in A1:E4.

The conditional formatting would then be

“Formula is” : =MAX($A4:$E4)=E4

Just make sure the formatting is ok and you shouldn’t have any problems.

The approach is pretty similar to this example:

http://www.navigatorpf.com/training/tutorials/dynamic-cross-cursor-in-excel-vba

Regards,
Rickard
http:blog.corality.com

cybpsych April 29, 2009

hi Rickard, i think u got the wrong idea (sorry!) :)

considering the range A1:E4 above…

i’m thinking of selecting this range, but in the conditional formatting (formula), it will highlight the MAX value (cell) in EACH ROW (i.e. each row will not interfere with the other rows)

the target result for each line is:

line1 – 8 will be highlighted
line2 – 86 will be highlighted
line3 – 1.2 will be highlighted
line4 – 9 will be highlight

what i’m doing now is very manual intensive: select line1, condition format it … select line2, condition format it … select line3, condition format it .. select line4, condition format it ….

i have hundreds of such lines :(

Chandoo April 29, 2009

@Cybpsych… the method Rickard suggested should work when you select all the cells in range A1:E4. The formula using relative reference for rows and absolute reference for columns. Just change the range to your actual range of cells and it should work smoothly.

Let us know if you face some problem.

Rickard Warnelid April 29, 2009

@cybpsych, yep, Chandoo’s comment is right. Just select the whole range before applying the conditional formatting. Drop me an email if you want me to forward the workbook.

Firstname.lastname@corality.com

cybpsych April 30, 2009

hi guys, i found out what’s the problem ….

i think i forgot to mention that i’m sing XL2007…

problem is this: if the condition format’s formula is =MAX($A4:$E4)=E4, then Line2-to-Line3 are all highlighted.

somehow, i accidentally solved this … when i pasted the formula, XL changed it to =MAX($A1:$E1)=A1 … then all the rows are *properly* highlighted ;)

well, that solves the problem ;)

thanks guys! you’ve been very helpful! :D

Jonathan May 7, 2009

I have a problem figuring out a way to conditionally format the following.

I want colum (v+) to highlight if (visitor) scores by more than 2
I want colum (v) to highlight if (visitor) scores by 1
Also the same principles for the (over) (under) columns
example:
V+ V T H H+ OVER # UNDER
1.70 1.40 3.00 2.20 2.60 1.80 5.5 1.60

tejas May 24, 2009

hi
m from mumbai
need to learn ms excel
i work for an investment co my clients ask for their weekly investment presentation , i really need to know how to do it .. how to give them report regarding their shares .. they want me to give a chart which shows them their buying cost ,average cost (if bought at diff price) current rates , value, etc etc..
i also want to learn excel in deep which would help me in my future
sir can u please help. my job needs these art very much now.

Chandoo May 24, 2009

@Jonathan: I am sorry, but I missed your query. Did you manage to solve it. Otherwise here is the solution.

You have to apply conditional formatting to each column separately. For eg. Select column V+ and in the formatting rules you can mention something like, >=2 and set the formatting as you prefer.

Just repeat this process for all columns and you will be able to highlight based on the values.

@Tejas: Welcome to PHD. You have come to the right place. I would recommend that you sign up for our email updates or RSS feeds so that you can receive new posts as they are up. Also, I suggest reading the excel and charting archives: http://chandoo.org/wp/category/excel/
http://chandoo.org/wp/category/visualization/

You may also want to check out some of the other popular resources for excel to stay up to date. You can start with http://peltiertech.com/WordPress/
http://blog.contextures.com/archives

Feel free to ask your questions here and I am sure me or some of the regular readers will be happy to help you.

DeskDiva June 12, 2009

Is it possible to use conditional formatting with vlookup? I would like to format all the values in column A based upon a textstring in column B.

Chandoo June 14, 2009

@DeskDiva: Welcome to PHD and thanks for asking a question. Yes, you can use vlookup with CF. I am not sure if I understood your question correctly though. Do you mean to say, you want to highlight all the cell in column A that have a string in cell B1 or something like that?

If that is the case, you can use find() or search() formulas and not necessarily use vlookup(). In the above article, the tip #4 talks about this.

If you need to use vlookup(), make sure you use absolute references (like this: $B$1:$C$10)

Let me know if you have more questions…

GrayWolf June 15, 2009

Hello Chandoo and hi to everyone else *waves*.

Been using your advice now for a few months, thank you for the awesome site and help you provide, and wanted to post up a question.

With respect to “2. Creating a quick project plan / gantt chart using conditional formatting:” you have posted above, can you or any of our readers think of a simpler way to create a gantt chart, in excel, to measure work done during a day. The data would need to be tracked on a “minute by minute” basis. I guess what it comes down to is trying to reduce the number of columns – each day is 8 hours, but start and stop at different times. I just feel that 480 colums seems like a lot to me.
Any advice and or tips are appreciated.

Chandoo June 16, 2009

@GaryWolf… welcome to PHD.. *waves even faster*

You can try using REPT() to create incell bar chart that prints spaces until start time and then prints | (pipe symbol) for the total minutes spent working on a task. It is very simple to develop and works smoothly. Some examples are here: http://chandoo.org/wp/tag/in-cell-charting/

Another option is to use actual bar chart, that way, you can just make one bar chart and forget about the nitty-gritties about the columns etc. Here is a useful tutorial:

http://chandoo.org/wp/2008/05/20/gantt-in-60-seconds-churn-out-an-excel-project-plan-in-no-time/

GrayWolf June 16, 2009

Awesome tip Chandoo, thank you.

I’m really liking the the REPT function. Ha ha , I have used it for may other graphs, surprised I did not think of it earlier. I am familiar with it’s normal, and easy, use.

I was wondering if you might assist in a REPT formula that can creat a Gantt like effect using a start number and an end number.

eg. If my total range was 0-10 but the start was 3 and the end was 7 how do I get the gantt like efffect to fill in spaces for 0-2 then fill in 3-7 with the “bars”.

I have a formula that can put the spaces in, but only get it to mark either the start OR the end, but not both in the same furmula.

I hope this makes sense.
Thank you again.

GrayWolf June 16, 2009

Hello, actually I think I found it … maybe a bit sloppy, but it works for me. Just tooled around with the formula a bit and voila.
A little concatenate can work some magic.

Here it is:
(FYI – 100 is the max value on my report)

=REPT(” “,start)&REPT(”|”,(end-start))&REPT(” “,(100-end))

Thank you

Chandoo June 16, 2009

@Gary.. kool. You are right, using REPT() with concatenate operator does the trick. You can see a more complicated but fun example of this technique in the in-cell dot plots post: http://chandoo.org/wp/2009/06/10/dot-plots-in-cell/

GrayWolf June 25, 2009

Thank you for your assistance Chandoo. Got it working perfectly witht he dot plots too.
Fyi: it’s Gray not Gary (^.O)
Peace.

sonato July 2, 2009

Hello chandoo,
I love this blog ever since i discovered it
barely a week ago
my problem is that i use excel 2007
which makes the above
a little difficult for me to demystify
any assistance?

ankit July 28, 2009

hii,
i want to make an attendence sheet and only 2 option present or absent to be there.

If a person is absent in front of its name cell we should have only 2 option to fill either absent or present and if present it should automatically fill with green and if absent it should automatically fill red

how to do this please suggest.

Sandeep August 16, 2009

Hi.. I am a die hard fan of excel. I haven’t learnt it from anyone… Just hit and trial method… a little help from here and there.. from internet etc. Till now I have found whatever I required, related to my work, as and when the need arose. But I certainly believe that having thourough knowledge of all functions is always useful. What I do is that I get some sample spreadsheets (for anything I need to learn), study them, practice them and use them in my work. But I really really wish to learn excel in a professional way… is there any way??… I am sure you would be having some way…!

Chandoo August 21, 2009

@Sandeep… Thanks for asking this question. I am planning to start a training program on this site for people like you. It will cover all important areas of excel and help you become an expert in using excel. I will update you once the program is ready.

JimH September 18, 2009

Chandoo, thanks for all your work on this site. I am trying to figure out how to identify clients of mine in a national report of company clients. I am not new to Excel but still a novice. I came to your site to learn about conditional formatting to help me accomplish this task and it does not seem that this will be able to pull this off ether. Am I wrong? Or do you know of something else I can try?

Chandoo September 21, 2009

@Jim.. Welcome. You can do this using conditional formatting. The technique is fairly straight forward. Assuming all your clients are listed in the named range jimClients

and assuming your national report has client names in the range A1:I2000, select this entire range and apply conditional formatting by rule like this,

=countif(jimClients,A1)>0

and set the formatting to the highlight color you wish.

This should highlight all the cells in rane A1:I2000 when the value is in jimClients.

Alassane October 28, 2009

I want Apply Excel Conditional Formatting to a column with dollars and units. All depending of the total Sales if ($) >= 50,000 Green
and < 50,000 red then I have to color their units as well.
Can you please help.
Thanks

Chandoo October 29, 2009

@Alassane… Welcome to PHD and thanks for the comments.

Here is how you can do this. Assuming dollars are in range A1:A10 and units in range B1:B10, Select the range of cells and open conditional formatting dialog.

Set rule 1 as “Formula is” “$A1>=50000. Set the formatting as green fill color.

Repeat the same but enter “$A1<50000" and color as red for rule 2.

Now you should see the colors. Let me know if you have any problems.

The trick lies in understanding relative vs absolute cell references. We have made the column (A) absolute and row (1) relative. So even when applying formats to column B (units), excel checks the dollar amount (col A). See more here: http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/

Jessica Hall October 29, 2009

I am trying to apply conditional formatting to a cell. I have three conditions : if the cells contents equals “H”, fill the cell with one colour. If the contents equal “SL”, fill it with another colour. If the contents equal “PH”, fill it with another colour. “SL” and “PH” work fine. I have done nothing different with “H”, but it won’t change colour. There are some cells on another page where “H” does fill in. When I copy the cell, the “H” stays in that colour. If I then delete the “H” and re-enter it, the cell stays white. The letters are selected from a drop down list, if that has any bearing. I’m using Excel 2003, and this is on a work computer, so I can’t update it in any way. Please can someone help?

Alassane October 29, 2009

Hi Chandoo,

The data is not in different column, it is in the same column for instance:
Sales $25,000
Units Sold: 201
Sample Units: 20

Then Another one same column but different product
Sales $55,000
Units Sold: 201
Sample Units: 20

so on. My boss does not want to change the vertical view to horizontal.
How can I do color the one >=50,000 and less than 50,000
Thanks

Chandoo October 30, 2009

@Jessica: seems strange. Here is what you can do. Select all the cells where you wanted CF. Go to Menu > Edit > Clear > formats. This will remove all the formatting done on these cells. Now go back to conditional formatting dialog and set the rules 1,2 and 3 to match the text H, SL and PH. This should work..

@Alassane.. oh.. is the data strictly 3 cells per item ? then you can use CF with formulas to do this. You need to use either OFFSET or INDEX to do this.

For eg. assuming the values are in A1:A30, you can apply the CF with this rule…

=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/3)*3,0,1,1)>=50000
Remember, A1 should have dollar amount, A2 units, A3 sample units. If you have ‘n’ cells per item instead of 3, just replace the /3)*3 with /n)*n

All the best.

Jessica Hall October 30, 2009

Hi Chandoo, thanks for the reply, I seem to have solved the problem, but I don’t know why. The whole issue hangs on the letter “H” (at least, I have experimented with various letters and phrases, and all else works except for “H”). So I then wondered if Excel couldn’t ’see’ the “H” because it thought it was something else. I have set the cells to “Text” and this appears to work. What I can’t explain is why no other letters affect it thus – could it be because “H” is used in connection with a short cut (at least, I think it is)? I haven’t experimented with, for example, “F” yet. When I regain the will to live, I will try your method, it would be interesting if that solves it as well, and seems to be a more ‘clean’ solution. Tell me, has anyone else had the temptation to shout, “Stupid thing!” at their Excel spreadsheet?! :-)

Alassane October 30, 2009

HI Chandoo,

I try it but it is not working
I have to give you an example
Fill Size + Configuration
Data 5 [1] 20 [1]
Sales $35,104 $116,851
Local Units Sold 721 3,175
Free Goods Units 7 4
Sample Units 2 4

Another one so on

Sales $72,976 $25,000
Local Units Sold 538 530
Free Goods Units 3 1
Sample Units 2 2

It is in many colums and different rows on the pivot.
The sales depend on the fill size of the goods and the products.
Again I want to highlight in green the sales >=50000
and in red sales <50000
Thanks for your effort…

emre November 4, 2009

very useful, thanks a lot

Chandoo November 5, 2009

@Alassane… can you upload the file somewhere and give us the url.. I am not able to visualize this.

@emre… You are welcome

Alassane November 9, 2009

Chandoo,
Thank you very much for your help,
I did it.
Thanks

Chandoo November 9, 2009

@Alassane.. I am happy for you.

savithri November 29, 2009

Hi,
This query refers to the 5th part of the above post.
Suppose the value and % is to be displayed with one decimal place in the dashboard and one or more of the values or % have zero after the decimal point for e.g., in your illustration, cells C11, C13, L10,H12…
To display values with one decimal, if I use instead of
ROUND(C11,0) & ” ” & IF—
this:-
IF(OR(ROUND(C11,1)=INT(C11),(ROUND(C11,1)=INT(C11)+1)),ROUND(C11,1)&”.0″,ROUND(C11,1))&” “& IF—-
the values get displayed with one decimal place.

Whereas, in the % panel, cells like L10, H12 have values, whose formula if is changed from
Round (cell reference *100,0)&”%”&IF—
to
Round (cell reference, 3)*100 &“%” &IF —
the values don’t get displayed with one decimal.

I’m struggling for the past half hour to make something similar to yours (for someone who is bent upon having one decimal place in the %) but with no luck till now…Would it be possible to advise?

Chandoo November 30, 2009

@Savithri.. try this: =ROUND(cell ref*100,1) instead of =ROUND(cell ref*100,0)

savithri November 30, 2009

Thank you but sorry – It doesn’t work. Pls ref value in cell H12 in your illustration. It is 73.95% which, when rounded off, displays 74% and not 74.0% as desired.
Wherever the %s on rounding off becomes #.0, when concatenated with “?” or “?”, don’t get displayed with one decimal.
(I am trying to display EVA (nominal values) & EVA rate of return %s where the %s are not that high and I’m told that the decimals are significant).

Chandoo December 1, 2009

@Savithri… Thanks, I didnt realize that earlier. You can try =TEXT(ROUND(H12*100;0);”0.0″), it forces the format thru TEXT formula.

savithri December 1, 2009

A Million thanks. It never once occurred to me to use ‘text’.

Pushkar December 8, 2009

Is it possible to use conditional formating in bar/columns charts? I mean if you have a 10 column chart and apply conditional formating to them:
eg. Values > 70 to be green coloured charts
>40<70 to be yello and < 40 to be red? I know its possible to change colours manually, but is it possible to apply conditional formating to columns / bars?

Chandoo January 7, 2010

@Pushkar… You have to use different chart series to achieve that.

FAZAL February 2, 2010

That really helpful…really nice and cool

KJ March 8, 2010

I’ve been using the REPT command with “|” to create a % completion bar. It looks great on screen but when I print, the bars are shifted and it no longer looks as it does on screen. Any solutions? Thanks!

Gary April 7, 2010

Thanks so much. My Excel charts look much better and are far more effective with the conditional formatting tips as described.

Yip Bop July 7, 2010

Hi, regarding the Gantt example, what formula would I need to use to automatically change the color when a task is finished or the date is complete?

Thanks

Andrew July 8, 2010

Hi Chandoo

I’m trying to solve the following conditional formatting problem:

In the table below I’m trying to:

- get the “AGE OF BUILD” by using the following conditional formula is: =TODAY()-A1>730, where >730 is the number of days (2 years) before the “BUILD” needs to be inspected. The “DATE OF BUILD” cell or the “AGE OF BUILD” cell needs to change colour to yellow.

Whichever cell changes colour, that same cell will also need to change colour to orange when the number of days reaches 365 before date of inspection.

When the same cell reaches the date of inspection (every 5 years from date of build) it turns red

DATE OF BUILD AGE OF BUILD
2000 10
2001 9
2002 8
2003 7

Andrew

Chandoo July 10, 2010

@Yip… You can use a formula like =completion_date<today() to see if the date is past. Of if there is a column with completion status (say done activities are 100%), you can use that.

@Andrew: I understand that you need 3 colors – yellow, orange and red. But I dont understand what conditions you are looking for. Can you pls. explain?

Andrew July 12, 2010

Hi Chandoo

Thanks for the response.

Conditions

Each vehicle has a date of build and they are all required to be inspected every 5 years until they are decommissioned. To plan the inspections, and there are a lot and they are big, I would like notification in advance with a simple color change to the cell (Date of Build). The first notification to be 2 years prior to the inspection (yellow), the second notification to be 1 year prior to the inspection (orange) and the third notification to be the year of notification (red – inspection year).

Now the calculation becomes more involved. The inspections have to happen every 5 years, therefore the color changes need to occur as above – 2,1,0 and at the following intervals – 5, 10, 15, 20, 25, 30, 35, 40, 45 etc.

Thanks

Andrew

Chandoo July 15, 2010

@Andrew …

very good question :)

Assuming your dates of build are in A1:A10,

Select A1:A10, go to CF > Add rule > Formula based rule

write the formula =MOD(YEAR(today())-YEAR(A1),5)=0
Set color as Red

Select A1:A10, go to CF > Add rule > Formula based rule

write the formula =MOD(YEAR(today())-YEAR(A1),5)=1
Set color as Orange

Select A1:A10, go to CF > Add rule > Formula based rule

write the formula =MOD(YEAR(today())-YEAR(A1),5)=2
Set color as Yellow

This should work.

Clark August 5, 2010

Sir Chandoo,

You are a genius. I have learned a lot from your site.

Meenie August 6, 2010

I love your graph where you have the up and down arrows beside the numbers to show if they had gone up or down since the last month, however when I use them it takes away the numbers after the decimal in the sheet. My sheet is formatted to show two numbers after the decimal. why does it take these numbers away? Most of my spreadsheets are either numbers with at least one number after the decimal or percentages with at least one number after the decimal :( (I have Excel 2003)
BTW, I LOVE your page and all the great Excel info!!! thanks so much for doing this!

Hallo August 23, 2010

Hi Chandoo,
Wonderfull site! You explain very well.
Used a lot of your examples in my sheets.

Chandoo August 24, 2010

@Meenie.. Sorry I took so long to get back to you. Are you still facing this problem. It could be due to ROUND(…,0) thingie. You can set it to ROUND (…,2) to show 2 decimal places.

@Hallo.. You are welcome Hallo. Thank you so much for the comments.

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL