Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Excel Conditional Formatting — 9 Best Uses (Bonus Video)

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.  

Related: 

Excel Sumifs & Sumif Functions – The No.1 Complete Guide

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.  

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

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.  

  1. Format cells that are greater than or less than a number
  2. Format cells that contain text
  3. Format only top or bottom ranked values
  4. Format cells using data bars
  5. Format cells using colour scales
  6. Format cells using an icon set
  7. Use a formula to determine which cells to format
  8. Highlighting an entire row based on the value of a cell
  9. 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
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
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.
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
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
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
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
Enter the formula base on which the formatting should occur

All odd numbers in the dataset are now formatted. 

d result of Formula based Conditional Formatting
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
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
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
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.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Setup and Edit Vendors in QuickBooks 2019

Tracing Task Paths in Microsoft Project 2013

Adding Resources to a Project Using the Resource Sheet in Microsoft Project 2016

How to Set Out Of Office in Outlook App? 2 Easy Methods

What’s new in QuickBooks 2020?

The Most Used Microsoft Outlook Shortcuts – Download

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)