Conditional formatting is something I only got into recently. I love when my spreadsheets do all kinds of things automatically, especially giving me statistics and pulling certain data from a big sheet. So naturally, because I also like my spreadsheets to look nice, I looked into conditional formatting. The basic usage is pretty simple, but if you want to take it a step further, you need some formulas, so I thought I’ll share how it’s done.
Applying conditional formatting is pretty easy. You can open the editor via the menu and already get a wide variety of options. You just have to define a range and choose a condition and the format.
For me, these options are too limited and they can’t do what I want to do. And the format is only applied to the cell that fits the condition. If you want to apply the formatting to a whole row for example, you have to use the last option “Custom formula is…”.
When you use a custom formula, the range now also indicates what range you want the format to be applied to. For formatting to be applied to a whole row, you also have to use the $ notation in the formula. For example range D:D becomes $D:$D and range D2:D5 becomes $D$2:$D$5.
Defining a formula is actually pretty easy. Just think about what you want, define the range for that and put it into an equation.
Here is just one examples that shows that you can choose any range you want to apply the format to. It doesn’t depend on the range you use for the condition.
Feel free to ask questions in the comments.
For more tutorials see this page.