cheapest price

[craigproudfoot]

i have an excel spreadsheet showing various products and prices. I
would like to use a conditional format that highlights the item with
the cheapest price in the list. As the prices change, i would like the
highlight cheapest to change dynamically. How do i do this

 

[Don Guillett]

try
format>conditional format>formula is =a2=min(a:a)>format as desired>copy
format to other cells

 

[Niek Otten]

Select all the cells, Format>Conditional Formatting,
Cell value is, equal to, =MIN($A$!:$A$20) and apply a Pattern

--
Kind regards,

Niek Otten



"craigproudfoot"

 

[Guest]

=MIN($A$2:$A$5)=A2

A2:A5 is the list.
A2 is the top-left item in the selection.
Use "Formula is"

 

[craigproudfoot]

Thanks, that formula works - i need to know only 2 more things
firstly, how do i make the whole row change to the formatted colour an
not just the single cell with the lowest number and (here i think i
gets complicated) the conditional format does not seem to apply when
filter the list of prices. how can i get this to work

 

[Roger Govier]

Hi Craig

Mark the whole range of data that you want the CF to apply to e.g. A2:M10
In the formula for the conditional formatting, instead of MIN() use SUBTOTAL(5,)

=$A2=SUBTOTAL(5,$A$2:$A$10)
Change ranges to suit.

Regards

Roger Govier