May 12 2021 12:10 PM
May 12 2021 12:10 PM
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.
May 12 2021 12:21 PM
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))
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.
Oct 17 2023 06:15 AM
@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?
Oct 26 2023 12:22 PM - edited Oct 26 2023 12:25 PM
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)
Oct 26 2023 12:30 PM - edited Oct 26 2023 12:38 PM
@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: