Yes. In Excel for Microsoft 365 on Windows, add a helper column that counts how often each row’s values occur across the columns you care about, then use Data > Filter to show counts greater than 1. This finds repeated combinations of values; it is different from finding a repeated value in just one column.
What counts as a duplicate across columns?
Usually, “duplicates across multiple columns” means that two or more rows contain the same combination of values. For example, if columns A, B, and C contain a customer, product, and order date, then two rows are duplicates when all three fields match. A customer appearing on several rows is not, by itself, a duplicate if the product or date differs.
It helps to decide which columns define a match before filtering. Include every field that matters to the record’s identity, and leave out fields that are expected to vary. If you include a transaction ID, for instance, otherwise identical rows with different IDs will not count as duplicates. If you leave out a product field, rows for different products may be treated as duplicates.
Excel’s ordinary filter dropdowns filter values in individual columns. Applying filters to several columns at once can narrow results, but it does not automatically identify repeated whole-row combinations. A helper column makes the matching rule explicit and gives you a field you can filter.
Count matching rows with a helper column
Suppose row 1 contains headers and columns A through C contain the fields to compare. Put a heading such as Duplicate count in D1. With the records in A2:C100, enter this formula in D2 and fill it down to D100:
With the fields to compare in A2:C100 and the formula entered in D2, this counts rows matching the current row in all three columns: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)
COUNTIFS checks each specified range together: a row contributes to the count only when its A value matches A2, its B value matches B2, and its C value matches C2. The dollar signs keep the full search ranges fixed as you fill the formula down, while the criteria references change from row to row.
A result of 1 means Excel found only that row’s combination. A result greater than 1 means that combination occurs repeatedly. For example, if rows 2 and 7 both contain North, Widget, and 10/1/2026 in A:C, the formula in D2 returns 2, and the formula in D7 also returns 2. Filter the helper column to show 2 or greater to see both rows.
If your data extends beyond row 100, change each range endpoint to cover the full list. Avoid using entire columns in every COUNTIFS formula on a very large workbook if recalculation becomes slow; a bounded range or an Excel Table is easier to manage. You can also turn a data range into a Table with Insert > Table. Tables expand as records are added, and formulas in a table column typically fill down automatically.
Filter the duplicate combinations
- Make sure each data column has a header and that there are no blank rows splitting the list.
- Enter the COUNTIFS formula in the helper column and fill it down for every record.
- Click a cell in the data range, then choose Data > Filter. Excel adds dropdown arrows to the header row.
- Open the Duplicate count dropdown, clear Select All, and select the counts greater than 1. Depending on the values, you can select the individual counts shown or use Number Filters > Greater Than and enter 1.
- Review the visible rows. To return to the full list, clear the helper-column filter or choose Data > Clear.
Filtering hides nonmatching records; it does not delete or rearrange the underlying data. If you need to share or analyze only the matches, copy the visible rows to another sheet after checking that the correct fields were used. If the list is an Excel Table, its header filters are built in, and the helper formula can remain part of the table as new records are appended.
Show a duplicate flag instead of a count
Some people prefer a simple TRUE/FALSE result or a readable label. The count is more informative when a combination appears three or more times, but either form can be filtered. For example, with the same A:C layout and formula in D2, this returns TRUE for a repeated combination and FALSE for a combination that appears once:
With the comparison fields in A2:C100 and the formula in D2, this tests whether the current combination appears more than once: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1
Fill the formula down, then filter the helper column to TRUE. Because the formula includes the current row in its count, every member of a repeated group is marked TRUE, including the first occurrence. That is often what you want when inspecting all copies. If you instead need to flag only later occurrences and leave the first unflagged, use a running count that checks only the rows up to the current one.
With the comparison fields in A2:C100 and the formula in D2, this running count flags the second and later appearances with TRUE: =COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)>1
Fill that formula down in row order. It returns FALSE for the first occurrence, then TRUE for each later matching row. This is useful when you plan to remove extra copies while keeping the first record, but check the sort order first: the row that appears first is the one preserved by this logic.
Use conditional formatting to spot matches
If you want to highlight matching rows rather than filter them, use conditional formatting with a COUNTIFS rule. Select the data area A2:C100, choose Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format. Enter the formula below, choose a format, and confirm. The references assume the selection starts at row 2; the relative row references then adjust for each row in the selection.
With A2:C100 selected and A:C as the fields that define a duplicate, this formula highlights cells in rows whose combination occurs more than once: =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1
Conditional formatting is a visual cue, not a filter or a data-cleaning operation. You can combine it with the helper column if you want both a quick visual scan and a filterable result. To change or remove the rule later, use Home > Conditional Formatting > Manage Rules.
Whole-row duplicates, one-column duplicates, and built-in tools
For exact duplicate rows across a selected range, Excel also offers Data > Advanced. Select the list, choose Advanced, and check Unique records only to display one copy of each record. Depending on the choices in the dialog, you can filter the list in place or copy the results elsewhere. This is useful for producing a unique list, but it is not the same as displaying only the repeated rows; the helper-column method is more direct for finding every member of each duplicate group.
Data > Remove Duplicates is another built-in option, but it removes records rather than merely hiding them. In its dialog, you choose the columns that define a duplicate. Excel keeps one record from each matching group and removes the others from the selected range. Make a copy before using it if you may need the original data: removing records is not a substitute for a reversible filter.
For duplicates in a single field, such as repeated email addresses in column B, you can use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. That built-in rule examines the selected cells, not a multi-column combination. If you need to show rows where a particular email repeats, a COUNTIF helper can count that one field, but use COUNTIFS when the identity of a record depends on several fields.
Common problems and matching details
- Wrong columns included: Recheck the fields that define a record. A varying note or timestamp can prevent otherwise similar rows from matching, while omitting a meaningful identifier can create false matches.
- Extra spaces: “North” and “North ” may not behave as expected in a comparison. Clean imported text with TRIM in a separate cleanup column, or inspect suspicious values. TRIM removes ordinary leading, trailing, and repeated internal spaces, but some imported nonbreaking spaces require separate cleanup.
- Dates and numbers: A displayed date can hide a time component, and numbers stored as text can differ from numeric values. If records look identical but do not match, check the underlying values and number formats rather than relying only on what is displayed.
- Blank fields: COUNTIFS can match blank cells. If rows with blank comparison fields should not count as duplicates, add a separate test or clean the source data before filtering. Decide whether two incomplete records should be treated as a matching pair.
- Case and wildcard characters: COUNTIFS comparisons are not case-sensitive, so “abc” and “ABC” are treated as the same text. Asterisks and question marks can also act as wildcard characters in criteria. If those characters are meaningful literal data, validate the results or use a different matching approach.
- Formula not filled to the last row: A missing helper result means a record cannot appear in the filtered matches. Check the last data row and extend the formula and filter range together.
It is also worth distinguishing duplicate values from duplicate records. A repeated surname is a repeated value in one field; it does not prove that two people or rows are duplicates. Confirm the business meaning of the selected columns before deleting or merging records.
Choosing a method for your task
Use a COUNTIFS helper column when you want to filter all rows belonging to a repeated combination, see how many times each combination occurs, or flag later occurrences. Use conditional formatting when visual review is the priority. Use Advanced Filter when the goal is a separate list of unique records, and Remove Duplicates only when you intend to alter the data after making an appropriate backup.
For repeat work, turn the range into a Table and keep the helper calculation beside the source fields. This makes the definition of “duplicate” visible to other workbook users and reduces the chance that new rows are left out. If the comparison needs to ignore spaces, normalize those fields consistently before counting; do not silently change source values unless that is acceptable for the workbook.
Frequently Asked Questions
Can I filter duplicates based on two columns only?
Yes. Include only those two fields in COUNTIFS. For example, if columns A and C define a match, use criteria ranges for A and C and leave column B out. Rows match when both included values agree.
Does Excel’s Remove Duplicates show me the repeated rows?
No. Remove Duplicates deletes extra copies from the selected range and keeps one record per combination of the checked columns. To inspect all repeated rows without deleting them, use a COUNTIFS helper column and filter its results.
Will the formula find duplicates if the rows are not next to each other?
Yes. COUNTIFS checks the full specified ranges, so matching records can be anywhere within the data list. The rows do not need to be sorted or adjacent.
Can I find duplicates across an entire row?
Yes. Include every column that should define an identical record in the COUNTIFS formula. If the range has many columns, Excel’s Advanced Filter or Remove Duplicates can be convenient for finding unique records or removing extras, respectively; use a helper formula when you need to filter the duplicates themselves.
Why do two rows that look the same not match?
Check for trailing spaces, hidden characters, dates containing different times, and numbers stored as text. Also confirm that the formula ranges cover both rows and that every included field is meant to be part of the match.
Related
- Excel CSV vs XLSX: Which File Should You Use?
- Excel Dependent Drop-Down Lists: Setup and Fixes
- Excel DOS Keyboard Shortcuts: What Actually Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query XLOOKUP: Matching Data in Excel ExplainedPower Query does not have an XLOOKUP function. To match rows from two tables in Power Query, use…
- Excel Shortcut to Paste a Formula: Keys and Tips in ExcelIn Excel for Microsoft 365 on Windows, copy the formula cell, select the destination, then press Ctrl+Alt+V, press…
- Excel Formula for Percentage Increase: Examples and TipsTo calculate percentage increase in Excel, subtract the old value from the new value, then divide by the…
- Excel Greater Than and Less Than Formulas ExplainedIn Excel, use > to test whether a value is greater than another and < to test whether…
- Excel Formula for Serial Numbers: Methods That WorkThe simplest Excel formula for a serial number is =ROW()-1, entered in A2 when row 1 contains a…
- Excel LEFT Before a Character: Formula and ExamplesTo return the text to the left of a character in Excel, put the text in A2 and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.