Finding multiple minimums???

[Texas10]

If I have a section of cells with numbers and want the 4 smallest numbers to
be highlighted what should I do? It would make life much easier if excel
could just highlight those 4 smallest values for me after all the data is
input into the range that has that formatting. Thanks for any help.

 

[Pete_UK]

Suppose those numbers are in A1:A20. Select that range, with A1 as the
active cell, then bing up the Conditional Formatting dialogue box and
choose Formula Is rather than Cell Value Is in the first box and enter
this formula:

=A1<SMALL(A$1:A$20,5)

Click on the Format button, then on the Patterns tab, and choose your
colour. Click OK twice, and then the 4 lowest values will be
highlighted with the colour you chose.

Hope this helps.

Pete

 

[Rick Rothstein]

Let's assume your "section of cells" is C7:H20... select them (I'll be
assuming C7 is the active cell... the active cell won't be shaded)... click
Format/Conditional Formatting... select "Formula Is" from the first drop
down and then put this in the empty field next to it...

=C7<=SMALL($C$7:$H$20,4)

Note the first use of C7 (also note the relative address) is because it is
the active cell. The second C7 (note the absolute address on it and the end
cell in the range)... that is the address range of your "section of cells".
Next, click the Format button, select the Patterns tab and select a
relatively light tinted color (so your value will show up against it) and
finally, OK your way back to the worksheet. Your lowest 4 values should now
have their cells shaded in the color you selected.

 

[Texas10]

Thank you both for the help

 

[Texas10]

Well some of the cells are blank and the formatting highlights the blank
cells as well, so how can I make an adjustment to the formula to have it
ignore blank cells and only highlight the 4 smallest numbers?

 

[Rick Rothstein]

Okay, in that case use this formula instead...

=AND(C7<=SMALL($C$7:$H$20,4),C7<>"")