Microsoft Office excel 2007 - upgrade

I have 4 columns of prices from 4 suppliers - can conditional formatting colour these red to green across the columns rather than across the range of prices? For example item 1 row 1 compare the four prices received that month? All i can get it to do is colour in that way but across all four columns and 50 rows...

Many thanks in advance !

I have Excel 2003, so I can only test this with 3 conditions. I believe with 2007, you should be able to set up at least 4 Conditional Formatting rules. Here's what I did for 3 conditions in Excel 2003 - Modify this as required for 2007.

Using A1:C1 as an example, select the range.

Under Format...Conditional Formatting, set up 3 conditions using "Formula Is..." and choose a color for each:

Condition 1:

=CHOOSE(RANK(A1,$A$1:$C$1),1,0,0)

RedCondition 2:

=CHOOSE(RANK(A1,$A$1:$C$1),0,1,0)

YellowCondition 3:

=CHOOSE(RANK(A1,$A$1:$C$1),0,0,1)

GreenNote the position of the "1" in each formula.

This will RANK the 3 values in A1:C1 and then use CHOOSE to find the "1". Based on which cell returns the "1", which CF will interpret as TRUE, CF will color the highest value

Red, the middle valueYellowand the lowest valueGreen.Equal values will have the same color based on where they rank.

Thanks for prompt response - however i am struggling to get that to do anything in my version of excel .....

re: "however i am struggling to get that to do anything in my version of excel"Is it because you don't know how to set up Conditional Formatting in Excel 2007 or is there some other issue?

Here is just one of many sites that explain how to use CF in 2007. Google around for more.

From the Ribbon select Home - Styles - Conditional Formatting,

From the drop down select 'Manage Rules' and select 'New Rule'

In the dialog box select the last item in the list 'Use a formula to determine which cells to format'

Enter the first formula in the box

Click the format button and from the 'Fill' Tab select the first color, click OK

Click OK and select 'New Rule'

Select 'Use a formula to determine which cells to format'

Enter the second formula in the box.

Click the format button and from the 'Fill' Tab select the second color, click OK

Repeat for third and fourth conditional format formulas.

When all four have been entered, 'Applies to' will show the selected range that the conditional formatting will apply to, and the 'Stop if True' boxes should be checked.

Click Apply and OKRegards

No I play with CF quite a bit, I will try Humar's steps next and post back.

Note: For 4 conditions, I believe you'll have to extend the formula to check for 4 ranks, not 3. In other words, note the extra 0.

=CHOOSE(RANK(A1,$A$1:$D$1),1,0,0,0)

Perhaps Humar can test the 4 conditions and see if my suggestions work.

(I've got Excel 2010 at home...I guess I should install it someday.)

!! Very many thanks to you both - Humar - set that up in a blank sheet using 1,2,3 and got it to work - just need to adjust the formula to work in my sheet but i'll try that now - many thanks again!!

Understood DerbyDad - thought that may be the case - anyone know how many it can process, 6,7,8, more .... Thanks again !!

Hmm - thought i had it cracked with your help, what i cannot get it to do now is repeat independently row by row, i.e. 1 2 3 (Nails)

1.1 7 6.2 (Screws)

3 .99 5 (Etc)....etc..... where A1, A2 and B3 would in the above example CF green as the cheapest

Per this site you essentially have an unlimited number of Conditional Format rules that can be applied in 2007. You should review the material at that site if you plan to use CF in 2007 to help you understand all of the new features available. It goes way beyond just "how many rules you can use". http://office.microsoft.com/en-us/e...

Therefore, by expanding my suggested formula by adding 0's and moving the 1, you should be able use it for as many CF's as you need.To Conditionally Format multiple rows with the same rules...

- Select the range you want to CF, e.g. A1:C3

- Follow the steps to CF, but change the formulas by dropping the $ next the Row references:=CHOOSE(RANK(A1,$A1:$C1),0,0,1)

That way the Formula will automatically increment the Row number and CF the entire range, row by row.

Hi, I tried DerbyDad03's CF's with the $ signs dropped before the Row numbers, using one row, but with four columns and four CFs.

It worked fine - four colors - in orders of value.

Here was the first CF:

=CHOOSE(RANK(A1,$A1:$G1),1,0,0,0)

and the last:

=CHOOSE(RANK(A1,$A1:$G1),0,0,0,1)

Note the $ signs.With this working for cells A1 to D1, I selected and copied cells A1 to D1,

then I selected cells A2 to D9 and did PasteSpecial... and selected Formatting in the 'Other Paste Options' group.All 9 rows now independently color the four values in each row according to their rank.

Regards

Brilliant - many thanks!!!!

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History