Conditional Formatting

Posted on July 30th, 2007 by James Sullivan
Generic Himcolin Buying Zelnorm Synthroid For Sale Premarin Price Mobic For Sale Order Remeron Detrol Without Prescription Buying Mentat Order Capoten Natural Vermox Rythmol Sr Pill Buying Eurax Purchase Levothroid Order Adalat Liv.52 Price Celebrex Without Prescription Pilex Price Kamagra Pill Stretchnil Pill Aristocort Price Order Atrovent Trandate Without Prescription Buying Shoot Purchase Herbolax Natural Isoptin Buying Aceon Buy Cardura Online Order Norpace Cr Aldactone Price Purchase Mysoline Abana Price Innopran Xl For Sale Buying Viagra Cialis Generic Didronel Famvir For Sale Buy Noroxin Online Medrol For Sale Buy Aceon Online Generic Mysoline Generic Trimox Ventolin Price Order Antabuse Buying Accupril Generic Calan Monoket Pill Deltasone Price Generic Singulair Augmentin Without Prescription Generic Flovent Order Kytril

Many of us are still using spreadsheets for tracking hours in professional service organizations. It’s easy to get time-sheets and track metrics in a quick Excel spreadsheet so we do it instead of soliciting IT to build or buy a system. Once you get more than a few sheets in your workbook it’s a common practice to build a cover worksheet that contains summary data - a dashboard of sorts.

Using conditional formatting, you can turn simple metrics into a little something more. You can set automatic formatting on cells to reflect the value within. This means you can automatically set the background color to red when the value is below an acceptable range. Here’s how you do it…

First, create your table (hopefully using a function referencing the actual data):
20070730-conditional-formatting-before.png

Select “Conditional Formatting…” from the “Format” menu:
20070730-conditional-formatting-step1.png

Using this dialog, you can define ranges of values and assign formatting elements.  In this case I used numbers (percentages) but you can use text also.  For instance you can set a color to green, use “greater than or equal to” in the middle box, and “Y” for the value on the right.  This codes “Yes” values to green.  The next rule could be “N” and mapped to red would give you a green/red based on answers to a questionnaire.

20070730-conditional-formatting-step2.png

When you’re done, juts click the “OK” button and your formatting will be applied to the cells.  You can always select the cells and return to this dialog to adjust the settings if you made a mistake or want to add another range.

20070730-conditional-formatting-after.png

This formatting makes key values really stand-out among a busy worksheet.

Tags: Excel 2002, Excel 2007, Excel 2003, Excel 2000 //

49 Responses to “Conditional Formatting”

  1. RYErnest // Dec 1, 2008 at 12:10 am

    Nice post u have here :D Added to my RSS reader

  2. trlbjb // Dec 16, 2008 at 6:48 pm

    ceguyomvpaxcetgolubxagbjzlwlcr

  3. ilpdtb // Feb 1, 2009 at 3:40 am

    fjmgttfhxhsflptbjyjiccsxrgdkbx

  4. zppnpj // Feb 1, 2009 at 3:29 pm

    hakirrzbusxbsgrtyxzcahgcbswgyl

  5. xavelu // Feb 7, 2009 at 5:44 pm

    qnxwpuoqjruglusciuxozzgtrjzwzn

  6. uyeavy // Apr 23, 2009 at 2:05 am

    fdflnkhwoouivswpzaxuxqgkghqwti

  7. KrisBelucci // Jun 2, 2009 at 4:32 pm

    Hi, good post. I have been wondering about this issue,so thanks for posting.

  8. GarykPatton // Jun 15, 2009 at 7:23 pm

    Hello, can you please post some more information on this topic? I would like to read more.

  9. CrisBetewsky // Jul 6, 2009 at 11:11 am

    Where did you take from such kind of information? Can you give me the source?

  10. CrisBetewsky // Jul 6, 2009 at 1:05 pm

    You know, I don’t read blogs. But yours is really worth beeing read.

  11. cialis på nettet // Jul 20, 2009 at 2:27 pm

    P3DQCm baowlvio kkwaachr qapltepu

  12. bqbess // Jul 29, 2009 at 5:00 pm

    fadrfknnzgdxbeupmxdsgwasnglisb

  13. viagra pas cher // Jul 31, 2009 at 3:05 am

    eqyontgp yavfjqph ovcdzbje

  14. viagra horn // Jul 31, 2009 at 8:26 pm

    auwvgyvv uamvxcum dpaqfqod

  15. epnmce // Aug 8, 2009 at 10:57 am

    vqbdztfmlbxethcflxbnvosiapaouk

  16. viagra generique // Aug 14, 2009 at 4:12 am

    sgoiedvb xeikovcp lplhdgbn

  17. cialis pas cher // Aug 22, 2009 at 12:36 pm

    qwgamyiv zdnfdxwi jpyjjciz

  18. Gundosmdp // Aug 28, 2009 at 4:05 pm

    Aloha! dpo

  19. viagra // Aug 28, 2009 at 8:50 pm

    igzezcix cfhsgpyu udbneqhh

  20. achat viagra // Aug 29, 2009 at 1:43 am

    slkeyojj rmaefwvh avhwjahb

  21. Google-TCW // Sep 8, 2009 at 7:51 pm

    Hi from google Google-TCW

  22. Joseph // Sep 17, 2009 at 12:13 am

    Thank you for blogging, please write more!

  23. byryndychek // Oct 26, 2009 at 2:34 am

    ребят смотрите тут,вроде норм - filmkolor.ru

  24. Îáîðóäîâàíèå äëÿ àâòîðåìîíòà // Jan 12, 2010 at 8:51 am

    Ñîâðåìåííàÿ ÑÒÎ ýôôåêòèâíî è êà÷åñòâåííî âûïîëíÿåò ñâîè ôóíêöèè ïðè ïîìîùè ðàçëè÷íîãî âèäà îáîðóäîâàíèÿ…

    àâòîðåìîíò…

  25. Ãîðíî-îáîãàòèòåëüíîå îáîðóäîâàíèå // Jan 20, 2010 at 2:29 am

    Øèðîêèé ñïåêòð ñîâðåìåííîãî øàõòíîãî è ãîðíîîáîãàòèòåëüíîãî îáîðóäîâàíèÿ íàñ÷èòûâàåò áîëåå 100 íàèìåíîâàíèé…

    âèáðàòîðû, ãðîõîòû, æåëåçîîòäåëèòåëè…

  26. Ñïåöòåõíèêà èç Êèòàÿ // Jan 25, 2010 at 9:48 am

    àñôàëüòîóêëàä÷èêè, àâòîêðàíû, áóëüäîçåðû, ãðåéäåðû, êàòêè, ïîãðóç÷èêè, ýêñêàâàòîðû…

    Ïîãðóç÷èêè èñïîëüçóþòñÿ äëÿ çàñûïêè, ðàñêîïêè, âûðàâíèâàíèè è äðóãèõ ðàáîò ñ ðàçëè÷íûìè ìàòåðèàëàìè…

  27. qyuzmy // Jan 28, 2010 at 8:11 pm

    flxrowzpxomathjqmtuqqznnblyxhc

  28. Çàðàáîòîê â ñåòè // Feb 7, 2010 at 3:35 pm

    Êàê çàðàáîòàòü â èíòåðíåòå…

    èíñòðóêöèè ïî çàðàáîòêó â ñåòè…

  29. Îòäûõ â Åãèïòå // Feb 9, 2010 at 7:43 am

    Ïÿòèçâåçäî÷íûå îòåëè Åãèïòà…

    Åãèïåò - æåì÷óæèíà ìèðîâîé êóëüòóðû…

  30. Ïðîìûøëåííàÿ âåíòèëÿöèÿ // Feb 13, 2010 at 4:51 am

    Âèäû âåíòèëÿöèîííîãî îáîðóäîâàíèÿ…

    Ñèñòåìû âåíòèëÿöèè…

  31. Îòäûõ â Òóðöèè // Feb 14, 2010 at 11:57 pm

    Ãîðîä Ñèäå ðàñïîëîæåí íà ïîëóîñòðîâå â 75 êì îò Àíòàëüè…

    Îòåëè ãîðîäà Ñèäå…

  32. Îòåëè Ãîà // Feb 16, 2010 at 12:31 am

    Ãîà íàõîäèòñÿ íà çàïàäå Èíäèè…

    Ìóçûêà, òàíöû, ïðèêëàäíîå è óñòíîå òâîð÷åñòâî ãëóáîêèìè êîðíÿìè óõîäÿò â äóøó Ãîà, à ðèòìû ïóëüñèðóþò â ñåðäöàõ åãî æèòåëåé…

  33. Ýëåêòðîòåõíè÷åñêîå îáîðóäîâàíèå] // Feb 17, 2010 at 2:10 am

    Êëàññèôèêàöèÿ è íàçíà÷åíèå êàáåëåé…

    Ýëåêòðîêàáåëè…

  34. Îòäûõ â Òóðöèè // Feb 18, 2010 at 1:32 am

    Îòåëè Àëàíèè…

    Êóðîðòû Òóðöèè…

  35. Ëåñîïèëüíîå îáîðóäîâàíèå // Feb 19, 2010 at 7:19 am

    Ïèëîðàìû…

    Áð¸âíîïèëüíûå äèñêîâûå ñòàíêè…

  36. Îòåëè Ïàðèæà // Feb 21, 2010 at 4:35 am

    Ïàðèæ - ñòîëèöà Ôðàíöèè…

    Ïðîâåäèòå îòïóñê âî Ôðàíöèè…

  37. Îòåëè Ïðàãè // Feb 22, 2010 at 8:41 am

    Ïðàãà - Ãîðîä ñîòíè øïèëåé…

    Ïðàãà - ñòîëèöà ×åõèè…

  38. Òóðû â Èñïàíèþ // Feb 23, 2010 at 4:20 am

    Êàíàðñêèå îñòðîâà…

    Âàëåíñèÿ…

  39. Íàñîñû è ãèäðàâëè÷åñêîå îáîðóäîâàíèå // Feb 24, 2010 at 5:40 am

    Ãèäðàâëè÷åñêèå íàñîñû…

    Íàñîñû è ãèäðàâëè÷åñêîå îáîðóäîâàíèå…

  40. Ïíåâìàòè÷åñêîå îáîðóäîâàíèå // Feb 25, 2010 at 2:39 am

    ïíåâìîàâòîìàòèêà…

    Ïðîìûøëåííîå èñïîëüçîâàíèå ïíåâìàòèêè íà÷àëîñü áîëåå ñòà ëåò íàçàä…

  41. Îòåëè Ìåêñèêè // Feb 25, 2010 at 11:12 pm

    Ìåêñèêà – âîïëîùåíèå èäåàëüíîãî òóðèñòè÷åñêîãî íàïðàâëåíèÿ…

    Ìåêñèêà ðàñïîëàãàåò ïî-íàñòîÿùåìó óíèêàëüíûìè âîçìîæíîñòÿìè äëÿ âñåõ âèäîâ òóðèçìà…

  42. Ìåäèöèíñêîå îáîðóäîâàíèå // Feb 27, 2010 at 6:51 am

    Ìåäèöèíñêèå èçäåëèÿ…

    Ìåäèöèíñêàÿ òåõíèêà…

  43. Îòäûõ â Èñïàíèè // Feb 27, 2010 at 11:17 pm

    ×åì òóðèñòîâ ïðèâëåêàåò Èñïàíèÿ…

    5 äîâîäîâ çà îòäûõ â Èñïàíèè…

  44. Îòäûõ â Òóíèñå // Mar 2, 2010 at 6:44 am

    Îáùàÿ èíôîðìàöèÿ î Òóíèñå…

    Äîñòîïðèìå÷àòåëüíîñòè Òóíèñà…

  45. Îòäûõ â ×åðíîãîðèè // Mar 3, 2010 at 10:20 pm

    Îáùàÿ èíôîðìàöèÿ î ×åðíîãîðèè…

    Äîñòîïðèìå÷àòåëüíîñòè ×åðíîãîðèè…

  46. Ìåòàëëîîáðàáàòûâàþùèå ñòàíêè // Mar 4, 2010 at 2:05 am

    Êëàññèôèêàöèÿ ñòàíêîâ è îáîðóäîâàíèÿ ïî ìåòàëëó…

    Ìåòàëëîðåæóùèé ñòàíîê…

  47. Îòåëè Òàéëàíäà // Mar 5, 2010 at 6:55 am

    Ïóòåøåñòâèå â Òàéëàíä…

    Âåëèêîëåïíûé îòäûõ â Òàéëàíäå…

  48. Ðàäèîãðàôè÷åñêèé êîíòðîëü // Mar 6, 2010 at 7:23 am

    Óëüòðàçâóêîâîé íåðàçðóøàþùèé êîíòðîëü…

    Óëüòðàçâóêîâûå ïðèáîðû…

  49. Èñòî÷íèêè ïîñòîÿííîãî òîêà // Mar 9, 2010 at 3:38 pm

    Ñòàáèëèçèðîâàííûå èñòî÷íèêè ïèòàíèÿ…

    Èñòî÷íèêè ïîñòîÿííîãî òîêà GW Instek/Motech…

Discussion Area - Leave a Comment




« Calculate Calendar Quarter of Fiscal Quarter From Date Week Numbers, Summarize Data By Week »