When using the Conditional Formatting in Excel 2013, I can't seem to apply one rule to multiple rows.
I use data which is not related in multiple rows. For example, row A contains prices, row B contains costs, and row C contains share prices.
I use the following rule "Format only values that are above or below average", as I want to highlight the cells which are either one st.dev. below or one st.dev. above average.
If I use the format painter to apply the rules established for row A to row B, it won't work (as it uses the average calculated in row A).
If i try to edit the formula ($A$2:$A$20 –> A$2:A$22), Excel won't apply the changes and continues to change it back to ($A$2:$A$20).
Does anyone have any idea how I can solve this problem?
Best Answer
The "secret" is to make the formula with "relative referencing", hence, if your formula looks like this: =$B$2>$A$2
then remove the $ sign before the row number, so it will look like this: =$B2>$A2
With this, you actually don't need to copy the conditional formatting. You can select an entire range before applying it, and insert the formula as if you selected only the first cell at the top. Excel will know to apply the correct formulas to all the other cells in the range (assuming the range is a column). Or you can apply this formula to the first row, and copy it with the format painter.
Hope this is helpful.