How to highlight the last duplicate row/cell in Excel?

This method will guide you to highlight the last duplicate cells in the specified column or highlight the entire rows based on the last duplicates in the specified column. Please do as follows:

1. Select a blank cell besides original range, says Cell E2, type the formula =COUNTIF($A$2:A2,A2) (A2 is the first data of specified column you will check duplicates) into it, and drag this cell’s AutoFill Handle down to the range as you need.

And then the helper column is added besides the original range. See screenshot:

2. Select the helper column, and click Data > Filter.

3. Click the Arrow button  in the column header of Helper column, uncheck 1 in the drop down list, and then click the OK button. See screenshot:

4. Select all filtered out rows (or cells) in the specified column or all filtered out rows, click Home > Fill Color, and then specify a fill color from the drop down list. See screenshot:

5. Delete the Helper column by right clicking this column and selecting the Delete from the right-clicking menu.

So far we have highlighted the rows based on the last duplicates in the specified column (or highlighted the last duplicate cells in the specified column). See screenshot:

Note: This method will highlight duplicates cells/rows except the first duplicate one. For example there are three “Black Tea” in the specified column, this method will highlight the second and the third “Blank Tea” cells in the column, or highlight rows based on the second and the third duplicates.

If you have Kutools for Excel installed, you can apply its Select Duplicate & Unique Cells utility to highlight the last duplicate cells in the specified column, highlight entire rows based on the last duplicates in the specified column, or highlight the last duplicate rows in the specified range.

1. Select the specified column you will check duplicates, and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

2. In the opening Select Duplicate & Unique Cells dialog box, please do as above screenshot shown:

(1) Check the Duplicates (Except 1st one) option in the Rule section;

(2) Check the Fill Backcolor option and specify a fill color from below drop down list;

(3) It’s optional to check the Select entire rows option.

For only highlighting last duplicate cells in the selected column, please do not check this Select entire rows option; for highlighting the entire rows based on the last duplicates in the selected column, please check this Select entire rows option.

In our case, we check the Select entire rows option.

3. Click the Ok button to apply this utility. And then a dialog box comes out and reminds you the number of selected rows. Just click the OK button to close it.

And then only the entire rows with the last duplicate cells in the specified column have been highlighted. See screenshot:

Notes:

(1) Above steps will highlight duplicates cells/rows except the first duplicate one. For example there are three “Black Tea” in the specified column, this method will highlight the second and the third “Blank Tea” cells in the column, or highlight rows based on the second and the third duplicates.

(2) The Select Duplicate & Unique Cells utility can also select and highlight the last duplicate rows in the specified range. See screenshot:

Highlight cells with same/duplicate values in Excel

Remove duplicate rows and keep highest values only in Excel