How to Search for Duplicates in Excel: A Step-by-Step Guide

Hey there, fellow Excel enthusiasts! As a CMO of a busy company, I'm always looking for ways to organize and streamline my workflow. One of the most common issues I face in Excel is dealing with duplicate data. If I don't spot the duplicates, it can throw off my analysis and give me inaccurate results. That's why I've put together this step-by-step guide to help you search for duplicates in Excel like a pro!

Step 1: Select Your Data Range

The first step in finding duplicates is to select the range of data you want to search. If you're not sure, select the entire sheet by clicking on the top left corner of the worksheet (the gray box above the row numbers and to the left of the column letters).

For a more specific range, click on the first cell of the range you want to search and drag your cursor down and across until you have selected all the necessary cells.

Step 2: Click on "Conditional Formatting"

Once your range is selected, click on "Conditional Formatting" in the "Home" tab of the Excel ribbon.

Step 3: Choose "Highlight Cells Rules"

After clicking on "Conditional Formatting", select "Highlight Cells Rules" from the dropdown menu.

Step 4: Click on "Duplicate Values"

Under "Highlight Cells Rules", select "Duplicate Values".

Step 5: Choose Your Formatting Style

Once you've selected "Duplicate Values", a new dialog box will appear. Here, you can choose how you want to format the duplicates. You can leave the default options selected or choose a different formatting style.

Step 6: Click "OK"

Click "OK" to apply the formatting style to your duplicates. Excel will highlight all the duplicate values in the selected range.

Step 7: Remove or Keep the Duplicates

Now that you've identified the duplicates, you have two options: you can either remove them or keep them. If you want to remove the duplicates, click on the "Data" tab and select "Remove Duplicates".

If, however, you want to keep the duplicates, you can use Excel's "IF()" function to highlight them in a separate column.


Well, there you have it, folks! A step-by-step guide to finding and dealing with duplicate values in Excel. This is just one of the many amazing features that Excel has to offer, and I hope this guide has helped you become more proficient in your Excel skills. Stay tuned for more tips and tricks to improve your productivity in Excel!

By clicking “Accept”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.