How to Highlight Duplicates in Excel — A Practical and Human Guide

In the land of spreadsheet applications, duplicate data is one of those quiet problems we will all face at least once. Perhaps you’re updating a contact list, reconciling your expenses or maintaining inventory — all at once there are duplicate entries that obscure the view and distort perspective. Fortunately, that is what something Excel can do very easily and give us some fantastic visual tools to get duplicates highlighted so we can see our issues in seconds instead of hours.

In this article, we’ll walk you through how to highlight duplicates in Excel step by step, explain why it matters, and share advanced tips so you feel confident every time you open a spreadsheet.

Why Highlighting Duplicates Matters

Imagine you’re looking at a long list of data — maybe a list of mutual fund transactions, such as the HDFC Mid Cap Opportunities Fund and its NAV trends (as seen on Moneycontrol). Without spotting repeated entries early, you might misinterpret trends or make mistakes in decisions. Highlighting duplicates visually lets you:

• Find repeated entries instantly
• Improve data accuracy
• Clean and prepare your dataset before analysis

Whether you’re a student, a small business owner, or a data analyst, duplicates can distort insights — and visual cues make them easy to catch and correct. Now, let’s jump into the how-to.

Step by Step: How to Highlight Duplicates in Excel

Excel’s built-in Conditional Formatting feature lets you change the style of cells based on rules. To highlight duplicate values:

1. Open Your Excel File

Start by opening the spreadsheet where you want to check for duplicates. Think of a list where entries might repeat — like names, IDs, or product codes.

2. Select the Data Range

Click and drag over the cells you want to check. If you want to scan an entire column, click the column header (like “A”) to select everything. This defines the area where Excel will look for duplicates.

3. Go to Conditional Formatting

At the top of Excel, click on the Home tab. In the ribbon, you’ll see a group called Styles. Click Conditional Formatting.

4. Choose Highlight Cells Rules

In the dropdown, hover over Highlight Cells Rules and then click Duplicate Values.

5. Choose How You Want to Highlight

You’ll see a small dialog box. Here you can choose:

✔ The type of rule — make sure Duplicate is selected

✔ The formatting — Excel offers color options (like light red fill with dark red text) or you can customize your own.

Once chosen, click OK.

Right away, Excel will highlight every cell in your selected range that contains values appearing more than once. This makes duplicates stand out and allows you to act fast — whether by correcting errors, removing them, or filtering them later.

Bonus Tips for Smarter Duplicate Handling

If you want to do more than just highlight duplicates, here are a few smart options you should know:

🔹 Highlight Only the Second and Later Occurrences

Sometimes the first instance of an entry is the correct one and only repeats afterward are problematic. You can create a custom rule with a formula such as:

=COUNTIF($A$2:$A2, A2)>1

This tells Excel to highlight a cell only if it has already appeared earlier in the list.

🔹 Highlight Duplicates Across Multiple Columns

If your data is in multiple columns and you want to highlight rows where two or more fields match exactly, use a formula-based rule like:

=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1

This highlights rows with matching values in both columns A and B.

🔹 Customize Your Formatting

Once you’ve applied conditional formatting, you can go back and change how duplicates look. Just go to Conditional Formatting > Manage Rules… and edit the style or range.

🔹 Remove Duplicates (Optional)

Excel also has a feature under the Data tab called Remove Duplicates. This actually deletes repeated rows — so always make a copy of your data before doing this.

Real-World Example: Why This Helps

Let’s say you’re tracking entries for a high-growth fund — like the HDFC Mid Cap Opportunities Fund — using daily NAV values from a site like Moneycontrol. By exporting data into Excel, you can quickly check if any NAV values were accidentally entered twice. Highlighting duplicates gives you a visual flag so you don’t include errors in your performance graphs or SIP analysis. (For example, the HDFC Mid Cap Fund’s growth trend can be misread if duplicate entries inflate averages.)

Tips for Common Issues

Even though Excel’s duplicate highlighting feature is handy, it’s not foolproof:

❗ Blanks are treated as duplicates. If your dataset has empty cells, Excel will flag them — so consider removing or filling them first.

❗ Formatting matters. Sometimes cells look identical but aren’t — one might be text, the other a number. Use Paste Special > Values to normalize them.

❗ Case sensitivity isn’t default. “apple” and “Apple” are treated as duplicates unless you use a case-aware method.

Learning how to find and highlight duplicates in Excel is one of those little skills that can put hours back into your work days, not to mention saving months or even years’ worth of frustration. With a couple of clicks, you can highlight duplicate entries, clean your data and make better decisions with accurate information.

So the next time your spreadsheet feels insurmountable, just remember: Excel’s conditional formatting is there for you — to help you quickly identify issues on sight. And after you’ve gotten the hang of basic highlighting, you can graduate to formula rules, multi-column checks and more advanced cleanup tools.