CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Shreeuday Kasat
Shreeuday Kasat

Reputation: 3

How to get the max value

I am trying to get the max value:

enter image description here

Here, Catee has 3 entries and out of which I need to highlight the maximum value which is 2000

Ramox has 4 entries and need to highlight the max value i.e. 144

I tried with condition formatting but I've more than 1900 rows plus 112+ records as like Catee and Ramox and applying condition formatting individually is a cumbersome process.

Record Ace has 550 as the highest number but appeared thrice. In this case, I need highlight them all

If there a better way to highlight the same:

Thanks!

Upvotes: 0

Views: 139

Answers (2)

Dominique
Dominique

Reputation: 17493

Are you aware of the Subtotals feature of Excel?

First, I'd advise you to add the names, as so beautifully explained in JvdV's video.
As a result, you get something like this:

enter image description here

Start the SubTotals feature, using the Max function:

enter image description here

The first result looks as:

enter image description here

Clicking on 2 in the margin modifies this into:

enter image description here

Have fun :-)

Upvotes: 2

JvdV
JvdV

Reputation: 75850

Make it easy on yourself. My advise:

  • Select C2:C22;
  • Add conditional formatting rule based on: =C2=MAXIFS(C$2:C$22,A$2:A$22,A2);
  • Now, select A2:C22;
  • Hit F5;
  • Select: 'Special' >> 'Blanks' >> 'OK';
  • Type =A2 and confirm with Ctrl+Enter.

enter image description here

Upvotes: 2

Related Questions