# 04 Best Options: How to Find and Remove Duplicates in Excel?

The question arises how to find and remove duplicates in Excel? When simplifying a table/dataset that contains many entries, or when compiling data from multiple sources, we might find that a table/dataset contains multiple matching entries.

We can easily find and remove duplicate data from a table/dataset by using any of the following methods:

# (01) HOW TO FIND AND REMOVE DUPLICATES IN EXCEL ➢ USING COUNTIFS FUNCTION

While working with a huge dataset, it is impossible to find out duplicates and removes them one by one. The COUNTIFS function is the best solution because it fills up the below requirement:

⇒ we can easily find and highlight duplicates in Excel dataset.

⇒ We have got a chance to recheck the duplicate data.

⇒ We can easily remove duplicates in Excel dataset without disturbing unique data.

■ Note: Instead of the COUNTIFS function, we can also apply the COUNTIF function.

## STEP 1: HOW TO FIND DUPLICATES IN EXCEL

Place an equality sign (=) in cell F2 and type ‘count…’, select COUNTIFS from the below suggestion list by the Down Arrow key () and then press the ‘Tab’ key.

■ Note: the upper or lower case does not matter in syntax, Excel by default considers it in upper case.

As a result, the COUNTIFS syntax appears with an open parenthesis.

Select the cell A2 as criteria_range1, the first argument of the COUNTIFS function. Then press full stop (.) from the keyboard, so Excel converts the cell reference into a range and the range starts from the same cell (or the same point) and it seems to be A2:A2.

Place a comma (,) to move to the next argument.

Again select cell A2 as criteria1, the second argument of COUNTIFS function.

Using Cell References

Simply select the starting cell reference of the criteria_range1 and press the F4 Key once which will convert the cell reference from the relative cell reference to the absolute cell reference.

Now the criteria_range1 seems to be \$A\$2:A2.

■ Note: We had detail discussed on Cell Reference in a separate tutorial, suggested you read this tutorial: 03 Types of Excel Cell Reference: Relative, Absolute & Mixed

Finally, press Enter to apply the formula in cell F2 and get the count value 1 as a result.

Extend the Formula Downward till the End of the Range:

It is the best practice to extend the formula from a cell to the end of the range with the ‘Formulas and number formats’ in the Paste Special dialog box.

Copy (Excel shortcut Ctrl+C) the cell F2 with formula ➪ Then makes a selection downward till to the end of the range with Shift+ Down arrow (⬇) ➪ After that either press Alt+E+S+R (sequentially press Alt, E, S, R) or  press Alt+Ctrl+V+R (Alt+Ctrl+V, then press R) which will select the ‘Formulas and number formats‘ option in the ‘Paste Special’ dialog box ➪ Click OK or press Enter to accept the formula. As a result, formula copied to the selected range without copying the cell formatting.

■ Note: We had detail discussed on Alt Key Excel Shortcuts in a separate tutorial, suggested you read this tutorial: 80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts

Alternatively, Copy (Ctrl+C) the cell F2 ➪ Then makes a selection downward till to the end of the range with Shift+ Down arrow (⬇) ➪ After that paste (Ctrl+V or simply press ‘Enter’). As a result, formula copied to the selected range with copying the same cell formatting.

Alternatively, select the cell that has the formula we want to fill down to the range ➪ either double click on the fill handle or drag the fill handle down to the range. As a result, formula copied to the selected range with copying the same cell formatting.

After copying the formula, we carefully notice that same duplicates in the dataset are marked in ascending order. For example, If we consider the Sales Manager Jackson, whose name  is duplicated three times in the dataset and the duplicate count is placed in ascending order likes 1, 2 and 3. So we should always keep the 1 value as a unique value and remove more than 1 value likes 2, 3, 4 etc. from the dataset as duplicates.

## STEP 2: HOW TO REMOVE DUPLICATES IN EXCEL

In the second step, we remove duplicates in Excel dataset with Excel Filter

It is the best practice to select the entire dataset with Ctrl+A Then press Ctrl+Shift+L (hold down the Ctrl and Shift keys and then press the L key) which will apply the Filter in the subject heading.

■ Note: We had detail discussed on Ctrl Key Excel Shortcuts in a separate tutorial, suggested you read this tutorial: 90+ Best Excel CTRL Shortcuts | Useful Keyboard Shortcuts |

Additionally, suggest you to read the usage of Excel Filter: 04 Simple to Advanced Methods: How to Filter in Excel?

After applying the filter, open the filter list in column F by using the Alt+ Down arrow (⬇) or clicking on the filter drop-down arrow ➪ Then unselect the value 1 (considers as a unique value) ➪ Finally, press OK.

As a result, all the duplicates are filtered ➪ Select the first row which one wants to delete either with the Shift+Spacebar or clicking on the row number ➪ Then select other rows downward with the Ctrl+Shift+Down arrow (⬇) or Shift+Down arrow (⬇) or Shift+Page Down ➪ After selection, press Alt+; (semicolon) for select visible cells only ➪ Finally, press Ctrl+- (minus) which will delete the all the rows with duplicates.

After deleting the rows, clear the filter with the Excel shortcut Alt+A+C (sequentially press Alt, A, C) or press Alt+D+F+S (sequentially press Alt, D, F, S).

As a result, only the unique values are present in the dataset.

# (02) HOW TO FIND AND REMOVE DUPLICATES IN EXCEL ➢ USING ‘REMOVE DUPLICATES’ COMMAND

We can remove duplicates in Excel dataset with the Remove Duplicates command in the Data Tools group under the Data tab.

(i) Click any cell in the dataset we want to work with.

(ii) Go to the Data tab ➪ Click Remove Duplicates in the Data Tools group ➪ The Remove Duplicates dialog box appears. In the Remove Duplicates dialog box, verify that My data has headers is selected and The Excel by default selected all columns.

Note: We can customize our column selection. First, click Unselect All and then check or uncheck the columns we want Excel to examine.

(iii) Click OK. Excel looks for and removes duplicates.

A message appears reporting how many duplicate records were removed (if any) and how many unique values remain.

# (03) HOW TO FIND AND REMOVE DUPLICATES IN EXCEL ➢ USING ADVANCED CONDITIONAL FORMATTING

Another method to find and remove duplicates in Excel is Conditional Formatting.

(i) Go to the Data tab ➪ Click Advanced in the Sort & Filter group which will open the Advanced Filter dialog box.

(ii) First, select the Copy to another location radio button.

Then click in the List range box and select the dataset range (in this case we select the range A1:E15).

Then click in the Copy to box and select a cell of the new location (in this case we select the cell G1).

Select the Unique records only checkbox.

(iii) Finally, click OK or press Enter

As a result, Excel removes all the duplicates from the dataset to a new location.

■ Note: We had detail discussed on Conditional Formatting in a separate tutorial, suggested you read this tutorial: 08 Best Examples: How to Use Excel Conditional Formatting?

# (04) HOW TO FIND AND HIGHLIGHT DUPLICATES IN EXCEL ➢ USING CONDITIONAL FORMATTING

This method only helps us to find and highlight duplicates in Excel, but it fails to remove duplicates from the dataset. Actually, this method is useful for data entry purposes. If we put any duplicate entry, the cell formatting will be changed and instantly alarming us this entry is a duplicate.

Either select only the range or select the entire column A with the shortcut Ctrl+Spacebar or clicking on the column header A ➪ Go to the Home tab ➪ Click Conditional Formatting drop-down ➪ Point over Highlight Cells Rules and a list opens ➪ Select Duplicate Values.

As a result, a Duplicate Values dialog box opens ➪ Consider all the default setting (or choose the different cell formatting using the values with drop-down) ➪ Click OK which will mark all the duplicates with a specific color.

Apply the filter using Excel shortcut Alt+D+L (sequentially press Alt, D, L) and open the filter drop-down list by pressing Alt+ ⬇ or clicking on it ➪ Point over Filter by Color ➪ Choose color from the Filter by Cell Color. As a result, all the duplicate values filtered out.

If we try to delete all the duplicates by color, then the unique item and its duplicates are removed, which means the actual entry is deleted from the dataset. In the given example, there are three entries of Sales Manager Jackson, we consider one is unique and the rest two are duplicates. If we delete by color, three entries of Jackson have been removed which means Jackson entry have been totally removed from the dataset.

Microsoft Excel 2019/Office 365 Series

Bestseller
4.9/5

Introduction to Database Development (Self-Paced Tutorial)

Bestseller
4.9/5

Advanced Microsoft Excel 2019/Office 365 (Self-Paced Tutorial)

Bestseller
4.9/5

Microsoft Access 2019 Certification Training

High Rated
4.9/5

Microsoft Office Specialist 2016 (MOS) Certification Training

Top Rated
4.7/5

Microsoft Project 2016

Bestseller
4.9/5

Accounting with MS Excel 2019 Suite

Bestseller
4.9/5

Microsoft Excel and Project 2019 Suite

Bestseller
4.9/5

Visual Basic Series

Bestseller
4.9/5

Bestseller
4.9/5

Bestseller
4.9/5

Data Science Fundamentals for Data Analysts

Bestseller
4.9/5

Infonomics II: Business Information Management and Measurement

Bestseller
4.9/5

Excel Fundamentals for Data Analysis

Bestseller
4.9/5

Data Visualization in Excel

Bestseller
4.9/5

Bestseller
4.9/5

Accounting Analytics

Bestseller
4.9/5

Modern Big Data Analysis with SQL

Bestseller
4.9/5

Master Excel Functions in Office 365 – Excel Dynamic Arrays (Learn to Use Excel’s NEW Functions (FILTER, UNIQUE, SORT, XLOOKUP.) to Dramatically Simplify the Work You Do in Excel.)

High Rated
4.9/5

Excel VBA Macros: Hyper-disambiguated Excel VBA Programming [Learn Excel VBA macros, using clean, clear VBA programming techniques and code that you fully understand]

Bestseller
4.8/5

Ultimate Excel Waterfall Chart Course (Impressive Excel Waterfall Charts for Your Reports – From Scratch. No Add-ins, No VBA – For ANY Excel Version!)

Bestseller
4.6/5

Project Finance and Financial Modeling in Excel (Learn Project Finance concepts and learn the modeling in excel)

Bestseller
4.8/5

Master Microsoft Access – Access from Beginner to Advanced (Develop dynamic Access databases fast in this easy to learn Microsoft Access Course (2010, 2013, 2016, 2019 & 365)

Bestseller
4.8/5

Master Microsoft Excel Macros and Excel VBA (Project Based Course on Excel VBA (Visual Basic for Applications) and Excel Macros)

High Rated
4.8/5

Power BI Masterclass – beginners to advanced (Microsoft Power BI – How to master self service business intelligence in no time. Power BI Training online)

Bestseller
4.8/5

MS Excel Exam Guide: Analyzing and Visualizing Data (Gain Skills in Data Analysis with Excel. Pass the Microsoft 70-779 Exam. Grow Your Career with an Excel Certification.)

Top rated
4.6/5

Microsoft Power BI Masterclass – Expand Excellence (achieve the next Level of Business Intelligence with Microsoft Power BI Desktop. How to master Power BI Desktop)

Top Rated
4.7/5

Something went wrong.

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

## Like it? Share with your friends!

Share via

Community grow with You. * VERIFY & CONFIRM YOUR EMAIL *

Fill the Correct Information.

Something went wrong.

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

We use cookies to ensure that we give you the best experience on our website (by analyzing).  Please see our cookies page for further details or agree by clicking the 'Accept' button.

Below you can choose which kind of cookies you allow on this website. Click on the "Save cookie settings" button to apply your choice.

FunctionalOur website uses functional cookies. These cookies are necessary to let our website work.

AnalyticalOur website uses analytical cookies to make it possible to analyze our website and optimize for the purpose of a.o. the usability.