OnePlus 5 review
i have 6 columns of numerical data (1 quote from each company) i need to conditionally format the cells (highlight yellow) to show me who has the lowest quote. can somebody give me an idiots guide how to do this?
was shown how to do this for 2 columns (in previous thread) how do i do it for 6 columns?
sorry for being so dumb!
thanks for any help
Assuming columns A to F with headings in Row 1.
In A2 Format > Conditional formatting. In the first dropdown select Formula Is. In the Formula box enter
apply the formatting and OK your way out. Then click on the Format Painter (paintbrush icon) then select all of your data to apply the formatting to all the cells.
I'm convinced Vog has an email update automatically when anyone posts an excel question :)
Do what VoG™ says.
i have a big job tender i am trying to get best prices from my different supplier - so here goes:
Column A lists products (100).
Column B lists the description of products.
Column C lists Quantity of products.
Columns DEFGH are for supplier 1
D = total price (quantity x unit price)
E = total price + qty 10% (quantity (eg 100) +10% = 110) so the price for 110 items
F = total price + qty 20%
G = total price - qty 10% (qty 100 - 10% = 90 items)
H = total price - qty 20%
Columns IJKLM are for supplier 2
Columns NOPQR are for suppler 3
i need to do conditional formatting for the relevant columns for each supplier eg i need to know who is the cheapest
supplier for each price column
i hope you can help vog, my brain hurts
Similar to above but start with cell D2 and in Formula Is enter
Use the Format Painter to apply all the way down column D, then apply to columns I, N, S, X and AC. This will just format the total price columns.
so now; can i conditionally format the other columns by changing the cell references
eg: =e2=min($e2,$j2,$o2,$t2,etc etc)? if this is possible then how do i ensure i paint the correct format to the relevant column, do i click on e2 then click format paintbrush.
As an aside when i format the cells they all turn green (i want the cell to turn green when it is the lowest value (price). If i put in data the lowest value cell remains green whilst the others return to no formatting (can live with this just wondering if its possible to have them all clear and only turn green if lowest value). Thank you so much for the help so far.
Re your first paragraph, that is exactly how to do it.
I'm not sure that I understand your second paragraph. If you mean when the cells are empty they all turn green then that is normal. It would be possible to modify the formula to get around this:
(this is using your original 6-column example for simplicity)
would therefore turn off the cell colour until it was shown to be the lowest value (price) by entering data into it and relative cells?
Yes. But - to go back to the easier 6-column example - if you have that formatting applied to A to F then as soon as you enter a value in any of the cells A to F then that cell would be coloured. Once you add data to another cell then the truly lower one would be coloured. It would be possible to adjust the formula to stop this from happening but it would be pretty complicated I think.
Spotted a typo in your formula
D2 at the end, not A2.
This thread is now locked and can not be replied to.