excel formula to remove duplicates in a cell

Tip: If you need to replace all the duplicate values (include the first one) with blank cells and only keep the unique values in the range, the following formula can help you, and you will get the following results: Then you can see all duplicates (except the first one) are replaced with blanks. Select the result cell and then drag the fill handle across the range that you want to apply this formula. Please do as follows:ġ.Select a blank cell to output the result such as cell D2, enter the below formula into it and press the Enter key. If there are some duplicate data in a range, the following two formulas will guide you to replace duplicates with blanks in the range. Remove duplicates and replace with blank cells with Kutools for Excel Remove duplicates and replace with blank cells with formulas This article is going to guide you remove duplicates and replace with blank cells in Excel.

But sometimes, you want the blank cells to replace the duplicate values, in this situation, the Remove Duplicate command will not work. Normally when you apply Remove Duplicates command in Excel, it removes the entire duplicate rows. It is a fast, simple way to see just how many duplicates you have within a sheet.How to remove duplicates and replace with blank cells in Excel? Now your spreadsheet will format each duplicate cell in the color you selected. Select Highlight Cells Rules followed by Duplicate Values, set a style to highlight duplicates and click OK.Open your spreadsheet on the page you need to sort.In larger spreadsheets it may be difficult to identify without a little help. In a small spreadsheet, they may be easily identifiable. Quickly and easily remove duplicates in Excelįirst, we need to identify if there are duplicates within a spreadsheet.

However, more complex spreadsheets containing formulas or filters already in place might cause you some headaches. Both the finding and removing parts of this tutorial are fairly safe for normal use as they use built-in tools. It could save time and heartache should something go wrong. If you are editing an important or work spreadsheet, make a backup first. Removing Duplicate Cells, Rows, & Columns

Excel formula to remove duplicates in a cell