Background
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.
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.
- In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.
- 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.
For example, in this worksheet, the January column has price information that we want to keep.
So, We unchecked January in the Remove Duplicates 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.
