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?

6 Answers

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.

3

A key point to understand Excel Conditional formatting is to know that

The formula you create for Conditional Formatting is based on the cell that is currently active. The cells affected (to be colored) are those in the Selection Range.

Source

So pay attention to where you have your cursor (active cell) before trying different formulas.

(Normally, if you leave the cursor at the top-left of your range and define the formula for the first row, Excel will guess correctly what you mean regarding the rest of the "Applies to" range.)

2

I ran across the same issue wanting to apply my conditional formatting to multiple rows. This was a simple solution I found on Microsoft's Office support site. You can use the format painter under on the Home tab of the ribbon in the Clipboard group. It is a paint brush located right below the copy button.

1

You can't use the built in Excel "Format highest value" or "Format lowest value" or whatever preset. If you remove the dollars from that, that is when Excel puts them back.

You need to pick "Use a formula to determine which cells to format"

Let's say your first row is A1 through J1. Go into A1, add a rule for conditional formatting using a formula and to highlight the lowest value of the row, put in the formula box: =A1=MIN($A1:$J1)

Then either format painter or copy/paste special formats to copy the conditional formatting to the rest of the row and then the rest of your cells and viola: conditional formatting by row for the entire range in one fell swoop.

This is not possible with Excel's base functionality. After lots of searching, trying recommended solutions, and trial-and-error, I must agree that a macro offers the only solution; for some reason Excel does not adjust cell references when a user attempts to copy conditional formatting (as it does when copying a cell formula). (I imagine Microsoft would call this a "feature".)

I was able to obtain this desired functionality, interestingly, with the freeware Kingsoft Spreadsheets (with which I have no connection); it DID correctly copy conditional formatting, adjusting relative cell references, after I defined the conditions for it. (Perhaps Kingsoft is more discriminating in the code they include in their product.) ;-)

Unfortunately, this does not provide a workaround to the OP's problem. Saving (from Kingsoft) in .xlsx format and re-opening in Microsoft Excel was unsuccessful; the (copied) conditional formatting was corrupted. But if one needs a lot of conditional formatting and doesn't necessarily need to use Excel, this might be useful.

1

This works no matter how you sort or whatever, basically it looks at each cell down the column until there is no data.

My conditional format applies to a row starting from column A to column C per your example, but you can get the concept.

Using the New Rule -- 'Use a formula to determine which cells to format'

=$A2=avg($A$2:$A$22)

hit OK after you select your format Then for the applies to section and input

=$A:$C

Will work for many different situations.