Find Duplicates in Excel: Shortcut Keys and a Quick Guide

How-to basics · Updated November 20, 2026

To find duplicate values in Excel for Microsoft 365 on Windows, select the cells to check, then press Alt, H, L, H, D in sequence. Choose a format in the Duplicate Values dialog and click OK. Excel highlights repeated values; it does not delete them. If the shortcut sequence does not work in your version, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

What You’ll Need

  • An Excel workbook containing the values you want to check. Save a copy first if you may later remove records.
  • A clear idea of what counts as a duplicate: the same value in one column, or a repeated value anywhere in a selected range. The built-in rule compares cell values, not entire records across several columns.
  • Excel for Microsoft 365 on Windows for the keyboard sequence below. Mac and Excel for the web users can use the menu path described in the steps.

Step-by-Step

  1. Decide what to compare. For duplicates in one field, such as customer email addresses, check only that column’s data cells. For example, select B2:B500 if B1 is a heading and the addresses begin in B2. Do not include the heading unless you deliberately want it considered as a value. If you select several columns, Excel looks for repeated values across the selected cells; it will not decide whether a complete row is a duplicate based on a matching combination of fields.

  2. Select the range. Click the first data cell and drag to the last one, or click the Name Box to the left of the formula bar, type a range such as B2:B500, and press Enter. A contiguous range is easiest to review. For a whole column, click its letter, but remember this includes the header and empty cells; selecting only the populated data range usually avoids confusing results.

  3. Open Duplicate Values with the Windows key sequence. With the range selected, press and release Alt, then H, L, H, and D. These are sequential Ribbon KeyTips, not five keys to hold down at once. In current Excel for Microsoft 365 on Windows, they open Home, Conditional Formatting, Highlight Cells Rules, and Duplicate Values. KeyTips can vary with the Ribbon or app version, so watch the letters shown on screen; if a letter differs, follow the visible KeyTip instead of guessing.

  4. Use the menu if the key sequence is unavailable. Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is also the practical route in Excel for Mac, where Windows Alt KeyTips do not have a consistent direct equivalent. Excel for the web can also use the Home conditional-formatting menus; browser and keyboard settings may affect shortcut behavior, so use the menu if pressing Alt does something else.

  5. Set the highlighting style. The Duplicate Values dialog has a choice for Duplicate or Unique and a format choice. Leave Duplicate selected to find repeated values. Pick a built-in fill and text color, or choose Custom Format if you need a different appearance, then click OK. The default formatting is often a light red fill with dark red text, but the available choices can depend on the app version.

  6. Review what Excel marks. Every occurrence of a repeated value in the selected range is highlighted, including the first occurrence as well as later ones. A value that appears just once is not highlighted. For instance, if an email appears in three cells, all three cells are marked; Excel does not designate only the second and third as duplicates. This behavior is useful for spotting groups but matters if you intend to keep the first record and remove only later copies.

  7. Check whether the matches are meaningful. Conditional formatting compares cell values, and it does not distinguish letter case in the way many people expect. For example, Excel generally treats Alex@example.com and alex@example.com as the same for this rule. Also inspect values that look alike but may differ because of spaces, punctuation, or stored data. A leading or trailing space can make an apparent match behave differently.

  8. Clear or change the rule when finished. To remove the highlighting without changing cell contents, select the same range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To adjust its formatting or range, choose Home > Conditional Formatting > Manage Rules, select the rule, and edit it. Do not use Delete or Remove Duplicates if you only mean to clear the color.

Optional formula check for one column: a formula can mark only later repetitions, rather than highlighting the first occurrence too. With the values in A2:A100 and the formula entered in B2, then filled down through B100, use =AND(A2<>"",COUNTIF($A$2:A2,A2)>1). It returns TRUE for a nonblank value after its first appearance and FALSE for its first appearance or a blank. The expanding end of the COUNTIF range is intentional: each row is checked against the values from A2 through the current row. This checks one column; adapt the range and row references if your data is elsewhere.

Common Mistakes

Expecting the shortcut to delete records. The Alt sequence opens a conditional-formatting rule, which only changes how matching cells look. It is safe for reviewing data, but it is not a removal command. To delete duplicates, use Data > Remove Duplicates only after deciding which columns define a duplicate and keeping a backup.

Selecting too much or too little. If you select only part of a table, a repeated value outside that range cannot be found. If you select the entire worksheet or include unrelated columns, identical values in different fields can be highlighted together. Choose the exact data range and the field you mean to compare.

Confusing repeated values with repeated rows. If two customer records share a city, the city may be highlighted even though the records are not duplicates. To identify repeated records, decide which combination of columns uniquely identifies a record, or use a helper check built for that combination. The built-in Duplicate Values option does not compare whole rows as a unit.

Assuming formatting is permanent data. Highlighting is conditional formatting, not a change to the cell’s value. Sorting, editing, or copying data can change which cells meet the rule, and copying formatted cells may carry the rule elsewhere. Check Manage Rules if the coloring appears in an unexpected range.

Tips

  • Keep a header out of the selection. Start with the first record, not the column name. This prevents a header from being treated like another value and makes the rule’s range easier to understand.
  • Use a distinct format. A strong fill color can make matches easy to scan, but avoid a color that is already used to signal another status. If your workbook is printed in grayscale, use a format with clear contrast or add a helper column.
  • Filter or sort to review a large list. The color itself does not group matching records. Once highlighted, you can use the column’s filter or sort options to examine the data, but sorting should be done on the full table so fields stay with the right records.
  • Normalize inconsistent data before judging it. Names, phone numbers, and imported text may contain inconsistent spaces or punctuation. Clean or standardize the values first if those differences should not distinguish records. Keep an untouched copy so you can compare the cleaned results with the original.
  • Separate finding from deleting. First mark and review matches, then decide whether they are truly redundant. When you do use Remove Duplicates, Excel removes rows based on the columns you select and keeps one instance; it does not provide a simple Ctrl+Z guarantee after saving and closing.

Troubleshooting

The shortcut opens a different command or does nothing. Make sure you are using desktop Excel for Windows, that the target range is selected, and that you press each KeyTip in sequence. Laptop function-key settings and other apps can affect keyboard input. Press Esc to close an unexpected menu, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. On Mac, use the menu path rather than expecting the Windows sequence.

No cells are highlighted. Confirm that the repeated entries are inside the selected range and that Duplicate, not Unique, is selected in the dialog. Check for extra spaces, different punctuation, or numbers stored as text. You can click two apparent matches and inspect the formula bar; if their underlying contents differ, the rule may correctly treat them as different. If the matches are in another column, create a rule for that column instead.

Cells that look unique are highlighted. Check the selected range for a second occurrence elsewhere, including a hidden row or a cell outside the visible area. The rule applies to its whole range, not just the rows currently on screen. Excel’s duplicate comparison is not case-sensitive, so capitalization alone may not make two text values unique. If you require case-sensitive matching, the built-in Duplicate Values dialog is not sufficient; use a specifically designed formula or data-cleaning method.

Color remains after you edit or clear cells. First check whether the rule still applies to the range: select a colored cell and open Home > Conditional Formatting > Manage Rules. If the rule is no longer wanted, clear it from the selected cells. If the formatting is ordinary manual fill rather than conditional formatting, Clear Rules will not remove it; use Home > Clear > Clear Formats carefully, since that clears other formatting too.

The same value is highlighted across unrelated fields. The rule was likely applied to multiple columns. Clear the rule from that selection, select just the intended column, and create it again. If the goal is to find identical complete records, use a method that compares the relevant fields together rather than applying Duplicate Values to a wide rectangular range.

Frequently Asked Questions

Is there one dedicated Excel shortcut key for duplicates?
There is no single universal key that instantly finds duplicates. On Windows desktop Excel, the sequential Ribbon KeyTips Alt, H, L, H, D open Duplicate Values after you select a range. The menu path is the fallback.

Does Excel highlight the first occurrence too?
Yes. The Duplicate Values conditional-formatting rule highlights every instance of a repeated value in the selected range, including the first. Use a running COUNTIF formula if you need to flag only later repetitions.

Can I find duplicates in more than one column?
You can select multiple columns, but the built-in rule then checks for repeated cell values across the selected cells. It does not identify a row as a duplicate only when several fields match together. For that, define the key columns and use a helper formula or a suitable data-cleaning workflow.

Does the shortcut work in Excel for Mac?
The Windows Alt KeyTip sequence is not a reliable Mac equivalent. Select the range and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Ribbon labels can vary slightly by app version.

How do I remove duplicates instead of highlighting them?
Make a backup, select the table, and choose Data > Remove Duplicates. In the dialog, select the columns that define a duplicate and confirm. Excel removes duplicate rows based on those selected columns, so review the selection carefully before accepting.

Can the rule distinguish uppercase and lowercase text?
No. The built-in duplicate rule is not case-sensitive, so values that differ only by capitalization are treated as duplicates. For a case-sensitive result, use a formula or another method designed for case-sensitive comparison.

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.