Excel Conditional Formatting — 9 Best Uses (Bonus Video)

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

Objective

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

Excel Conditional Formatting Explained

Have you ever come across beautiful-looking Excel sheets that are visually appealing to the viewer, highlighting important data points without creating any clutter?

That is the power of Excel conditional formatting.

It takes your data and presents it to the audience in a very intuitive, easy-to-understand way.

If used properly, it can even make your presentation, everyone’s centre of attention.

Create An Excel Dashboard In 5 Minutes – The Best Guide

Dynamic Dropdown Lists In Excel – Top Data Validation Guide

Conditional formatting in Excel changes the appearance of a cell or a range of cells based on the 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, colour scales, and icon sets.

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

Higher test scores are more orange/red, while lower test scores are more yellow/green

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

Video Tutorial – Conditional Formatting in Excel

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

Video Tutorial – Conditional Formatting in Excel

Excel Conditional Formatting – 9 Common Uses

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

Format cells that are greater than or less than a number
Format cells that contain text
Format only top or bottom ranked values
Format cells using data bars
Format cells using colour scales
Format cells using an icon set
Use a formula to determine which cells to format
Highlighting an entire row based on the value of a cell
Managing and clearing Excel conditional formatting

Now let’s jump right into each of these uses and see how things are done in detail.

But as a precursor,

Select the range of cells you want to apply Conditional Formatting to
Click the Home tab
Click Conditional Formatting

Select the range of cells and click Conditional Formatting under the Home tab

1.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.

Select the cell range that contains the test scores
Click the Home tab
Click Conditional Formatting
Click Highlight Cells Rules
Click Greater Than
Enter the number 75
Select a formatting option
Click OK

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

Enter the value based on which cells should be formatted — Enter the value based on which the cells should be formatted

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.

2.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.’

Select column C
Click the Home tab
Click Conditional Formatting
Click Highlight Cells Rules
Click Text that Contains
Enter the text ‘Beatles’
Select a formatting option
Click OK

Enter the Text bases on which the Cells should be formatted

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

Also Read:

Predict Future Values Using Excel Forecast Sheet – The Best Guide

Getting Started With Excel 3d Maps – The Top 5 Steps

Excel Dynamic Array: 8 Must-know Formulas

3.Format only top or bottom ranked values

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

Select the History column
Click the Home tab
Click Conditional Formatting
Click Top/Bottom Rules
Click Top 10%
Use the scroll arrows to adjust the percentage if required

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

Click OK

Enter the % Percentage value based on which the cells should be formatted. Proceed with your preferred formatting style.

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

4.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.

Select a range of cells
Click the Home tab
Click Conditional Formatting
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.

Under the Data Bars section choose either of Gradient fill or Solid Fill options

5.Format cells using colour scales

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

Select a range of cells
Click the Home tab
Click Conditional Formatting
Click Color Scales

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

Under the Colour Scales section choose the type of colour scale that you want to display

6.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 67th and 33rd percentile. It then assigns an appropriate icon depending on which 3rd the value falls in.

Select a range of cells
Click the Home tab
Click Conditional Formatting
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.

Under the Icon sets section choose the kind of icon set to display and proceed to set the conditions

7.Use a formula to determine which cells to format

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

Select a range of cells
Click the Home tab
Click Conditional Formatting
Click New Rule
Select Use a formula to determine which cells to format
Type the formula into the Format values where this formula is true field
Click Format
Select the desired formatting style
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.

Enter the formula base on which the formatting should occur

All odd numbers in the dataset are now formatted.

End result of Formula based Conditional Formatting

8.Highlighting an entire row based on the value of a cell

You can create an Excel 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.

Select a range of cells
Click the Home tab
Click Conditional Formatting
Click New Rule
Select Use a formula to determine which cells to format
Type the formula into the Format values where this formula is a true field
Click Format
Select the desired formatting style
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.

Specify the Value for which you need Conditional formatting should occur

9.Managing and clearing Excel conditional formatting

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

Edit and delete using the Rules Manager

Click the Home tab
Click Conditional Formatting
Click Manage Rules
Select a rule from the list
Click Edit Rule to make changes
Click Delete Rule to remove from the list

Add, Delete & Edit Conditional formatting rules here

Click OK

Clear Formatting Rules

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

Click the Home tab
Click Conditional Formatting
Click Clear Rules

You can clear all conditional formatting Rules by hitting Clear Rules

Suggested Reads:

Excel For Beginners – Formula Bootcamp

Macros And Basic Vba In Excel – Recorded Webinar

Importing And Cleaning Data In Excel

To see more ways of using Excel 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

FAQs

How do you do conditional formatting in Excel based on another cell value?

Select the cells that you want to conditionally format. Go to Conditional Formatting and select Format by Formula. Enter the formula which refers to the other cell, based on which you want to format. Click Format to Proceed with Formatting Style.

Can I copy conditional formatting from one cell to another cell?

Select the cells that you want to copy. Locate and click the format painter button in the Home Ribbon. Now, drag the brush icon across the cells that you want the conditional formatting to be copied to.

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.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

Excel Conditional Formatting — 9 Best Uses (Bonus Video)

Objective

Excel Conditional Formatting Explained

Video Tutorial – Conditional Formatting in Excel

Excel Conditional Formatting – 9 Common Uses