Excel Conditional Formatting: How to Set It Up

To add conditional formatting in Excel for Microsoft 365 on Windows, select the cells you want to format, choose Home > Conditional Formatting, pick a rule, enter its criteria, and choose a format. Excel then changes the appearance of cells automatically when their values meet the rule. You can use built-in rules for common tasks or create a formula rule for more control.

Conditional formatting helps important values stand out, such as overdue dates, high sales, duplicates, or low stock. It changes how cells look; it does not change their values or replace the data. Before you begin, make sure you know which cells the rule should cover and what condition should trigger it. It is also useful to have a small sample of your data available so you can check the result.

What You’ll Need

A workbook open in Excel for Microsoft 365 on Windows. The main steps also work in recent desktop versions of Excel.
A selected cell range or a structured table column to format. Include the data rows you want the rule to evaluate.
A clear condition, such as “greater than 100,” “contains the word Late,” or “date is before today.”
For a formula-based rule, a working formula and a clear understanding of which row is the first row of your selected range.

Step-by-Step

Select the cells to format. Click and drag across the target range, or click its first cell, hold Shift, and click its last cell. For example, select B2:B50 to format values in that column, rather than selecting the entire worksheet. If you are working in an Excel table, select the relevant cells in the table column. Check that the header is not included unless you want it formatted too.
Open the conditional-formatting menu. On the ribbon, choose Home > Conditional Formatting. The menu offers built-in rules, color scales, data bars, icon sets, and options for managing or creating rules. On Excel for Mac, use Home > Conditional Formatting; the controls may be arranged differently depending on the version. Excel for the web also offers conditional formatting, but some advanced rule controls can differ from the Windows desktop app.
Choose a built-in rule for a straightforward condition. For example, choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than to highlight numbers above a threshold. Enter the threshold, select a preset format from the list, and click OK. Other choices in Highlight Cells Rules include Less Than, Between, Equal To, Text that Contains, A Date Occurring, and Duplicate Values. The exact available choices depend on the rule type.
Set the condition and appearance. Enter a number, date, or text value that matches your goal. For instance, to make high values easy to scan, enter your chosen cutoff and select a light red fill or another readable style. To use a custom appearance, open the format list and choose Custom Format when that option is available. In the Format Cells dialog, choose a fill, font, border, or number format, then confirm with OK. Use strong contrast so the cell text remains readable.
Try a visual rule for a range of values. Select the numeric cells, then choose Home > Conditional Formatting > Data Bars, Color Scales, or Icon Sets and select a style. Data bars show relative size within the selected range; color scales apply colors according to values; icon sets add symbols such as arrows or traffic lights. These rules are useful for scanning patterns, but they may be less suitable when readers need exact thresholds. Check that the visual meaning is clear and not conveyed by color alone.
Use a formula when built-in rules are not specific enough. Select the range first, then choose Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter a formula that evaluates to TRUE or FALSE, choose Format, set the appearance, and click OK. With a selected range whose first data row is row 2, relative and absolute references determine how Excel checks each cell. For example, with sales amounts in B2:B50 and the rule applied to B2:B50, use =B2>=1000 to highlight values of at least 1,000. B2 is the first cell in the selected range, so Excel adjusts the row reference as it evaluates the other cells.
Use a formula to format a whole row based on one column, if needed. Suppose each record is in A2:D50 and column C contains the status. Select A2:D50, open New Rule, choose Use a formula to determine which cells to format, and enter =$C2="Late". Choose a format and confirm. The dollar sign fixes the status column at C, while the row number can change for each record. The rule highlights all four cells in a row when its status is Late. If your first data row is not row 2, change the row reference to match it.
Check the result and the range. Look at several cells that should match and several that should not. To review the rule, select a cell in its range and choose Home > Conditional Formatting > Manage Rules. In the Show formatting rules for list, choose Current Selection or This Worksheet to find the relevant rule. Check the Applies to range, the rule condition, and the format. Click Apply if you make a change, then OK.
Save the workbook. Save after checking the rules, especially if the formatting is important to a report or shared workbook. To remove a rule later, select the relevant cells and choose Home > Conditional Formatting > Clear Rules. Pick Clear Rules from Selected Cells to keep other worksheet rules, or Clear Rules from Entire Sheet only when you intend to remove all rules on that sheet.

Common Mistakes

Selecting the wrong range: A rule only affects its Applies to range. Selecting just one cell or leaving out new data rows can make the formatting seem incomplete. Check the range in Manage Rules.
Using the wrong reference in a formula: In a formula rule, a reference such as B2 usually shifts across the applied range. A reference such as $B$2 remains fixed. For row-based formatting, you commonly want to fix the column but let the row change, as in $C2. Match the starting row in the formula to the first row of the Applies to range.
Including a heading in the data range: A numeric rule may not treat a text heading as expected, and the heading can receive an unwanted format. Select data rows only unless there is a specific reason to include the header.
Choosing a threshold that does not fit the data: A Greater Than rule for 100 will not highlight values equal to 100. Decide whether the condition should be greater than or greater than or equal to, and test a value at the boundary.
Using color as the only signal: A subtle fill or a color distinction that is hard to see can obscure meaning. Use clear contrast and consider labels, icons, or another accessible way to communicate the status.
Adding several overlapping rules without checking order: Multiple rules can apply to the same cell, and their order or Stop If True setting can affect the result. Review the rules for that selection in Manage Rules rather than adding another rule blindly.

Tips

Start with a small, representative range and verify the rule before applying it to thousands of rows. This makes formula-reference mistakes easier to spot.
Use built-in rules for simple comparisons and formula rules only when you need a condition that depends on another cell, multiple conditions, or row-level logic.
For duplicate checks, select the cells to examine and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Confirm whether you want Excel to highlight duplicates or unique values.
When applying a rule to a table, check that new rows receive the intended formatting. Table behavior can be convenient, but verify the Applies to range after adding data, especially if rules were created before the table was expanded.
Use a descriptive rule condition and consistent colors across a workbook. For instance, reserve one fill for warnings and another for completed items, rather than assigning meanings inconsistently.
If you are sharing the workbook, tell readers what the colors or icons mean. Conditional formatting is a visual aid, not a substitute for a clear column heading or explanation of a business rule.

Troubleshooting

Nothing changes after I create the rule. Confirm that the selected cells contain values of the type the rule expects. A numeric comparison will not behave as intended if numbers are stored as text. Then check the condition, the Applies to range, and whether the chosen format is noticeable. In Manage Rules, select Current Selection or This Worksheet to locate the rule.

The wrong rows are highlighted by a formula rule. Check that the formula refers to the first row of the selected range and that the dollar signs are in the right places. For a rule applied to A2:D50 that checks status in C, the column should stay fixed while the row changes. If the range starts at row 3 but the formula still refers to row 2, Excel evaluates the wrong status row.

Some values are missed. Look for blanks, text-formatted numbers, unexpected spaces in text, or dates stored as text. Verify a value directly in the formula bar and check the rule’s boundary condition. For example, Greater Than excludes a value exactly equal to the threshold, while Greater Than or Equal To includes it.

A different format appears than the one I chose. More than one rule may apply to the same cells. Open Home > Conditional Formatting > Manage Rules, show rules for the worksheet if needed, and inspect their order and Stop If True settings. Adjust the rules or remove one that is no longer needed. Also check whether the cell has a manually applied fill that makes the conditional format difficult to distinguish.

The formatting disappears after clearing or copying cells. Clearing formats, pasting from another range, or changing the Applies to range can alter rules. Use Manage Rules to confirm the rule still exists and covers the cells you expect. If copying cells, check the destination before assuming the original rule was extended.

Frequently Asked Questions

Can I apply conditional formatting to an entire row?
Yes. Select the rows or range to format, create a formula rule that checks the relevant column, and fix that column with a dollar sign while leaving the row relative. Make sure the formula’s row matches the first row of the selected range.

Will conditional formatting change the cell’s value?
No. It changes the cell’s appearance when a rule is true. The underlying value, formula, and data remain unchanged.

Can I use more than one conditional-formatting rule on a cell?
Yes. Rules can overlap. Review their order and any Stop If True settings in Manage Rules if the displayed result is not what you expect.

How do I copy conditional formatting to other cells?
You can copy a formatted cell and use Paste Special > Formats on the destination, or use Format Painter. Check the copied rule’s Applies to range and formula references afterward, because relative references may shift.

Can I remove conditional formatting without deleting data?
Yes. Select the cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes the rule, not the cell contents.

Does conditional formatting work in Excel for Mac and the web?
Basic conditional formatting is available in both. The ribbon layout and some advanced rule-editing options can vary, so use the equivalent Conditional Formatting controls on the Home tab and open the desktop Windows version if a particular control is unavailable.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.