How to Color Code Names in Excel with Conditional Formatting

How-to basics · Updated November 20, 2026

To color code names in Excel, select the name cells, then use Home > Conditional Formatting to create a rule for each name. For exact matches, choose a formula rule such as =A2="Maya"; Excel will apply the chosen color to matching names and update it when the names change. You can also use the Fill Color button for a quick, manual color that does not update automatically.

What You’ll Need

  • Excel for Microsoft 365 on Windows, or a version with Conditional Formatting.
  • A worksheet with names in a consistent range, such as A2:A100. A header in A1 is fine; exclude it from the selection.
  • A plan for what each color means. For example, green for one team and blue for another. If you are assigning a separate color to every person, choose the colors before you create the rules.

Save a copy first if the workbook is important. Conditional formatting is reversible, but a backup makes it easier to recover the original layout if you change many rules.

Step-by-Step

  1. Check and select the name range. Make sure the names are in one column and that the cells do not include a heading. Click the first name cell and drag to the last one, or click the Name Box to the left of the formula bar, type a range such as A2:A100, and press Enter. If the names are in a different column, use that column’s address instead.
  2. Decide whether you need a quick color or a rule. To color a few names once, select their cells and go to Home > Fill Color (the paint-bucket icon) to pick a color. This is a manual fill: if a name is edited or moved, the fill stays where it was. For a list that changes, use Conditional Formatting so Excel can apply or remove the color based on the cell value.
  3. Create a simple rule for text that contains a name. With the range selected, choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter the name, select a suggested format or choose Custom Format to set a fill color, then click OK. This is convenient, but it matches text containing those characters, not necessarily an exact name. A rule for Ann, for example, can also match Annette.
  4. Use an exact-match formula when names must match completely. Select A2:A100, then choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format. With names in A2:A100 and A2 as the first selected cell, enter =A2="Maya". Click Format, choose the Fill tab and a color, and click OK to close the Format Cells dialog. Click OK again to create the rule. The formula checks each cell against Maya and avoids matching a longer name such as Mayanne.
  5. Repeat the rule for each name or group. To give Leo a different color in A2:A100, create another formula rule with =A2="Leo" and choose a different fill. Keep the range the same for each rule. If several names should share one color, you can make a separate rule for each name using the same fill, or organize the data into categories and color by category. Excel does not automatically assign a unique color to every new, distinct name.
  6. Check the rule’s range and formula reference. Open Home > Conditional Formatting > Manage Rules. In the Show formatting rules for menu, choose This Worksheet if needed. Confirm that Applies to covers the intended cells, such as =$A$2:$A$100. For a rule applied to A2:A100, the formula should start with A2, not A1 or A3, because Excel adjusts the row reference as it checks down the range. Click Apply or OK to keep changes.
  7. Color a whole row based on the name in one column, if useful. Suppose names are in column A and the table runs from A2 to D100. Select A2:D100, create a new formula rule, and enter =$A2="Maya". Choose a fill and confirm the rule. The dollar sign before A keeps the check in the name column while the row number changes for each row. Repeat with another name and color. Keep the formula’s row number aligned with the first row in the selected range.
  8. Check the result with real examples. Look at a matching name, a different name, and a similar-looking name. Confirm the intended cells or rows have the correct fills. Edit a test cell to another name and check whether the formatting changes; conditional formatting should respond. If the color appears on a heading or an unintended column, revisit the Applies to range in Manage Rules.

The menu names above describe Excel for Microsoft 365 on Windows. Excel for Mac has Conditional Formatting on the Home tab as well, although dialog layouts can vary slightly by release. In Excel for the web, use the Home tab’s Conditional Formatting menu; if a formula-based rule or its controls are unavailable in your current web experience, open the workbook in the desktop app to create or adjust it.

Common Mistakes

  • Using Text that Contains when you mean exact match. That rule can color longer names that include the entered text. Use a formula rule with an equals comparison for an exact name.
  • Starting the formula on the wrong row. If the selected range begins at A2, a formula referring to A1 evaluates the wrong row. Match the formula’s starting row to the top row of Applies to.
  • Locking the row by mistake. In a whole-row rule, =$A2="Maya" locks the name column but lets the row change. Using =$A$2="Maya" makes every row check A2, which usually is not what you want.
  • Applying rules to only part of the list. New names below the formatted range will not receive the rule. Expand Applies to or convert the data range to an Excel table using Home > Format as Table; check the rules when adding rows.
  • Using indistinguishable fills. Several rules with very similar colors are difficult to scan. Choose fills with clear contrast and make sure the text remains readable.

Tips

  • Write down what colors mean. A small legend near the table helps other people understand the categories. Use a label as well as color when the information matters, since color alone may not be accessible to everyone.
  • Use a short palette. A handful of distinct colors is easier to recognize than a different shade for every person. If you have many names, color by team, status, or another useful category instead.
  • Keep the names consistent. Extra spaces, alternate spellings, and different punctuation can make a name fail an exact-match rule. Standardize entries before adding rules, and use Excel’s Find feature to locate unexpected spellings.
  • Prefer conditional formatting for changing lists. It responds when the cell value changes, unlike a manually applied fill. If a name is added, make sure the rule’s Applies to range includes the new row.
  • Review conflicts if a cell has multiple rules. In Manage Rules, check rule order and the Stop If True option where shown. Simplifying overlapping rules is often clearer than relying on a stack of competing formats.

Troubleshooting

  • The name does not change color: Open Manage Rules and check that the formula text matches the cell exactly, including spaces and spelling. Confirm the Applies to range includes the cell. Verify that the first cell in the selected range and the formula’s row reference line up.
  • The wrong name or a longer name changes color: Replace a Text that Contains rule with an exact-match formula rule. Check whether the cell has trailing spaces or a different spelling. Select the cell and inspect its contents in the formula bar.
  • The wrong row is colored: For a whole-row rule, make sure the formula uses a dollar sign before the name column but not before the row number. For example, with names in A and the selected table starting on row 2, use =$A2="Maya".
  • The color appears in the wrong column or too many cells: In Manage Rules, inspect Applies to. A name-only rule might apply to =$A$2:$A$100, while a whole-row rule could apply to =$A$2:$D$100. Correct the range and use a formula reference that checks column A.
  • A different fill overrides the one you expect: Look for another conditional formatting rule on the same cells, including duplicate rules. Remove unnecessary rules or adjust their order in Manage Rules. Also check whether a manual fill is making the worksheet harder to interpret.
  • The color does not follow a newly added name: Extend the rule’s Applies to range to include the new cells, or use a table and verify that the formatting rule covers the table rows. Creating a rule for one name does not create rules for other names automatically.

Frequently Asked Questions

Can I assign a different color to every name automatically?
Not with one standard conditional formatting rule. Create a rule for each name or category and choose its fill. For a long list, a smaller set of category colors is usually easier to manage.

How do I color all duplicate names the same way?
Select the name range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Select a format and click OK. This identifies repeated values; it does not give each distinct name its own separate color.

Can I color the entire row based on a name?
Yes. Select the full table range and create a formula rule that checks the name column. For names in A and a selected range beginning on row 2, use =$A2="Maya", then choose the desired fill.

Will the color update if I change a name?
Conditional formatting recalculates when the value changes, so the old name’s rule stops matching and a rule for the new name can apply. A manually applied Fill Color does not move or update automatically.

Can I use the same color for several names?
Yes. Create a rule for each name and choose the same fill, or color a category field instead. Using categories can mean fewer rules when a list has many people.

Can I remove the color without deleting the names?
Yes. To remove a conditional formatting rule, select its range and choose Home > Conditional Formatting > Clear Rules, then choose whether to clear rules from selected cells or the entire sheet. To remove a manual fill, select the cells and use Home > Fill Color > No Fill.

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.