Excel Cell Color Based on Input: A Step-by-Step Guide

How-to basics · Updated November 20, 2026

To make an Excel cell change color when you enter a value, select the cells, then choose Home > Conditional Formatting and create a rule for that input. Conditional formatting applies a fill automatically when a condition is true, so the color updates as you edit the cell. For example, you can make “Approved” green, “Pending” yellow, or numbers above a limit red.

What You’ll Need

  • Excel for Microsoft 365 on Windows, with a workbook open.
  • The cells where you will type inputs, and a clear idea of which values should trigger which colors.
  • Optional: a list of permitted input values, such as Approved, Pending, and Rejected. A drop-down list is not required, but can help prevent spelling differences.

The instructions below cover two useful setups: coloring the input cell itself and coloring another cell on the same row based on the input. Conditional formatting changes the display, not the cell’s underlying value. If you need a permanent fill that does not change when the input changes, use the regular Fill Color button instead.

Step-by-Step

  1. Choose the cells the rule should format. If users will type a status in A2 through A100 and you want those same cells colored, select A2:A100. You can select the range by dragging, or click the Name Box to the left of the formula bar, type A2:A100, and press Enter. Selecting the whole intended range first helps Excel apply the rule consistently to future entries in those rows. Avoid selecting the entire worksheet unless you really want the rule to affect every cell.
  2. Open the conditional formatting options. On the Windows ribbon, go to Home > Conditional Formatting. For a quick rule based on a particular word, choose Highlight Cells Rules > Text that Contains. Type the trigger text, such as Approved, in the dialog. Choose a suggested format or select Custom Format to set your own fill, font, or border. Click OK to apply it. When the text appears in any selected cell, Excel displays the chosen formatting.
  3. Create a rule for an exact value when you need more control. Select the range again if necessary, then choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Format only cells that contain. Set the rule to format cells whose specific text contains the value you want, or choose a cell-value condition for numbers and dates. Enter the trigger, click Format, select the Fill tab, choose a color, and click OK. Confirm the rule with another OK. This is useful when setting distinct formats for several statuses: create one rule for each status and select a different fill for each.
  4. Use a formula for exact matches or more complex conditions. To color a cell only when its full value matches a status, select A2:A100, choose Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format. With the status inputs in A2:A100, enter =A2="Approved" in the formula box. Click Format, choose a fill color, and confirm. Because A2 is the first cell in the selected range, Excel adjusts the reference for each row. The rule is true for a cell containing exactly Approved, but not for a longer entry such as Approved – checked.
  5. Format a different cell based on the input. For example, you might want to highlight the whole row’s task cell in B when the status is in A. Select B2:B100, create a formula-based rule as described above, and use =$A2="Late". With statuses in A2:A100 and the cells to color in B2:B100, this rule checks the status in column A for each row and colors the corresponding B cell when it is Late. The dollar sign fixes the column reference, while the row number remains relative. To color a wider row, select B2:F100 before creating the same rule.
  6. Choose colors that are easy to distinguish. In the Format Cells dialog, use the Fill tab to set the background color. You can also set a font color or bold style if that improves readability. Prefer colors with enough contrast against the text; a pale fill with dark text is often easier to read than a saturated fill. Select OK in the formatting dialog, then OK again in the rule dialog. Type a test value in the selected range and confirm that the expected cell changes color.
  7. Check and edit the rule’s range. Go to Home > Conditional Formatting > Manage Rules. In the Show formatting rules for list, choose This Worksheet if you cannot see the rule. Check the Applies to box: it should cover the cells you intend to format, such as =$A$2:$A$100. Edit the range if needed, then select Apply and OK. If the list has several rules for the same cells, use the arrows to adjust their order; a higher rule can affect which format appears when conditions overlap.

For numeric inputs, you can often use a built-in rule rather than a formula. Select the range and choose Home > Conditional Formatting > Highlight Cells Rules, then select Greater Than, Less Than, or Between. Enter the threshold or limits, choose a format, and confirm. For example, a quantity greater than 10 can receive a different fill from a quantity at or below 10. For dates, use a date-related highlight rule or a formula-based rule if the condition is more specific.

Common Mistakes

  • Typing a different spelling or extra spaces. A rule for Approved may not match an entry with a trailing space or a different spelling. Use a drop-down list when entries should come from a fixed set, or use a text-contains rule if a partial match is acceptable.
  • Applying the rule to the wrong range. A correct condition still appears not to work if Applies to excludes the cell being edited. Check the range in Manage Rules.
  • Using a fixed row reference unintentionally. In a rule intended to check each row, a reference such as =$A$2="Late" locks both the column and row, so every formatted row checks A2. Use a relative row reference, as in the earlier example, when each row should check its own status.
  • Expecting a manual fill to update itself. The paint-bucket Fill Color button applies a static color. Use Conditional Formatting for a fill that responds to changing input.
  • Creating conflicting rules. If multiple rules apply to the same cells, one may override or combine with another. Review rule order and conditions in Manage Rules.

Tips

  • Make input consistent. To add a drop-down for status values, select the input cells and choose Data > Data Validation. Set Allow to List and enter the permitted values as the source, separated by commas, or select a range that contains the choices. Keep the spelling in the list consistent with the conditional formatting rules.
  • Use one color per meaning. Green for complete and red for overdue can be intuitive, but include clear status text as well. Color alone may be difficult to interpret for some people, and printed or grayscale copies may not preserve the distinction.
  • Plan for additional rows. Apply the rule to a larger reasonable range, such as A2:A500, if people will add entries later. If the data is an Excel table, conditional formatting can often be applied to the table’s data cells; check the Applies to range after adding rows.
  • Keep rules simple. A separate rule for each short status is easier to inspect than one complicated formula. Use formula-based rules when you need exact matching, row-based checks, or multiple conditions.
  • Know where the formatting lives. Conditional formatting is saved with the workbook and normally recalculates when a cell changes. Copying and pasting cells can also copy rules, so review Manage Rules if a workbook begins showing unexpected colors.

Troubleshooting

If typing the trigger does not change the color, first click the cell and check the exact value in the formula bar. Compare its spelling and spaces with the rule. Then open Home > Conditional Formatting > Manage Rules, select This Worksheet, and confirm that the condition and Applies to range include the edited cell. Check that the rule has a visible fill color rather than only a font or border format.

If a formula-based rule colors the wrong row, inspect the reference in the formula and the first cell in the Applies to range. For a range beginning at row 2, the formula should usually refer to row 2 and allow the row number to adjust. Lock a column with a dollar sign when checking a status column, but do not lock the row if each formatted row needs its own check. If the formula works for the first row but not later rows, the reference anchoring or the applies-to range is a likely cause.

If the cell stays colored after you remove the trigger, check for another rule that also applies to it. Use Manage Rules to inspect every rule for that range and adjust its condition, order, or Applies to range. If the color remains even after you remove all relevant conditional formatting rules, a regular manual fill may be underneath; select the cell and choose Home > Clear > Clear Formats only if you are comfortable removing its other formatting too.

On Excel for Mac, the Conditional Formatting commands are on the Home tab, though dialog layouts can differ slightly from Windows. Excel for the web also supports conditional formatting, but some rule-editing options and dialog details may vary. If a workbook’s formatting is difficult to manage in the browser, open it in the desktop app and review the rules there.

Frequently Asked Questions

Can Excel change a cell’s color automatically when I type?
Yes. Create a conditional formatting rule for the input range. Excel updates the displayed fill when the cell meets the rule, and updates it again if the value changes.

Can I use different colors for different inputs?
Yes. Create a separate conditional formatting rule for each value or condition, and assign each rule a different fill. For example, Approved can be green, Pending yellow, and Rejected red.

Can I color one cell based on the value in another cell?
Yes. Select the cells you want to color, create a formula-based rule, and refer to the input cell on the same row. Use a fixed column and a relative row when the input is in one column and each row should be checked independently.

Does conditional formatting change the value in the cell?
No. It changes how the cell looks, not its contents. The value remains available for formulas, sorting, filtering, and other workbook tasks.

Will the color appear when I print the worksheet?
Usually, conditional formatting appears in printouts, but printer settings, print previews, and grayscale printing can affect how it looks. Check Print Preview and avoid relying on color as the only way to communicate a status.

How do I remove the color rule?
Select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes conditional formatting rules for that selection; a manual fill may need to be removed separately.

Related

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.