Conditional formatting changes the look of a cell, such as its fill color, font color or icon, when the value meets a condition. It lets you spot problems and patterns in seconds: sales below target in red, overdue dates in orange, duplicates in yellow, the top ten values in green. Because the formatting follows the data, it updates when the numbers change, so you never have to color cells by hand again. This guide shows the built-in rules, data bars, color scales and icon sets, and then the most powerful feature, formula-based rules that can format whole rows. A small sales list with numbers you can check by hand is used for the examples.
Updated November 2026
In this guide
- What conditional formatting does
- Example data
- Highlight cells greater than a value
- Highlight duplicates
- Top and bottom rules
- Data bars
- Color scales
- Icon sets
- Formula-based rules
- Highlight overdue dates
- Highlight alternate rows and weekends
- Manage, edit and clear rules
- Common mistakes
- Practice problems
- Quick recap
What conditional formatting does
A normal format applies to a cell whatever it contains. A conditional format applies only when a rule is true. You select the cells, create a rule and choose the format. Excel checks the rule every time the sheet recalculates. Rules are stored with the sheet and can be copied, changed or removed. You find them on the Home tab, in the Styles group, under Conditional Formatting. A cell can have several rules, and the order of the rules decides which one wins when they conflict.
Example data
Use a list of eight sales orders in A1:D9 with the headers Order, Date, Amount and Status. The amounts are 1,200, 450, 980, 1,500, 300, 1,050, 760 and 1,200. The total is 7,440 and the average is 930. The Status column has Paid, Open or Overdue. These numbers will help you check which cells the rules should highlight. Two amounts, 1,200 and 1,200, are equal, which lets us test the duplicate rule too.
Highlight cells greater than a value
Select the Amount cells, go to Home, Conditional Formatting, Highlight Cells Rules, Greater Than. Type 1000 and choose a format, such as Light Red Fill with Dark Red Text, then click OK. The cells with 1,200, 1,500, 1,050 and 1,200 are highlighted, which is four cells. Change 1000 to another number, and the highlight changes at once. The same menu has Less Than, Between, Equal To, Text that Contains, A Date Occurring and Duplicate Values. Use the one that matches your question.
Highlight duplicates
Select the Amount cells and choose Highlight Cells Rules, Duplicate Values. Choose a format and click OK. Both cells with 1,200 are highlighted, since each one is a duplicate of the other. You can switch the first dropdown from Duplicate to Unique to highlight values that appear only once. This is a quick way to spot repeated invoice numbers or customer IDs. If you want to delete the duplicates afterward, our remove duplicates guide shows how to do it safely.
Top and bottom rules
Select the Amount cells and go to Conditional Formatting, Top/Bottom Rules. Choose Top 10 Items, change 10 to 3 and click OK. The three highest amounts, 1,500, 1,200 and 1,200, are highlighted. Choose Bottom 10 Items for the lowest, which would be 300, 450 and 760 for the bottom three. Above Average highlights the values above the average of 930, which are 1,200, 980, 1,500, 1,050 and 1,200, so five cells. Percent options such as Top 10 Percent work in the same way. These rules adapt when new data arrives.
Data bars
Data bars draw a bar in each cell, with a length that is proportional to the value. Select the Amount cells, choose Data Bars and pick a gradient or solid fill. The cell with 1,500 gets the longest bar and the cell with 300 gets the shortest. Data bars give a quick visual comparison without a chart. To control them, open Manage Rules, Edit Rule and set the minimum and maximum, or show the bar only, with the number hidden. Use one color to keep the sheet clean.
Color scales
A color scale shades each cell according to its value, from a low color to a high color. A common choice is a red to green scale, with red for the lowest and green for the highest. Select the cells and choose Color Scales, then a two-color or three-color scale. In a three-color scale, the middle color is applied to the midpoint, which is 50th percentile by default. Color scales are popular for heat maps, such as sales by region and month. Avoid red and green together if some readers are color-blind, and use blue and orange instead.
Icon sets
Icon sets add small symbols, such as arrows, flags or traffic lights, to cells. Choose Icon Sets and pick, for example, three traffic lights. By default, Excel splits the values into thirds: the top third gets green, the middle third yellow and the bottom third red. You can change the thresholds in Manage Rules, Edit Rule. For example, set green for 1,000 or more, yellow for 500 or more and red below that. With our data, 4 values are green, 2 are yellow (980 and 760) and 2 are red (450 and 300), so check that your icons match.
Formula-based rules
The most flexible option is a formula. Select the cells, go to Conditional Formatting, New Rule, and choose Use a formula to determine which cells to format. The formula must return TRUE or FALSE, and it is written for the first selected cell. To highlight a whole row when the amount is above 1,000, select A2:D9 and use =$C2>1000. The dollar sign before C locks the column, so every cell in the row looks at the amount in column C. Without it, the rule would shift as it moves across the columns.
Highlight overdue dates
To flag overdue orders, select the rows and use a formula that compares the date with today: =AND($B2<TODAY(),$D2<>"Paid"). It is true when the date has passed and the status is not Paid. Choose an orange fill. If an order dated yesterday has the status Open, the row is highlighted. If the same order is Paid, it is not. Because TODAY updates each day, the highlight appears by itself when a deadline passes. Our DATEDIF guide helps when you also want the number of days overdue.
Highlight alternate rows and weekends
For banded rows, use =MOD(ROW(),2)=0, which is true for every even row number. For weekends in a date column, use =WEEKDAY($B2,2)>5, since Saturday is 6 and Sunday is 7 with the second argument set to 2. These patterns are useful in schedules, trackers and attendance sheets, such as the one in our attendance sheet guide. If you use a table, banded rows are built in, so you do not need a rule.
Manage, edit and clear rules
Open Conditional Formatting, Manage Rules to see all rules. Use the drop-down at the top to show the rules for the current selection or for the whole sheet. Select a rule and click Edit Rule to change it, or Delete Rule to remove it. Use the arrows to change the order, and tick Stop If True when a rule should block the ones below. To remove all rules from a selection, choose Clear Rules, Clear Rules from Selected Cells. Check that the Applies To range is correct, since a wrong range is the most common problem.
Common mistakes
The most common mistake is to write a formula rule with the wrong reference style. If you type =C2>1000 without dollar signs for a row rule, only the first column works as expected. Another is to copy and paste cells many times, which splits one rule into many small ones and slows the file. A third is to use too many colors. A fourth is to forget that a rule refers to the first cell of the selection. A fifth is to apply a rule to a whole column, which can slow down large files. Keep the ranges as small as the data.
Practice problems
Try these on the example data. Problem 1: highlight the amounts above 1,000. Four cells should be highlighted. Problem 2: highlight the duplicate amounts, which should be the two 1,200 cells. Problem 3: use Top 3 and check that you get 1,500, 1,200 and 1,200. Problem 4: add data bars and see that 1,500 has the longest bar. Problem 5: write a formula rule that highlights the whole row when the status is Overdue, using =$D2="Overdue".
Quick recap
Select the cells, open Home, Conditional Formatting and choose a rule. Use Highlight Cells Rules for simple tests, Top and Bottom Rules for rankings, Data Bars, Color Scales and Icon Sets for visual comparison, and a formula rule for whole rows and dates. Lock the column with a dollar sign when the rule should look at one column. Manage and clear rules in Manage Rules. Keep rules few and simple, and your sheet will show what matters at a glance.
Frequently asked questions
What is conditional formatting in Excel?
It changes the format of cells automatically when they meet a condition.
How do I highlight a whole row based on a cell?
Use a formula rule with a locked column, such as =$C2>1000.
How do I remove conditional formatting?
Choose Clear Rules from the Conditional Formatting menu.
Can I use conditional formatting with dates?
Yes, use a rule such as =$B2
Why is my rule not working?
Check the formula references and the Applies To range.


