How to Use Conditional Formatting in Excel

This Conditional Formatting in Excel tutorial is suitable for users of Excel 2010, 2013,2016,2019, and Microsoft 365.  

Objective 

Use Conditional Formatting to highlight important data visually and identify patterns and trends.  

Conditional Formatting Explained 

Conditional Formatting changes the appearance of a cell or a range of cells based on conditions you specify. It makes it easy to highlight important information in a dataset, detect issues, emphasize anomalies, and visually analyze data using data bars, color scales, and icon sets.   

There are many built-in conditional formats in Excel, or you can create custom formats.  

Conditional Formatting can be applied to numerical datasets (either a selection or named range), and an Excel table or even a PivotTable report.  

Method 

There are many different types of Conditional Formatting in Excel. Let us explore some of the most useful.  

1. Select the range of cells you want to apply Conditional Formatting to 

2. Click the Home tab 

3. Click Conditional Formatting 

Format cells that are greater than or less than a number 

In this example, I want to format all the cells that contain a test score greater than 75.  

1. Select the cell range that contains the test scores 

2. Click the Home tab 

3. Click Conditional Formatting 

4. Click Highlight Cells Rules 

5. Click Greater Than 

6. Enter the number 75 

7. Select a formatting option 

8. Click OK 

Excel will format all the cells that contain a test score of 75 or above.  

You can use the same method to find test scores lower than a specified number by selecting the Less Than menu option from the Highlight Cells Rules drop-down.  

Format cells that contain text  

Cells can be formatted based on the text contained within them. For example, in this range, I want to format all of the cells in column C that contain the word ‘Beatles.’

1. Select column C 

2. Click the Home tab 

3. Click Conditional Formatting 

4. Click Highlight Cells Rules 

5. Click Text that Contains 

6. Enter the text ‘Beatles’ 

7. Select a formatting option 

8. Click OK 

You can use the same method to find specific dates in a range and duplicate values.  

Format only top or bottom ranked values

You can find the highest and lowest values in a range of cells based on criteria you specify. In this example, I want to find which students rank in the top 10% for history.  

1. Select the History column 

2. Click the Home tab 

3. Click Conditional Formatting 

4. Click Top/Bottom Rules 

5. Click Top 10% 

6. Use the scroll arrows to adjust the percentage if required 

To format cells using different colors other than the ones specified in the list, click Custom Format.  

7. Click OK 

You can use the same method to format the bottom 10%, the top/bottom items, and those above or below average.  

Format cells using data bars 

Data bars help you visualize values in cells relative to other values. The length of the data bar represents that value in the cell. The longer the bar, the higher the value.  

1. Select a range of cells 

2. Click the Home tab 

3. Click Conditional Formatting 

4. Click Data Bars 

In this example, I have applied Gradient Fill to the Maths and Science columns and Solid Fill to the English and History columns.  

Format cells using color scales 

Color scales are visual guides that help you understand how data is distributed. Like a heat map, color scales are useful in helping you visualize variations in data. You can apply two-color scales or three-color scales to a data range, and the value in each cell is represented by two or three colors depending on which color scale you selected.  

1. Select a range of cells 

2. Click the Home tab 

3. Click Conditional Formatting 

4. Click Color Scales 

In this example, I have used a two-color scale. The higher values have a yellow color and the lower values are green.  

Format cells using an icon set 

Icon sets are used to group or classify data into three to five categories. They make it easy to visualize values in a range of cells with each icon representing a range of values. For example, if you select a 3-icon set to apply to a range of cells, Excel looks at the values in the selected cells and calculates the 67^th and 33^rd percentile. It then assigns an appropriate icon depending on which 3^rd the value falls in.  

1. Select a range of cells 

2. Click the Home tab 

3. Click Conditional Formatting 

4. Click Icon Sets 

In this example, I have selected the ‘3 Traffic Lights’ icon set. Excel has assigned the red traffic light to the values that fall in the lowest third, the orange traffic light to the values that fall in the middle third, and the green traffic light for values that fall in the highest third.  

Use a formula to determine which cells to format

Formulas can be utilized with conditional formatting. You can use a logical formula to specify the formatting criteria and create your own conditional formatting rule.  

1. Select a range of cells 

2. Click the Home tab 

3. Click Conditional Formatting 

4. Click New Rule 

5. Select Use a formula to determine which cells to format 

6. Type the formula into the Format values where this formula is true field 

7. Click Format 

8. Select the desired formatting style 

9. Click OK 

In this example, I have created a rule that contains a formula to find all the cells in the selected range that contain an odd value and format those cells with a purple background fill and white font. The cell reference in brackets (C7) is the only argument for the ISODD function. The cell reference used should always be the top left-most cell in the selected dataset.  

All odd numbers in the dataset are now formatted. 

Highlighting an entire row based on the value of a cell 

You can create a conditional formatting rule that highlights an entire row based on the value of a cell. It is much easier to visualize important data when the whole row is highlighted rather than a specific cell, particularly if you have a large dataset.  

1. Select a range of cells 

2. Click the Home tab 

3. Click Conditional Formatting 

4. Click New Rule 

5. Select Use a formula to determine which cells to format 

6. Type the formula into the Format values where this formula is a true field 

7. Click Format 

8. Select the desired formatting style 

9. Click OK 

In this example, I have created a rule that highlights all rows that contain the name ‘Deborah Ashby.’ The cell reference $E4 is used as it is the first cell in the column that contains the text string I am searching for. When you copy this formula down, the row number (4) is not locked but the column letter (E) is.  

Managing and clearing conditional formatting 

Conditional Formatting rules can be edited, deleted, and cleared. 

Edit and delete using the Rules Manager 

1. Click the Home tab 

2. Click Conditional Formatting 

3. Click Manage Rules 

4. Select a rule from the list 

5. Click Edit Rule to make changes  

6. Click Delete Rule to remove from the list 

7. Click OK 

Clear Formatting Rules 

Conditional Formatting can be cleared from the selected cells or the entire sheet.  

1. Click the Home tab 

2. Click Conditional Formatting 

3. Click Clear Rules 

To see more ways of using conditional formatting to highlight data in your worksheet, check out the following links: 

Microsoft – Use Conditional Formatting to highlight important information 

Spreadsheeto – How to Use Conditional Formatting in Excel: 15 Awesome Tricks 

ExcelJet – Conditional formatting with formulas (10 examples) 

Video Tutorial 

To see Conditional Formatting in action, please watch the following video tutorial.  

Link – Conditional Formatting in Excel (16:08) 

For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.

To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.