How to Use Conditional Formatting in Excel: Rules & Tips 2026

Open a spreadsheet, scan a thousand rows of sales numbers, and try to spot the underperformers. Good luck. Your eyes glaze in seconds and the outliers blend into the average. This is what conditional formatting was built to fix — Excel paints cells based on rules you set, so the data tells you the story before you even start looking.

The core idea is simple. You hand Excel a condition (greater than 100, contains "Late", duplicates, top 10%, today's date) and a format (red fill, green text, an arrow icon, a data bar). Cells that match the condition get the format automatically. Change the data, the colors update on the fly. It's the difference between staring at numbers and actually reading them.

This guide covers the whole feature end to end — the Home > Conditional Formatting menu and every option underneath it, the five most-used built-in rule types, formula-based rules that color an entire row based on one cell's value, the Manage Rules panel where rules pile up and clash, performance with big datasets, how to copy formats to new ranges, and what to do when you need to clear everything and start over. By the time you're done, conditional formatting will be muscle memory.

Conditional formatting lives under Home > Conditional Formatting on the ribbon. Click the button and a dropdown opens with seven sections. The first five are rule types — Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. The bottom two are management — New Rule for fully custom formulas, Clear Rules for wiping the slate, and Manage Rules for the panel that shows every active rule in the workbook.

The workflow is always the same. Select the range you want to format. Open the Conditional Formatting menu. Pick a rule type. Excel opens a dialog or submenu where you set the threshold or condition and the format. Click OK. The cells in your selection that match the rule pick up the format immediately. Cells that don't match stay untouched.

One thing worth knowing upfront — Excel applies conditional formatting cell by cell. Each cell is tested against the rule independently. That means a single rule applied to a 10-column range tests every one of those cells, returning a TRUE or FALSE per cell. The visible cells are the ones where the rule returned TRUE. Important when you build formula-based rules later, because the formula has to evaluate correctly for every cell in the range, not just the active one.

Rules persist with the workbook. Save the file, close it, reopen — the formatting comes right back. Send the file to someone else, they see the same rules. Useful when you build a template; the conditional formatting travels with it and applies to whatever data lands in those cells.

Highlight Cells Rules is the section you'll use most. Open it from the Conditional Formatting menu — Excel shows seven options: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, and Duplicate Values. Each is a one-click rule for the most common conditions.

Greater Than is the bread and butter. Select a column of sales numbers. Pick Conditional Formatting > Highlight Cells Rules > Greater Than. A dialog opens with a value field and a format dropdown. Type 5000 in the value field. Pick "Light Red Fill with Dark Red Text" or any of the preset formats. Click OK. Every cell with a value above 5000 turns red. Less than, between, equal to — same flow, different threshold.

Text that Contains is brilliant for status columns. You've got a Status column with values like "Paid", "Pending", "Late", "Cancelled". Select the column. Conditional Formatting > Highlight Cells Rules > Text that Contains. Type "Late" in the value field, pick a red format, click OK. Every "Late" cell goes red. Repeat with "Pending" in yellow and "Paid" in green. You've built a status dashboard in 60 seconds.

A Date Occurring is the niche power user move. Excel knows what "today" and "yesterday" mean, plus "in the last 7 days", "next month", and similar relative ranges. Select your date column. Conditional Formatting > Highlight Cells Rules > A Date Occurring. Pick "Today" from the dropdown, set a format, OK. Today's date turns yellow. Tomorrow when you open the file, yesterday's date stays plain and the new today highlights itself. Dynamic without any formulas.

Duplicate Values lives at the bottom of the same menu. Useful for catching repeated entries in a customer list or invoice numbers that should be unique. Select the column. Conditional Formatting > Highlight Cells Rules > Duplicate Values. The dialog gives you a choice — highlight Duplicate or Unique values. Pick Duplicate, pick a format, click OK. Every cell that appears more than once in the range gets highlighted. The dialog also catches the unique-only version if you flip the dropdown.

Top/Bottom Rules is the second section, and it answers the question "where are my best and worst performers?" in one click. The submenu has six options — Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average.

Select a column of numbers. Conditional Formatting > Top/Bottom Rules > Top 10 Items. A dialog opens with a spinner — you can change 10 to any number — and a format dropdown. Pick a green format, click OK. The ten highest values in the range turn green. Bottom 10 Items works the same in reverse. Top 10% and Bottom 10% switch from count to percentage of the range size — useful when range sizes change over time.

Above Average and Below Average are sneakier than they look. They calculate the average of the selected range and highlight every cell above (or below) that average. Useful for a quick "who's pulling their weight?" view across a sales team or a class of test scores. The threshold updates automatically when data changes — add a new row, the average shifts, the highlighting adjusts.

One thing to watch — these rules treat blanks and text as ignored. If your column has empty rows or text values mixed in, Top 10 looks only at the numeric cells. Useful behavior most of the time, occasionally a gotcha when you expected blanks to be treated as zero.

Color scales and data bars are visual rules — they don't just paint cells one color, they create gradients or bars proportional to the values. Best for at-a-glance dashboards where you want a heat map without doing the work.

Data Bars adds a tiny horizontal bar inside each cell. Bar length is proportional to the value. Highest value in the range gets a full-width bar, lowest gets a tiny one (or nothing if you turn on the gradient option). Select the range. Conditional Formatting > Data Bars. Pick a color from the gallery — gradient fills with a fade, solid fills without. The bars appear instantly, sized to fit each cell. Negative numbers get bars on the opposite side of the axis.

Color Scales applies a gradient across the range. Lowest values get one color, highest get another, middle values get an interpolated blend. The standard is green-yellow-red — green for high, red for low, yellow in the middle. Excel calculates the gradient automatically; you don't pick thresholds. Conditional Formatting > Color Scales — pick a preset. Two-color and three-color options. Reversed presets put red on top if higher numbers mean worse (like late payments or error counts).

For more control over color scales, open the Manage Rules panel after applying one, double-click the rule, and switch the type values from Lowest/Highest to Number or Percentile. Set explicit thresholds — like "anything above 90 is green, below 30 is red, 60 is the midpoint". The gradient then anchors to your absolute values, not the range's own min/max.

Icon Sets is the third visual rule type. Each cell gets an icon — traffic light, arrow, flag, star, check — based on which bucket the value falls into. The default is three buckets, with thresholds at 33% and 67% of the range. Select a column. Conditional Formatting > Icon Sets. Pick the icon group you want. The icons appear left of the number in each cell. Customize the thresholds via Manage Rules > double-click the rule > switch from Percent to Number and set the boundaries explicitly.

The built-in rules cover the obvious cases. The real power is in formula-based rules — where you write your own condition and Excel highlights any cell where the formula returns TRUE. This unlocks "color the entire row based on the status column" and similar moves that the built-in rules can't do.

Open Conditional Formatting > New Rule. The New Formatting Rule dialog appears with six rule types listed. Pick the last one — "Use a formula to determine which cells to format". The dialog changes to show a formula field and a format button.

Here's the trick — the formula has to be entered as if you were writing it for the first cell of the range, with the right mix of absolute and relative references so it scrolls correctly. Say you've got data in A2:E100 with a Status column in B. You want every row where B says "Late" highlighted red.

Select A2:E100. New Rule. Pick the formula option. In the formula field, type =$B2="Late". The dollar sign locks the B column (always check column B) but leaves the row number relative (so it advances row by row). Set the format to red fill. Click OK. Every row with "Late" in column B turns red.

Switch the absolute/relative logic depending on what you want. =$B2="Late" colors the whole row. =B2="Late" colors only column B's cells. =B$2="Late" colors all cells where row 2 of their column has "Late" — useful for highlighting columns instead of rows. The dollar signs control direction.

Formula rules also accept full Excel logic. =AND($B2="Late",$D2>1000) colors rows where the status is Late and the amount is over 1000. =OR($B2="Late",$B2="Overdue") colors rows matching either status. =NOT(ISBLANK($B2)) colors any row where the status column isn't empty. The full function library is available — IF, AND, OR, SUMIF, COUNTIF, MATCH, VLOOKUP, anything.

A common formula-rule pattern — highlight dates that are overdue. =AND($D2<TODAY(),$E2<>"Paid") colors any row where the due date in column D has passed and the payment status in E isn't "Paid". TODAY() recalculates daily, so the highlighting updates on its own. Open the file tomorrow, the overdue list updates without you touching anything.

Once you've built a few rules, the Manage Rules panel becomes your control center. Open it from Home > Conditional Formatting > Manage Rules. A dialog appears listing every active rule, with columns for rule type, format preview, the range it applies to, and a "Stop If True" checkbox.

The "Show formatting rules for" dropdown at the top filters the view. Default is "Current Selection", which shows only rules touching the cells you've got highlighted. Switch to "This Worksheet" to see every rule on the active sheet, or "This Workbook" for the whole file. Useful when you've inherited a file and want to know what's already styled.

The rules in the list have priority. The one at the top runs first, the one below runs next. When two rules format the same cell with conflicting formats — one says red fill, another says green fill — the higher-priority rule wins for the conflicting property. Reorder rules with the up and down arrows at the top of the dialog to fix priority conflicts.

Stop If True is the kill switch. Check the box on a rule, and any rule below it doesn't run for the cells that rule matched. Useful for cascading logic — if a cell is blank, stop; otherwise, apply the rest of the rules. Without Stop If True, every rule evaluates for every cell, which can get expensive on large datasets.

To edit a rule, double-click it. The original rule dialog opens with all settings preserved. Tweak the threshold, change the format, adjust the range, click OK. To delete, select the rule and click the Delete Rule button. To duplicate, click Duplicate Rule — handy when you want a near-copy with a different threshold.

The Applies To field next to each rule is the range the rule covers. Click in that field and you can edit it directly — type a new range like =$A$2:$E$500, or click the little grid icon at the right end of the field to select a new range with the mouse. Useful when your data grew past the original range and the new rows aren't styled.

Excel sometimes splits rules unexpectedly. If you copy formatted cells and paste them somewhere else, the same rule might appear twice in the Manage Rules list — once for the original range, once for the pasted range. Consolidate by editing the Applies To of one rule to cover both ranges (using a comma to separate), then delete the duplicate. Or wrap both ranges into a single Applies To: =$A$2:$E$100,$A$200:$E$300.

Copy-pasting cells with conditional formatting is the most common way to multiply rules. Each paste can create a new rule entry. After a few rounds of copy-paste, the Manage Rules dialog has 15 rules where you expected 3. Clean them up periodically — consolidate Applies To ranges, delete duplicates, and your file stays manageable.

To copy formatting from one range to another without rebuilding the rule, use the Format Painter. Select a cell or range that already has conditional formatting applied. Click Format Painter on the Home tab (the paintbrush icon). The cursor changes to a paintbrush with a plus sign. Click and drag over the destination range. The conditional formatting copies, and Excel automatically updates the Applies To field on the existing rule to include the new range.

Format Painter is faster than rebuilding rules. Double-click the Format Painter button to lock it on — you can paint multiple non-contiguous ranges before pressing Escape to release the tool. Useful when you've got a complex set of rules on one column and want them applied to three other columns.

The alternative is Paste Special > Formats. Copy the source range with Ctrl+C, select the destination, right-click and pick Paste Special > Formats (or Paste Special dialog > Formats radio button). The formatting copies, including conditional formatting rules, without affecting values or other formatting properties. Cleaner than Format Painter when you don't want to copy borders, fonts, or other non-conditional styles.

Note — these methods copy the rules but reference the original Applies To range plus the new one. If you want the new range to have an independent copy of the rule, build it from scratch on the new range. Otherwise editing the rule changes the behavior everywhere it's applied.

Conditional formatting is fast for hundreds of cells, sluggish for tens of thousands, and can hang Excel for millions. Three reasons. First, every rule re-evaluates whenever the worksheet recalculates — and formula-based rules count as volatile, so they trigger on any change. Second, complex rules like Color Scales and Icon Sets sort the range to compute thresholds, which scales poorly. Third, when rules overlap on the same cells, Excel evaluates each one in priority order until one matches.

For datasets over 50,000 rows, follow a few rules. Prefer simple Highlight Cells rules over Color Scales and Icon Sets — single-condition rules are O(1) per cell, gradient and ranking rules are O(n log n) over the range. Use specific Applies To ranges instead of whole columns ($A:$A formats all 1 million-plus rows; $A$2:$A$10000 formats only what's needed). Avoid TODAY() and NOW() in formula rules unless necessary — they're volatile and force recalculation on every change.

If a file is sluggish, open Manage Rules and look for rules with massive Applies To ranges. Trim them down to actual data ranges. Look for duplicate rules from copy-paste and consolidate. Look for Color Scales applied to many thousands of rows — consider switching to a simpler Highlight Cells rule.

Stop If True helps with performance too. Order your rules so the most common case is on top with Stop If True checked. Subsequent rules don't run for cells the first rule matched, cutting evaluation in half or better for typical datasets.

To clear rules, use the Clear Rules submenu under Conditional Formatting. Four options — Clear Rules from Selected Cells, Clear Rules from Entire Sheet, Clear Rules from This Table, and Clear Rules from This PivotTable. Each one wipes rules from its respective scope.

The most common use is Clear Rules from Selected Cells. Select the range you want to clear, Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Every conditional formatting rule covering any cell in that selection is removed. The cells revert to default formatting (whatever fixed format you've manually applied still stays — only the conditional layer goes).

Clear Rules from Entire Sheet is the nuclear option. Wipes every rule on the active worksheet. Useful when you've inherited a file with rules you don't understand and want to start fresh. Saves time over deleting each rule individually in Manage Rules.

Manage Rules also has a Delete Rule button for surgical removal. Open Manage Rules, switch the dropdown to This Worksheet or This Workbook, select the rule you want gone, click Delete Rule. Useful when you want to keep most rules but kill one specific entry.

Cleared rules don't come back when you save and reopen. They're truly gone. If you want to preserve a rule for later, document it elsewhere (a Notes column, a separate sheet) before clearing, or screenshot the Manage Rules dialog. Otherwise you'll be rebuilding from memory.

A custom date rule shows the formula-rule power. Built-in date rules cover today, yesterday, last week — but not "due in the next 14 days but more than 7 days out" or "anything dated this fiscal quarter". For those, write a formula rule.

Select your date range. New Rule > "Use a formula to determine which cells to format". For "between 7 and 14 days from today", use =AND($A2>=TODAY()+7,$A2<=TODAY()+14). Set a format. OK. The dates in the target window highlight; everything else stays plain.

For fiscal quarter logic — say your fiscal year starts in April — use =AND(MONTH($A2)>=4,MONTH($A2)<=6) for Q1 of a April-start fiscal year. Or for the current fiscal quarter dynamically, combine TODAY() and INT((MONTH(TODAY())-1)/3): the math gets fancier but the formula rule handles it without breaking a sweat.

For "highlight weekends in a date column", =WEEKDAY($A2,2)>5. WEEKDAY's second argument set to 2 returns 1 for Monday through 7 for Sunday — values above 5 are Saturday and Sunday. One formula, dynamic weekend highlighting forever.

One real-world example to tie everything together. You're tracking 200 client invoices in a spreadsheet. Columns A-G hold invoice number, client name, issue date, due date, amount, status, days late. You want a single view that shows everything at a glance.

Start with a formula rule on the entire data range A2:G201. Open Conditional Formatting > New Rule > "Use a formula to determine which cells to format". Formula: =$F2="Paid". Set a light green fill. OK. Every row marked Paid turns green. Add another formula rule: =AND($F2<>"Paid",$D2<TODAY()). Set a red fill. OK. Every overdue, unpaid row turns red. Add a third: =AND($F2<>"Paid",$D2>=TODAY(),$D2<=TODAY()+7). Set yellow fill. Anything due in the next week, not yet paid, goes yellow.

Now layer in data bars on the Amount column. Select F2:F201. Conditional Formatting > Data Bars. Pick gradient blue. The bar length shows each invoice's relative size at a glance.

Add a Top 10 Items rule on the amount column too. Conditional Formatting > Top/Bottom Rules > Top 10 Items. Set a thicker border or a darker text color. The biggest ten invoices stand out, even within the green/yellow/red row scheme.

Open Manage Rules. You've got four rules layered. Use the up/down arrows to set priority — the Paid rule first (so paid invoices stay green even if they're overdue technically), the overdue rule next, the due-soon rule after, the data bars and Top 10 last. Tick Stop If True on the Paid rule, so subsequent rules skip paid invoices entirely. Save the file. You've just built a live invoice dashboard from a flat spreadsheet, no add-ins required.