Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • OneDrive
      • Google Docs
      • Google Sheets
      • Google Drive
    • Email
      • Gmail
      • Outlook
      • Emal Etiquette
    • Office Etiquette
    • Productivity
    Inside The Web
    Home»Office»Excel»Conditional Formatting in Excel—Detailed Guide

    Conditional Formatting in Excel—Detailed Guide

    Jabin ManandharBy Jabin ManandharAugust 15, 2023 Excel 6 Mins Read

    As the name suggests, conditional formatting in Excel simply means applying certain formatting to cells based on some condition. 

    However, unlike format painter, which also helps you copy and apply formatting to other cells, conditional formatting is dynamic. Meaning, it will automatically change the formatting of a cell even if its value changes in the future.

    With several built-in conditional formatting options, you can highlight certain cells to make them stand out visually. At the same time, you could discover some interesting trends and patterns in a large dataset without breaking a sweat.

    Things to consider
    • Avoid using too many conditional formatting rules as it could slow down your whole worksheet.
    • If you have conflicting conditional formatting rules, the one that comes first on the Conditional Formatting Rules Manager list will be executed.

    Using Built-in Options

    Excel provides a list of several most basic and frequently used conditional formatting options under the Home tab. These options are easy to use and quite self-explanatory, which cover formatting dates, text, numbers, and so on. 

    Quick Tip: Hover over different conditional formatting options to preview the cells and get an idea of how a particular option works beforehand.

    Highlight Cells Rules

    These rules include common use cases like highlighting cells with values greater, less, or equal to another specified value. Also, you can highlight cells with a specific text and number, including duplicate values. 

    In the image below, we will use the “Greater than” option to highlight cells having values greater than 60 with a green background.

    Note: The “Text that contains” option isn’t case-sensitive. Meaning, it will treat “Apple” and “apple” as the same.

    Top/Bottom Rules

    By default, the rules in this section display the top/bottom 10 values (in numbers and percentages) among the selected cells. You can also specify a different number of items or percentages if you want.

    Example: 
    Here, we will be highlighting the top two scores with a green background.

    Highlight-Top-two-scores-conditional-formatting

    Data Bars

    These options calculate the total percentage for each selected cell based on the highest value and display it in terms of data bars. Here, the longer data bar represents cells with higher values and vice-versa.

    Example: 
    In the image below, we will use data bars to visualize salaries ranging from low to high.

    Color Scales

    These rules display different colors or color gradients for each selected cell ranging from highest to lowest value. 

    Example: 
    Here, we will use the data bars to indicate the price of items ranging from cheap to most expensive with colors like green, yellow, orange, and red.

    Icon Sets

    These options display different icons based on how much percentage each selected cell value constitutes out of the total. The icon sets include icons like flags, arrows, circles, ratings, etc. 

    Example: 
    Here, we will use the up/down arrows to indicate positive or negative growth depending on whether the cell value is less or greater than 2%. Meanwhile, the right-facing arrow will indicate those having a growth rate equal to 2%.

    Using Formula

    Apart from the built-in conditional formatting options, you can use a custom formula to highlight cells that match your specific criteria (s). 

    For instance, you can apply conditional formatting to an entire row based on the values in a particular column.

    Here, we are trying to highlight the whole row containing customers that are subscribed to the “Gold” plan using conditional formatting.

    Now, we could use one of the built-in options, specifically the “Text that Contains” option.  However, such a rule formats only the cells with the “Gold” value instead of highlighting an entire row. It doesn’t work even if we select all the customer data. 

    So, we need to enter a custom formula in this particular case.

    1. Select all cells containing the dataset values. 
      Select-cells-containing-required-dataset-values
    2. To apply conditional formatting with a formula, click Conditional Formatting and select New Rule.
      Create-new-conditional-formatting-rule
    3. Then, select the Use a formula to determine which cells to format option.
    4. Type =$I3=”Gold”. Here, the first entry under the Plan column is I3. So, we used mixed cell referencing ($I3) to lock the “I” column and consider only the cells under the Plan column. Similarly, we used relative referencing starting from row 3 to apply formatting to the entire row.
      Enter-formula-conditional-formatting-rule
    5. Now, click Format and choose an appropriate fill or font color.
      Format-cells-using-condtional-formatting
    6. Click OK.

    The final result is as follows.

    Using a similar approach, you can format every first, second, or nth row according to your specific condition. On top of it, you can even format duplicate rows using conditional formatting.

    Note: You can enter several other built-in functions like ISEVEN(), TODAY(), MOD(), etc according to your particular case while using the above option.

    How to Apply Conditional Formatting Based on Multiple Criteria?

    In case you need to apply conditional formatting based on multiple conditions, you can do so using logical operators like AND and OR.

    For instance, in the previous example, we applied formatting to an entire row based on one condition — whether the cell contains the “Gold” value or not. 

    However this time, we’ll apply formatting to the entire row only depending on multiple conditions.

    Using AND function

    Using the logical AND function, you can apply conditional formatting to cells only if they match all the conditions.

    For instance, format an entire row for customers that have the Gold plan with a due payment of more than 60 dollars.

    Here, the formula is
    =AND($I3="GOLD",$J3>60)=

    After using the AND function inside the formula, the result is as follows:

    Using OR Function

    Using it, you can apply conditional formatting to cells only if any one of the conditions returns TRUE.

    For instance, format an entire row for customers that have either a Gold or platinum plan.

    Here, the formula is
    =OR($I3="GOLD",$I3="Platinum")

    After using the OR function inside the formula, the result is as follows.

    Bonus Tips

    • If you convert your cell range into a table, Excel will automatically extend and apply the conditional formatting rule to new entries inside the table.
    • Along with regular cells, you can even use conditional formatting for pivot tables and charts.
    • While using the “Highlight Cells Rules” and “Top/Bottom Rules” options, you can also enter a cell reference instead of manually inputting the value.

    Related Questions

    How to Find and Remove Conditional Formatting?

    If you later decide to remove conditional formatting from your worksheet, you can find all of them inside the Conditional Formatting Rules Manager. Then, you can delete or even choose to edit the rule from there. Just select Conditional Formatting > Manage Rules under the Home tab.

    Cell Formats Excel Basics
    Jabin Manandhar

      As a tech content writer, Jabin covers Excel-related articles at InsideTheWeb. His articles mainly involve helping new users to quickly familiarize themselves with the Excel interface and explaining various essential features. While he got introduced to Excel in his early school days, he developed a keen interest in it after working on a college project. He was impressed at how quickly one could accomplish several tasks with built-in functions like the filter function and user-friendly tools like the power query. Keeping beginner audiences in mind, he loves to explain even the most fundamental Excel concepts in detail and break down complex topics with a step-by-step approach. As an avid Excel user, he believes every task can be done a lot quicker if you know the right tools and techniques. When he’s not behind a keyboard, he loves to listen to interesting audiobooks and podcasts.

      Related Posts

      Excel By Nisha Gurung

      How to Compare Two Columns in Excel Using VLOOKUP

      Excel By Nisha Gurung

      How to Share Excel Files with Multiple Users

      Excel By Nisha Gurung

      How to Filter Based on a List in Excel

      Add A Comment
      Table of ContentsToggle Table of ContentToggle
      • Using Built-in Options
        • Highlight Cells Rules
        • Top/Bottom Rules
        • Data Bars
        • Color Scales
        • Icon Sets
      • Using Formula
      • How to Apply Conditional Formatting Based on Multiple Criteria?
        • Using AND function
        • Using OR Function
      • Bonus Tips
      • Related Questions
        • How to Find and Remove Conditional Formatting?
      • Home
      • About Us
      • Privacy Policy
      © 2025 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.