Function Problems

How to Extract and Display Duplicate Excel Values in a Separate Column

Bushra Parveen Sep 30, 2026 870 views

Question details

The user needs to extract duplicated values from an existing column and display them in a separate, smaller list without causing the spreadsheet application to freeze or crash.

How to Extract and Display Duplicate Excel Values in a Separate Column

Product: Excel
Device & OS: not provided
Scenario: Identifying and extracting duplicate entries from a dataset for further analysis into a new column.
Observed behavior: Applying array formulas to entire columns causes the application to hang, freeze, or incorrectly return a zero value when clicking the sheet.

Before you start

Identify the exact range of your data (for example, C1:C100) instead of selecting the entire column. Using full column references in complex array formulas forces the software to calculate over a million rows, which severely impacts performance and causes freezing.

Solution 1Recommended

Extract All Duplicated Entries Using FILTER and COUNTIF

Use the FILTER and COUNTIF functions together with a strictly defined data range to extract every instance of duplicated values efficiently.

This formula searches through your specified range and returns all values that appear more than once. Because it limits calculations to a specific range, it avoids the processing overload that causes freezing.

Select the destination cell

Click on the top cell of the separate column where you want the extracted duplicates to appear.

Enter the extraction formula

Type the formula: =SORT(FILTER(C1:C100,COUNTIF(C1:C100,C1:C100)>1,"")). Be sure to adjust 'C1:C100' to match your actual data range.

Apply the formula

Press Enter to generate the list of all duplicated values, automatically sorted in order.

Extract All Duplicated Entries Using FILTER and COUNTIF

Performance Tip: Using a defined range like C1:C100 rather than a full column reference like C:C is the key to preventing the spreadsheet from hanging.

Solution 2

Extract Only Unique Duplicate Values

If a value appears multiple times but you only want it listed once in your new column, wrap your extraction formula with the UNIQUE function.

Process Large Datasets Smoothly

Use WPS Spreadsheet to Manage Duplicates Fast

WPS Spreadsheet offers excellent performance for handling complex array formulas like FILTER and UNIQUE. You can efficiently manage, extract, and highlight duplicate data without experiencing freezes or slowdowns.

1. Open your data file: Launch WPS Spreadsheet and open your document.
2. Identify your data range: Note the exact range of your dataset (e.g., A1:A500) to optimize calculation speed.
3. Apply the formula: Type the =SORT(UNIQUE(FILTER(...))) formula into your target cell using your specific range.
4. View your duplicates: Press Enter to instantly view your extracted duplicate values in a separate column.

Fast and smooth calculation of complex array formulas without freezing.Fully compatible with Microsoft Excel functions, formulas, and formats.Built-in intuitive tools to highlight or remove duplicates instantly.Lightweight architecture runs efficiently even on older devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my spreadsheet freeze when extracting duplicate values?

Array formulas like FILTER and COUNTIF require significant processing power. If you apply them to an entire column (which contains over a million rows), the application will attempt millions of calculations simultaneously, causing it to hang or freeze. Always define specific ranges like C1:C100 to avoid this.

Can I just highlight the duplicates instead of extracting them to a new column?

Yes. You can use the Conditional Formatting feature. Select your data range, navigate to the Home tab, click on Conditional Formatting, choose Highlight Cells Rules, and then select Duplicate Values to visually identify them with a color.

What does the empty string ("") do at the end of the FILTER formula?

The empty string ("") acts as the 'if_empty' argument in the FILTER function. If the formula finds no duplicate values within your specified range, it will display a clean, blank cell instead of showing an unsightly #CALC! error.

Excel & Data Problems