How to highlight duplicate rows

To highlight duplicate rows in Excel, use custom Conditional Formatting with a COUNTIFS formula (e.g., =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2,$D$2:$D$100,$D2)>1), helper columns with concatenation or TEXTJOIN, or Power Query for large datasets. These methods check matches across multiple columns simultaneously, unlike single-column highlighting which uses simple COUNTIF on one range.

What methods does Excel offer to highlight duplicate rows?

Excel provides three primary methods to highlight duplicate rows:

1. Custom COUNTIFS formula (most flexible): Select the full row range and apply Conditional Formatting with a formula that checks all columns simultaneously. The formula =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2,$D$2:$D$100,$D2)>1 flags repeats across all specified columns, unlike single-column COUNTIF which ignores cross-column context.
2. Helper column with concatenation: Add a helper column containing =A2&"|"&B2&"|"&C2&"|"&D2 or =TEXTJOIN("|",TRUE,A2:D2) to create a unique string per row, then apply Conditional Formatting using =COUNTIF($E$2:$E$100,E2)>1. This simulates row uniqueness via a single string for simpler duplicate detection.
3. Power Query (for large datasets): Load data via Data > From Table/Range, group by all columns to count occurrences, then filter groups greater than 1 and merge back for highlighting. This method handles scalability better than formulas but requires manual refresh.

How to use COUNTIFS to highlight duplicate rows

To use COUNTIFS for highlighting duplicate rows, create a custom Conditional Formatting rule that verifies matches across all specified columns. This method excludes the first occurrence by default and highlights only subsequent duplicate rows.

Prepare your data

1. Organize data as a table starting at row 2 (e.g., columns A through D in range A2:D100).
2. Place headers in row 1.
3. Sort data ascending for sequential highlighting when needed.

Select the data range

1. Click the top-left cell of your data (A2).
2. Press Ctrl+Shift+End or drag to select the entire data range (A2:D100).
3. Ensure A2 remains the active cell for relative references to function correctly.

Create the Conditional Formatting rule

1. Go to the Home tab.
2. Click Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format."
4. Enter the formula: =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2,$D$2:$D$100,$D2)>1
5. Use absolute column ranges with $ signs for the lookup ranges.
6. Use relative row references without $ for the criteria cells (e.g., $A2 instead of $A$2).

Apply formatting

1. Click Format.
2. Choose Fill and select a color (e.g., red).
3. Adjust Font or Border settings as needed.
4. Click OK twice to apply the rule.
5. Subsequent duplicate rows highlight immediately.

Manage and verify

1. Access rules through Conditional Formatting > Manage Rules to edit range, formula, or priority.
2. Add test duplicate rows to verify dynamic updates.
3. Adjust the range to $A:$D for automatically expanding data.

How to use helper columns with concatenation to highlight duplicate rows

To use helper columns with concatenation, create a unique string per row by joining all column values, then apply COUNTIF-based Conditional Formatting to detect matches. This method simplifies multi-column checks into a single formula operation.

Insert the helper column

1. Add a new column (e.g., column E) next to your data in columns A through D.
2. Enter "RowKey" as the header in E1.
3. In E2, enter =A2&"|"&B2&"|"&C2&"|"&D2
4. Use "|" or CHAR(10) as the delimiter to avoid false matches (e.g., "AB" incorrectly matching "A" concatenated with "B").
5. Copy the formula down to cover all data rows (e.g., E2:E100).

Apply Conditional Formatting

1. Select the data range A2:D100.
2. Go to Home > Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format."
4. Enter =COUNTIF($E$2:$E$100,E2)>1
5. Set a format such as red fill.
6. Click OK twice to apply.
7. Subsequent duplicate rows highlight based on the concatenated key.

Refine the results

1. Hide column E by right-clicking and selecting Hide to keep the worksheet clean.
2. The formula updates dynamically when new data is added.
3. Test by duplicating rows to confirm full-row highlighting functions correctly.

How to use TEXTJOIN instead of basic concatenation

To use TEXTJOIN for the helper column method, replace manual concatenation with a single function that handles ranges dynamically and skips blank cells. TEXTJOIN requires Excel 2019 or Microsoft 365.

TEXTJOIN syntax

1. In the helper column (e.g., E2), enter =TEXTJOIN("|",TRUE,A2:D2)
2. The first argument "|" specifies the delimiter between values.
3. The second argument TRUE instructs the function to skip empty cells.
4. The third argument A2:D2 defines the row range to join.
5. Copy the formula down for all data rows.

Advantages over basic concatenation

TEXTJOIN handles ranges dynamically (e.g., A2:Z2 for wide datasets) without listing each cell individually. The function ignores blanks without requiring additional IF statements. Formulas remain shorter and more maintainable for datasets with many columns. Consistent delimiters prevent false positives from ambiguous joins where "12" might incorrectly match "1" concatenated with "2".

Integrate with Conditional Formatting

1. After populating column E with TEXTJOIN formulas, select the data range A2:D100.
2. Apply Conditional Formatting with the formula =COUNTIF($E$2:$E$100,E2)>1
3. Full duplicate rows highlight based on the joined key.

Compatibility

TEXTJOIN requires Excel 2019 or Microsoft 365. For older versions, use CONCATENATE or the ampersand operator (&) as a fallback, though these lack the ignore_blanks parameter and range flexibility.

How to use Power Query to highlight duplicate rows in large datasets

To use Power Query for highlighting duplicate rows, load data into the Power Query Editor, group by all columns to count occurrences, then flag duplicates and load results back to Excel. This method handles millions of rows without performance issues from volatile formulas.

Load data into Power Query

1. Select your data range.
2. Go to Data tab > From Table/Range.
3. Confirm table creation when prompted.
4. The Power Query Editor opens with your data loaded.
5. Ensure all columns needed for row matching are included.

Group and count duplicates

1. Go to Home > Transform > Group By.
2. Select all columns for matching by holding Ctrl and clicking each column name.
3. Name the new column "DuplicateCount."
4. Set the operation to "Count Rows."
5. Click OK.
6. The data collapses to unique rows with counts greater than 1 indicating duplicates.

Expand and flag results

1. Click the double-arrow icon on the DuplicateCount column header.
2. Uncheck "Use original column name as prefix."
3. Click OK to expand back to full rows.
4. Go to Add Column > Custom Column.
5. Enter the formula: if [DuplicateCount] > 1 then "Duplicate" else "Unique"
6. Name the column "Flag."
7. Click OK.

Load and apply highlighting

1. Click Close & Load to output results to a new sheet.
2. Select the Flag column in the output.
3. Apply Conditional Formatting via Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
4. Enter "Duplicate" and select red fill.
5. Click OK.

Refresh for updated data

Right-click the query in the Queries & Connections pane and select Refresh to update results after data changes. Power Query scales efficiently for large datasets without recalculating entire sheets through volatile formulas.