How to Handle Duplicates Based on Multiple Columns in Excel (Step-by-Step Guide)

Have you ever found yourself working with a large dataset in Excel and struggling to identify duplicates—especially when they’re not just in one column, but spread across several? For example, maybe you’re managing customer records, inventory lists, or transaction data, and you notice that some rows look identical based on a few key columns. You’re left wondering:

• How do I check for duplicates when they depend on multiple columns?
• Can I highlight or delete these duplicates without accidentally removing unique data?

If you’ve been nodding along, you’re in the right place! This problem is common when dealing with real-world data. Excel’s built-in tools often fall short, requiring creative yet simple solutions to tackle such challenges effectively.

In this guide, I’ll show you exactly how to identify duplicates based on multiple columns, highlight or remove them as needed, and even provide tips to handle future data changes seamlessly.

Step 1: Combine Columns into a Helper Column

What is a Helper Column?

A helper column is an additional column you create temporarily to simplify your work. It doesn’t change your original data but acts as a manual tool to calculate or analyze specific results. In this case, we’ll use it to create unique combinations of values across multiple columns.

Creating the Helper Column

1. Insert a new column next to your dataset and name it “Combined.”
2. Use one of the following formulas to combine values across your target columns:

TEXTJOIN (Excel 2016+):

=TEXTJOIN(” “,TRUE,B2:E2)

Pros:

• Easily adds a delimiter (e.g., commas, spaces) between values.
• Ignores blank cells automatically when TRUE is used.
• Ideal for datasets with irregular data.

CONCATENATE (Older Excel versions):

=B2&” “&C2&” “&D2&” “&E2

Pros:

• Simple to use in older Excel versions.
• Directly combines values without adding delimiters.
  Cons:
• Cannot handle blank cells or delimiters automatically.

When to Use Each

• Use TEXTJOIN if you need delimiters or have blanks in your dataset.
• Use CONCATENATE for basic combinations when working in older Excel versions.

Drag the formula down to combine all rows into unique strings.

Step 2: Use COUNTIF to Identify Duplicates

What is COUNTIF?

The COUNTIF function is one of Excel’s most versatile tools for analyzing data. It works by counting how many times a specific value appears within a given range. In our case, the range will be the helper column we created, which combines values from multiple columns.

Why Use COUNTIF for Duplicates?

When dealing with duplicates, we need a way to flag rows that appear more than once. By counting the occurrences of each combined value, COUNTIF gives us a clear indicator:

• Value = 1: The row is unique because the combined value appears only once in the dataset.
• Value > 1: The row is a duplicate because the combined value appears multiple times.

Why Does a Value Greater Than 1 Indicate Duplicates?

A duplicate means that the same combination of values exists in more than one row. By counting how many times each combination appears, we can determine whether it’s unique or not.

Using COUNTIF simplifies the process of identifying duplicates by assigning each row a clear, quantifiable result. We can then use these results to either highlight duplicates for review or remove them entirely.

Steps

1. Add a new column named “Duplicate Check.”
2. In the first cell of this column, use the formula:

=COUNTIF($F$2:$F$11,F2)

This checks how often the value in “Combined” appears in the range $F$2:$F$11.

3. Drag the formula down the column to calculate duplicate counts for all rows.

• Result = 1: Unique row.
• Result > 1: Duplicate row.

Step 3: Highlight or Delete Duplicates

Option 1: Highlight Duplicates

1. Select the “Duplicate Check” column.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format
3. Use this formula:

=H2>1

4. Choose a format (e.g., fill color) and apply the rule.

Option 2: Delete Duplicates

When it comes to deleting duplicates, it’s essential to be cautious. More often than not, you don’t want to remove all instances of duplicate values—doing so could accidentally delete useful data. Instead, the goal is usually to keep one instance of each duplicate and remove only the extras, leaving behind a single unique row.

Let’s break this into two approaches based on your needs:

Scenario 1: Delete All Instances of Duplicates

If your goal is to completely remove any rows that have duplicates (including the original), follow these steps:

1. Apply a filter to the “Duplicate Check” column.
2. Filter for values greater than 1 in the column.
3. Select all visible rows in the filtered range and delete them.

This approach removes every row associated with duplicates, leaving only rows where the value appears once in the dataset.

Scenario 2: Keep One Instance of Each Duplicate

If you want to remove the extras but retain one instance of each duplicate combination, follow these steps instead:

1. Apply a filter to the “Duplicate Check” column.
2. Filter for values greater than 1 in the column.
3. Sort the dataset by the “Combined” column or another identifying column (like a timestamp) to group duplicate rows together.
4. Manually or using conditional logic, keep the first occurrence of each duplicate and delete the rest.
   • One way to automate this is to add an additional helper column using this method:

Formula for Flagging First Occurrences

1. Add a helper column labeled “Keep or Delete Row”
2. Use this formula:

=IF(COUNTIF($F$2:F2, F2)=1, “Keep”, “Delete”)

Explanation:

• $F$2:F2 dynamically expands the range as the formula is copied down.
• F2 is the cell containing the combined unique identifier in each row (e.g., “Combined 2” in your example).
• The formula checks if the current instance is the first occurrence by comparing the count in the range $F$2:F2. If the count is 1, it flags the row as “Keep.” For all subsequent duplicates, the count will be greater than 1, so they are marked “Delete.”

Steps After Applying the Formula:

1. Filter the “Keep or Delete Row” column for “Delete.”
2. Delete all visible rows.

Why Keep One Instance of Each Duplicate?

In most scenarios, keeping one instance ensures your data remains complete while removing redundancies. For example, if you’re cleaning customer records or transaction logs, you’ll likely want to keep the first or most recent occurrence of a duplicate entry rather than deleting all duplicates indiscriminately.

By carefully choosing the right approach, you can ensure that your data remains accurate and complete without unnecessary loss of information.

Conclusion

Handling duplicates based on multiple columns is a common but often frustrating challenge in Excel. By following the techniques outlined in this guide, you now have a robust toolkit to address this issue effectively. From creating helper columns with powerful functions like TEXTJOIN and CONCATENATE, to leveraging COUNTIF for precise duplicate detection, you can confidently identify, highlight, or delete duplicates with ease.

Importantly, we emphasized the critical decision of whether to delete all duplicates or retain one unique instance—a distinction that can make or break the integrity of your data.

Armed with these strategies, you’ll not only save time but also ensure your datasets remain accurate and well-organized.

So the next time you’re faced with a messy spreadsheet, remember: Excel’s flexibility, paired with smart problem-solving techniques, can handle even the trickiest data challenges.

Have your own tips or questions about managing duplicates? Share them in the comments—we’d love to hear your insights!