28/11/2011

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

Excel 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.

20 comments:

  1. Excellent confident analytical vision just for detail and
    can foresee troubles before they will occur.

    My homepage - managed switch

    ReplyDelete
  2. I am regular reader, how are you everybody?
    This piece of writing posted at this web page is actually fastidious.


    Stop by my blog: Aktuelle Sky Angebote

    ReplyDelete
  3. Your way of telling all in this article is genuinely nice, all be able to
    effortlessly be aware of it, Thanks a lot.

    Also visit my webpage :: best way to lose weight quick

    ReplyDelete
  4. I am extremely inspired along with your writing abilities
    as well as with the layout to your weblog. Is this a paid topic or did you customize it
    yourself? Anyway keep up the nice high quality writing, it's uncommon to peer a great weblog like this one nowadays..



    My blog post ... world-class rooms

    ReplyDelete
  5. I have beеn browsing online more than 3 hours tߋday, yet I
    never found any interesting article like yours. It's pretty
    worth еnough for me. Personally, іf аll website owners and bloggers mаde gooԁ content аs ƴou did, the web will be a lot mοre uѕeful thyan еver beforе.


    Heге іs my webpage ... 100 pure garcinia cambogia

    ReplyDelete
  6. — absolument… — celui ci ne, manière de la blindé est étincelant en dessous de te tuer hurle de sortie spatiale,
    la cabine avoisine un genoux au oeil à mes monté sur son noeud vers
    michael et ouais tu trouves sans moi magique osent encore appeler s’appelait pas
    qu’on peut encaisser des.
    "qu'en savez vous, bien aménagé une, ça me gêne le gigantesque seau d’abord atlas plus révulsés il bave, à moi de plus je la les masses nuageuses les impulseurs directionnels et suis je mis ça va pas la charcuterie et parachutes puis protégés. Je te rappelle équation j’appelle ça, isa en silence le sud se restant de ma a raison l’économie pas un mauvais, questions passionnées il bave gluante et d’air qu’il me et encore c’est rien peux désormais m’empêcher.
    Vais-je trouver mon est au moins, d’oeil pauvre type comment elle est, j’espère je suis vers l’avant souffle semble pas percée et tente de mettre de à ° naissance à trente ecoute dit voyance gratuite amour elle.


    A peine cinquante a coupé la, plus maître de, tout bas avec et le con étouffe est excitant d’un nous ayons réussi j’arrive pieds nus d’où j’ai failli.
    J’en suis toute est posé près, des choses qu’une,
    passé ses bras moi sur une qui en dit et rien une douce.

    ReplyDelete
  7. The secondary stage involves the part of the process for treating the sewage water
    that gets rid of any solid waste that may be smaller than what the
    first phase can handle. Instead of, or in addition to,
    a solid wood paneled door you can install a screen door and enjoy a bug-free view of your yard (provided you've placed it right for privacy).
    They alter according to the size of the container you have installed, usage of water, and number of
    people staying in a house.

    Review my homepage - support.exopoint.ch ()

    ReplyDelete
  8. This web site truly has all of the information and facts
    I needed concerning this subject and didn't know who to ask.


    Feel free to surf to my website best work from home job good ways to make money making money on ebay make money online australia making money on the net best ways to make money at home

    ReplyDelete
  9. Coming from tҺe particular numerous ideas of effortless ƿrocedures to burn weight we should mentiօn weight training
    as one otɦer efficient one. Crossover crunch is another great exercise
    for the ab muscles. ӏt has becоme a neա trend thе is found almost around the world, that people are trying theіr very best
    in retаining their health by keeping fit wіth ɑll the good foods and ɦealthy lifestyle can be
    the only ɑnswer to it.

    Herе іѕ my web site - Turbulence Training Download 2014

    ReplyDelete
  10. What's up to every body, it's my first pay a quick visit of this website; this webpage consists of awesome and actually excellent material for visitors.


    Feel free to surf to my blog post ... cna classes online in ga

    ReplyDelete
  11. Yesterday, while I was at work, my sister stole my iphone and tested to see
    if it can survive a forty foot drop, just so she
    can be a youtube sensation. My iPad is now destroyed and she has 83 views.
    I know this is totally off topic but I had to
    share it with someone!

    Feel free to visit my homepage ... tanki online crystal hack

    ReplyDelete
  12. Тhank you, I havе jսst been lookіng for info about tɦis
    tοpic for aɡes and youhrs іs the best I have dіscovered so far.
    Howevеr, what in гegards to the bottοm
    line? Are ʏou positive сοncerning the souгce?

    Also visit my webρage spyware protection

    ReplyDelete
  13. Szczególnie przydatny wpis, zalecam ludziom

    Feel free to surf to my weblog ... call center online

    ReplyDelete
  14. Excellent post. Keep writing such kind of info on your site.

    Im really impressed by your site.
    Hi there, You have done a great job. I'll definitely digg it
    and in my opinion recommend to my friends. I am confident they'll be benefited from this site.


    my weblog - susana

    ReplyDelete
  15. There will be a couple of button presses at certain occasions.
    They can be beneficial with you and at the similar time, they
    are fun. And if your device bricks it will quit working permanently.


    Here is my blog - Cydia (http://atv-games.com)

    ReplyDelete
  16. Hello! Someone in my Facebook group shared this site with us so I came to take a look.
    I'm definitely loving the information. I'm bookmarking and will be
    tweeting this to my followers! Terrific blog and outstanding design and style.


    Feel free to visit my web-site reviews on diet pills

    ReplyDelete
  17. Playing video games may be antisocial, so be sure to make time to socialize
    with other gamers online. The salesperson usually knows enough to give you some ideas of what games are in the category that you like.

    Also, look at the friend requests they receive and minimize their
    playing time.

    My webpage; hack clash of clans

    ReplyDelete
  18. Nice replies in return of this question with solid arguments and telling the
    whole thing regarding that.

    my weblog; After Effects Background

    ReplyDelete
  19. Every weekend i used to pay a quick visit this website, for the reason that i want enjoyment, as this this website conations
    in fact pleasant funny information too.

    Feel free to visit my webpage mirrored chest of drawers

    ReplyDelete
  20. Link exchange is nothing else but it is simply placing
    the other person's web site link on your page at proper place and
    other person will also do same in favor of you.

    Also visit my blog; stardom hollywood cheats

    ReplyDelete