. Whether it's an import error or someone accidentally pasting something twice, duplicate data makes your spreadsheets less useful. Here's how to find duplicates in Excel, so you can delete them yourself, and how to find and delete duplicates in one quick motion. How to Find Duplicates in ExcelIf you simply want to find duplicates, so you can decide yourself whether or not to delete them, your best bet is highlighting all duplicate content using conditional formatting. Select the columns you want to check for duplicate information, and click Home > Highlight Cell Rules > Duplicate Values. You will be asked which color scheme you'd like to use for the highlighting. Click OK, and all duplicate rows in your selected columns will be highlighted. You can now review the data and decide yourself whether to delete anything that's redundant. How to Remove Duplicates in ExcelIf you'd like to delete duplicates in Excel without manually reviewing them first, you can. Select the data you'd like to remove duplicates from, then click Data > Remove Duplicates. You will be asked which columns you'd like to include in your search for redundant data. This is useful if there is one column that wouldn’t include any duplicate data. Click OK and Excel will find and delete all rows containing duplicate data. Want to learn more Excel tricks? Check out our Excel overview for updates, tutorials, and ways to integrate Excel with thousands of other apps. mentioned apps
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 formulasThe 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: =IF(COUNTIFS($A$2:$A$12,$A2,$B$2:$B$12,$B2,$C$2:$C$12,$C2)>1, "Duplicate row", "") 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:
Tips: If you want to find duplicate rows without the first occurrences, please apply the following formula: =IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2) >1, "Duplicate row", "") Highlight duplicate rows across multiple columns with Conditional FormattingIf 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:
Note: In the above formula, $D$2:$D$12 is the column D that you have combined the other column values. 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:
Select or highlight duplicate rows across multiple columns with a handy featureThe 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. Note:To apply this Select duplicate & unique cells, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily. 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:
3. Then click OK, and the duplicate rows are selected as following screenshots:
Download and free trial Kutools for Excel Now ! More relative articles:
No ratings yet. Be the first to rate! |