Conditional Formatting in Excel: How to Apply Rules and Custom Formats

What Is Conditional Formatting in Excel?

Every spreadsheet eventually reaches the point where the raw numbers alone aren't enough — you need visual cues to make sense of what you're looking at. That's where conditional formatting comes in. Instead of manually scanning columns of figures or highlighting cells by hand, you set rules and let Excel handle the formatting automatically. Change a number, and the cell's appearance instantly reflects whether it's above a target, below average, or a duplicate. It's the difference between a wall of text and data that tells you something at a glance.

Conditional formatting in Excel automatically changes the appearance of cells based on their values — highlighting numbers above a threshold, flagging duplicates, applying colour scales to show relative magnitude, or adding data bars that visually represent the size of each value. It's one of Excel's most practical features for making data readable at a glance, and it works without changing the underlying data. The formatting updates dynamically: if a cell's value changes to meet (or stop meeting) the condition, the formatting adjusts immediately.

You've probably used conditional formatting without thinking about it formally. Any time you want to draw attention to specific values in a spreadsheet — overdue invoices, failing test scores, cells that exceed a budget, duplicate entries in a list — conditional formatting is the tool. It replaces the tedious process of manually scanning rows of numbers and applying colour by hand, which doesn't scale and goes stale the moment data changes.

Excel provides several categories of conditional formatting rules. The built-in options — Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets — handle the most common scenarios without any formula knowledge. For situations where built-in rules don't fit, you can write custom rules using Excel formulas, which opens up virtually unlimited formatting possibilities. You can format cells based on the value of another cell, based on whether a date falls within a range, based on the combination of multiple conditions, or based on any logical expression you can express in a formula.

This guide covers all the built-in conditional formatting options, walks through creating custom formula-based rules, explains how to manage and troubleshoot rules, and addresses the common mistakes that trip up Excel users when working with conditional formatting for the first time.

Built-in Conditional Formatting Rules Explained

Excel's built-in conditional formatting rules handle the majority of everyday formatting needs without requiring any formula knowledge. They're fast to apply and easy to understand.

Highlight Cells Rules are the most frequently used category. 'Greater Than' highlights cells with values above a threshold — useful for flagging values that exceed a budget, a deadline, or a performance target. 'Less Than' does the opposite. 'Between' highlights cells within a specified range, which works well for acceptable-range indicators. 'Equal To' highlights exact matches.

'Text That Contains' highlights cells containing a specific word or phrase, regardless of what else is in the cell — handy for scanning text columns for specific entries like status labels or category names.

'Duplicate Values' instantly highlights any value that appears more than once in the selected range, which is one of the fastest ways to identify duplicates in a list. A 'Date Occurring' rule highlights dates falling in predefined periods — yesterday, today, tomorrow, last week, this month, and other common ranges — without needing to write any date formula. This is useful in project tracking and scheduling tools where you want visual indicators for recent or upcoming dates without any manual intervention or formula work.

Top/Bottom Rules highlight cells based on their relative position in the dataset rather than a fixed threshold. 'Top 10 Items' highlights the 10 highest values (you can change the number). 'Top 10%' highlights the top 10 percent of values. 'Bottom 10 Items' and 'Bottom 10%' work in the opposite direction. 'Above Average' and 'Below Average' highlight cells that fall above or below the mathematical average of the selected range. These are particularly useful in performance comparisons where you don't know the exact threshold in advance — you want to see what's above or below the group average.

Data Bars add a horizontal bar inside each cell proportional to the cell's value relative to the range. The highest value gets the longest bar, the lowest gets the shortest. This creates a built-in histogram effect that makes it easy to compare values visually across rows. You can choose solid or gradient fill, and you can customise the bar colour and direction. Data bars are effective for financial data, scores, and any column where relative magnitude matters more than absolute values.

Color Scales apply a gradient across the range — typically green for the highest values, red for the lowest, and yellow in the middle (though you can customize the colours). This creates a heat map effect where patterns in the data become immediately visible. Color scales work best with large datasets where individual values would take too long to scan manually. They're widely used in financial reports, scientific data analysis, and project management dashboards.

Icon Sets display small icons (arrows, traffic lights, flags, stars, or rating bars) inside cells based on value thresholds. A green upward arrow might indicate values in the top third, yellow sideways arrows for the middle, and red downward arrows for the bottom. Icon sets are useful for dashboards and summary reports where you want to communicate status at a glance without the reader needing to interpret specific numbers.

Practical Examples of Conditional Formatting

Understanding how conditional formatting works in real scenarios makes it much easier to apply. Here are several examples that cover the most common use cases.

For a sales performance dashboard, apply a colour scale to a column of monthly sales figures. Select the sales column, go to Conditional Formatting → Color Scales, and choose the Green-Yellow-Red scale. The highest sales figures turn green, the lowest turn red, and middle values shade into yellow. At a glance, you can see which months performed best and which underperformed without reading individual numbers.

For an expense tracking spreadsheet, highlight any expense over £500 in red. Select the expense column, go to Conditional Formatting → Highlight Cells Rules → Greater Than, enter 500, and choose Red Fill. Every expense exceeding the threshold is immediately flagged. You can add a second rule for expenses over £1,000 using a darker red, giving you two tiers of visual priority.

For a project tracker with deadlines, highlight overdue tasks. Select the deadline column, go to Conditional Formatting → New Rule → Use a formula, and enter =A1=TODAY(),A1<=TODAY()+3) with an orange fill to create an early warning tier.

For duplicate detection in a customer list, select the column containing customer names or IDs. Go to Conditional Formatting → Highlight Cells Rules → Duplicate Values. Excel instantly highlights every value that appears more than once. This is one of the fastest ways to find duplicates in a dataset — far quicker than manually scanning or writing a COUNTIF formula, and the formatting updates in real time as you add or modify entries.

For grade tracking or performance reviews, combining a color scale with icon sets creates a powerful visual summary. Apply a green-red color scale to the scores column to show relative performance across the group, then add an icon set rule that displays a checkmark for passing grades and an X for failing grades. The two layers of formatting — subtle colour for relative context and clear icons for categorical status — give readers both comparative and absolute information simultaneously without requiring separate columns or charts.

Advanced Conditional Formatting Techniques

Beyond the standard built-in rules, several advanced techniques unlock more sophisticated formatting scenarios.

Formatting an entire row based on a single cell's value is one of the most requested advanced techniques. To highlight an entire row when column D contains the word 'Complete,' select the full data range (not just column D), create a new formula-based rule with =$D1='Complete', and set the format. The $ before D locks the column, so Excel always checks column D regardless of which column in the row is being evaluated. The row reference (1, in this case) is relative, so it adjusts for each row.

Alternating row colours (zebra striping) can be done through conditional formatting instead of Table formatting. Select your data range, create a new formula-based rule with =MOD(ROW(),2)=0, and set the fill colour. This formula uses MOD to check whether the row number is even — even rows get the formatting, creating alternating stripes. This approach is useful when you don't want to convert your range to a Table but still want the visual benefit of alternating colours.

Cascading rules — multiple rules on the same range that create different formatting tiers — are powerful for creating dashboards. For example, you might apply three rules to a performance column: green fill for values above 90, yellow for values between 70 and 90, and red for values below 70. Create all three rules, then arrange them in Manage Rules so the most specific rule (above 90) is evaluated first with 'Stop If True' checked, preventing the broader rules from overriding it.

Cross-sheet conditional formatting is possible but limited. You can reference cells on other sheets in your formula-based rules (e.g. =A1>Sheet2!$B$1), but the Manage Rules dialog only shows rules for the active sheet. This technique works for comparing values against a reference table on another sheet — for instance, highlighting any price that exceeds the maximum price listed on a reference sheet — but it's not supported for Data Bars, Color Scales, or Icon Sets, which only evaluate values within their own range.

Performance Considerations for Large Workbooks

Conditional formatting is evaluated on every worksheet recalculation — every time you enter data, press F9, or open the workbook. For small datasets (a few hundred rows with a handful of rules), this is imperceptible. But for large workbooks with thousands of rows and many conditional formatting rules, the cumulative evaluation time can noticeably slow down the spreadsheet.

The most common performance issue comes from rules applied to entire columns (A:A) rather than specific data ranges (A2:A500). An entire-column rule evaluates over a million cells on every recalculation, even though most are empty. Always scope your conditional formatting to the actual data range — use the last row of your data as the end of the range rather than leaving it open-ended.

Another performance consideration is the number of distinct rules. Ten different conditional formatting rules on the same range means ten separate evaluations per cell per recalculation. If the range has 5,000 rows, that's 50,000 evaluations — still usually fast, but in workbooks with multiple sheets all formatted heavily, this adds up. Consolidating rules where possible (using a single formula with OR logic instead of two separate rules) reduces evaluation overhead.

Formula-based rules that reference volatile functions — like TODAY(), NOW(), INDIRECT(), or OFFSET() — force recalculation even when no data has changed, because Excel can't tell whether the result would be different without recalculating. This is usually fine for one or two rules but becomes a problem when combined with large ranges. If performance becomes an issue, consider replacing the conditional formatting with VBA macro-based formatting that only runs on command rather than on every recalculation.

One underused optimisation is using named ranges as your conditional formatting target. If your data grows or shrinks regularly, a dynamic named range (using OFFSET or a Table reference) ensures the conditional formatting only applies to cells that actually contain data. This is more efficient than applying rules to a fixed large range that includes many empty rows. Combined with avoiding volatile functions in your rule formulas, scoping rules to dynamic named ranges is one of the most effective ways to keep a heavily formatted workbook responsive.

Removing and Resetting Conditional Formatting

Clearing conditional formatting is straightforward: go to Home → Conditional Formatting → Clear Rules. You can choose to clear rules from the selected cells only or from the entire sheet. Clearing rules removes the conditional formatting but leaves all other cell formatting (manual colours, borders, number formats) intact. This is important to understand — clearing conditional formatting doesn't reset the cell to its completely unformatted state; it only removes the rules-based formatting layer.

If you need to reset a range to completely unformatted state — removing both conditional formatting and any manual formatting — select the range, go to Home → Clear (in the Editing group) → Clear Formats. This removes everything: conditional formatting, cell colours, fonts, borders, and number formats. It doesn't delete the data in the cells, only the formatting. This is useful when you've inherited a spreadsheet with heavy formatting that you want to strip back to a clean state for your own analysis or restructuring. The data itself remains completely untouched.

For targeted rule removal, Manage Rules gives you granular control. Open the Manage Rules dialog (Home → Conditional Formatting → Manage Rules), select the specific rule you want to remove, and click Delete Rule. This preserves all other rules and all manual formatting — you're removing only the specific conditional formatting rule you selected. This is the best approach when you want to adjust your formatting by removing one rule while keeping others active.