Conditional Formatting each row individually

Copper Contributor

Hi - I am currently trying to apply conditional formatting to my entire worksheet to show the min and max of each row individually.  Is there a way to apply this to the entire worksheet while still keeping each row individually analyzed?  I'm hoping I don't have to apply over and over row by row.

 

Thanks!

11 Replies

@alsommer4 

Select the entire worksheet, or just the rows in use.

The active cell should be cell A1.

 

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula =AND(A1<>"",A1=MIN(1:1))

Click Format...

Activate the Fill tab and select a highlight color.

You can also specify a font color and/or borders.

Click OK, then click OK again.

 

Repeat these steps, but with the formula =AND(A1<>"",A1=MAX(1:1)) and with a different color.

 

S0399.png

As you can see in F2 / G2, if there is more than one cell with the minimum (or maximum) value in a row, all of them will be highlighted.

@Hans Vogelaar how can you amend this so that the top 5 and bottom 5 values in each individual row are highlighted? Also, my data sheet is right to left (orientation) and the first cell with relevant data that needs to be formatted is k2, does that matter at all?

@Consulting999 

The steps should be the same, with the formulas

 

=AND(A1<>"",A1<=SMALL(1:1, 5))

 

and

 

=AND(A1<>"",A1>=LARGE(1:1, 5))

@Hans Vogelaar 

It didn't work for me. Of around 800 cells only 5-10 of the minimum condition were filled (none for maximum, I suspect this could be because of my data but thought I'd share it) and not even the right ones. I copy-pasted so what could be the issue? Also I restarted Excel in case it wasn't loaded (has happened before with a lot of conditional formatting).

I have the 16.78 version of Excel if it helps.

Thanks in advance!

 

Edit: Forgot to mention, I was careful about the starting cell (F2 to K857, so I replaced A1 with F2)

@SotirisP97 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar This is from F796 to K822, you can also see the problem with the formatting. I also included the Conditional formatting window.

 

Image upload not working correctly here's a google drive link:

https://drive.google.com/drive/folders/11KgmmahkiBM4iGKJ2lwyFpUtXq19yb5l?usp=share_link

PS: I didn't think about sharing a worksheet.. I added it now to the drive.
I tried in a different empty workbook and it worked, I don't know what's going on...

@SotirisP97 

As you can see, the formulas in the rules refer to the wrong ranges:

HansVogelaar_0-1698352060368.png

They should look like this:

HansVogelaar_1-1698352166633.png

All of the suggestions severely fail the "I'm hoping I don't have to apply over and over row by row." constraint. Is there any method that doesn't?

@Hakaar 

You can apply the rules to many rows in one go. Simply select the entire range before creating the rules.

The formulas should always refer to the top row of the selection. As long as you use relative row references, Excel will automatically adjust the rules for the row it is applying them to.