+ Reply to Thread
Results 1 to 8 of 8

conditional formatting three columns or more

  1. #1
    Registered User
    Join Date
    08-10-2010
    Location
    CA.
    MS-Off Ver
    Excel 2010
    Posts
    3

    conditional formatting three columns or more

    Hi, I am new to using conditional formatting and just installed excel 2010 and want to take advantage of this ability. However I can not figure out how to compare 3 cells and format based off contents. I have attached a real simple example probelm. I am applying this to compare bid results from numerous vendors.

    I would like to apply the color yellow when a cell contains the lowest value compared to the other 2 cells in its row. Next apply green fill to cells who are second lowest value in the row.

    How would the rules change as the number of values to compare increases? i.e. comparing 5 values across 1 row.

    Thanks for the help
    Attached Files Attached Files
    Last edited by awinchester; 08-11-2010 at 12:34 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: conditional formatting three columns or more

    Select the range and go to Home tab and select Conditional Formatting|Manage Rules.

    Click New Rule and select use formula to determine which cells to format from top area:

    Enter formula:

    =A1=SMALL($A1:$C1,3) and click Format and choose Red from Fill tab (this is for 3rd smallest).

    Click Ok... Click New Rule and repeat above with formula:

    =A1=SMALL($A1:$C1,2) and apply green for second smallest and repeat again with

    =A1=SMALL($A1:$C1,1) and apply yellow for smallest.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-10-2010
    Location
    CA.
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: conditional formatting three columns or more

    NBVC, thank you for your help and your answer to my problem worked however I am having problems applying it so I am attaching an actual sheet I am trying to format. I tried to copy the basic formula but I believe I am making errors in selection of ranges so if you would be so kind as to review the attached below. Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: conditional formatting three columns or more

    Which columns are we talking about?

  5. #5
    Registered User
    Join Date
    08-10-2010
    Location
    CA.
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: conditional formatting three columns or more

    Quote Originally Posted by NBVC View Post
    Which columns are we talking about?
    Coulmns H, J, K

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: conditional formatting three columns or more

    See attached.

    I selected H2 to K11 and applied the conditional formats.

    You may need to change the colour scheme to what you desire.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-13-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    4

    Re: conditional formatting three columns or more

    I have two spreadsheets that contain 90% of the same data and I need to find the variances.On sheet 1 I need to compare each row's:

    Column A - order #
    Column C - count
    Column D - date


    To sheet 2 to find where they are an exact match to:

    Column A - order #
    Column B - order 2
    Column C - date

    I need to highlight column A in the row on both spreadsheets when the data in those three compared columns is a match. In this example, row 6 and 14 have different dates and 7 and 15 have different quantities, so they would not highlight. I combined on one sheet in this example but there are two sheets. I could add the data from 2 to 1 by adding more columns and then separate once the comparison is made, if that is easier.
    image.jpeg

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: conditional formatting three columns or more

    G56709 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    Also, Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1