+ Reply to Thread
Results 1 to 12 of 12

highlight highest 1st,2nd and 3rd values in multiple columns

  1. #1
    Forum Contributor
    Join Date
    09-13-2013
    Location
    Saudi Arabia
    MS-Off Ver
    2010, 2013
    Posts
    192

    highlight highest 1st,2nd and 3rd values in multiple columns

    Hai Again,

    I am trying to highlight first 3 highest values through conditional formatting but unable to do that.

    Can some 1 help to apply conditional formatting.

    I have a values in columns B-E and i applying formula in conditional formatting as mentioned below:-

    1st highest values Blue: =B2= MAX($B2:$E2)
    2nd highest values Green:=C2 = LARGE($B2:$E2,2)
    3rdst highest values Red:=B2 = MIN($B2:$E2)

    Copy of workbook is attached for your reference.
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    Do you mean the largest three values from the entire range B2:E58?

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    Quote Originally Posted by Abdur_rahman View Post
    1st highest values Blue: =B2= MAX($B2:$E2)
    2nd highest values Green:=C2 = LARGE($B2:$E2,2)
    3rdst highest values Red:=B2 = MIN($B2:$E2)
    Should C2 be B2?
    And
    3rd highest value: = LARGE($B2:$E2,3)

    Does it work?
    Quang PT

  4. #4
    Forum Contributor
    Join Date
    09-13-2013
    Location
    Saudi Arabia
    MS-Off Ver
    2010, 2013
    Posts
    192

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    Thanks for your response.

    yes i change it but doesn't work.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    So , try to mock up your expected outcome with value and manual colour few rows then post a gain.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    Please answer my question

  7. #7
    Forum Contributor
    Join Date
    09-13-2013
    Location
    Saudi Arabia
    MS-Off Ver
    2010, 2013
    Posts
    192

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    Sorry.
    i need to check every row for 4 columns the top 3 values.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    (From post #4) Thanks for your response. yes i change it but doesn't work.
    Just saying that something doesn't work, without explaining why it doesn't work, is not going to be of help to those whom are attempting to resolve the issue.
    It may help to do as bebo suggest and manually highlight cells in a few rows so that we have something with which to compare the results of our proposals.
    Note that column K is not displaying the 3rd largest value in the row, but rather the smallest (4th largest) value.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Forum Contributor
    Join Date
    09-13-2013
    Location
    Saudi Arabia
    MS-Off Ver
    2010, 2013
    Posts
    192

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    Thank U JeteMc,

    i mean to say that i have change the below by selecting columns (B2:E2) but it doesn't work

    1st highest values Blue: =B2= MAX($B2:$E2)
    2nd highest values Green:=B2 = LARGE($B2:$E2,2)
    3rdst highest values Red:=B2 = MIN($B2:$E2)

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    To get the third highest value, as bebo stated in post #3, you need =B2 = LARGE($B2:$E2,3)
    Are any of the conditional formatting rules working as you want?
    Can you tell, or better yet show, us which ones are not and why?

  11. #11
    Forum Contributor
    Join Date
    09-13-2013
    Location
    Saudi Arabia
    MS-Off Ver
    2010, 2013
    Posts
    192

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    Yes it works
    Thanks for solution.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: highlight highest 1st,2nd and 3rd values in multiple columns

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Total the highest 4 values over multiple columns
    By SloopJohnB in forum Excel General
    Replies: 4
    Last Post: 10-23-2018, 03:24 AM
  2. Filter highest values of columns
    By neil40 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-26-2018, 05:15 PM
  3. [SOLVED] highlight highest value in row excel (flexible rows and columns)
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-06-2017, 07:23 AM
  4. Replies: 3
    Last Post: 06-09-2016, 12:51 PM
  5. How to: Highlight lowest and highest values multiple times in same row?
    By Luz1978 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-29-2014, 01:02 AM
  6. Replies: 0
    Last Post: 07-17-2014, 10:06 AM
  7. [SOLVED] Highlight second highest values, problem with ties
    By Djimidimi in forum Excel General
    Replies: 12
    Last Post: 06-07-2012, 10:47 AM

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