• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Conditional Formatting with Formulas

You are here: Home / Excel Formulas / Excel Conditional Formatting with Formulas
Excel Conditional Formatting with Formulas
March 6, 2013 by Mynda Treacy

Excel’s Conditional Formatting tool is diverse with loads of built in rules that you simply point and click to use, but I find more often than not that I need to use a formula based rule.

I can sympathise if you’ve ever tried to use formulas in your conditional formatting and ended up tearing your hair out in frustration.

Thankfully there are only 3 simple rules you need to know. Once you understand these rules you’ll never look back.

Watch the Video

Subscribe YouTube

 

Conditional Formatting with Formulas - Written Instructions

Rule 1 – the formula must evaluate to TRUE or FALSE*

Conditional formatting is looking for a true or false outcome, or their numeric equivalents 1 and 0. If the outcome is true or 1 it will apply the format, if it’s false or 0 it won’t. It’s black and white.

*A little known fact is formulas that evaluate to any positive or negative number will also be considered as 'TRUE' and the format will be applied. And any formulas that evaluate to zero will not have the formatting applied.

I like to test my rules in the worksheet first. Once I know they’re returning the correct result I can create my new rule and paste the formula into the conditional format rule description.

For example, let’s say we have loaned out some books and we want to flag when they are overdue by formatting them in red like this (by the way, my dates are formatted dd/mm/yyyy):

Excel Conditional Formatting with formulas

In cell E1 I have the current date, and in column D I’ll enter my test formula:

=C2<=$E$1

Then I can copy the formula down the column and check each row is evaluating as I’d expect.

Excel 
Conditional Formatting formula

Ok, now I’m happy with my formula I’ll edit cell D2 and copy the formula to my clipboard so it’s ready to paste into the Conditional Formatting rule description.

Rule 2 – Select Your Cells

Before you go and set up your conditional formatting rule you need to select all of the cells you want formatted.

Since I want to format from column A to C for each row, I’ll select my whole table like this:

Excel Conditional Formatting formula

Rule 3 – Absolute References

The next trick with Conditional Formatting formulas is when to use absolute references.

I’ll create my new rule:

Excel Conditional Formatting formula

And paste my formula into the 'Format values where this formula is true' field:

Excel Conditional Formatting formula

As I’m comparing the dates in column C to our current date, I need to absolute the column reference in my formula, but not the row reference.

And that’s because in the background Excel is applying that formula to every cell I selected (back in rule # 2) to test for a TRUE/FALSE outcome.

This is the way I remember this rule:

When you enter the formula in the 'Format values where this formula is true' field Excel is applying it to every cell you have selected.

When it does so, the cell references dynamically update, just as they would if you entered it in cell C2 and then copied and pasted it over your selected area, except instead of actually pasting it the Conditional Formatting tool does the calculation in the background.

I picture it as a separate layer of the workbook, like in the image below. The top layer is the Conditional Formatting formula and the bottom layer is my workbook.

Excel Conditional Formatting formula

Note how in the Conditional Formatting layer the formula in each row has dynamically updated to pick up the current row but the column reference remains with C. i.e. testing the due date in column C for each row.

It would then evaluate like this:

Excel Conditional Formatting formula

If I didn’t absolute column C Excel would think I wanted to do this:

Excel Conditional Formatting formula

See how the above formulas would be wrong since it’s testing every cell to see if it’s < or = to cell E1 not just column C for each row?

Ok, now that I’ve entered my formula I can set up my formatting and I’m done:

Excel Conditional Formatting formula

Excel Conditional Formatting with Formulas

More Conditional Formatting Posts

conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

How to apply conditional formatting to tables and matrices in Power BI. Use color, icons, data bars and URL's.
project management dashboard

Excel Project Management Dashboard

Excel project management dashboard video tutorial covering various techniques including conditional formatting, PivotTables, Slicers, charts and more.
Highlight Selected Cell In Excel and Preserve Cell Format

Highlight Selected Cells in Excel and Preserve Cell Formatting

Use shapes to highlight the selected, active cells in Excel & preserve cell formatting. So you won't lose any conditional formatting, borders, colors etc.
Conditional Formatting PivotTables

Conditional Formatting PivotTables

Conditional Formatting PivotTables values areas will automatically expand/contract as you add new data or make changes to the filters, rows or columns.
excel conditional formatting gantt charts

Excel Conditional Formatting Gantt Chart

You can build a Conditional Formatting Excel Gantt Chart easily with just a few WORKDAY.INTL formulas and relative references.
Excel Factor 10 Conditional Formatting Painting by Numbers

Excel Factor 10 Conditional Formatting Painting by Numbers

Excel Factor 8 Highlight Cells Containing Formulas

Excel Factor 8 Highlight Cells Containing Formulas

Automatically highlight cells containing formulas in Excel to reduce the chance of them being deleted or over-written
How to Use Excel Conditional Formatting

How to Use Excel Conditional Formatting

Excel Conditional Formatting examples and workbook.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: conditional formatting
Previous Post:Excel PivotTables Unique Count 3 WaysExcel PivotTables Unique Count 3 Ways
Next Post:World’s Most Accurate Pie ChartWorlds Most Accurate Pie Chart

Reader Interactions

Comments

  1. Janey

    August 11, 2018 at 8:51 am

    Hi I am trying to format certain cells in a work book using dates and the sum of 2 colums as the criteria. I have a list of customers in col A, settlement date in col B, deposit $ in col c, deposit $ returned in col D. At certain points in time I need to refund deposits. What I would like to achieve is if today’s date is settlement date + 15 days and Col C-Col D is not $0 format col C in red

    Reply
    • Mynda Treacy

      August 13, 2018 at 10:25 am

      Hi Janey,

      Try this formula:

      =IF(AND(TODAY()>$B2+15,$C2-$D20),TRUE,FALSE)

      Mynda

      Reply
      • Jane

        February 11, 2019 at 2:21 pm

        Hi Mynda, thanks for the above info. I now want to change the formula slightly so instead of Col C& D being numbers, I want the a cell to format if Col D is today +15 days & col f is blank

        Reply
        • Mynda Treacy

          February 13, 2019 at 9:16 pm

          Hi Jane,

          =AND($D$1=TODAY()+15, ISBLANK($F$1))

          Mynda

          Reply
  2. Warren Prosser

    July 4, 2018 at 12:57 pm

    Hello, I have been trying unsuccessfully to create a conditional format. Here is what I would like to accomplish:

    I want the values in columns BJ and BL to be in red, as a result of two conditions: if the value in BM is greater or less than 0, AND if BO is blank.

    I tried this formula, but it came back as an error:

    =AND ($BM20, $BO=””)

    Any help would be greatly appreciated!

    Reply
    • Catalin Bombea

      July 4, 2018 at 10:45 pm

      Hi Warren,
      Try this one, applied to rangeBJ2:BJ1000:
      =AND($BM2<>0, LEN($BO2)=0)
      (the row number from formula should match the row number from the applied range)

      Reply
  3. NY

    May 18, 2018 at 7:31 am

    I would like to know if we can apply conditional formatting to a cell if that cell’s value is changed?

    Reply
    • Mynda Treacy

      May 18, 2018 at 9:50 am

      Hi NY,

      No, not with Conditional Formatting, only with VBA.

      Mynda

      Reply
      • NY

        May 18, 2018 at 9:57 am

        Thanks for the reply. Are you able to help with the code in VBA?

        Reply
        • Mynda Treacy

          May 18, 2018 at 9:58 am

          Please post your question in our Excel forum.

          Reply
  4. Farooq Baddi

    August 16, 2017 at 4:30 am

    I avoid using conditional formatting but after reading your article, it looks easy.
    Your explanation of the concepts is superb. Well Done!

    Reply
    • Mynda Treacy

      August 16, 2017 at 12:18 pm

      Thanks, Farooq. Glad you’re keen to give it a go πŸ™‚

      Mynda

      Reply
  5. Leah

    July 26, 2017 at 6:10 am

    I’m trying to format a cell to be highlighted when it is < 89% of another cell
    B2 50 and D2 100
    I want B2 to color red if less than 89% of D2, and if B2 is 90%-99% of D2 yellow, and if B2 is <=100% of D2 green

    Is this possible?

    Reply
    • Mynda Treacy

      July 26, 2017 at 9:07 am

      Hi Leah,

      Yes, it’s possible. You’ll need a separate format for each colour. Set them up in the order below, that way the yellow rule will override the red rule and the green rule will override the yellow and red rules.

      Red: =$B2/$D2<89%
      Yellow: =$B2/$D2>89%
      Green: =$B2/$D2>=100%

      Select all of the cells you want to apply the format to before inserting the formats.

      If you get stuck please post your question and sample Excel file on our Excel forum.

      Mynda

      Reply
  6. Alex Ashin

    March 24, 2017 at 2:30 pm

    Hi,
    I have list with data from two sources.
    It takes figures for each month in the year.
    One source takes place in odd rows and the other in even rows and I want to highlight cells that do not match (both sources should be identical).
    The way I do it is to conditional formatting one cell (no absolute) like b3b2, copy it to the entire row and then copy it (with the brash) to each other row.
    I can write a macro to do the job but wonder if there is a way to do it with the conditional tools only.

    Reply
    • Catalin Bombea

      March 24, 2017 at 3:59 pm

      Hi Alex,
      There is no tool that I know to apply the format to other rows in mass, only workarounds as you mentioned.

      Reply
  7. Keri

    February 28, 2017 at 2:31 am

    I’m trying to setup a formula for the following:
    Column Q is tracking expiration dates (for permits). Column W is tracking updated expiration dates.
    Column Q already has conditional formatting to fill RED when the date is expired. I’d like Column Q to format back to “no fill” if there is a date entered into Column W.
    Does that make sense, and is it possible to do?

    Reply
    • Mynda Treacy

      February 28, 2017 at 12:06 pm

      Hi Keri,

      Yes, just add another rule to test if column W contains a value. This will override the first rule.

      Mynda

      Reply
  8. Adrian Anderson

    February 23, 2017 at 3:12 pm

    Very Helpful post, this will make my formatting more useful to identify cells that meet specific date criteria.

    Much Appreciated

    Reply
    • Mynda Treacy

      February 23, 2017 at 9:05 pm

      Thanks, Adrian. Glad you’ll find a use for it.

      Mynda

      Reply
  9. Gordon Miller

    February 11, 2017 at 7:58 am

    Many thanks Mynda. I Have tried to explain conditional formatting to my accounting staff, but was never able to create a visual that’s unforgettable–as you provided above with the “layering”

    Reply
    • Mynda Treacy

      February 11, 2017 at 10:48 am

      Fantastic, Gordon! I’m so pleased you found it useful. It was something that I struggled with in the beginning too.

      Cheers,

      Mynda

      Reply
  10. CJ

    December 15, 2016 at 4:54 am

    Mynda, I think I just figured it out. Needed to change my formula to be AP63=TRUE and have it change the font to black from white. This seems to work. Don’t understand why the opposite scenario didn’t work – but at least this works now!

    Thanks for your great tips!

    Reply
    • Mynda Treacy

      December 15, 2016 at 8:05 am

      Glad you figured it out, CJ πŸ™‚

      Reply
  11. CJ

    December 15, 2016 at 4:07 am

    I have a Check Box linked to cell AP63. When the Check Box is unchecked (making cell AP63 contain “FALSE”), I would like range C89:Y98 to change font color from black to white. Is this possible? I thought I could get it to work with Conditional Formatting, but it doesn’t seem to be working for me. I use a formula in Conditional Formatting of: =$AP$63=FALSE. Then I set the font color to be white. It doesn’t work – any ideas?

    Reply
  12. Gina

    September 15, 2016 at 8:55 am

    Hi there – I have really enjoyed your webinars and our Customer Service Analyst purchased your Excel for Customer Service Professionals course back in July. She is getting a lot of mileage from it. One thing I am having trouble with and I cannot find any good examples online, is the following: I have a simple spreadsheet tracking issues including date resolved and current status. I am using the built-in conditional formatting rules for “Highlight Date occurring.” So that last week, last month and yesterday and today are red. Tomorrow, next week and next month are green. I also have as the first condition, no format set. Basically I would like to clear all formatting if the current status column has “Completed.” I have tried different combinations of formulas including IF AND statements combined with TODAY() and I cannot get this to work! Help a poor soul dying a slow death by Excel in Texas????

    Reply
    • Mynda Treacy

      September 15, 2016 at 10:52 am

      Hi Gina,

      Great to hear your analyst is enjoying the customer Service course πŸ™‚

      We’d be happy to help you with your Conditional Formatting conundrum. Can you please post your question and workbook (or sample workbook) on our Excel Forum so we can see your question in context and give you a specific answer.

      Thanks,

      Mynda

      Reply
  13. Dayan

    January 7, 2016 at 2:07 am

    Hi,

    Let’s say I have a Target number and an Actual number, if the Actual number is grater than the Target one I need the cell to be green. If the Actual is less, I need the cell to be red. I have done this using the less than and greater than rule.

    However, now I need the Actual cell to be yellow if it is less than 3% below the Target number. I am not able to get this part to work. Any advice?

    Reply
    • Mynda Treacy

      January 7, 2016 at 8:06 pm

      Hi Dayan,

      You can use this formula:

      =Actual/Target>=0.97

      Mynda

      Reply
  14. JERALD

    July 28, 2014 at 6:01 pm

    regarding above ex..in column if it is amounts. how to add that paricular amounts(total amounts) in cell f.

    Reply
    • Catalin Bombea

      July 29, 2014 at 3:30 pm

      Hi Jerald,
      Try this formula:

      =SUMPRODUCT((A2:A11<=E1)*C2:C11)

      Catalin

      Reply
  15. Mark Woodhouse

    July 25, 2014 at 9:51 pm

    I ran across a conditional format that I can figure out the formula says:
    =ActualBeyond
    or
    =plan
    But there are no named ranges and i cannot find any links to these. how is this done… Any thoughts?

    Reply
    • Mynda Treacy

      July 26, 2014 at 1:18 pm

      Hi Mark,

      I suspect these names are referencing UDF’s (User Defined Functions). Check in the VB Editor for them.

      Kind regards,

      Mynda

      Reply
  16. Che

    July 5, 2014 at 7:55 am

    Hi. I have a column of dates. I would like to see which dates are within 30 days from today and have it filled in color red.

    Reply
    • Che

      July 5, 2014 at 8:01 am

      The conditioning formula I used is =B7-TTODAY()<30. But why is it that dates like feb, march, may are also filledmin red?

      Reply
      • Mynda Treacy

        July 5, 2014 at 8:51 pm

        Hi Che,

        I suspect there is something wrong with the format of the date in cell B7. Assuming your formula is actually

        =$B7-TODAY()<30

        If you’d like to send us your file via the help desk we can troubleshoot what might be the problem.

        Mynda

        Reply
  17. maggie pont

    June 2, 2014 at 10:16 am

    Just sensational tutorials, many thanks

    Kind regards

    maggie

    Reply
    • Mynda Treacy

      June 2, 2014 at 10:32 am

      Wow, thanks Maggie πŸ™‚ Glad we could help.

      Reply
  18. Amy

    December 11, 2013 at 7:37 am

    BOX/SKID? FULL LAYERS
    box 58
    box 61
    skid 12
    box 20
    skid 15

    If it is a box, I need to know if the full layers are over 60 so I can calculate a new skid and if it is a skid I need to know if it is over 12 layers, I need to calculate a new skid
    So my question is this: is there a conditional formatting I can use that will highlight any boxes that are over 60 and any skids that are over 12? They do share the same columns.

    Thanks so much
    Amy

    Reply
    • Catalin Bombea

      December 11, 2013 at 11:25 pm

      Use conditional formatting with these formulas:
      =AND($A2=”skid”,$B2>12)
      =AND($A2=”box”,$B2>60)
      The range of values are assumed to start from row 2.
      Cheers,
      Catalin

      Reply
      • Amy

        December 12, 2013 at 12:00 am

        Thanks. I have added these rules, but one small issue. It is highlight two cells above the one it should be? The cells are not next to one another, would that cause an issue? Cells are “Y” and “AB”
        Thanks
        Amy

        Reply
        • Catalin Bombea

          December 12, 2013 at 2:29 am

          Hi Amy,
          I have to see what you have done, hard to tell… The range to highlight must be correlated to the formula: for example, if the range starts from AB2, the formula should refer to the same row, row 2: IF($Y2=”box”……, and the second check should start from the same row 2: IF(AB2>60….
          Can you upload a sample workbook on Help Desk? https://www.myonlinetraininghub.com/helpdesk/
          I will gladly help you πŸ™‚
          Cheers,
          Catalin

          Reply
          • Amy

            December 12, 2013 at 2:50 am

            Oh, I see. I had selected the range starting at Y1, not Y3, so I corrected and now it is fine. Awesome. Thanks so much for your help. You guys are amazing!!!
            Amy

          • Catalin Bombea

            December 12, 2013 at 2:59 am

            Glad it works fine for you Amy, you’re always wellcome.
            Catalin

  19. Mary Willoughby

    November 23, 2013 at 10:15 am

    Great explanation. Just curious, rather than entering a date in E4, could the cell be formatted to default to the system date?

    Reply
    • Catalin Bombea

      November 23, 2013 at 3:31 pm

      Hi Mary,
      You are right, you can enter a formula to return system date: =TODAY() or =NOW()
      Cheers,
      Catalin

      Reply
  20. Ashley

    October 8, 2013 at 10:52 pm

    I’m trying to make a formula that changes the coloring of different rows to make them color coded by the difference of the date that is in the cell and the current date. This is items that we need people to give us paperwork for annually, and the dates in the cells are the dates we have for the item. If the item is overdue (todays date a year ago or earlier) I want it to be the standard “bad” cell style (light red with dark red font) if it is within one month of being overdue I would like it to be the standard neutral style (yellow and yellow) and if it has more than 1 month to be overdue I would like it to be the standard good style (green and green.) I can put in the colors myself if someone can tell me how to do the formula. Thanks!

    Reply
    • Mynda Treacy

      October 9, 2013 at 9:29 pm

      Hi Ashley,

      This formula will tell you if the date in cell E8 is more than a year ago:

      =$E$8<TODAY()-365

      The TODAY function returns the current date as per your computer clock. All you need to do is calculate what the date would be for each of your criteria and then compare that date to the dates you have to see if they are less than or greater than your calculated date.

      I hope that helps put you on the right track. Please let me know if you get stuck.

      Kind regards,

      Mynda.

      Reply
  21. Israel Boyd

    August 30, 2013 at 10:08 pm

    Hello, I am trying to create a conditional formatting sheet which shows either ‘yellow’ when today’s date is within 2 months of a set date and ‘red’ when today’s date is within 1 month of today’s date.

    Basically, I have a spreadsheet with a list of dates of certificates and when they expire. What I want to be able to do is to have excel highlight the cell when it is going to expire both 1 month and 2 months away so that I can look at my spreadsheet and see visually when I need to remind people to renew their certificates.

    I have tried so many ways and I feel so stupid because I just cannot get it to work. All other examples I have found make the colour change AFTER today’s date and not before.

    HELP

    Reply
    • Mynda Treacy

      August 31, 2013 at 7:47 pm

      Hi Israel,

      You can use this formula for dates <60 days from today:

      =B2-TODAY()<60

      Where B2 is your 'set date'.

      And for <30 days you can use this:

      =B2-TODAY()<30

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  22. Shekhar

    July 25, 2013 at 10:34 pm

    I have a quick question. Currently I have a spreadsheet that is grouped up by columns. Meaning I have a total of 20 columns but they are broken into sub groups. The 1st group contains 10 columns, the 2nd group contains 6 columns and the 3rd group contains 4 columns. Ultimately what I would like to do is everytime the 1st group edits the row for the entire row to shade yellow. Anytime the 2nd group edits that row for the entire row to shade amber and finally anytime the 3rd group edits the row for the entire row to shade say light blue. Esentially my goal is that anytime a group edits the row whenever someone goes back into the spreadsheet that person will know who made the changes. Is this possible?

    Reply
    • Mynda Treacy

      July 26, 2013 at 6:49 pm

      Hi Shekhar,

      What happens is they don’t edit it in order? Excel can’t tell who edited it last. You could have a rule that shaded the row the specific colour based on which group had data in it, but if they don’t edit the row in order (group 1 first, group 2 second and group 3 third), it won’t work.

      A slightly different solution would be to have a drop down list that the person editing selected their name from before saving the file. This way the next person to open the file would see who was in it last.

      If you wanted to automate this you could do so but it would require VBA.

      Kind regards,

      Mynda.

      Reply
  23. Ilias

    July 14, 2013 at 8:14 pm

    I think that we help me

    Reply
    • Mynda Treacy

      July 14, 2013 at 9:27 pm

      Glad to help, Ilias πŸ™‚

      Reply
  24. Vic Sneed

    June 22, 2013 at 2:52 am

    I have a spreadsheet where employees daily record defects in equipment. They use data validation (drop down box) to record common defects or select No Defects if everything is fine. I want yellow fill to be applied whenever a defect is noted. If No Defect is noted (or if the cell is blank) I want no fill applied. I can use conditional formatting to apply no fill when No Defects is entered, but then the rest of the spreadsheet (or cells selected) is always yellow. I want no fill unless a defect is specified. Can I do this?

    Reply
    • Mynda Treacy

      June 22, 2013 at 2:59 pm

      Hi Vic,

      Yes, conditional formatting can do that.

      It sounds like you have the whole worksheet highlighted yellow and you remove the formatting with the conditional formatting. I would do it the other way around.

      If you get stuck feel free to send me your workbook via the help desk. Please repeat your instructions so I can easily help you.

      Kind regards,

      Mynda.

      Reply
  25. Dante G. Millanes

    May 30, 2013 at 4:43 pm

    thanks

    Reply
    • Mynda Treacy

      May 30, 2013 at 7:46 pm

      You’re welcome, Dante πŸ™‚

      Reply
  26. Ravi Xavier

    May 30, 2013 at 1:10 pm

    Thank you for this sharing.

    Reply
    • Mynda Treacy

      May 30, 2013 at 2:48 pm

      You’re welcome, Ravi πŸ™‚

      Reply
  27. Adri van den Dool

    May 22, 2013 at 8:06 pm

    Hi. How do you apply conditional formatting in reverse?
    Per definition: Conditional formatting is applied to one or more cells and, when the data in those cells meet the condition or conditions specified, the chosen formats are applied.
    How can I rather apply a certain condition to a cell with a chosen format? For instance: some cells in a pricelist contain green highlighted prices. Those prices should be treated different from the rest. I want a way to test “format of the cell is green” as a TRUE or FALSE.

    Reply
    • Mynda Treacy

      May 22, 2013 at 9:58 pm

      Hi Adri,

      You need VBA to do that. There aren’t any formulas that can detect cell colour.

      The only other option is to apply filters to your data and manually filter on the cell colour.

      Kind regards,

      Mynda.

      Reply
      • Adri van den Dool

        May 22, 2013 at 11:03 pm

        Thanks Mynda for your answer. You potentially saved me many frustrating hours of searching for nothing πŸ™‚
        I personally do not like filters at all, it feels like a copout for writing proper formulas!! That is why I started looking for a formula based solution. I never know what happens to cells between the filtered cells when I copy or paste or add comments etc. But I guess filters is the middle way until I learn how to use VBA.

        Reply
        • Mynda Treacy

          May 23, 2013 at 9:56 am

          I understand your concerns with the filtered cells. Some things skip them and others don’t. From memory pasting comments seems to be applied to all cells hidden or not.

          Reply
    • Riaan van den Dool

      May 22, 2013 at 11:23 pm

      1. Go Insert>Name>Define and name this formula that you are about to create “color”
      2. In the Refers To box type:

      =GET.CELL(63,OFFSET(INDIRECT(“RC”,FALSE),-1,0))
      3.Back in the sheet, in the immediately adjacent cell below the one where you want to determine the fill colour type (so yellow cell in A1, then you enter the following in A2):

      =color

      This will return the colorindex of the cell interior (6 is bright yellow in a default installation).

      So you can use this in a formula to do as you wish eg:

      =IF(color=6,1,”It ain’t yellow”)

      Source: http://www.ozgrid.com/forum/showthread.php?t=82173

      Reply
      • Mynda Treacy

        May 23, 2013 at 10:06 am

        Thanks for sharing, Riaan.

        Note: GET.CELL is an old XLM4 Macro function (VBA) with very limited use.

        Reply
  28. Alex Leblois

    May 22, 2013 at 1:00 am

    Hi!

    “Excel conditional formatting row color” are used commonly in data entry task or managing data. So thank for sharing this important information.
    I have find out some other links that’s also helpful for beginner.

    http://www.contextures.com/xlCondFormat02.html
    http://www.techrepublic.com/blog/10things/10-cool-ways-to-use-excels-conditional-formatting-feature/3166
    http://www.mindstick.com/Articles/550457d6-a06a-4cd6-b8b5-8975e20d0868/?Excel%20conditional%20formatting%20row%20color

    Reply
    • Mynda Treacy

      May 22, 2013 at 2:13 pm

      Thanks for sharing, Alex πŸ™‚

      Reply
  29. Tobi Hossner

    March 20, 2013 at 9:59 am

    I recently started reading your articles on excel and formulas Mynda. I have to say you go in depth with great illustrations which makes it so much easy to understand for readers. Thank you, since I learned alot and will use some of your techniques on my articles. Keep up the good work!

    Reply
    • Carlo Estopia

      March 21, 2013 at 1:37 am

      Hi Tobi,

      On Behalf of Mynda,
      Thank You!

      Cheers.

      CarloE

      Reply
      • Max

        April 20, 2013 at 4:32 pm

        Hi Mynda and Carlo,

        I have 26 Columns, the first 9 are white coloured cells to imput data. The other remaining columns are coloured gray to remind me not to touch them as they have formulas in them to automatically fill in when I put data into the first 9 columns.

        Within the first 9 columns I have two with dates in them, one has an entry date (column 4) and one has an exit date (column 7).

        What I would like to achieve is when these 9 columns/cells are empty they remain white, as they are now.

        When I input an entry date (column 4) into the cell I would like these first 9 columns/cells to be filled with the colour yellow. But once I put in the exit date (column 7) I would like these first 9 columns/cells to be filled with the colour gray matching the other 17 columns/cells to distinguish that this row is finished with.

        Any help would be appreciated, I can get a few things going ok but not the lot at once. Many thanks.

        Kind Regards …. Max

        Reply
        • Max

          April 20, 2013 at 4:54 pm

          Hi Mynda & Carlo,

          I have worked this out eventually, I hope you haven’t spent any time on it as of yet.

          Many thanks all the same.

          Cheers … Max

          Reply
          • Carlo Estopia

            April 20, 2013 at 8:46 pm

            Good that you figured it out already. CHeers Max!

          • Max

            April 21, 2013 at 9:39 am

            Hi Carlo & Mynda,

            Well … I thought I had it worked out.

            I got 1 row working so thought I’d be able to expand the ‘applies to’ area and it would be all done, simple but no, something is wrong.

            I have 1500 rows within this sharetrading spreadsheet.

            If you can take a look at this for me that would be fantastic.

            Many thanks again …. Max

          • Max

            April 22, 2013 at 11:59 am

            HI Carlo & Mynda,

            Well after some time finally got it finished.

            Thanks for all the tips and clues on the website, it steered me in the right direction to get this done.

            Respect your efforts to help others, very generous.

            Kind regards … Max

          • Carlo Estopia

            April 22, 2013 at 3:39 pm

            Cheers Max on Behalf of Mynda! πŸ™‚

  30. Weynshet Demessie

    March 7, 2013 at 6:16 am

    As always, I would like to thank you for your great explanation on how to work on Excel in a simple and visual way.

    Reply
    • Mynda Treacy

      March 7, 2013 at 7:06 pm

      Thanks, Weynshet πŸ™‚

      Reply
  31. Mary

    March 7, 2013 at 12:24 am

    I have a series of time in one column and a series of time in another column. Each column represents a different process. I want to assess each row for the two time columns and conditionally format the times in one column that are greater than the times in another column.
    1…..00:22:06……….00:26:36 – since the second column with time is greater than the first column, I’d like to format the second column in a color to make it stand out.

    Mary

    Reply
    • Carlo Estopia

      March 7, 2013 at 2:57 pm

      Hi Mary,

      1 Go to Home Ribbon
      2 Click Conditional Formatting
      3 Add Manage Rules
      4 CLick New Rule
      5 Select Rule Type: Use Formula to determine which cell to format

      FIRST RULE:

      a) Position cursor in A1
      b) In the "Format Values where this formula is true" enter this formula : =A1>B1
      c) Click format button, and select your color (self-explanatory)
      d) Click OK
      e) You will be brought to the "Applies To" box: enter the range you want
         your rule to be applied to i.e. =$A$1:$A$10... or you may simply select
         the range.
      f) Click Apply then OK
      

      SECOND RULE
      Do the same for the second Rule…but this time:

      a) Position cursor in B1
      b) Formula is B1>A1
      e) Applies to =$B$1:$B$10
      

      Read more : Conditional Formatting

      Cheers.

      CarloE

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x