Forum Discussion
Conditional Formatting by row or column for numerical values
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 co...
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)
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)
- 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. 😞
- 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)
