Explanation

Conditional Formatting lets the user apply formatting to a cell or a range of cells based on specific criteria.

There are various conditions that can be tested in order to apply the formatting:

• Format cells based on their values
• Format cells that contain a certain text
• Format top/bottom-ranked cells in a range
• Format duplicates
• Format cells based on a formula
• And much more!

Basic Usage

To use conditional formatting, select the cell/range of cells that you would like to format. 

Then, from the Home tab, select “Conditional Formatting”:

Conditional Formatting Examples

Formatting cells based on their values

We can format cells based on their values using Conditional Formatting. 

For example, this is how we tell Excel to change the background color of all values greater than 60 to green:

First, let’s select the range we’d like to format:

Next, click on Conditional Formatting – Highlight Cells Rules – Greater Than:

Now, in the value type “60”, and select “Green Fill with Dark Green Text”:

And here’s the final result:

Note – If you want to apply a style that doesn’t appear in the basic menu, just select “Custom Format”:

Format cells if their text contains a certain text

Now, let’s see how we can format only the cities which contain the text “ster”:

We can do it by clicking Conditional Formatting – Highlight Cells Rules – Text That Contains:

Formatting top/bottom cells in a range

We can quickly find top/bottom cells using Conditional Formatting.

How can we format the Top 10% of students’ test scores?

To do this, select Conditional Formatting – Top/Bottom Rules – Top 10%:

Formatting Duplicate Cells

We can quickly understand which cells are duplicates within our range using Conditional Formatting – Highlight Cells Rules – Duplicate Values.

Let’s see how can find all the duplicate cities in the following table:

Tip – If you wish to remove duplicates, use the “Remove Duplicates” tool – Click here to read the tutorial!

Format Cells Based on a Formula

This is probably the most powerful feature of Conditional Formatting – We can apply any formula we want as a condition!

Now, Let’s see how we can highlight all cells that contain more than 6 characters.

To do so, we can use the LEN formula to calculate the number of characters in each cell.

So, let’s select Conditional Formatting – Highlight Cells Rules – More Rules – Use a formula to determine which cells to format:

Please note – within the LEN formula, we select only one of the cells in the range. Please make sure that the cell does not have any reference (absolute or partial) to allow the formula to adjust itself to each of the cells in the range. More about absolute/partial references can be found in our Excel Basics guide.

Format using a Color Scale

We can apply a Color Scale on our cells, for example – if we want to quickly identify the hottest temperatures (Hotter – Red, Colder – Green). 

Just Select Conditional Formatting – Color Scales – Select the desired Color Scale:

Practice Conditional Formatting

Now, let’s practice Conditional Formatting. Click here to download the Conditional Formatting exercise for free!