How to Highlight Duplicates in an Excel Row with Rules

How-to basics · Updated November 18, 2026

To highlight repeated values in one Excel row, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and pick a format. If you need Excel to check each row separately across a larger block, create a formula-based conditional formatting rule instead. The distinction matters: the built-in rule checks duplicates across the whole selected range, while the formula can reset the check at each row.

What You’ll Need

  • A workbook open in Microsoft Excel for Microsoft 365 on Windows. Excel for Mac and Excel for the web have similar conditional formatting features, though some dialog labels or layouts may differ.
  • The row or range of rows you want to check, with a clear idea of whether duplicates should be compared within each row or across the entire selection.
  • For the formula method, the first cell and last column of the range you plan to format. The example below checks columns A through H.

Step-by-Step

  1. Choose the scope of the check. If you want to identify repeated values within just one row, use the built-in Duplicate Values rule on that row’s data cells. For example, if a record is in cells B4:J4, select B4:J4. Avoid selecting the row number at the far left or the entire worksheet row unless you really intend to include those cells. If you want to check multiple rows independently, use the formula method in the next steps. Selecting a multirow block and applying the built-in rule will find values repeated anywhere in that block, including values that occur in different rows.
  2. Apply the built-in rule for one row. With the data cells selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a preset highlight format, or choose Custom Format to set a fill, font, or border. Click OK. Excel applies the format to each cell whose value occurs more than once in the selected range. If you need a different color later, select the same cells and edit the rule through Conditional Formatting > Manage Rules.
  3. For a multirow list, select the complete data area. Suppose the values to compare run from A2 through H100. Select A2:H100, including all the rows and columns you want checked. Do not include a heading row unless you want headings included in the duplicate check. Keeping the selected range limited to the actual data makes the rule easier to understand and prevents unrelated cells from being highlighted.
  4. Open a formula-based rule. On the Home tab, choose Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Use a formula to determine which cells to format. Enter the formula below, assuming the selected range is A2:H100, with A2 as its top-left cell. The formula checks each cell against the other cells in its own row and ignores empty cells: =AND(A2<>"",COUNTIF($A2:$H2,A2)>1)
  5. Set the highlight format. Click Format in the rule dialog, choose a fill color or another visible style on the relevant tabs, and click OK. Click OK again to create the rule. Excel highlights each nonblank value that appears at least twice in its row. For example, if row 2 contains “Red, Blue, Red” in A2:C2, both cells containing Red are highlighted; Blue is not. A value in row 3 is compared with row 3, not with row 2.
  6. Confirm the rule applies to the intended cells. Open Home > Conditional Formatting > Manage Rules. Check the Applies to range. For the example, it should be =$A$2:$H$100. If the range is wrong, edit it, then apply the change. In the rule formula, A2 is deliberately relative so it adjusts for each cell, while $A and $H keep the comparison columns fixed. The row number is deliberately not locked, so Excel checks the corresponding row as the rule moves down.
  7. Test with a known example. Temporarily enter a repeated value in two cells on the same row and confirm that both highlight. Then put that value in a different row only once; it should not highlight under the per-row rule. Remove the test entries when you are done. This quick check can reveal a misaligned formula reference or an Applies to range that starts in the wrong cell.

In Excel for Mac, select the cells and use the Home tab’s Conditional Formatting controls to reach the duplicate-value rule or create a formula rule. The layout can differ from Windows, but the rule logic is the same. Excel for the web also offers conditional formatting controls on Home; if a formula rule or a particular dialog option is unavailable in your version, open the workbook in the desktop app to create or adjust it.

Common Mistakes

Using the built-in rule on several rows when you mean to compare within each row. Duplicate Values evaluates the selected range as a whole. For example, if “North” appears once in row 2 and once in row 3, the built-in rule may highlight both because the value repeats in the selection. Use the formula rule when duplicates should be identified only among cells in the same row.

Locking the wrong references in the formula. For a selection beginning at A2, the formula must refer to A2 as the cell being evaluated. If you write $A$2 as the final COUNTIF argument, Excel will keep checking the value in A2 instead of checking each cell in the selection. Similarly, making the comparison range $A$2:$H$2 fixes it to row 2, so lower rows are checked against the wrong data. Keep the columns fixed and let the row adjust: $A2:$H2.

Including headings or unrelated columns. A heading that appears more than once can be marked as a duplicate, and including extra columns can create matches you did not intend. Select only the cells that belong in the comparison. If the data has a different number of columns in different sections, create separate rules with the appropriate ranges.

Expecting blank cells to remain unformatted with every formula. COUNTIF can treat blank cells as matching other blank cells. The example formula uses A2<>"" to exclude empty cells. If blanks are meaningful entries in your data and you want repeated blanks highlighted, remove that condition and use a formula suited to that purpose.

Confusing the displayed appearance with the stored value. Conditional formatting evaluates cell contents, not just what the number format displays. Two numbers may look the same because of rounding but have different underlying values. Conversely, dates are stored as numbers, and values displayed in different formats may compare as the same value.

Tips

  • Choose the rule based on the question. Use Duplicate Values for “Does this value appear more than once anywhere in this selected range?” Use the formula method for “Does this value appear more than once in this particular row?” This choice prevents many confusing results.
  • Keep one consistent range shape. If each record has the same set of columns, such as A:H, apply the formula rule across the full block. When you add rows below the current Applies to range, check whether the rule includes them; extend the range if needed.
  • Pick a format that remains readable. A light fill with dark text is often easier to scan than a strong fill that obscures text. If the cells already use important colors, use a font or border format instead, or choose a new color that does not conflict with your existing meaning.
  • Remember that text matching is generally not case-sensitive. COUNTIF treats “East” and “east” as the same criterion for this purpose. Leading or trailing spaces can make otherwise similar text different, so values such as “East” and “East ” may not match. If the source data is inconsistent, clean or standardize it before relying on the highlight.
  • Use the rule as a visual check, not as a data change. Conditional formatting does not delete or combine duplicate values. It changes how qualifying cells look, and the formatting updates when cell values change. Review the highlighted cells before editing records.
  • Keep a simple test row nearby when building a rule. A row with one repeated value and one unique value makes it easy to verify the logic. After checking it, remove the test data or place the example outside the real data range.

Troubleshooting

Nothing highlights. Check that the rule’s Applies to range covers the cells you are looking at. For a formula rule, make sure its first reference matches the top-left cell of that range. If the range starts at B4, the formula should be written for B4, not A2. Also confirm that the duplicate values are actually equal; extra spaces or different underlying numbers can prevent a match.

Cells highlight in different rows when that is not expected. This usually means the built-in Duplicate Values rule was applied to a multirow selection. Replace it with a formula rule that checks each row independently. Check that the formula’s comparison range uses a relative row, such as $A2:$H2, and that the Applies to range begins on row 2.

Only one cell in a repeated pair highlights. Review the formula’s cell reference and the rule range. The first cell reference in the formula should correspond to the top-left cell of Applies to. If you accidentally fixed that reference with dollar signs, Excel may test one cell repeatedly rather than evaluating each cell in turn.

Blank cells are highlighted. Edit the formula rule and include a nonblank check, as in the example. If cells look empty but contain spaces or formulas returning an empty string, they may need to be cleaned or handled separately; a cell containing a space is not truly empty.

A rule appears to have no effect because another rule is involved. Open Home > Conditional Formatting > Manage Rules and inspect the rules that apply to the range. Another rule may use a similar format, or a rule order or Stop If True setting may affect the final appearance. Adjust the conflicting rule or choose a format that is easy to distinguish.

The highlighting stops after new rows are added. Open Manage Rules and extend the Applies to range to include the new rows. If the data is an Excel table, check how the rule behaves as table rows are added; if it does not expand as expected, update the range or create the rule for the expanded data area.

Frequently Asked Questions

Can Excel highlight duplicates across a row without a formula?
Yes. Select the cells in that one row and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is the quickest option when the selected range is only the row you want to check.

How do I highlight duplicates separately in every row of a table?
Select the data block and create a formula-based conditional formatting rule that compares each cell with the other cells in its own row. For a block from A2:H100, the example rule in this guide checks each row separately and skips blanks.

Will this rule delete or remove duplicate values?
No. It only changes the appearance of cells that meet the rule. To remove records, use a separate review process; highlighting is useful for finding candidates, but it does not decide which entry should be kept.

Does Excel treat uppercase and lowercase text as different?
Not with the COUNTIF formula shown here. It is not case-sensitive, so “Apple” and “apple” count as a match. If capitalization must distinguish values, you need a different formula-based approach.

Why are two values that look identical not highlighted?
They may contain different hidden spaces or different underlying values. For instance, two decimal numbers can display as 5.0 while one is actually 5.04. Compare the formula bar or increase displayed precision, and standardize the data if appropriate.

Can I use these steps in Excel for the web?
Excel for the web supports conditional formatting, and many workbooks can use the same basic approach. If you cannot find the formula option or need to edit a rule that the web interface does not expose, use Excel for Microsoft 365 on Windows or Mac to make the change.

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.