How to remove duplicates in Excel – 2 easy methods

Table of Contents
If you want to learn how to delete duplicates in Excel, then you’ve landed on the right page.
When dealing with a large dataset in Excel, you need to be careful as there can often be duplicate values inside the sheet. While some duplicate values are there on purpose, others can be accidentally added while sorting the sheet. But what can you really do about them? Is there a way to get rid of them quickly? Or will you have to delete the entire sheet?
Prime Day is finally here! Find all the biggest tech and PC deals below.
- Sapphire 11348-03-20G Pulse AMD Radeon™ RX 9070 XT Was $779 Now $739
- AMD Ryzen 7 7800X3D 8-Core, 16-Thread Desktop Processor Was $449 Now $341
- ASUS RTX™ 5060 OC Edition Graphics Card Was $379 Now $339
- LG 77-Inch Class OLED evo AI 4K C5 Series Smart TV Was $3,696 Now $2,796
- Intel® Core™ i7-14700K New Gaming Desktop Was $320.99 Now $274
- Lexar 2TB NM1090 w/HeatSink SSD PCIe Gen5x4 NVMe M.2 Was $281.97 Now $214.98
- Apple Watch Series 10 GPS + Cellular 42mm case Smartwatch Was $499.99 Now $379.99
- ASUS ROG Strix G16 (2025) 16" FHD, RTX 5060 gaming laptop Was $1,499.99 Now $1,274.99
- Apple iPad mini (A17 Pro): Apple Intelligence Was $499.99 Now $379.99
*Prices and savings subject to change. Click through to get the current prices.
This is where we come in. In this guide, we’ll walk you through the entire process of getting rid of duplicates in Excel.
How to delete duplicates in Excel through the Data tab
Let’s take a look at how you can remove duplicates through the Data tab.
Step
Select the cells
Firstly, you need to select the range of cells that you want to remove the duplicates from.
Step
Go to the Data tab
Find the Data tab along the ribbon banner at the top of your screen. In the Data Tools group, select the button that’s named ‘Remove Duplicates’.
Step
Select the columns
Now select one or more of your spreadsheet’s columns in the box that will appear on your screen. To select all of the columns, press the button labeled Select All. On the other hand, to unselect all of the columns, click Unselect All.
Once done, click ‘OK, and Excel will remove all duplicates that were found. You will get notified about how many duplicates were found and removed from the sheet.
How to remove duplicates using Conditional Formatting
Alternatively, you can use Conditional Formatting to remove duplicates from Excel.
Step
Select the cells and go to Conditional Formatting
The first thing you need to do is select the cells that need to be checked. Then, go to the Home tab and click on Conditional Formatting.
Step
Highlight the duplicate values
Now, click on Highlight Cell Rules and then select Duplicate Values.
In the box that will pop up on the screen, select the formatting. For this example, we will select ‘Light Red Fill with Dark Red Text.’
Step
Remove the duplicates
After you have selected a suitable format, Excel will highlight all the cells that contain duplicate values. You can then go through the sheet and remove the ones that you don’t need.
Summary
Now that you know how to remove duplicates in Excel, you can sort your data without deleting the entire sheet. The first method can help you remove them automatically, while the second method is for those who want to remove only specific duplicate values.
If you want to learn more about Excel, then give these guides a read: