Method 1: Highlight Duplicates with Conditional Formatting

This is the best and safest way to start. It doesn’t change or delete any of your data; it simply applies a color to cells that contain duplicate values, making them easy to spot. This is perfect for reviewing your data before taking further action.

1. Select the data you want to check. Click and drag your mouse to highlight the cells. You can select a single column, multiple columns, or a whole table. A quick way to select a whole column is to click its letter header (like A, B, C). For a block of data, click the top-left cell, hold the Shift key, and then click the bottom-right cell.
2. Navigate to the Home tab on the Excel ribbon at the top of the screen.
3. In the "Styles" group, click on Conditional Formatting. A dropdown menu will appear.
4. Hover over Highlight Cells Rules, and from the next menu that appears, click on Duplicate Values.
5. A small dialog box will pop up. The first dropdown should already say "Duplicate." In the second dropdown, "Values with," you can choose the formatting style. "Light Red Fill with Dark Red Text" is the default and is very visible. Click OK.

Immediately, all cells in your selection that contain duplicate values will be highlighted with the color you chose. You can now visually scan your data to see where the duplicates are. Note that this tool also allows you to highlight "Unique" values by changing the first dropdown in the dialog box.

Method 2: Filter Data to Show Only Duplicates

After highlighting duplicates using Method 1, you might want to view only the duplicate rows together. Filtering allows you to temporarily hide all the unique data, making it easier to review or manually delete the duplicate entries.

1. First, ensure you have highlighted duplicates using the Conditional Formatting steps above.
2. Select the header row of your data. If you don't have a header row, select the first row of your data.
3. Go to the Data tab on the Excel ribbon.
4. In the "Sort & Filter" group, click the large Filter icon. Small dropdown arrows will appear in each header cell of your selected data.
5. Click the filter arrow for the column where you highlighted duplicates.
6. In the filter menu, hover over Filter by Color.
7. Select the fill color you used for highlighting duplicates (e.g., the light red color).

Your data table will instantly hide all rows that are not highlighted, leaving you with a clean list of just the duplicate entries. From here, you can decide which ones to edit or delete manually. To see all your data again, go back to the Data tab and click the Filter icon to turn it off.

Method 3: Permanently Remove Duplicates

When you're certain you need to delete duplicate rows, Excel has a powerful built-in tool for the job. This action is permanent once you save the file, so be absolutely sure you're working on a backup copy.

1. Click any single cell inside your data table or list. Excel is usually smart enough to detect the entire range of your data automatically.
2. Go to the Data tab on the ribbon.
3. In the "Data Tools" group, click on Remove Duplicates.
4. A dialog box will appear. It will list all the columns in your data, with checkboxes next to each one. If your data has headers, make sure the "My data has headers" box is checked.
5. This is the most important step: You must tell Excel what defines a duplicate.
   • If you want to remove rows that are identical across all columns, leave all columns checked.
   • If a duplicate is defined by just one column (e.g., an email address), click Unselect All and then check the box for only the "Email" column. This will keep the first row with a specific email address and delete all other rows that have the same email, regardless of what's in the other columns.
6. Once you've selected the correct columns, click OK.

Excel will then scan your data, remove the duplicate rows based on your criteria, and show a message telling you how many duplicate values were found and removed, and how many unique values remain. This tool always keeps the first instance of a value it finds (from top to bottom) and deletes the subsequent ones.

Method 4: Use a Formula to Find Duplicates (Advanced)

For more control, you can use a formula to identify duplicates. This method doesn't delete anything; it just flags duplicates in a new column, giving you more information to work with. We'll use the `COUNTIF` formula.

1. Add a helper column. Go to the first empty column to the right of your data. In the header row, give it a name like "Duplicate Check."
2. Let's assume the data you want to check for duplicates is in column A, starting from cell A2 (since A1 is the header). In the first cell of your helper column (e.g., D2), type the following formula: =COUNTIF(A:A, A2)
3. Breakdown of the formula:
   • =COUNTIF(...) is the function that counts cells based on a given condition.
   • A:A is the range to check. This tells Excel to look at the entire Column A.
   • A2 is the criteria. This tells the function to count how many times the value from cell A2 appears in the range (A:A).
4. Press Enter. The cell will now show a number.
5. Copy the formula down the column. Click on the cell with the formula (D2). Move your cursor to the small green square at the bottom-right corner of the cell (the fill handle) until it turns into a black plus sign. Double-click it. The formula will automatically copy all the way down your helper column.

Now, look at the results. Any row with a "1" in the "Duplicate Check" column is unique. Any row with a number greater than 1 is a duplicate. You can now filter this column to show only values greater than 1 to see all your duplicates grouped together.

Document what changed

Save the final settings, decisions, or checks so the same process is easier to repeat later.