How to Find Duplicate Values ​​in an Excel Table Column

A table with identical values ​​is a serious problem for many Microsoft Excel users. Repetitive information can be removed using the tools built into the program, bringing the table to a unique look. How to do it correctly will be discussed in this article.

Method 1 How to check the table for duplicates and remove them using the Conditional Formatting tool

So that the same information is not duplicated several times, it must be found and removed from the table array, leaving only one option. To do this, you need to do the following steps:

1. Use the left mouse button to select the range of cells that you want to check for duplicate information. If necessary, you can select the entire table.
2. At the top of the screen, click on the “Home” tab. Now, under the toolbar, an area with the functions of this section should be displayed.
3. In the “Styles” subsection, left-click on the “Conditional Formatting” button to see the possibilities of this function.
4. In the context menu that appears, find the line “Create a rule …” and click on it with LMB.

5. In the next menu, in the “Select the type of rule” section, you will need to select the line “Use a formula to determine the formatted cells.”
6. Now, in the input line below this subsection, you must manually enter the formula from the keyboard “=COUNTIF($B$2:$B$9; B2)>1”. The letters in parentheses indicate the range of cells among which formatting and searching for duplicates will be performed. In brackets, it is necessary to prescribe a specific range of table elements and hang dollar signs on the cells so that the formula does not “move out” during the formatting process.

7. If desired, in the “Create a formatting rule” menu, the user can click on the “Format” button in order to specify the color that will be used to highlight duplicates in the next window. This is convenient, because repeated values ​​immediately catch the eye.

Pay attention! You can find duplicates in an Excel spreadsheet manually, by eye, by checking each cell. However, this will take the user a lot of time, especially if a large table is being checked.

Method 2: Find and remove duplicate values ​​using the Remove Duplicates button

Microsoft Office Excel has a special feature that allows you to immediately uninstall cells with duplicate information from a table. This option is activated as follows:

1. Similarly, highlight a table or a specific range of cells in an Excel worksheet.
2. In the list of tools at the top of the main menu of the program, click on the word “Data” once with the left mouse button.
3. In the “Working with data” subsection, click on the “Delete duplicates” button.

4. In the menu that should appear after performing the above manipulations, check the box next to the line “My data” contains headers. In the “Columns” section, the names of all the columns of the plate will be written, you also need to check the box next to them, and then click “OK” at the bottom of the window.

5. A notification about found duplicates will appear on the screen. They will be automatically deleted.

Important! After uninstalling duplicate values, the plate will have to be brought to the “proper” form manually or using the formatting option, because some columns and rows may move out.

Method 3: Using an advanced filter

This method of removing duplicates has a simple implementation. To complete it, you will need:

1. In the “Data” section, next to the “Filter” button, click on the word “Advanced”. The Advanced Filter window opens.

2. Put the toggle switch next to the line “Copy results to another location” and click on the icon located next to the “Initial range” field.
3. Select with the mouse the range of cells where you want to find duplicates. The selection window will automatically close.
4. Next, in the line “Place result in range”, you also need to click LMB on the icon at the end and select any cell outside the table. This will be the starting element where the edited label will be inserted.

5. Check the box “Only unique records” and click “OK”. As a result, an edited table without duplicates will appear next to the original array.

Additional Information! The old range of cells can be deleted, leaving only the corrected label.

Method 4: Use PivotTables

This method assumes compliance with the following step-by-step algorithm:

1. Add an auxiliary column to the original table and number it from 1 to N. N is the number of the last row in the array.

2. Go to the “Insert” section and click on the “Pivot Table” button.

3. In the next window, put the toggle switch in the line “To an existing sheet”, in the “Table or range” field, specify a specific range of cells.
4. In the “Range” line, specify the initial cell in which the corrected table array will be added and click on “OK”.

5. In the window on the left of the worksheet, check the boxes next to the names of the table columns.

6. Check result.

Conclusion

Thus, there are several ways to remove duplicates in Excel. Each of their methods can be called simple and effective. To understand the topic, you must carefully read the above information.