Yes, you can list duplicate values in a spreadsheet column using a filter — the standard approach is to apply a COUNTIF formula in a helper column, then filter that column to show only counts greater than 1. This works in both Microsoft Excel and Google Sheets, and the exact steps are nearly identical.
Formula
The core formula to flag duplicates is:
=COUNTIF(range, cell)
Where:
- range — the entire column or list you are checking (e.g., A:A or A2:A100).
- cell — the first cell in that range for the current row (e.g., A2).
COUNTIF counts how many times the value in cell appears within range. If the result is greater than 1, that value is a duplicate.
Worked Example
Suppose column A contains the following values in rows 2 through 7:
- A2: Apple
- A3: Banana
- A4: Apple
- A5: Cherry
- A6: Banana
- A7: Apple
In cell B2, enter the formula:
=COUNTIF(A$2:A$7, A2)
Copy this formula down to B7. The results will be:
- B2: 3 (Apple appears 3 times)
- B3: 2 (Banana appears 2 times)
- B4: 3
- B5: 1 (Cherry appears once)
- B6: 2
- B7: 3
Now apply a filter to the range (Data → Filter). Click the dropdown arrow in cell B1 and uncheck the value “1�. The visible rows will show only the duplicate entries (all rows except the Cherry row).
Why Use a Helper Column?
A helper column is a temporary column you add next to your data to perform intermediate calculations. It is the most flexible and widely compatible method because it works with any version of Excel (2010, 2013, 2016, 365) and Google Sheets, without needing advanced features like dynamic arrays or Power Query.
The helper column approach also lets you see the actual count for each row, which can be useful if you want to know how many times a value appears, not just whether it is duplicated. You can also sort by the count column to group all duplicates together.
Step-by-Step: Excel and Google Sheets
- Select the column that contains your data (e.g., column A).
- Insert a new column to the right (right-click on the column header and choose “Insert�).
- In the first data cell of the new column (e.g., B2), type the formula:
=COUNTIF(A:A, A2)— adjust the range to match your data. - Copy the formula down to the last row of your data.
- Select the entire data range (including the helper column).
- Go to the Data tab and click the Filter icon (or use the shortcut Ctrl+Shift+L on Windows, Cmd+Shift+L on Mac).
- Click the dropdown arrow on the helper column header.
- Uncheck the value “1� (and any other numbers you want to exclude, like 0 if there are blanks).
- Click OK. Now only rows with duplicate values are visible.
In Google Sheets, the steps are the same, but the filter icon is found in the toolbar (it looks like a funnel).
Common Mistakes and How to Avoid Them
- Using relative ranges incorrectly — If you enter
=COUNTIF(A2:A7, A2)and copy down, the range will shift for each row, causing incorrect counts. Always use absolute references (with $ signs) for the range, e.g.,=COUNTIF(A$2:A$7, A2). - Filtering the wrong column — Make sure you filter the helper column, not the original data column. The helper column contains the counts.
- Hidden rows after filtering — When you filter, rows that do not meet the criteria are hidden, but they are not deleted. If you copy the visible rows, only the duplicates will be copied.
- Case sensitivity — COUNTIF is not case-sensitive. “Apple� and “apple� are considered the same. If you need case-sensitive matching, you would need a different formula (e.g., SUMPRODUCT with EXACT).
- Trailing spaces — Extra spaces in text can make two values look different even though they are visually identical. Use the TRIM function to clean data before counting.
Alternatives to a Helper Column
If you prefer not to add a helper column, there are other methods:
- Conditional Formatting — Select your data, go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. This highlights duplicates in place, but it does not list them separately; you still need to filter or sort by color to isolate them.
- Remove Duplicates — This is a built-in tool (Data → Remove Duplicates) that deletes duplicate rows, leaving only unique values. It does not list duplicates; it removes them.
- Pivot Table — Create a pivot table with the field in Rows and also in Values (as Count). This gives you a summary of each value and its count, but it does not show the original rows.
- Advanced Filter (Excel) — You can use the Advanced Filter to copy unique records to another location, but again, it does not list duplicates.
- Power Query (Excel 365/2016+) — You can load the data into Power Query, group by the column, and filter counts greater than 1. This is more advanced but powerful for large datasets.
Frequently Asked Questions
How do I list only the duplicate values, not the entire rows?
After filtering the helper column to show counts > 1, you can copy the visible cells in the original data column and paste them elsewhere. The filter hides non-duplicate rows, so the copy will contain only the duplicate values (including repeated occurrences). If you want a unique list of duplicates, use the Remove Duplicates tool on the copied list.
Can I use the filter to show duplicates without a helper column?
No, the standard filter cannot directly detect duplicates because it filters based on cell values, not on their frequency. The helper column is the simplest way to make duplicate detection possible through filtering.
Why does my COUNTIF formula return 0 for some cells?
This usually happens when the range is not correctly defined or when the cell contains a number stored as text. Ensure the range matches the data area and that all values are in the same format (all text or all numbers).
Does this method work on a column with blank cells?
Yes, but blanks are counted as duplicates if there are multiple blanks. To exclude blanks, you can modify the formula to =IF(A2="","",COUNTIF(A:A,A2)) so that blank cells show an empty string, and then filter out those empty cells as well.
Can I use this method on multiple columns at once?
The COUNTIF method works on a single column. To find duplicates across multiple columns, you would need to concatenate the columns into a helper column first (e.g., =A2&"-"&B2), then apply the same COUNTIF and filter technique on that concatenated column.
Is there a way to list duplicates without using formulas at all?
Yes, you can use Conditional Formatting to highlight duplicates, then sort by color to group them. However, this does not create a separate list; it only visually marks them. For a true list, the formula-based approach is the most direct.