When you work with a large list of data in an excel spreadsheet, chances are that some of the cells may contain duplicate sets of data. You may want to remove duplicates values in Excel and keep the unique values in a particular cell or column.

In this short tutorial, we will learn how you can remove duplicate entries in an excel column or row, using a simple data filter.

Remove Duplicate Data In an Excel Row Or Column

1. Open the Excel spreadsheet and highlight the row or column whose values you want to check. You have to click the top of the column in order to select the entire column; should you want to select only specific cells, simply drag the mouse cursor to create a selection.

2. When you are done selecting the cells from which the duplicate entries are to be removed, highlight the “Data” tab, as shown below:

3.  Now hit the “Remove Duplicates” button and Excel will automatically delete all the duplicate entries from your selection.

Microsoft Office will further show you a confirmation message like this:

The above message is shown when Excel finds that there are 3 instances of the same data spread across different cells. Among the 3 instances of the repeating data, Excel will remove 2 duplicate entries while keeping only one instance of the data in the selected row or column.

The above trick works for as many duplicate sets you might have in a specific cell or column. If you want to scan the entire spreadsheet for duplicate values, hit “Control + A”, switch to the “Data” tab and select “Remove Duplicate Entries”.

Highlighting Duplicate Values And Manually Deleting Them Using Conditional Formatting

There may be situations when you might want to manually delete the duplicate entries one by one. The question is: How do you highlight the duplicate values in an excel row or column ? How can we easily sort the duplicate entries and then check each of them one by one ?

This can be easily achieved using Excel conditional formatting, as described below:

1. Select the row or column where you want to filter the duplicate entries. If you want to select the entire spreadsheet, hit “Control + A”

2. Switch to the “Home” tab and click “Conditional formatting >Highlight cell rules > Equal to”

3. Next, enter the exact value which you want to filter or highlight in the chosen row. If you are not sure regarding the duplicates values that are to be filtered out, first run a “Duplicate value” check as described earlier in the article.

4. As soon as you enter the value in the conditional formatting window, Excel will automatically highlight the redundant entries. Now you can manually remove them in any order of your choice.

Remove Duplicate Values From A Google Docs Spreadsheet

If you are like me and use Google Docs spreadsheets a lot for managing accounts, drawing charts – you might want to learn how to remove duplicate values from a Google Docs spreadsheet.

Google Docs does not supports filtering values using Logical functions, but you can still borrow some help from Excel’s conditional formatting. What this means is that you can apply conditional formatting in a Google Docs spreadsheet and filter duplicate values in any row or column.

1. Select the entire row or column where you want to filter the duplicate values. You may also select specific cells using a manual selection (dragging).

2. Click the small arrow icon over the column, as shown below:

3.  This will open the conditional formatting window where you can add multiple rules for that particular row or column.

4. To highlight all the duplicate values in the selected row or column, choose “Is equal to” and then enter the value and choose a background.

5. Save your rule and voila ! Google Docs will now highlight all the duplicate entries in the chosen row or column.

Unfortunately, there is no automatic way to delete all the duplicate entries in one shot. However, you can use Google Doc’s conditional formatting to highlight the redundant entries and then manually delete the duplicates.

These are two simplest ways to find duplicate entries in Excel spreadsheet, you may write your own Excel functions to remove duplicate values from a spreadsheet depending upon your requirements or rules.

Know any excel tip which you want to share with fellow readers? Let us know your thoughts and ideas in the comments.

More tips on Microsoft Excel:

1. Use Macros to detect the formatting properties of a cell
2. Open Excel spreadsheets without Excel