Excel Conditional Formatting Rules: Complete Guide with Examples

Conditional formatting transforms raw numbers into visual information. Instead of scanning rows of data to find outliers, trends, or specific values, you apply rules that automatically highlight cells based on their content. A sales report becomes instantly scannable when the top performers are shaded green and underperforming regions appear in red.

This guide covers every conditional formatting option in Excel, from the built-in highlight rules to custom formulas, with practical examples you can apply to your own spreadsheets.

Table of Contents

1. Accessing Conditional Formatting
2. Highlight Cell Rules
3. Top/Bottom Rules
4. Data Bars, Color Scales, and Icon Sets
5. Custom Formula Rules
6. Managing and Prioritizing Rules
7. Best Practices and Common Pitfalls

Accessing Conditional Formatting

Select the range of cells you want to format, then navigate to Home > Conditional Formatting on the ribbon. The dropdown menu groups formatting options into five categories:

• Highlight Cells Rules: Apply formatting based on specific values, text, dates, or duplicates.
• Top/Bottom Rules: Highlight the highest or lowest values, or values above/below average.
• Data Bars: Display proportional bars within cells based on their values.
• Color Scales: Apply a gradient color scheme across a range based on value ranking.
• Icon Sets: Display directional or status icons based on value thresholds.

At the bottom of the menu, New Rule opens the full rule builder for custom formulas, and Manage Rules displays all rules applied to the current selection.

Highlight Cell Rules

These are the simplest conditional formatting rules and the most frequently used.

Greater Than / Less Than / Between

Highlight all values in a range that exceed a threshold:

1. Select your data range (e.g., B2:B100).
2. Go to Conditional Formatting > Highlight Cells Rules > Greater Than.
3. Enter the threshold value (e.g., 10000).
4. Choose a formatting preset (Light Red Fill with Dark Red Text, or Custom Format).
5. Click OK.

For the Between option, specify two values. Cells with values falling between the two thresholds receive the formatting. This works well for highlighting values within a target range, such as sales between $5,000 and $15,000.

Text That Contains

Highlight cells containing specific text:

1. Select your range.
2. Go to Conditional Formatting > Highlight Cells Rules > Text That Contains.
3. Enter the text to search for (e.g., urgent).
4. Choose your formatting style.

This rule is case-insensitive. "Urgent," "URGENT," and "urgent" all match.

A Date Occurring

Highlight cells based on date ranges:

1. Select your date column.
2. Go to Conditional Formatting > Highlight Cells Rules > A Date Occurring.
3. Choose from: Yesterday, Today, Tomorrow, Last 7 days, Last week, This week, Next week, Last month, This month, or Next month.

This is particularly useful for project timelines, invoice due dates, or contract expiration tracking.

Duplicate Values

Highlight duplicates with a single click:

1. Select the column you want to check.
2. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose Duplicate or Unique from the dropdown.
4. Select your formatting and click OK.

Tip: To highlight entire rows based on a duplicate value in one column, use a custom formula instead (covered below). The built-in Duplicate Values rule only formats the cells in the selected range, not the entire row.

Top/Bottom Rules

These rules highlight extreme values in your dataset without requiring you to know the exact threshold.

Rule	What It Does	Example Use
Top 10 Items	Highlights the N highest values	Top 10 sales reps
Top 10%	Highlights the top N percent of values	Top 10% of products by revenue
Bottom 10 Items	Highlights the N lowest values	Bottom 10 performing stores
Bottom 10%	Highlights the bottom N percent	Bottom 10% of delivery times
Above Average	Highlights values above the range average	Above-average monthly sales
Below Average	Highlights values below the range average	Below-average test scores

Click the number "10" in the dialog box to change it to any value. For "Above Average" and "Below Average," Excel calculates the average automatically from the selected range.

Data Bars, Color Scales, and Icon Sets

These three visualization types add graphical elements directly into cells, creating an immediate visual representation of your data.

Data Bars

Data bars fill each cell with a colored bar proportional to the cell's value relative to the rest of the range. The largest value gets a full-width bar; smaller values get proportionally shorter bars.

1. Select your numeric data range.
2. Go to Conditional Formatting > Data Bars.
3. Choose a gradient or solid fill color.

To customize the minimum and maximum values that determine bar length:

1. Go to Conditional Formatting > Manage Rules.
2. Select the data bar rule and click Edit Rule.
3. Change Minimum and Maximum from "Automatic" to "Number" and enter fixed values.

This is useful when you want all bars to be sized against a known target (e.g., a sales quota of $100,000) rather than the highest value in the current dataset.

Color Scales

Color scales apply a gradient across your range. The most common is the three-color scale:

• Green-Yellow-Red: High values are green, middle values are yellow, low values are red.
• Red-Yellow-Green: The inverse -- useful when lower values are better (e.g., error rates).

Excel supports two-color and three-color scales. You can customize the colors and the percentile or number thresholds for each color stop.

Icon Sets

Icon sets display small icons in cells based on value thresholds. Available icon sets include:

• Directional: Arrows (green up, yellow sideways, red down)
• Shapes: Traffic lights, flags, ratings stars
• Indicators: Check marks, X marks, warning signs

By default, Excel divides your range into three equal percentile segments (top 33%, middle 33%, bottom 33%). Customize these thresholds in the rule editor:

1. Manage Rules > Edit Rule.
2. Change Type from "Percent" to "Number" for precise thresholds.
3. Enter your threshold values (e.g., >= 90 gets a green check, >= 70 gets yellow, < 70 gets red X).

Custom Formula Rules

When built-in rules do not cover your scenario, custom formulas provide unlimited flexibility. The formula you write must return TRUE or FALSE -- cells where the formula evaluates to TRUE receive the formatting.

Highlight Entire Row Based on One Column

This is the most requested custom formatting scenario. To highlight entire rows where column C contains "Completed":

1. Select the entire data range including all columns (e.g., A2:E100).
2. Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter this formula:

=$C2="Completed"

The dollar sign before C locks the column reference, so the formula checks column C for every cell in the row. The row number (2) matches the first row of your selected range and adjusts automatically for subsequent rows.

Highlight Alternating Rows

=MOD(ROW(),2)=0

This applies formatting to every even-numbered row. Change =0 to =1 for odd-numbered rows. Alternating row shading (also called banded rows) improves readability in large tables.

Highlight Cells That Contain Specific Text (Partial Match)

=ISNUMBER(SEARCH("urgent", A2))

This highlights any cell in column A that contains the word "urgent" anywhere within its text, regardless of surrounding characters.

Highlight Based on Comparison with Another Cell

=B2>C2

Highlights cells in column B where the value exceeds the corresponding value in column C. Useful for comparing actual vs. budget, this year vs. last year, or forecast vs. actual.

Highlight Dates Within a Specific Range

=AND(A2>=TODAY(), A2<=TODAY()+30)

Highlights dates in column A that fall within the next 30 days. Replace TODAY() with a specific date reference, or change +30 to adjust the range.

Managing and Prioritizing Rules

When multiple rules apply to the same cells, Excel evaluates them in order. Access the rule manager through Conditional Formatting > Manage Rules.

Rule Precedence

Rules at the top of the list take priority over rules below them. Use the up and down arrow buttons to reorder rules. When two rules conflict (e.g., one formats a cell green and another formats it red), the higher rule wins.

Stop If True

Check the Stop If True box on any rule to prevent Excel from evaluating lower rules when this rule matches. This is useful for creating exception handling:

1. Rule 1: Highlight cells equal to 0 in gray (Stop If True: checked).
2. Rule 2: Apply a color scale to all other cells.

Without Stop If True, Rule 2 would overwrite Rule 1's formatting on cells containing zero.

Applies To Range

Each rule specifies an Applies to range. You can edit this range to expand or contract the cells affected by the rule. When you insert new rows within an existing range, Excel automatically extends the rule to include the new rows if they are contiguous.

Best Practices and Common Pitfalls

Performance

Conditional formatting recalculates every time the worksheet changes. With thousands of rules or very large ranges, this can noticeably slow down Excel. To maintain performance:

• Apply rules to specific ranges rather than entire columns (use A2:A1000 instead of A:A).
• Limit the number of rules. Combine multiple conditions into a single formula when possible.
• Use Excel Tables, which automatically extend formatting to new rows without requiring rule modifications.

Common Issues

Problem	Cause	Solution
Formatting disappears after sorting	Formula uses relative references incorrectly	Use absolute references ($A$1) where the reference should not shift
Rule does not apply to new rows	Applies to range does not include new rows	Extend the range in Manage Rules, or convert to an Excel Table
Multiple rules conflict	Rule order is incorrect	Reorder rules in Manage Rules so the priority rule is at the top
Formula evaluates incorrectly	Row number in formula does not match first row of selection	Ensure the row number in the formula matches the first row of the Applies to range

Copying Formatting

To apply existing conditional formatting to a new range:

1. Select a cell with the formatting you want to copy.
2. Click Format Painter on the Home tab.
3. Drag across the target range.

This copies the conditional formatting rules along with their applies-to ranges adjusted for the new location.

Related Articles

• XLOOKUP with Multiple Criteria in Excel
• Excel Pivot Table Tutorial for Beginners
• SharePoint Document Library Best Practices

Frequently Asked Questions

How many conditional formatting rules can I have in Excel?

Excel supports a very large number of conditional formatting rules -- the practical limit is determined by workbook size and performance rather than a hard cap. However, having more than 20-30 rules on a single sheet can cause noticeable slowdowns during recalculation.

Can conditional formatting change font color and cell fill color independently?

Yes. When you click Custom Format in any rule dialog, you can set independent formatting for Font (color, style, size), Fill (background color and pattern), and Border. You can also change number formatting within a conditional rule.

Does conditional formatting work on Excel for the web and mobile?

Excel for the web displays existing conditional formatting correctly. However, creating or editing complex rules (especially custom formulas) has limited functionality compared to the desktop application. Excel for iOS and Android displays formatting but has minimal rule creation capabilities.

Can I use VLOOKUP or other functions inside conditional formatting formulas?

Yes. Any function that returns TRUE or FALSE can be used in a conditional formatting formula. For example: =VLOOKUP(A2, LookupTable, 2, FALSE)="Active" highlights rows where the lookup result equals "Active." Just ensure the function is compatible with your Excel version.

How do I remove all conditional formatting from a worksheet?

Go to Conditional Formatting > Clear Rules and choose either Clear Rules from Selected Cells (to remove formatting from a specific range) or Clear Rules from Entire Sheet (to remove all rules at once). This is useful when you want to start fresh or share a clean version of your data.