Excel Conditional Formatting: A Complete Guide

Excel conditional formatting is a powerful feature that allows you to automatically apply formatting—such as colors, icons, and data bars—based on the content of a cell or a range of cells. It helps visualize data more effectively, making it easier to spot trends, highlight important figures, or identify outliers.

In this guide, we will dive deep into how to use Excel Conditional Formatting to improve your spreadsheets, including step-by-step instructions, advanced techniques, and practical examples. By the end of this tutorial, you’ll know how to use this tool like a pro.

1. What is Excel Conditional Formatting?

Conditional formatting enables you to format cells in a worksheet based on specific conditions. The formatting is applied dynamically, which means the cells will change their appearance if the condition is met or if the data changes.

For example, you can automatically highlight all sales figures greater than $1,000, or use color scales to show sales performance over time. Excel offers both pre-built rules and custom options for more advanced users.

2. How to Apply Basic Excel Conditional Formatting

Let’s start with the basics. Here’s a step-by-step guide to apply conditional formatting:

1. Select the range of cells you want to apply the formatting to.
2. Go to the Home tab on the ribbon.
3. In the Styles group, click on Conditional Formatting.
4. Choose from predefined rules or create a new rule.

For instance, to highlight cells greater than a certain value:

1. Click Conditional Formatting > Highlight Cells Rules > Greater Than.
2. Enter the value (e.g., 2) and choose the formatting style (e.g., Light Red Fill with Dark Red Text).

3. Using Predefined Conditional Formatting Rules

Excel offers a variety of built-in rules for common scenarios. Here’s an overview:

Highlight Cells Rules

• Greater Than: Highlights cells greater than a specific value.
• Less Than: Highlights cells less than a specific value.
• Between: Highlights cells within a range of values.
• Text That Contains: Highlights cells that contain certain text.

Top/Bottom Rules

• Top 10 Items: Highlights the top 10 items in a range.
• Bottom 10 Items: Highlights the bottom 10 items.
• Above/Below Average: Highlights cells with values above or below the average.

Data Bars

Data Bars in Excel are a type of excel conditional formatting that visually represent the value of a cell using horizontal bars. The length of the bar corresponds to the cell’s value relative to other cells in the selected range. Larger numbers will display longer bars, and smaller numbers will have shorter bars, allowing you to quickly compare data at a glance.

Key features of Data Bars include:

• Relative Comparison: Excel automatically scales the data bars based on the highest and lowest values in the selected range.
• Color Customization: You can choose different colors for positive and negative values, making it easy to distinguish between them.
• Gradient or Solid Bars: You can opt for gradient fill, where the bar color gradually changes, or solid fill for a uniform bar color.
• Overlay or Standalone Bars: The bars can be placed over the numeric values in the cells, or you can hide the values and display only the bars.

Data Bars are particularly useful for financial data, sales reports, or any dataset where relative size or performance needs to be quickly assessed.

You can apply Data Bars by selecting the cells, going to Conditional Formatting > Data Bars, and choosing a color and style.

Color Scales

Color Scales in Excel are a excel conditional formatting feature that applies a gradient of colors to cells based on their values, helping users quickly identify trends and patterns. The color range typically moves from one color for the lowest values to another for the highest values, with intermediate colors representing values in between.

Key details about Color Scales:

• Two-Color Scale: This applies one color (e.g., red) to the lowest values and another (e.g., green) to the highest values, with a gradient for the values in between.
• Three-Color Scale: This applies a third color to represent midpoint values (e.g., yellow) while keeping the extremes in two other colors.
• Dynamic Visualization: As the data changes, Excel automatically adjusts the colors, making Color Scales ideal for datasets that frequently update.

Use cases include highlighting performance metrics (e.g., low-to-high sales figures) or visualizing data distributions.

You can apply Color Scales by selecting cells, then navigating to Conditional Formatting > Color Scales, and choosing a predefined color scale or customizing your own.

Icon Sets

Icon Sets in Excel are a form of excel conditional formatting that represent data visually using icons, such as arrows, flags, stars, or traffic lights. Each icon corresponds to a range of values, making it easy to identify trends or categories in your data at a glance.

Key features of Icon Sets:

• Relative Value Representation: Icons change based on the value of each cell in comparison to others. For example, green arrows or flags may represent high values, while red ones indicate low values.
• Different Icon Styles: Excel offers several sets, including arrows, traffic lights, shapes (such as circles), and ratings (e.g., stars).
• Customizable Thresholds: You can adjust the value ranges that determine which icons are applied, giving you control over what the icons represent (e.g., set a green arrow for values above 75%).

Icon sets are especially useful in dashboards and reports to quickly convey data performance, such as project progress, sales figures, or customer satisfaction.

To apply Icon Sets, select the cells, go to Conditional Formatting > Icon Sets, and choose a predefined set, or customize the icon logic based on your needs.

4. Creating Custom Conditional Formatting Rules

Custom conditional formatting in Excel gives you more flexibility than predefined options, allowing you to format cells based on complex conditions or formulas. Here’s a complete guide on how to create custom conditional formatting rules, including explanations of all the rule types available under New Formatting Rule.

Step-by-Step Guide to Creating Custom Conditional Formatting Rules

1. Select the Range: Choose the cells or range to which you want to apply the custom rule.
2. Go to the Home tab on the Ribbon.
3. Click on Conditional Formatting in the Styles group.
4. Select New Rule from the dropdown menu.

This opens the New Formatting Rule dialog box, where you can choose from different rule types.

Rule Types in New Formatting Rule

1. Format all cells based on their values
   • This rule allows you to apply formatting based on the actual values within the selected cells. You can choose to format the cells with:
     • 2-Color Scale: Applies two colors (e.g., from red to green) to indicate high and low values.
     • 3-Color Scale: Uses three colors (e.g., red, yellow, and green) for low, medium, and high values.
     • Data Bars: Displays a colored bar inside the cell, where the length of the bar is proportional to the cell’s value.
     • Icon Sets: Displays icons (e.g., arrows, traffic lights) based on the value of the cell.
2. Format only cells that contain
   • Use this rule when you want to format specific cells that meet a condition, such as:
     • Cell Value: Format cells based on a comparison (e.g., greater than, less than, equal to a specific value).
     • Specific Text: Format cells that contain certain text (e.g., “urgent” or “complete”).
     • Dates Occurring: Format cells containing dates within a certain range (e.g., next week, last month).
     • Blanks or Non-blanks: Format cells that are either empty or contain any data.
     • Errors: Format cells that contain error values (e.g., #DIV/0!).
3. Format only top or bottom ranked values
   • This rule highlights the top or bottom numbers in the selected range:
     • Top/Bottom X: Highlights the top or bottom X values (e.g., top 10 sales).
     • Above/Below Average: Highlights cells that are above or below the average value of the range.
4. Format only values that are above or below average
   • Automatically format cells that are higher or lower than the average of the range. This is useful for comparing data, such as highlighting sales values above or below the average.
5. Format only unique or duplicate values
   • This rule highlights unique or duplicate values within a selected range. For example, this could be used to identify repeated entries in a list of customers.
6. Use a formula to determine which cells to format
   • This option gives you the most flexibility and allows you to use Excel formulas to create conditions. For example:
     • Highlight cells in Column A if the value in Column B is greater than a certain number:
     =B2>500
     • Highlight every other row to create a banded rows effect:
     =MOD(ROW(),2)=0
   The formula must return TRUE for the formatting to apply to a particular cell. This option is useful for advanced scenarios where the predefined conditions don’t meet your needs.

5. How to Manage and Remove Conditional Formatting

Over time, your worksheet might accumulate multiple rules. To manage them:

1. Go to Conditional Formatting > Manage Rules.
2. You’ll see all the rules applied to the selected range. Here, you can edit or delete rules.

To remove conditional formatting:

1. Select the cells.
2. Click Conditional Formatting > Clear Rules and choose whether to clear rules from the selected cells or the entire sheet.

6. Advanced Conditional Formatting Techniques

Conditional Formatting with Formulas

Formulas give you the ultimate flexibility. For example, to highlight cells that contain the word “urgent”:

=SEARCH("urgent",A2)

Conditional Formatting Based on Another Cell’s Value

This is especially useful when you want to format one cell based on the value of another. For example, highlight a cell if its corresponding value in another column exceeds a threshold.

Example: To highlight cells in column A if the value in column B is greater than 100:

=$B1>100

7. Conditional Formatting Limitations

While conditional formatting is powerful, it has some limitations:

• You can’t apply formatting to entire rows based on one cell’s value without a custom formula.
• Too many rules can slow down large workbooks.
• Conditional formatting doesn’t support 3D references.

8. Conclusion

Conditional formatting in Excel is a great tool for making your data visually appealing and easier to analyze. Whether you’re creating dashboards, reports, or simply organizing personal tasks, understanding how to use conditional formatting will help you make the most out of Excel’s capabilities.

Sources:

• Microsoft Excel Support
• Excel Easy: Conditional Formatting

Learn more:

Excel integration with other Microsoft Office applications

Microsoft Excel Lookup Function – VLOOKUP, HLOOKUP AND XLOOKUP

Learn Microsoft Excel Function SUMIFS()