Filter by Categories
Select all
Business Statements
Federal Income Tax
Gantt Chart
GST India
Health, Logs and Personal Trackers
Invoice/ Bill
Payroll & HR
Project Reports & Timelines
Purchase Order/Requisition
Rental Ledger
Risk Registers
Sales/ Marketing
SWOT Analysis
Timesheet/ Attendance
Filter by File Types

How to remove duplicates in Excel ?

How to remove duplicates in MS Excel


While working with a data set in Microsoft excel, we often stumble upon duplicate values and data lines. Duplicate values are sometimes useful, and sometimes we need to filter and rule them out to make our data analytics more efficient and useful. Use of conditional formatting becomes essential in such cases. Conditional formatting has many other usage as well. In this tutorial we will discuss about how to remove duplicates in Excel using conditional formatting.

This tutorial is relevant for all Microsoft Office packages namely, Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010.

What is conditional formatting ?

Conditional formatting can help make patterns and trends in your data more apparent. To use it, you create rules that determine the format of cells based on their values, such as the following monthly temperature data with cell colors tied to cell values. You can apply conditional formatting to a range of cells (either a selection or a named range), an Excel table, and in Excel for Windows, even a PivotTable report.

Conditional formatting example

How to remove duplicate in excel – tutorial 

Now, let us jump into the tutorial, this tutorial blog covers two sections – one to find the duplicate values and other to remove the duplicate values. You can follow the steps provided below to find and remove duplicates in excel

Find Duplicate values in Excel

  • Firstly, Select the cells you want to check for duplicates.

Excel can’t highlight duplicates in the Values area of a PivotTable report.

  • Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Highlight cell rules

  • In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.

Duplicate Values dialog box

  • With the highlighted part, now you successfully found the list of duplicate values in your data set. You can use color filter option in excel to filter out the duplicate values and delete the unwanted line items.

Remove the duplicate values

When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.

  • First, Select the range of cells that has duplicate values you want to remove.
  • Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.

Remove Duplicates

For example, in this worksheet, the January column has price information that we want to keep.

Duplicate values highlighted

So, We unchecked January in the Remove Duplicates box.

Remove duplicates dialog box

  • Click OK.

  • Once you click OK button, all your duplicate values in the selected columns will be deleted automatically. This is the easiest method and in-built feature available in Excel to remove the duplicate values.

Macro Codes in Excel

Learn Excel Quickly
Get full list of Excel Macro Codes