To color cells in Excel according to their values, select the range, then choose Home > Conditional Formatting and pick a rule such as Greater Than, Color Scales, or New Rule. Conditional formatting applies and updates the color automatically when values change; you do not need to fill each cell by hand. These steps are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with the numbers, dates, or text you want to format.
- A clear idea of what the colors should mean, such as red for values below a target and green for values at or above it.
- For a formula-based rule, identify the first cell in the selected range and the cells or columns the rule should examine.
Conditional formatting is available in current desktop versions of Excel and Excel for the web. The ribbon layout can differ slightly on Mac, but the Conditional Formatting feature works in current Excel for Mac as well.
Step-by-Step
- Select the cells to format. Click and drag over the relevant range, such as B2:B20 for a list of scores. You can also click the column letter to select a whole column, but a specific range is usually easier to manage and avoids formatting empty cells. If you are coloring rows based on a value in one column, select the full rows or table area that should receive the color.
- Open the conditional formatting choices. On the Windows ribbon, go to Home > Conditional Formatting. Choose a rule type from the menu. Highlight Cells Rules is useful for straightforward thresholds or text matches; Top/Bottom Rules identifies high or low values; Color Scales shades cells according to their relative values; and Data Bars or Icon Sets add visual indicators inside cells.
- Choose a rule that matches your goal. For a threshold, open Highlight Cells Rules and select Greater Than, Less Than, Between, Equal To, or another suitable option. For example, choose Greater Than to color scores above a target. For a gradient that makes low, middle, and high values look different, choose Color Scales and select a two-color or three-color palette. The scale uses the selected range’s values to determine the shades.
- Enter the condition and choose a format. If you chose Greater Than, type the cutoff value in the dialog box, then select a preset fill or use Custom Format to choose a fill color, font color, or border. Click OK to apply it. As a concrete example, selecting B2:B20, choosing Greater Than, and entering 100 colors cells with values above 100; a cell containing 125 receives the format, while a cell containing 99 does not.
- Use New Rule for more control. To set a rule with specific colors or conditions, select the range and choose Home > Conditional Formatting > New Rule. Choose a rule type in the dialog. For numeric data, Format all cells based on their values offers color scales, data bars, and icon sets. To define a simple threshold, choose Format only cells that contain, set the condition, and click Format to choose the appearance. Click OK to save the rule.
- Color cells using a formula when the built-in choices are not enough. Select the cells that should be colored, then go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format. With values in B2:B20, enter
=B2>=100to format cells in that range whose values are at least 100. Because B2 is the top-left cell of the selected range, Excel adjusts the reference for each row. Click Format, choose a fill, and select OK. For example, B2 containing 100 is formatted, and B3 containing 85 is not. - Base a row color on a different column if needed. Suppose A2:C20 contains a task table and column C has each task’s status. Select A2:C20, open New Rule, choose Use a formula to determine which cells to format, and enter
=$C2="Overdue". Choose a fill color and confirm. The dollar sign keeps the test in column C as the rule moves across each row, while the row number changes. A row where C2 says Overdue is colored across columns A to C; a row with a different status is not. - Check the range and save your workbook. Change a sample value temporarily to confirm the color responds as expected, then undo the test if you do not want to keep the change. To inspect or adjust rules, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. In Show formatting rules for, choose the relevant worksheet if necessary. Check the rule and its Applies to range, then click Apply and OK. Save the workbook when the result looks right.
To remove a rule without deleting cell contents, select the affected range and choose Home > Conditional Formatting > Clear Rules. Select Clear Rules from Selected Cells or Clear Rules from Entire Sheet carefully; the second choice removes conditional formatting from the whole worksheet. On Excel for Mac, use the Home tab and Conditional Formatting menu to create and manage rules; dialog wording or layout may vary. In Excel for the web, use Home > Conditional Formatting, though some advanced editing options may be arranged differently from desktop Excel.
Common Mistakes
- Using a static fill instead of a rule. The paint-bucket Fill Color button colors cells manually. It will not change when a value changes. Use Conditional Formatting when the color should represent the current value.
- Selecting the wrong range. A rule only applies to its Applies to range. If you select B2:B20 but expect B21 to change too, add that cell or extend the range in Manage Rules.
- Using the wrong comparison. Greater Than 100 excludes 100, while Greater Than or Equal To 100 includes it. Check whether your threshold should include the boundary value.
- Referencing the wrong cell in a formula rule. The formula should usually refer to the top-left cell of the selected range. If the selection begins on row 2, a formula written as though it begins on row 1 can produce shifted results.
- Applying colors that are hard to distinguish. A very subtle fill, or red and green alone, may be difficult to read. Use clear contrast and consider adding labels or another visual cue for important information.
Tips
- Use a two-color scale for a simple low-to-high comparison and a three-color scale when you want to show low, middle, and high zones. Pick colors with enough contrast against the worksheet background.
- For a fixed business threshold, use a rule such as Greater Than or a formula rule. A color scale is relative to the selected values, so the same number may appear differently when the range changes.
- In formula rules, use dollar signs only where a reference should stay fixed. For instance, in
=$C2="Overdue", the column is fixed so every cell in a row checks column C, but the row adjusts for each task. - If your data is an Excel table, apply the rule to the data cells and review its range after adding rows. Table expansion and rule range behavior can depend on how the rule was created, so verify that new records are included.
- Use Manage Rules to review several rules together. If rules overlap, their order can affect the displayed format. Move the most important rule higher in the list and, where appropriate, select Stop If True to prevent lower-priority rules from applying.
- Keep a small legend near the data when colors carry a specific meaning. For example, note that amber means approaching a deadline and red means overdue. This makes the worksheet easier to interpret for other people.
Troubleshooting
The color does not appear. Open Manage Rules and check that the correct worksheet is selected in Show formatting rules for and that the Applies to address includes the cell. Confirm that the cell actually meets the condition. If the rule compares a number with text, the test may not work as intended; check whether the value is stored as a number rather than text.
The wrong cells are colored. For a formula rule, verify that the reference matches the top-left cell of the selected range. If you are coloring a whole row based on one column, lock the column with a dollar sign, as in =$C2="Overdue", and make sure the range starts on the same row as the formula reference. Also inspect the rule order for an overlapping rule that may override the format.
The color does not update after editing a value. Check that the rule is conditional formatting rather than a manual fill. Confirm that Excel calculation is set to update automatically if the rule relies on formulas: go to Formulas > Calculation Options > Automatic. If the condition uses a formula that refers to another cell, verify the reference and test it with a value you know should match.
A color scale looks misleading. Color scales compare values within their selected range. A single unusually high or low value can make most other cells look similar. Consider using a fixed threshold rule instead, or adjust the rule’s minimum, midpoint, or maximum in its formatting settings so the colors reflect the comparison you want to communicate.
Frequently Asked Questions
Can Excel color a cell automatically when I type a value?
Yes. Apply a conditional formatting rule to the cell or range first. Excel then evaluates the rule when a value is entered or changed and updates the format when the condition is met.
How do I color values above or below a number?
Select the range, then choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than or Less Than. Enter the cutoff and choose a format. Use Greater Than or Equal To in a formula rule if the boundary value should also be included.
Can I color an entire row based on one cell?
Yes. Select the full table range, create a formula rule, and use a fixed column reference with a relative row reference. For example, when the status is in column C and the selection starts at row 2, =$C2="Overdue" colors rows whose status is Overdue.
Will the color scale change if the values change?
Yes. A conditional formatting color scale updates as the values in its Applies to range change. Because the shades are relative to the range, adding an extreme value can change the appearance of other cells.
How do I remove the colors but keep the data?
Select the formatted cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes the conditional formatting rules from those cells without deleting their contents.
Can I use more than one color rule on the same range?
Yes. Add multiple rules and review their order in Manage Rules. When conditions overlap, order and Stop If True settings can affect which formats appear, so test the range with values that meet each condition.
Related
- How to Center an Excel Page for Printing
- How to Center an Excel Spreadsheet on a Page for Printing
- How to Center an Excel Table in Word Without Distorting It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add a Named Range in Excel: Steps, Uses, and Helpful TipsTo add a named range in Excel for Microsoft 365 on Windows, select the cell or range, click…
- Excel Shortcut for Filtering a Table: Keys and TipsIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filter buttons on or off for the…
- Excel Heat Mapping: How to Create Useful Color ScalesIn Excel, heat mapping usually means using Conditional Formatting color scales to shade cells according to their values.…
- Excel Insert Row: How to Shift Cells DownTo insert a row in Excel and shift the existing rows down, select the row header where the…
- Nested Dropdowns in Google Sheets: How to Set Them UpYes. You can make a nested, or dependent, dropdown in Google Sheets by making the choices in a…
- How to Hyperlink in Excel on a Mac: Web, Files, and CellsTo add a hyperlink in Excel for Mac, select the cell, press Command+K, enter or choose the link…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.