Last Updated on August 24, 2026
Managing large spreadsheets is difficult enough without worrying about hidden duplicates skewing your totals and analysis. Manually reviewing thousands of rows is inefficient and highly prone to human error. Fortunately, Excel provides built-in tools to surface these inconsistencies instantly.
In this guide, we will show you exactly how to use Conditional Formatting to highlight duplicate or unique values. This allows you to manage your data with absolute confidence. As datasets grow, even small inconsistencies can quietly undermine accuracy, reporting, and decision-making.
Duplicate entries may not always be obvious at a quick glance. They can completely distort totals, skew analysis, and create mass confusion when data is shared across corporate teams. Having a reliable way to surface and address these issues early saves hours of cleanup later.
Manually reviewing long columns row by row increases the risk of human error. When similar values appear scattered throughout a worksheet, duplicates easily get overlooked or removed inconsistently. Built-in Excel tools become especially valuable here. They allow you to identify patterns and problem areas quickly without altering the underlying data structure.
Excel makes this process incredibly easy by allowing you to format the cells using a conditional format. This specific format will highlight the duplicate cells so you can easily review and remove the bad data. You can also use it to highlight unique values if you are looking for standalone data instead. Please note that you cannot apply this specific conditional formatting rule to fields in the Values area of a PivotTable report.
How to Highlight Duplicate Cells
1. Highlight the Target Range
Highlight the specific range of cells, columns, or rows you want to check for duplicates. You can select an entire column by clicking the letter at the top of the worksheet. Drag your mouse across multiple columns to scan a massive dataset all at once.
2. Open Conditional Formatting
Look at the top ribbon menu and click on the Home tab. Locate the Styles group on the right side of the screen. Click on the Conditional Formatting button to open the drop-down menu containing your visual data rules.
3. Select Duplicate Values
Hover your mouse cursor over the Highlight Cells Rules option. A secondary slide-out menu will appear directly on your screen. Select “Duplicate Values…” from this new list to launch the configuration prompt.
4. Configure the Formatting Rule
A small pop-up box will appear in the center of your screen. Ensure the word “Duplicate” is selected in the very first dropdown menu. You can change this to “Unique” later if you need to find standalone data points.
5. Choose a Highlight Color
Look at the second dropdown menu inside the prompt box. Choose your preferred highlight color from the provided list. “Light Red Fill with Dark Red Text” is the standard default choice for flagging bad data.
6. Apply the Formatting
Click the OK button to apply the new formatting rule to your spreadsheet. All duplicate entries in your selected range will now be highlighted instantly. Now that the duplicate cells are marked with the color you chose, it makes it incredibly easy to spot them and clean up your data.
How to Quickly Remove Duplicates
If you do not need to see the visual highlights, you can skip formatting entirely. Sometimes you just want the duplicates gone completely without any manual review. You can use a dedicated data tool to remove them directly from your worksheet.
1. Select the Data Range
Select your specific data range that contains the redundant information. Make sure you highlight the entire table if your rows contain connected data. Selecting only one column will delete values without keeping the adjacent data aligned.
2. Navigate to the Data Tab
Go to the Data tab located on your top Excel ribbon. This section contains powerful tools for sorting, filtering, and cleaning up messy spreadsheets. It handles the heavy lifting for massive corporate datasets.
3. Click Remove Duplicates
Look for the Data Tools group near the middle of the ribbon menu. Click on the Remove Duplicates button to launch the deletion wizard. A new prompt will appear asking you to confirm your exact column selection.
4. Execute the Deletion
Choose which specific columns you want the program to check for exact matches. Click the OK button to permanently delete the redundant rows. Excel will immediately tell you exactly how many duplicate values were found and removed from your spreadsheet.
