To color values in Excel, select the cells, then choose Home > Conditional Formatting to apply a color automatically when values meet a rule. To color cells manually instead, select them and use the Fill Color button on the Home tab. Conditional formatting is best when colors should update as your data changes; manual fill is useful for one-off emphasis.
What You’ll Need
- A workbook open in Microsoft Excel for Microsoft 365 on Windows.
- A range of cells containing the values you want to color. If you want colors to update automatically, decide what condition should trigger each color, such as a value above a target or a duplicate entry.
- Optional: a color plan. For example, use green for on-target results, yellow for items to review, and red for exceptions. A consistent scheme is easier to read than assigning colors arbitrarily.
The steps also work in many recent desktop versions of Excel. Excel for Mac has similar conditional-formatting controls, though the ribbon layout can vary slightly. Excel for the web supports common fill and conditional-formatting options, but some advanced rule settings may be more limited than in the desktop app.
Step-by-Step
- Select the cells you want to color. Click and drag across a continuous range, or click the first cell and hold Shift while selecting the last one. To select separate cells or ranges in Windows, hold Ctrl while clicking or dragging each selection. On a Mac, use Command instead of Ctrl for non-adjacent selections. Check that the selection includes only the data you intend to format; including a heading can make it colored or affect how rules are interpreted.
- Choose whether the color should be manual or automatic. Use a manual fill when the selected cells should keep the same color regardless of their contents. Use conditional formatting when the color should respond to values, such as highlighting every amount over 500 or marking repeated names. If values are likely to change or new rows will be added, conditional formatting usually takes less maintenance.
- Apply a manual fill color if that is all you need. On the ribbon, open Home and find the paint-bucket Fill Color control in the Font group. Click its drop-down arrow to choose a color from the theme, standard colors, or available custom-color options. Clicking the main part of the button applies the currently displayed fill color immediately. The fill changes the cell background; it does not change the value or font color.
- Open the conditional-formatting rules for automatic coloring. Keep the target cells selected, then choose Home > Conditional Formatting. The menu contains rule categories such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. Pick a category that matches the purpose of the color. For example, choose Highlight Cells Rules > Greater Than to mark values above a threshold.
- Set the condition and choose its appearance. For a Greater Than rule, enter the comparison value in the dialog, then pick a preset format or choose a custom format if offered. To define a custom fill and possibly a font color, open the formatting options, select the Fill tab, choose a color, and confirm the dialog. Click OK to apply the rule. Excel colors cells in the selected range whose values satisfy the condition. Choose a threshold that matches the units and scale in your sheet; entering 500 for data recorded in thousands, for instance, would highlight a very different set of cells than expected.
- Use the right rule type for other common cases. Under Highlight Cells Rules, options such as Equal To, Text that Contains, A Date Occurring, and Duplicate Values can color matching cells. Under Top/Bottom Rules, you can identify the top or bottom items or percentages. A Color Scale shades cells according to their relative values, which is useful for spotting high and low points across a numeric range. A Data Bar draws a bar inside each cell, while an Icon Set adds symbols. These visualizations are not ordinary cell fills, so use them when they make comparisons clearer.
- Check the result against the data. Look at values near the rule’s boundary as well as values that clearly should and should not match. If you set a Greater Than rule of 500, a value of 501 should be colored and a value of 500 should not. For a Duplicate Values rule, check that repeated entries receive the intended format. This quick check catches a mistyped threshold, a misselected range, or an unexpected mix of numbers stored as text.
- Adjust or remove a rule when needed. Select a cell in the formatted area and choose Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the rule, its range, and its formatting. Depending on the dialog’s Show formatting rules for setting, you may need to choose the current selection or worksheet to see the rule you want. Edit the condition or range, change its order if rules overlap, or delete it. To remove all conditional formatting from a selected range, choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This does not remove manual fill colors.
Example: Suppose cells B2:B25 contain weekly sales and you want to color figures above 1,000. Select B2:B25, choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than, enter 1000, select a fill style, and confirm. If a value later changes from 950 to 1,050, Excel updates its formatting because the rule is based on the cell value. If you instead manually fill B2 green, that green remains even if the value changes.
Common Mistakes
- Using manual fill when the color needs to update. A manually colored cell does not automatically change color when its value changes. Use a conditional-formatting rule for data-driven highlights.
- Selecting the wrong range. A rule may apply to only the first few rows, include totals, or miss new data. Confirm the range in Manage Rules. If the data is an Excel table, applying a rule to the relevant table column can make it easier to keep formatting aligned as rows are added, but verify the rule range after changes.
- Choosing colors that are hard to distinguish. Similar shades, pale fills, and red-green-only combinations can be difficult to read. Use strong contrast and, where possible, pair color with a label, symbol, or clear number format so the meaning is not carried by color alone.
- Assuming a color means the cell’s value changed. Fill color is formatting, not data. It does not alter calculations, sort order, or the underlying number. Conversely, copying a cell can copy its formatting along with its value, so paste options matter when you want to preserve the destination’s appearance.
- Creating overlapping rules without checking their priority. Two conditional-formatting rules may both apply to a cell. Excel’s rule order and settings determine which formatting appears, and some formatting attributes can combine. Review rule order and the Stop If True option, where available, in Manage Rules rather than guessing which rule is taking effect.
Tips
- Use one visual language throughout the workbook. Decide what each color means and use that meaning consistently across sheets. If amber means “review,” do not use it elsewhere to mean “complete.” Add a short legend near the data if readers may not know the color key.
- Prefer conditional formatting for thresholds and patterns. It is especially useful for overdue dates, values outside a target range, duplicates, and top or bottom results. A single well-chosen rule can make a large list easier to scan without manually checking every cell.
- Keep color scales focused on comparable values. A color scale works best when the cells represent the same kind of measurement. Comparing unlike quantities in one scale can give a misleading impression. If one outlier makes all other cells look nearly identical, consider a threshold rule or separate scales for comparable groups.
- Use the Format Painter carefully. It can copy cell formatting, but it can also copy more than just the fill. If you need only the background color, use the Fill Color control or choose a paste option that preserves the desired content without unwanted formatting.
- Consider accessibility and printing. Check that text remains readable over the chosen fill and that the difference remains understandable in grayscale or for readers with color-vision differences. A border, icon, or text label can reinforce a color-based distinction.
- Save a copy before broad formatting changes. Formatting can be cleared or changed later, but large edits are easier to reverse when you have a recent saved version. This is especially helpful before applying a rule across an entire worksheet.
Troubleshooting
The cells do not change color: First confirm that the rule applies to the selected cells. Open Manage Rules and check the range. Then verify the condition and test a value that should clearly match it. For example, with a Greater Than threshold of 100, try a known numeric value such as 150. A number stored as text may not behave like a number; check whether it is left-aligned unexpectedly or has a warning indicator, then convert it to a number if appropriate.
The wrong cells are colored: Check the comparison operator, threshold, and selected range. Greater Than does not include a value equal to the threshold; use Greater Than or Equal To when the boundary should count. For text rules, verify the text being matched and whether spaces or inconsistent spelling are present. Also inspect other rules that might apply to the same cells and affect the visible result.
The color looks different from what you chose: A theme color may look different from a standard or custom color, and another rule may be overriding the fill. Open Manage Rules and inspect the order and formatting of all rules applying to the cell. If you want a fixed background independent of the condition, remove the conditional rule affecting it and apply a manual fill instead.
Old colors remain after removing a rule: Clearing conditional formatting removes rule-based formatting only. Manually applied fills remain. Select the affected cells and use the Fill Color drop-down to choose No Fill if you want to remove manual background color. Avoid using Clear All unless you also intend to remove cell contents and other formatting.
New rows do not receive the same color rule: Review the rule’s Applies to range in Manage Rules. Extend that range to cover the new rows, or consider formatting a table column so its formatting can expand with the table. Always check the resulting range; adding rows or copying cells does not guarantee every rule will extend exactly as intended.
Frequently Asked Questions
Can I color cells based on their values automatically?
Yes. Select the range and use Home > Conditional Formatting to create a rule, such as Greater Than, Equal To, Duplicate Values, or a color scale. Excel updates rule-based colors when the cell values change.
How do I color a cell manually in Excel?
Select the cell, open Home, and use the drop-down beside Fill Color in the Font group. Choose a color. This applies a fixed background color and does not depend on the cell’s value.
Can I color only values above or below a number?
Yes. Select the numeric range and choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than or Less Than. Enter the boundary and select a format. Check whether values equal to the boundary should be included; choose a greater-than-or-equal or less-than-or-equal rule if appropriate.
How do I remove color without deleting the values?
For conditional formatting, select the range and choose Home > Conditional Formatting > Clear Rules, then select the relevant clearing option. For a manual fill, use the Fill Color drop-down and choose No Fill. Both actions leave cell contents in place.
Can I use different colors for different ranges?
Yes. Create separate conditional-formatting rules for each condition, or manually apply different fills to separate ranges. If the rules overlap, review their order in Manage Rules so the displayed formatting matches your intended priority.
Do the steps work in Excel for Mac and Excel for the web?
The core ideas are the same: select cells, choose a fill or conditional-formatting rule, and check the result. Mac ribbon labels are generally similar, though their placement can differ. Excel for the web offers common rules and fills; use the desktop app if a particular advanced formatting control is unavailable.
Related
- Capitalize in Excel: Shortcut Keys and Quick Methods
- How to Capitalize Text in Google Sheets Without Retyping
- Capitalize the First Word in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Conditional Formatting for Weekend DatesTo highlight weekend dates in Excel, select the date cells, choose Home > Conditional Formatting > New Rule…
- Google Sheets Checkbox Column: Add and Use CheckboxesTo add a checkbox column in Google Sheets, select the cells where the checkboxes should go, then choose…
- Excel Row Stays When Scrolling? Keep It VisibleTo make an Excel row stay visible while you scroll, freeze it: in Excel for Microsoft 365 on…
- How to Create a Letterhead Template in Microsoft WordTo create a letterhead template in Microsoft Word, place your logo and contact details in the header, check…
- Excel Ungroup Columns Shortcut: Keys, Steps, and FixesIn Excel for Microsoft 365 on Windows, select the grouped columns and press Alt+Shift+Left Arrow to ungroup them.…
- Microsoft Excel .xlsx Files: Workbooks and WorksheetsA Microsoft Excel .xlsx file is an Excel workbook: a file that can contain one or more worksheets,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.