Essential Conditional Formatting Tricks

2 Flares Filament.io 2 Flares ×

Conditional Formatting is an amazing feature of Excel. It is sure to create a spark of interest and questions during training. People see the potential in their spreadsheets and how easy their team could visualise their data and create engaging forms and reports.

This tutorial looks at the two most requested Conditional Formatting tricks asked by Conditional Formatting enthusiasts.

How to Format the Entire Row

Formatting a cell is great, but what if your data is many columns wide. Applying Conditional Formatting to the entire row will be far more effective in spreadsheets that have many columns.

For example, in the spreadsheet below I want to format the entire row where the exam grade is 75% or higher.

List of grades

  1. Select the entire table of data excluding the header row.
  2. Click the Home tab on the Ribbon and then the Conditional Formatting button.
  3. Select New Rule from the list.
  4. Click on Use a formula to determine which cells to format in the top half of the screen.
  5. Enter the required formula in the Format values where this formula is true box.

The IF function give us the ability to reference a different cell to the one being formatted. The mixed reference (column is fixed, but row is relative) of $B2 is used to check down column B, but not across the columns.

=IF($B2>=75%, TRUE, FALSE)

  1. Click the Format button and choose the formatting you would like to apply.
  2. Click Ok.

Use a formula for your Conditional Formatting tricks

How to Use Multiple Conditions

The Conditional Formatting tool makes it easy to set up simple conditional rules. These may be OK for most of your needs. The second of our essential Conditional Formatting tricks takes things a step further and uses multiple conditions for a rule.

To use multiple conditions in Conditional Formatting you will need to enter a formula. Depending on the logic you want to use this will likely be either the AND or OR function. These functions can handle as many as 255 conditions. I think that should be enough J (Find out more on the AND and OR functions in Excel).

For example, I would like to format the entire row if the exam grade is 75% or higher and the coursework grade is 80% or higher.

  1. Select the range of cells you want to apply the Conditional Formatting to. In this case A2:C10.
  2. Click the Home tab on the Ribbon and then the Conditional Formatting button.
  3. Select New Rule from the list.
  4. Click on Use a formula to determine which cells to format in the top half of the screen.
  5. Enter the required formula in the Format values where this formula is true box.

The formula below uses the AND function to ensure that both conditions are met.

=AND($B2>=75%,$C2>=80%)

  1. Click the Format button and choose the formatting you would like to apply.
  2. Click Ok.

There is no limit to how complex this criteria may be. Conditional Formatting rules can be created to meet your needs whatever they may be, you just need to know the formulas you need to use.

Other Conditional Formatting Tricks

Please share your own favourite Conditional Formatting tricks by commenting on this post below.

Leave a Reply

Your email address will not be published. Required fields are marked *