CONDITIONAL FORMATTING of non-adjacent cells in Excel

Image of spanners to represent tools
Purpose

To apply conditional formatting in Excel to non-adjacent cells.

Key point

Use a comma to separate the non-adjacent cells/ranges in the selection field, but do not add a comma at the end of the selection.

=L4:L13,M4:M13,N4:N15,O4:O15

Video player icon
Video
This short video shows how to apply conditional formatting to non-adjacent cells in Excel
Method
  1. Click the first cell, or range of cells, that the conditional formatting is to be applied to.
  2. Create the formatting rule and click OK.
  3. With the cursor still in the cell or range, select Conditional formatting | Manage rules, for ‘Current selection‘ (screenshot below).
  4. Click the range icon next to the rule, and the first cell or range you selected will be highlighted.
  5. Add a comma after the first selection, and now select the next cell or range that you want to add.
  6. Keep adding more selections with a comma in between each.
  7. When done make sure your last selection is not followed by a comma (or your selections will be lost and you will have to start again).
  8. Click OK.
Watch out for

Don’t add a comma at the end of the selection, otherwise, when you apply the change, your selections will be lost and you will need to reselect.