How to find and highlight duplicate rows in a range in Excel?
Sometimes, there are may be some duplicate records in your data range of a worksheet, and now you want to find or highlight the duplicate rows in the range as following screenshots shown. Of course you can find them one after one by checking for the rows. But this is not a good choice if there are hundreds of rows. Here, I will talk about some useful ways for you to deal with this task.
 |
 |
Find duplicate rows across multiple columns with formulas
Highlight duplicate rows across multiple columns with Conditional Formatting
Select or highlight duplicate rows across multiple columns with a handy feature
Find duplicate rows across multiple columns with formulas
The following formula can help you to find the duplicate records, please do as this:
1. In the adjacent blank cell, cell D2 in this case, please enter the below formula:
2. And then drag the fill handle down to the cells for applying this formula, now, you can see, if there are identical rows in this used range, it will display Duplicate row, see screenshot:
- Notes:
- 1. In the formula, $A$2:$A$12, $B$2:$B$12, $C$2:$C$12 indicate the range columns that you want to find the duplicate from. You can change them as you want. And A2, B2, C2 indicate the first cells in the each column of the data which needed to be applied this formula, you can change them as well.
- 2. The above formula is based on data in 3 columns, if there are 4 or more columns in your data range which needed to be found the duplicated values from the first row, you just need to add the column references as this formula shown: =IF(COUNTIFS($A$2:$A$12,$A2,$B$2:$B$12,$B2,$C$2:$C$12,$C2,$D$2:$D$12,$D2)>1, "Duplicate row", "").
Tips: If you want to find duplicate rows without the first occurrences, please apply the following formula:
Highlight duplicate rows across multiple columns with Conditional Formatting
If you can’t apply the formula correctly, please don’t worry, the Conditional Formatting utility also can help you to highlight the duplicate rows. Do with the following steps:
1. The first step you should to use the CONCATENATE function to combine all the data into one cell for each row. Type the below formulain cell D2, then copy the formula down until the last row of data see screenshot:
2. Then, select the range that you want to find the duplicate rows including the formulas in column D, and then go to Home tab, and click Conditional Formatting > New Rule, see screenshot:
3. In the New Formatting Rule dialog box, please do the following operations:
- Click Use a formula to determine which cells to format from the Select a Rule Type list box;
- And then, enter this formula =COUNTIF($D$2:$D$12,$D2)>1 (Highlight duplicate rows with first occurrences) or =COUNTIF($D$2:$D2,$D2)>1 (Highlight duplicate rows without first occurrences) into the Format values where this formula is true text box;
- At last, click Format button.
4. In the popped out Format Cells dialog box, click Fill tab, and then, choose one color that you need to highlight the duplicates.
5. Click OK > OK to close the dialog boxes, and the duplicate rows are highlighted by the color you choose at once, see screenshot:
| Highlight duplicate rows with the first ones | Highlight duplicate row without the first ones |
 |
 |
Select or highlight duplicate rows across multiple columns with a handy feature
The above methods are somewhat troublesome for you, so here, I can introduce you an easy and handy tool-Kutools for Excel, with its Select duplicate & unique cells utility, you can quickly select the duplicate rows or unique rows as you need.
After installing Kutools for Excel, please do as this:
1. Click Kutools > Select > Select Duplicate & Unique Cells, see screenshot:
2. In the Select duplicate & unique cells dialog box, please do the following operations:
- Click
button to select the range that you want to use; - Then, select Each row from the Based on section;
- And then,check Duplicates(Except 1st one) or All duplicates(Including 1st one) option under Rule section as you need;
- At last, you can specify a background color or font color for the duplicate rows under the Processing of results.
3. Then click OK, and the duplicate rows are selected as following screenshots:
| Select duplicate rows including the first ones | Select duplicate rows excluding the first ones |
 |
 |
- Notes:
- 1. If you check Select entire rows option, the entire duplicate or unique rows will be selected.
- 2. If you check Case sensitive option, the text will be compared case sensitive.
Download and free trial Kutools for Excel Now !
More relative articles:
- Highlight Consecutive Duplicate Cells From A List
- Supposing, I have a long list of data which contains some consecutive duplicate values, how could I highlight those consecutive duplicate cells as following screenshot shown in Excel?
- Combine Duplicate Rows And Sum The Values In Excel
- In Excel，you may always meet this problem, when you have a range of data which contains some duplicate entries, and now you want to combine the duplicate data and sum the corresponding values in another column, as following screenshots shown. How could you solve this problem?
- Highlight Duplicate Values In Different Colors In Excel
- In Excel, we can easily highlight the duplicate values in a column with one color by using the Conditional Formatting, but, sometimes, we need to highlight the duplicate values in different colors to recognize the duplicates quickly and easily as following screenshot shown. How could you solve this task in Excel?
- Align Duplicates Or Matching Values In Two Columns
- If you have two list of names, and you want to compare these two columns and find the duplicates in both, and then align or display the matching names based on the first column in a new column as following screenshot shown. To list the duplicate values which exist in both columns, this article may introduce some tricks for solving it.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
