Excel Online: How do I extend conditional formatting rules to succeeding rows?

Question

Hi,

This seems like a simple operation but I couldn't figure it out or find answers to my question.

For Excel Online: I was able to create a conditional formatting rule for a cell, which changes its colour according to the value in a different cell of the same row. See below example:

Fig. 1

When I select Effect or Pendulum/Effect from the dropdown list in F18, an orange fill is applied to A18 as expected:

Fig. 2

The problem is I can't find a way to apply this rule across all other rows. Copy-pasting formatting doesn't seem to work for conditional formatting rules (at least for Excel Online). Is there a way of doing this? I have the dropdown options on a separate sheet called Data:

Fig. 3

Note: Colours on the Data sheet are applied on this sheet simply for future reference.

Hope someone is able to help - please let me know if more info is required. Thanks :)

Answer 1

The formula in the conditional formatting rule is written for the first row the formula should apply to. You have written this for A18 so you reference cell $F$18. If you change this to F18 you can then change the range of cells it applies to from A18:A18 to A18:A31. By removing the $s, when the formula is applied to cell A19, it will check the contents of cell F19 to determine if the conditional formatting applies, etc.