Excel

Conditional Formatting

Introduction

Let's say you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts and sparklines, conditional formatting provides a way to visualize data and make worksheets easier to understand.

Optional: Download our practice workbook .

Watch the video below to learn more about conditional formatting in Excel.

Understanding conditional formatting

Conditional formatting allows you to automatically apply formatting—such as colors , icons , and data bars —to one or more cells based on the cell value . To do this, you'll need to create a conditional formatting rule . For example, a conditional formatting rule might be: If the value is less than $2000, color the cell red . By applying this rule, you'd be able to quickly see which cells contain values less than $2000.

Conditional Formatting in Excel

To create a conditional formatting rule:

In our example, we have a worksheet containing sales data, and we'd like to see which salespeople are meeting their monthly sales goals. The sales goal is $4000 per month, so we'll create a conditional formatting rule for any cells containing a value higher than 4000.

  1. Select the desired cells for the conditional formatting rule.
    Selecting cells
  2. From the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
  3. Hover the mouse over the desired conditional formatting type , then select the desired rule from the menu that appears. In our example, we want to highlight cells that are greater than $4000.
    The Conditional Formatting menu on the Home tab
  4. A dialog box will appear. Enter the desired value(s) into the blank field. In our example, we'll enter 4000 as our value.
  5. Select a formatting style from the drop-down menu. In our example, we'll choose Green Fill with Dark Green Text , then click OK .
    The Greater Than dialog box
  6. The conditional formatting will be applied to the selected cells. In our example, it's easy to see which salespeople reached the $4000 sales goal for each month.
    A conditional formatting rule is now applied to the worksheet

You can apply multiple conditional formatting rules to a cell range or worksheet, allowing you to visualize different trends and patterns in your data.

Multiple conditional formatting rules on the same area of a worksheet

Conditional formatting presets

Excel has several predefined styles—or presets —you can use to quickly apply conditional formatting to your data. They are grouped into three categories:

  • Data Bars are horizontal bars added to each cell, much like a bar graph .
    Data bars
  • Color Scales change the color of each cell based on its value. Each color scale uses a two- or three-color gradient . For example, in the Green-Yellow-Red color scale, the highest values are green, the average values are yellow, and the lowest values are red.
    Color scales
  • Icon Sets add a specific icon to each cell based on its value.
    Icon sets

To use preset conditional formatting:

  1. Select the desired cells for the conditional formatting rule.
    Select cells
  2. Click the Conditional Formatting command. A drop-down menu will appear.
  3. Hover the mouse over the desired preset , then choose a preset style from the menu that appears.
    The presets on the Conditional Formatting menu
  4. The conditional formatting will be applied to the selected cells.
    The preset has been applied to the worksheet

Removing conditional formatting

To remove conditional formatting:

  1. Click the Conditional Formatting command. A drop-down menu will appear.
  2. Hover the mouse over Clear Rules , then choose which rules you want to clear. In our example, we'll select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet.
    Clear Rules on the Conditional Formatting menu
  3. The conditional formatting will be removed.
    The conditional formatting has been removed

Click Manage Rules to edit or delete individual rules. This is especially useful if you've applied multiple rules to a worksheet.

The Conditional Formatting Rules Manager

Challenge!

  1. Open our practice workbook .
  2. Click the Challenge worksheet tab in the bottom-left of the workbook.
  3. Select cells B3:J17 .
  4. Let's say you're the teacher and want to easily see all of the grades that are below passing. Apply Conditional Formatting so it Highlights Cells containing values Less Than 70 with a light red fill .
  5. Now you want to see how the grades compare to each other. Under the Conditional Formatting tab, select the Icon Set called 3 Symbols (Circled) . Hint : The names of the icon sets will appear when you hover over them.
  6. Your spreadsheet should look like this:
    spreadsheet after completing Challenge
  7. Using the Manage Rules feature, remove the light red fill , but keep the icon set .