Want to be an Excel Conditional Formatting Rock Star ? Read this

By Chandoo at 13 March, 2008, 8:50 pm



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

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.

Also read : Create dash boards in Excel | How to draw impressive charts in Excel | Changing the default chart settings to impress everyone



Categories : Analytics | Excel Tips | Featured | hacks | ideas | technology | visualization
Tagged with: | | | | | | | | | | |

Did you enjoy reading this post? If so, give me some love

Subscribe to Pointy Haired Dilbert - Chandoo.org - RSS FeedSave to Delicious by bookmarking this post Stumble this post  

Consider subscribing to my newsletter every weekday I will send you one email with hot excel tips, technology tidbits or business insights that can make you more productive. Each mail will have unsubscribe link so you can stop receiving the mails at any time you wish.



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.

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL