Conditional Formatting by row or column for numerical values

[DeniseF]

Hi all. I have two questions and after spinning my wheels for several hours I'm hoping you can help.

1. I work with large datasets and often I would like to know the top, say, 2 values in each column of numbers. There may be hundreds of columns, and say 90 rows. It's fine if the top values are all colored the same, but each column needs to be analyzed separately from every other column. I cannot seem to create a formula that works. :(

Similarly,

2. I am working on a file where I want to know if any of the large number of columns are greater than or equal to 5% of the value in the first column. So IF D1:ID1=(=>($C1*.05)) use red fill, and if D2:ID2=(=>$C2*.05)) use red fill, etc. The same color in each row is fine, but I need each row analyzed individually to highlight only those columns where the value is greater than or equal to 5% of the first value in the row.

Any assistance would be greatly appreciated!

[#2]

try:
1) =(D1>=LARGE($D1:$ID1,2)
2) =(D1>=($C1+0.05)))
make sure the range it is applied to is D1:ID90 or more specifically that the upper left of the range (D1) matches the cell in the formula (D1)

[#3]

Thank you @mtarler! But wouldn't that mean that I would need to apply that formula individually to each row or column as I go along? I'm hoping for formulas that I could apply to the whole worksheet, otherwise it's too time-consuming. :(

[#4]

no. you could apply that to nearly the whole sheet (i.e. starting with column D and going right). The way conditional formatting works is that it will look at the top left cell in the range and apply the formula and then as it moves to each other cell in the range it applies the formula as if it was copied there and the references that are not locked using the '$' before them move relative to the location of the new cell location. SO locking $D...:$ID... and $C... lock those parts of the equation but let the rows adjust and the D1 will always point at the cell of interest. (again assuming the top left of the Applied To range is D1)