Sometimes you need more than a color highlight—maybe you want to count how many times a duplicate appears. That’s where the COUNTIF formula comes in. It’s a little like a bouncer at a club who keeps a tally of how many times someone tries to sneak in.
Type this into a new cell next to your list: =COUNTIF(A:A, A2). This tells Excel, “Hey, count how many times whatever is in cell A2 shows up in column A.” If you see a number bigger than 1, you’ve got a double (or triple) entry. Drag that formula down and instantly see which items are party crashers.
For example, if you’re tracking your vinyl record collection and “Abbey Road” shows up with a 3, you either really love The Beatles or you bought the same album twice. Time to return one.
How to Highlight Duplicates in Excel: 2 Easy Ways - Guiding Tech
Don’t Forget the Case Sensitivity Trap
Here’s a little curveball: Excel usually treats “apple” and “Apple” as different things. That’s like saying a Granny Smith and a Red Delicious aren’t both apples. If you want to catch those sneaky duplicates, you’ll need a slightly more advanced trick—but don’t panic.
One workaround is the EXACT function combined with Conditional Formatting. Or, you can simply use the “Remove Duplicates” button under the Data tab, which does a quick sweep. Just know that perfectionists beware: that button will delete the extras permanently, so save a backup first.
