How to Use Conditional Format in Microsoft Excel

Suppose you’ve seen a colleague’s or friend’s spreadsheet that dynamically highlights cells based on preset rules or custom formulas. You liked the formatting styles and features so much that you want to implement the same on your own Excel workbooks but you don’t know where to start.

Read this ultimate Excel tutorial to learn how to use conditional formatting in Excel. This is a must-have Excel skill that allows you to create visually appealing and insightful Excel workbooks that attract the attention of recruiters, employers, supervisors, clients, and a wider audience.

What Is Conditional Formatting in Excel?

Conditional formatting in Excel is a feature that lets you change the appearance of cells based on specific conditions or rules you set. It helps you highlight or emphasize data that meets certain criteria, making it easier to spot trends, outliers, or important information in your spreadsheet.

Using Conditional Formatting to highlight stores
Using Conditional Formatting to highlight stores

For instance, you’re importing point-of-sale data of all retail stores into your master sales worksheet. Such a dataset is usually large. Here, you want Excel to dynamically highlight sales values above $500.00.

You can apply a conditional formatting rule instead of complex formulas or Find and Replace. Once set for a cell range, it becomes a permanent style. Even if you delete the underlying data, the rule stays.

Benefits of Excel Conditional Formatting

Here’s a list of formatting styles you can impose on a cell or cell range using conditional formatting in Excel:

  • Apply colors to cells based on their values, creating a gradient effect to visually represent data trends.
  • Highlight the entire row based on a value in a cell of the row.
  • Use icons such as arrows, circles, or flags to represent different conditions or rankings within cells.
  • Highlight the top or bottom percentage or number of values in a range.
  • Format cells based on date criteria, such as highlighting dates that are before or after a certain date.
  • Combine multiple conditions using AND/OR logic to create complex formatting rules for in-depth analysis.
  • Format cells based on a formula like IF, INDEX, SUM, MOD, etc.

Limitations of Excel Conditional Formatting

Find below the shortcomings of conditional formatting in Excel:

  • Conditional formatting doesn’t support data for formulas referenced from an external workbook.
  • Its rules cannot directly reference the values of other cells, which might be needed in certain scenarios.
  • Conditional formatting may not always appear as expected when printing and certain formatting may not be visible on paper.

How to Access Conditional Formatting in Excel

There are several ways to apply conditional formatting in your dataset. Find below the most common ways:

Using the Quick Analysis Tool

This is the quickest and easiest method to apply conditional formatting to a dataset. However, you can only choose from a preset list of cell formatting if you use the Quick Analysis Tool.

Go to your worksheet and highlight the dataset to which you want to format using a rule. Now, click on the Quick Analysis Tool icon in the bottom-right corner of the selection to open a panel of available conditional formatting.

The rules you can apply change automatically depending on the dataset you’ve selected. For example, in the above dataset, I can choose from the following conditional formatting options:

  • Data Bars
  • Color Scales
  • Icon Set
  • Greater Than
  • Text That Contains

I’ve selected the Color Scales conditional formatting and Excel has automatically applied the rule to the appropriate column that contains numerical values.

Using the Excel Ribbon Menu

Ribbon menu for conditional formatting
Ribbon menu for conditional formatting

To access all the features of the Excel Conditional Formatting tool, you can go to the Home tab. There, locate the Styles commands block and click on the Conditional Formatting drop-down menu.

Here, you’ll find the following features:

  • Conditional formatting rules that can be customized:
    • Highlight Cells Rules
    • Top/Bottom Rules
  • Preconfigured rules:
    • Data Bars
    • Color Scales
    • Icon Sets
  • Conditional formatting options:
    • New Rule
    • Clear Rules
    • Manage Rules
Shortcuts for conditional formatting
Shortcuts for conditional formatting

The shortcut key combination to access the Conditional Formatting tool on the Excel ribbon menu is Alt > H > L.

After bringing up the Conditional Formatting menu, you can press the designated key on the keyboard to expand the options to choose from.

Data bar rules using shortcuts
Data bar rules using shortcuts

For example, after highlighting your dataset, press Alt > H > L > D to apply the Data Bars rule to the selection.

How To Use Conditional Formatting in Excel

Example of quarterly sales
Example of quarterly sales

Find below a step-by-step guide to use the Excel Conditional Formatting tool to do the following in a sales report shown above:

  • Highlight and format cells that contain values less than 20% of total sales.
  • Highlight and format all quarterly sales values that are more than 30% of total sales.
Going to more rules
Going to more rules

Select the cell range B3:E6 and go to Home > Conditional Formatting > Highlight Cells Rules > More Rules.

Edit formatting rules
Edit formatting rules

Doing so will open the New Formatting Rule dialog. There, select the Use a formula… option and enter the following formula into the Format values… field:

=B4<=$F4*0.3
Format cells
Format cells

Now click on the Format button to apply the cell formatting you’re looking for. In this example, I’m using red font, parenthesis for values, and yellow shade as cell fill color.

Preview of rules of formatting
Preview of rules of formatting

The Preview panel on the New Formatting Rule dialog should show the outcome of the formatting you’ve chosen. If you’re satisfied, click OK to apply the rule.

Applying rules format painter
Applying rules format painter

To apply the same formatting to the rest of the sales data, highlight the cell range B3:E6 and double-click on the Format Painter button in the Home tab.

Highlight the cell range B8:E11 and Excel will automatically apply the formatting there. Now, also select the cell range B13:E17 to apply the above conditional formatting instantly.

To highlight values of more than 30% of total sales, I’d repeat the same steps mentioned above except for the formula to apply conditional formatting, which is as below. Also, I’m using an automatic font color and a green shade as the fill color.

=B3>=$F3*0.3
dataset showing required values
dataset showing required values

Finally, the dataset appears as shown above. Here, the audience can easily notice sales that are below 20% of total sales (cells with yellow fill color) and above 30% of total sales (cells with green fill color).

Using Conditional Formatting in Excel in a PivotTable

If you want to enhance the readability of a large PivotTable, you can apply conditional formatting rules to it depending on your data analytics requirements. However, some conditional formatting rules might not be available.

Conditional formatting for PivotTable
Conditional formatting for PivotTable

As you can see in the above example, only Greater Than, Less Than, Equal To, and Between rules are applicable. The rest of the conditional formatting is grayed out due to incompatibility.

PivotTable with rules
PivotTable with rules

The method to apply conditional formatting to a PivotTable is also the same as described earlier in this article.

Excel Conditional Formatting Using Preset Rules

You can also use the conditional formatting feature in trend analysis. Here, you can utilize the green, red, and yellow arrow icons to show upward, downward, and flat trends in profits, performance, sales, etc.

Icon Sets in Conditional Formatting
Icon Sets in Conditional Formatting

The arrow icons fall into the Directional group of Icon Sets preset of Excel Conditional Formatting. Other icons available in this Icon Sets are Shapes, Indicators, and Ratings.

3 Arrows directional icon sets
3 Arrows directional icon sets

Suppose, you’ve got a dataset with Profit and Trend columns. To easily identify the movement of profits and trends, highlight the dataset, go to Home > Conditional Formatting, and hover the mouse cursor over the Icon Sets option.

Excel will show several options for icon sets. There, click on the 3 Arrows option. Excel will automatically populate trend arrows according to the values of the highlighted cells in the dataset.

Excel Conditional Formatting: Quick Tips

Find below some expert tips that’ll allow you to use conditional formatting in Excel effectively:

Finding Cells Formatted With Conditional Formatting

If you’ve opened a new workbook shared by a colleague, friend, or client, here’s how you can locate those cells formatted using conditional formatting rules.

Go To dialog
Go To dialog

Open the target worksheet and press Ctrl + G to bring up the Go To dialog.

Go to special
Go to special

There, click on the Special button to access the Go To Special dialog box.

Under the Select section, click on the category that says Conditional formats. Click OK. Excel will instantly highlight all cells and cell ranges formatted with conditional formatting rules.

Editing Excel Conditional Formatting

Instead of deleting an existing or incorrect formatting rule, you can easily edit it to save time and effort.

Manage rules
Manage rules

To edit a rule that has been applied already, click on the Manage Rules option in Home > Conditional Formatting.

This worksheet
This worksheet

The Conditional Formatting Rules Manager will pop up. There, click on the Current Selection drop-down and choose This Worksheet.

Conditional formatting rules
Conditional formatting rules

You should see all the rules applied in the worksheet.

Editing rules
Editing rules

Now, double-click on a rule to access the Edit Formatting Rule dialog where you can edit the formatting rules. Don’t forget to click Apply to make the changes effective.

Editing the Order of Evaluation of Rules

Changing evaluation priority
Changing evaluation priority

If you’ve created more than one conditional formatting rule for the same cell or cell range, Excel will format the cell based on the first rule in the Rules Manager dialog.

So, you can simply move a rule up or down to create the order of priority of the rules.

Clearing Conditional Formatting Rules

Excel’s calculation performance can reduce notably if you use numerous conditional formatting rules in the worksheet. In this situation, you might want to remove some rules to speed up Excel.

To remove a conditional formatting rule, highlight the cell or cell range that contains a rule and click on the Conditional Formatting button on the Home tab.

Clear conditional formatting rules
Clear conditional formatting rules

A context menu will open. Now, hover the cursor over the Clear Rules option and choose Clear Rules from Selected Cells. Contrarily, if you want to get rid of all the conditional formatting rules in the worksheet, click on the Clear Rules from Entire Sheet option.

How to Use Excel VBA to Create a Conditional Formatting Rule

Excel VBA is the go-to tool to automate conditional formatting rule creation in Excel. If you can write an appropriate script, you don’t need to manually set up a formatting rule. No worries if you don’t know Excel VBA or if you’re just learning Excel VBA.

Find below a simple Excel VBA script that formats a cell range based on a value from another cell.

Example dataset
Example dataset

For example, you need to highlight all the sales values in the above dataset in green if they are above $700 and format the rest in yellow.

Conditional formatting based on VBA
Conditional formatting based on VBA

Use the following script to create two rules for the above task:

VBA script for conditional formatting
VBA script for conditional formatting
Sub FormatCellsBasedonAnotherCell()
    Dim selectedRange As Range
    Dim baseCell As Range
    Dim lessThanColor As Long
    Dim greaterThanColor As Long

    ' Show input box to select the cell range to be formatted
    On Error Resume Next
    Set selectedRange = Application.InputBox("Select the cell range to be formatted", Type:=8)
    On Error GoTo 0
    
    ' Exit if the user cancels the input box
    If selectedRange Is Nothing Then Exit Sub

    ' Show input box to select the base cell
    On Error Resume Next
    Set baseCell = Application.InputBox("Select the base cell for comparison", Type:=8)
    On Error GoTo 0

    ' Exit if the user cancels the input box
    If baseCell Is Nothing Then Exit Sub

    ' Set the fill colors
    lessThanColor = RGB(255, 255, 153) ' Light yellow
    greaterThanColor = RGB(144, 238, 144) ' Light green

    ' Clear existing conditional formatting
    selectedRange.FormatConditions.Delete

    ' Add conditional formatting rules
    With selectedRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=" & baseCell.Address)
        .Interior.Color = lessThanColor
    End With

    With selectedRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=" & baseCell.Address)
        .Interior.Color = greaterThanColor
    End With
End Sub

Don’t know how to create a VBA macro using an Excel VBA script? No Worries! Read the following article to learn how to create a VBA macro:

📒 Read More: How To Use The VBA Code You Find Online

Excel VBA-based conditional formatting rules also show up in the Manage Rules dialog. So, you can edit these rules manually or remove them if needed. I’ve already explained these processes earlier in the Quick Tips section.

Conclusions

You might think the Excel Conditional Formatting tool is a complicated feature but actually, it’s fairly easy if you follow the instructions and tips mentioned in this article.

You shouldn’t face any issues creating and applying rules using the manual method from the Quick Analysis Tool or the Excel ribbon menu.

Try out the Excel VBA method if your worksheet dataset is similar to the example script mentioned in this article. If you’re looking for a different conditional formatting rule like Greater Than, Less Than, Color Scales, Icon Sets, etc., in Excel VBA, you can comment below.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃