Forum Discussion
Conditional Formatting each row individually
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!
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.
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.
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)
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?
HansVogelaar 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?
The steps should be the same, with the formulas
=AND(A1<>"",A1<=SMALL(1:1, 5))
and
=AND(A1<>"",A1>=LARGE(1:1, 5))
