Excel Conditional Formatting: Add Color to Cells by Rule

To add color conditional formatting in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting and pick a rule, such as Highlight Cells Rules > Greater Than. Set the condition and choose a fill color; Excel will apply that color automatically whenever a cell meets the rule.

What conditional formatting does

Conditional formatting changes a cell’s appearance when its value meets a condition. Unlike applying a fill color manually, it responds to changes in the worksheet: if a score rises above a threshold, a date becomes overdue, or a status changes, the format can update without you recoloring the cell yourself. The underlying value is not changed, and the formatting does not replace or calculate data.

Color is one of the most useful ways to make patterns visible. A fill can draw attention to exceptions, while a color scale can show how values compare across a range. For example, a sales table might use red fill for low monthly totals and green fill for high totals. A schedule might highlight due dates that have passed. A color rule can also help readers scan a long list, but it should support—not substitute for—clear labels and numbers.

Conditional formatting is available in Excel for Microsoft 365 on Windows, Mac, and the web, though some menus and dialog layouts differ. The ribbon paths below refer to the Windows desktop version. In Excel for Mac, use the Home tab and its Conditional Formatting menu; the rule choices are broadly similar. In Excel for the web, the same feature is on the Home ribbon, but some advanced rule controls may be more limited than in the desktop app.

Choose the right kind of color rule

Excel includes several types of conditional formatting. The best choice depends on whether you want to flag a particular value, compare values, or format a whole row based on one column.

Highlight Cells Rules: Mark values greater than, less than, equal to, between two values, or matching text or dates. These are convenient for simple thresholds, such as highlighting expenses above a set amount.
Top/Bottom Rules: Color the highest or lowest items, a percentage of the range, or values above or below the average. These rules are useful when the cutoff should follow the data rather than stay at a fixed number.
Data Bars, Color Scales, and Icon Sets: Add visual indicators inside or across cells. A color scale, for instance, can shade low values one color and high values another, with intermediate values receiving blended colors.
Formula-based rules: Apply a format when a formula evaluates to TRUE. These provide more control, including the ability to color a complete row based on one cell in that row.

For a simple yes-or-no threshold, use a Highlight Cells Rule. For a gradual comparison across numbers, use a color scale. If the condition depends on more than one column, or if you need an entire record highlighted, a formula rule is usually the more flexible option.

Apply a basic fill-color rule

Suppose column B contains order totals and you want totals above 500 to stand out. Select the relevant cells in column B, such as B2:B50, then follow these steps in Excel for Microsoft 365 on Windows:

Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
Enter 500 in the value box. Excel may offer a default format in the adjacent list.
Choose a preset, or select Custom Format to choose a fill color and, if desired, a font color or border.
Select OK to apply the rule.

Cells with numeric values greater than 500 should now show the chosen formatting. If a value changes from 480 to 520, Excel applies the format; if it changes back to 480, the condition is no longer true and the conditional format disappears. Blank cells and text generally do not meet a numeric greater-than test.

To format a text value, select the range and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter the text to find, choose a format, and confirm. For date-based rules, use Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring and choose an available time period, such as last week or next month. Exact choices depend on the rule type.

Use color scales to show a range

A color scale compares each cell with the other cells in the selected range. To add one, select the numeric cells and choose Home > Conditional Formatting > Color Scales, then select a gradient. Excel assigns colors according to the values in the range; a common three-color scale uses one color for low values, another for middle values, and a third for high values.

Color scales are useful for spotting relative highs and lows in a block of comparable numbers, such as monthly results across several teams. They can be misleading if readers assume a particular shade always means the same absolute value. In one range, green might represent 80; in another, the same shade may correspond to 800 because the scale is based on that range’s minimum, midpoint, and maximum. If a specific cutoff matters, use a threshold rule instead, or edit the scale’s rule settings to specify numeric limits.

Data bars are another option for comparing magnitudes. Choose Home > Conditional Formatting > Data Bars to display bars within cells, with longer bars representing larger values. These can make comparisons quick, but leave enough room to read the cell values and check whether the scale is appropriate for the data.

Format a whole row with a formula

A common request is to color an entire row when one column meets a condition. For example, if column B contains a sales amount and you want rows with sales of at least 1,000 highlighted, select the full data range first, such as A2:D50. Then choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter the formula below.

With sales amounts in B2:B50 and the data rows spanning A2:D50, use =$B2>=1000 as the rule formula. Choose Format, select a fill color on the Fill tab, then select OK to finish creating the rule. The dollar sign fixes the check to column B, while the row number remains relative so the rule checks B2 for the first row, B3 for the second, and so on. With a sales value of 1,250 in B2, the formula is TRUE and the cells in A2:D2 receive the format.

The formula’s starting row must match the first row of the selected range. If your selection begins at row 5, adjust the reference to B5. Also check Applies to in the Conditional Formatting Rules Manager: it should cover the intended rows and columns. A correct formula applied to the wrong range can appear to work inconsistently.

Change, reorder, or remove rules

To review existing rules, select a cell in the affected area and choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, use the dropdown to show rules for the current selection or the worksheet. Select a rule to edit it, change its format, or remove it. The Applies to box lets you adjust the cells governed by the rule.

When multiple rules apply to the same cells, their order can affect the result. Use the up and down controls in the Rules Manager to change priority. The Stop If True option can prevent lower-priority rules from being evaluated for cells where a higher-priority rule is true. Use it carefully: it can make another color rule appear not to work, even though that rule still exists.

Copying and pasting cells can also copy conditional formatting. If a worksheet accumulates duplicate or overlapping rules, review the Rules Manager and remove the rules you do not need. To clear formatting rules without deleting cell values, select the cells and choose Home > Conditional Formatting > Clear Rules, then choose whether to clear rules from the selected cells or the entire worksheet. Clearing rules removes conditional formatting, not the underlying data.

Common problems and practical checks

If a cell does not change color as expected, first check the condition and the value’s data type. A number stored as text may not behave like a numeric value in a comparison. Dates can also be stored as text, so a date rule may not recognize them. Check whether the threshold is inclusive or exclusive: “Greater Than 500” does not include 500, while “Greater Than or Equal To 500” does.

Next, inspect the selected range, the rule formula, and the Applies to area. Formula rules are particularly sensitive to relative and absolute references. If every row is testing the same cell, a row reference may have been fixed unintentionally; if each row checks the wrong column, the column reference may not be anchored. Also look for another rule with higher priority or a format that is difficult to see against the worksheet background.

When the fill color looks different from the one you selected, check whether another rule is also applying a fill. A normal cell fill set manually can also make the sheet harder to interpret alongside conditional formatting. For a clean test, apply the rule to a small sample range, verify the condition with a few known values, then extend it to the full dataset.

Make color formatting clear and accessible

Use color consistently and sparingly. If red means a problem in one table and a positive result in another, readers have to relearn the visual code. Add labels or a legend when colors carry meaning, and do not rely on color alone to communicate an important status. Include words such as Overdue or Complete, or use a clear numeric threshold, so the information remains understandable when printed in grayscale or viewed by someone who has difficulty distinguishing colors.

Be cautious with color scales across unrelated groups. Comparing employee sales across one shared range is different from comparing each employee’s month-to-month results using separate scales. Decide whether the purpose is to compare absolute values or show relative patterns within a group, then choose a rule that matches that purpose. In a shared workbook, document thresholds that are not obvious so another person can understand why a cell is colored.

Frequently Asked Questions

Can Excel automatically change a cell’s color when I type a value?
Yes. Create a conditional formatting rule for the cell or range. Excel reevaluates the rule when the value changes and applies or removes the formatting according to whether the condition is met.

Can I color an entire row based on one cell?
Yes. Select the full row range you want to format, create a formula-based rule, and use a mixed reference that fixes the relevant column but leaves the row relative. For example, with values in column B and data beginning in row 2, the rule =$B2>=1000 checks column B separately for each selected row.

Can I use more than one conditional formatting rule on the same cells?
Yes. Multiple rules can apply to the same range. Review their order in Home > Conditional Formatting > Manage Rules, because priority and the Stop If True setting can affect which formatting is visible.

How do I remove conditional formatting but keep my data?
Select the cells, then use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To remove rules from the whole sheet, choose the worksheet option instead. The cell contents remain in place.

Does conditional formatting change the cell’s value?
No. It changes how the cell is displayed, not the stored value. A red fill does not turn a number into text or alter a calculation; it is a visual signal controlled by the rule.

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.