Google Sheets Course: Conditional Formatting

As its name suggests, conditional formatting is formatting that is applied under certain conditions.

It allows you to modify the following formats:

  • Background color
  • Text color
  • Text effects (bold, italic, underline, strikethrough)

Apply Conditional Formatting

To apply conditional formatting to the following table (lesson-9.xlsx), select the range of cells containing the amounts and select "Conditional formatting" in the Format menu:

google sheets conditional formatting

Then select one of the "Format rules" in the right panel:

google sheets conditional formatting greater png

The formatting will then be applied only when the cell meets the set criteria.

With the rule "Greater than or equal to" and the value set to 50000, only cells with a number greater than or equal to 50000 will be formatted:

google sheets conditional formatting greater 50000 png

After clicking Done, the new conditional formatting is added (if other conditional formats have been applied to the current selection, they will also be displayed on this panel).

From this panel, you can modify conditional formats, delete them or add new ones:

google sheets list conditional formatting png

From this panel, you can also change the order of conditional formatting:

google sheets order conditional formatting png

Color Gradient

By clicking on the second tab of the conditional formatting panel, you can easily apply a color gradient depending on the values of each cell:

google sheets conditional formatting gradient png

Then simply modify the colors of the gradient:

google sheets conditional formatting gradient colors png

Use of formulas in conditional formatting

In the first example, the conditional formatting turned the cells with a number greater than or equal to 50000 blue:

google sheets conditional formatting greater 50000 png

But often, this number is the result of a formula...

In this case, know that you can enter your formula directly in this field:

google sheets conditional formatting average png

The values greater than or equal to the average are therefore turned blue.

Don't forget to fix the range of cells with $ since this range must remain the same for all cells with this conditional formatting (otherwise it will be shifted as when copying).

Custom Conditional Formatting

If no "Format rule" is suitable (despite all the available rules and the possibility of using formulas), there is one last option, the use of a custom formula:

google sheets conditional formatting custom png

An example of custom conditional formatting is detailed on the next page...