How to highlight duplicates in Excel, and what the rule counts

The same rule on the web, on a Mac, and with Quick Analysis

“In the Style list, choose Classic”

That is the step a Mac adds to the advanced rule, on Microsoft’s Mac-only page Filter for or remove duplicate values: New Rule, Classic in the Style list, then Format only unique or duplicate values. For the quick rule the Mac pages print the Windows names. Highlight patterns and trends: on the Home tab select Conditional Formatting, point to Highlight Cells Rules, select Duplicate Values, and next to values in the selected range choose unique or duplicate. The page on filtering then has you choose the options in the New Formatting Rule dialog box. Both pages list Excel for Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac.

Excel for the web. The Web tab of Use conditional formatting to highlight information prints Home, Styles, Conditional Formatting, Highlight Cell Rules, Duplicate Values, with Cell where the Windows menu says Cells. Then you enter the values you want to use and select a fill, text or border colour. On the web the rules live in a Conditional Formatting task pane, which Manage Rules opens. The Web tab of the page on unique and duplicate values covers removing them and nothing else.

Quick Analysis, on Windows. Select the data, then the Quick Analysis button at the lower-right corner of the selection, or press Ctrl+Q. On its Formatting tab, the Windows tab says, Duplicate and Unique are offered when the selection contains only text; for numbers, or numbers and text, the list is Data Bars, Colors, Icon Sets, Greater, Top 10% and Clear. So a column of numbers goes through the ribbon; that advice is ours.

A key sequence. Wall Street Prep, a training company whose guide was read for this one, gives Alt, H, L, H, D, each key pressed on its own, for the Windows ribbon. No Microsoft page read for this guide prints that sequence.

A formula rule: COUNTIF for one column, COUNTIFS for whole rows

Microsoft prints the formula itself, in a tip on the Windows tab: for duplicates in A2:A400, a COUNTIF formula rule with =COUNTIF($A$2:$A$400,A2)>1, where the tip has a space before the 1. The reason it gives is narrow. With a proofing language such as Japanese, Duplicate Values treats halfwidth and fullwidth characters as the same, and to tell them apart it points to the formula. The Mac page on formulas uses the same pattern, =COUNTIF($D$2:$D$11,D2)>1, on a list of ten cities, where it marks Seattle and Spokane, and Google’s page prints it for Sheets.

Where to type it. On Windows: Home, Conditional Formatting, Manage Rules, New Rule, Use a formula to determine which cells to format, then the formula in Format values where this formula is true, and Format. On the web, Formula in the Rule Type list. On a Mac: New Rule, Classic in the Style box, Use a formula to determine which cells to format, and Customised format in the Format with box. The range carries dollar signs and the single cell does not, so each cell is counted against the whole range; Wall Street Prep explains the anchors the same way. More on references is on the page on conditional formatting with a formula.

Rows that repeat across columns. COUNTIF takes one criterion, and its page says to use COUNTIFS for more. To mark rows whose values in both A and B repeat, select A2:B400 and use =COUNTIFS($A$2:$A$400,$A2,$B$2:$B$400,$B2)>1. That formula is ours, written to the syntax on Microsoft’s COUNTIFS page, and it has not been tested in Excel for this guide; Acuity Training, another training company read for this guide, prints the same pattern with =2, which marks the rows that appear exactly twice. The dollar before A and B keeps every cell of a row on the same test, so the whole row takes the colour; that reading is ours. Excel University, a third, joins the columns into a helper column with CONCAT instead.

Every repeat but the first. Excel University’s =COUNTIF($B$2:$B2,$B2)>1 counts from the top of the list down to the current row, so the first occurrence stays white and the later ones are marked. That method is theirs, not Microsoft’s.

What Excel counts as a duplicate, and where the rule does not reach

“A comparison of duplicate values depends on what appears in the cell—not the underlying value stored in the cell.”

That is Microsoft’s page on unique and duplicate values, for five products and no Mac, and its example is a date shown as 3/8/2006 in one cell and Mar 8, 2006 in another: two unique values. The Mac page says the same with 12/8/2017 and Dec 8, 2017, and adds two cases. Formulas that differ and return the same value, =2-1 and =3-2, are duplicates when the format is the same; and “If the same value is formatted using different number formats, they are not considered duplicates.” Both pages write this in their opening, before the sections on filtering, removing and conditional formatting; that it holds for the highlight rule too is this guide’s reading.

Asterisks and question marks. The Windows tab of Use conditional formatting to highlight information: the Unique and Duplicate Values rules read * and ? as wildcard characters, even when they are used in a formula in a cell. So a cell holding a* can match cells that are not equal to it; that consequence is ours, and Acuity Training reports the case of a question mark. COUNTIF reads wildcards in its criteria too, by its own page, which says a tilde in front finds an actual question mark or asterisk. No page read for this guide applies the tilde to a duplicate rule.

Capitals. The COUNTIF page: “Criteria aren’t case sensitive.” So Apple and apple count as one value in the formula rule, by this guide’s reading. No Microsoft page read for this guide says whether Duplicate Values tells them apart; Acuity Training writes that neither the rule nor COUNTIF does.

Spaces and long text. The COUNTIF page warns that leading and trailing spaces, mixed straight and curly quotation marks, or nonprinting characters can make it return an unexpected value, and names CLEAN and TRIM; the guide on removing spaces in Excel covers both. It also returns incorrect results for strings longer than 255 characters.

A PivotTable. The Values area is out of reach: the Windows tab says you cannot format such fields by unique or duplicate values. Other cells of a pivot table are in the list of what you can select.