To set color in Excel based on a value, select the cells, then choose Home > Conditional Formatting and create a rule for the values you want to highlight. Conditional formatting applies a fill or font color automatically and updates it when a cell’s value changes. You’ll need a worksheet with the values you want to check and a clear rule, such as “greater than 100” or “contains Overdue.”
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 on Windows, with the values you want to format.
- A rule that describes which cells should receive color. For example, you might highlight scores below 60, overdue statuses, or high sales.
- Optional: a color choice and, for a formula-based rule, a clear idea of how the relevant rows and columns line up.
You do not need to install an add-in or write a macro. Conditional formatting is built into Excel. The examples below use the Windows desktop version. On Mac, the same conditional formatting options are available from the Home tab, though some dialogs may be arranged differently. Excel for the web has common conditional formatting options too; use the desktop app if a formula-based rule or its management options are not available in your web session.
Step-by-Step
- Choose the cells to format. Select the cells whose displayed color should change. For a single column of numbers, select the numeric cells, not the heading. For a whole row that should be colored based on one column, select the full rows of data, such as A2:F100, and build a formula rule based on the relevant column. Selecting the intended range first helps Excel apply the rule consistently. If you expect to add more records, you can select extra blank rows in advance, or convert the data range to an Excel table so formatting can extend as you add rows.
- Open Conditional Formatting. On the ribbon, choose Home > Conditional Formatting. For a quick rule, point to Highlight Cells Rules. Choose a condition such as Greater Than, Less Than, Between, Equal To, Text that Contains, or A Date Occurring. The available choices depend on the kind of value you are checking. To shade cells according to their relative numbers, choose Color Scales instead.
- Enter the condition. In the rule dialog, type the value or text to compare against. For example, if the selected cells contain test scores and you want to call attention to low scores, choose Less Than and enter 60. For a status column, choose Text that Contains and enter Overdue. Check that the selected comparison matches your intention: Greater Than 100 does not include 100, while a rule for values equal to 100 does. If the dialog offers a preset format, select one, or choose Custom Format to set the appearance yourself.
- Pick a fill or font color. In the formatting options, select a fill color for the cell background, a font color for the text, or both. Choose a color with enough contrast to keep the value readable. If you need more control over the style, choose Custom Format, use the Fill tab to set the background, or use the Font tab to set text color and style, and confirm the dialogs. Click OK to create the rule. Excel displays the formatting on cells that currently satisfy it.
- Use a color scale when the value is a range, not a yes-or-no condition. Select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a two-color or three-color scale. Excel maps the selected values from low to high, so the colors show relative magnitude rather than a single cutoff. This is useful for comparing sales, response times, or scores at a glance. It can be misleading if the range contains outliers, so check that the color distribution makes the differences you care about easy to see.
- Create a custom rule for a specific cutoff or a full-row highlight. Choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Use a formula to determine which cells to format. With numeric values in B2:B100 and B2:B100 selected, enter
=B2>=100to format values of 100 or more. Click Format, choose the desired fill or font color, and click OK to finish. Because the reference is relative, Excel checks each selected cell against its own value as the rule moves down the range. - Base a row’s color on a value in another column. Select the complete data area, for example A2:F100, if you want every cell in a row to change color based on its status in column B. With statuses in B2:B100 and the rows A2:F100 selected, create a formula rule and enter
=$B2="Overdue". Choose the format and confirm. The dollar sign locks the check to column B, while the row number changes for each row. If the status cell says Overdue, the formatting is applied across that row; if not, the row does not meet this rule. - Review the range and rule. Select a cell in the formatted area and choose Home > Conditional Formatting > Manage Rules. In the Show formatting rules for list, select This Worksheet if you need to see rules beyond the current selection. Check the Applies to range and make sure it covers all intended cells. If rules overlap, inspect their order and any Stop If True setting. Click Apply to preview changes and OK to close the manager.
For formula rules, the location of the first cell in the selection matters. Excel interprets relative references from the top-left cell of the Applies to range. In the row example, the selection begins at row 2, so the formula checks B2 for the first row, then B3, B4, and so on. If your data starts on row 5, change the row reference in the formula to 5 before applying it. Use a dollar sign only where you want a reference to stay fixed as the rule is evaluated across the range.
Common Mistakes
- Selecting the wrong range. A rule can be correct but appear to color the wrong cells because Applies to includes a heading, misses later rows, or covers only one column when you meant to shade full rows. Review the range in Manage Rules before changing the condition.
- Using the wrong reference in a formula rule. In a full-row rule, a reference such as B2 can shift to C2 as the formatting is evaluated in the next column. Lock the condition column with $B2 when every cell in a row should be controlled by column B. Do not lock the row if each row needs to check its own status.
- Expecting an exact match from a partial-text rule. Text that Contains can match a longer label that includes the search word. If the rule should apply only to one precise status, use an equality-based formula rule instead of a contains condition.
- Comparing numbers stored as text. A cell that looks like 100 may be text, especially after importing data. Numeric rules and color scales may then behave unexpectedly. Check the cell alignment and test whether the values are recognized as numbers; convert the source data to numbers before relying on numeric comparisons.
- Applying a static fill instead of a conditional rule. Home > Fill Color changes the selected cells manually and will not change color when their values change. Use Conditional Formatting for color that should respond to the cell contents.
- Choosing colors that obscure the data. Dark fills can make default black text hard to read, and too many bright colors make a worksheet difficult to scan. Use a small, consistent set of colors and check both the cell value and its contrast.
Tips
- Decide what each color means before building rules. For example, use red for action needed, amber for a warning, and green for an acceptable result, then apply the same meanings throughout the workbook.
- For a single threshold, use a built-in Highlight Cells Rule; it is easier to review than a formula. Use a formula when the condition depends on another cell, needs multiple criteria, or should format an entire row.
- For a three-color scale, consider setting meaningful low, midpoint, and high values in a New Rule dialog rather than letting Excel use the minimum and maximum automatically. This keeps colors comparable if the data range changes.
- Keep the source values intact. Conditional formatting changes appearance, not the underlying number or text, so calculations and sorting continue to use the actual cell contents.
- When you copy cells, Excel may copy their conditional formatting too. If a pasted area picks up unwanted colors, inspect Manage Rules and remove or adjust the extra rule rather than painting over the formatting manually.
- For an ongoing list, use an Excel table or include a reasonable future range in Applies to. Check the rule after adding rows to confirm the new records are covered.
Troubleshooting
The cells do not change color. Open Manage Rules and confirm that the rule is enabled for the expected range. Verify that the entered condition matches the actual cell value, including spaces in text. For numeric rules, confirm the cells contain numbers rather than text. In a formula rule, make sure the formula begins with an equals sign and refers to the correct first row of the selected range.
The wrong rows or columns are colored. Check the Applies to range and the formula references. For row-based formatting, confirm that the column containing the condition is fixed with a dollar sign and that the row number matches the top row of the selected data. If the rule starts at row 2 but the formula checks row 1, Excel evaluates the wrong status for each row.
A cell has an unexpected color. More than one rule may apply to it. Choose Home > Conditional Formatting > Manage Rules, select This Worksheet, and review the rules in order. Change their order or edit their ranges and conditions. If Stop If True is selected for a rule, later rules may not be evaluated for cells where that rule is true; clear it if later formatting should also be considered.
The colors look different after sorting or adding data. Check whether the rule’s Applies to range moved or failed to include new rows. Recheck the range after inserting or pasting records. If a pasted block has brought in its own rules, remove the duplicates and keep one rule that covers the intended data area.
The color remains after the value changes. Make sure the color came from Conditional Formatting rather than a manual fill. If it is a conditional format, inspect the condition and rule order. A cell might still meet a different overlapping rule even after it stops meeting the first one.
Frequently Asked Questions
Can Excel change a cell’s color automatically when I enter a value?
Yes. Create a conditional formatting rule for the value or text you want to detect. Excel updates the appearance when the cell changes and the rule is recalculated.
Can I color an entire row based on one cell?
Yes. Select the full data rows, create a formula-based conditional formatting rule, and refer to the condition column with an absolute column reference, such as $B2. Use the row number that matches the first row in the selected range.
Can I use more than two colors?
Yes. Use a three-color scale for a continuous range of numeric values, or create several separate rules with different conditions and formats. Review overlapping rules so the result is predictable.
Can I color cells based on text such as Yes or No?
Yes. Select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter the text and choose a format. Use a formula rule if you need an exact match or want to color other cells on the same row.
How do I remove conditional colors?
Select the affected cells and choose Home > Conditional Formatting > Clear Rules. Choose Clear Rules from Selected Cells to preserve rules elsewhere, or Clear Rules from Entire Sheet to remove all conditional formatting on the worksheet. This removes conditional rules, not necessarily manually applied fill colors.
Will conditional formatting change the actual value?
No. It changes how a cell looks, not the content stored in it. The displayed number, text, and formula result remain unchanged, so the color is a visual signal rather than a replacement for the underlying data.
Related
- How to Repair an Excel File in Windows 11
- How to Repair Excel Desktop App: Windows Steps and Fixes
- Rename a Sheet in Excel Without a Mouse
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Freeze and Unfreeze Panes in Excel: A Step-by-Step GuideTo keep headings or identifying columns visible while you scroll in Excel, select the cell just below the…
- How to Set Out of Office in Outlook 365 WebTo set an out-of-office reply in Outlook 365 on the web, open Settings, go to Accounts > Automatic…
- Excel Ordinal Number Format: Show 1st, 2nd, and 3rdExcel does not have a built-in number format that automatically changes a number to an ordinal such as…
- Switch Excel Columns From Letters to Numbers?To switch Excel column headings from letters to numbers in Microsoft 365 for Windows, select File > Options…
- Google Sheets IMPORTRANGE: Setup, Uses, and Common FixesGoogle Sheets’ IMPORTRANGE function brings a cell range from one spreadsheet into another and keeps the imported results…
- Copy and Paste in Excel While Keeping FormattingTo copy cells in Excel for Microsoft 365 on Windows and keep their formatting, select them, press Ctrl+C,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.