How to copy a column and paste only the unique records in Excel?
When working with Excel data that contains numerous duplicate entries, you might often need to create a version of your column that contains only the unique values, excluding all repetitions. This demand typically arises in tasks such as generating summary lists, preparing data for reports, or analyzing distinct items in large datasets. If you're unsure how to efficiently extract and copy just the unique records from a column, this tutorial provides practical and easy-to-follow solutions for various Excel versions and user preferences.
Paste only the unique records from a list with the Advanced Filter function
Paste only the unique records from a list with Kutools for Excel
Paste only the unique records using the Excel UNIQUE function
Paste only the unique records using a VBA macro
Paste only the unique records from a list with the Advanced Filter function
When you want to copy and paste only the unique records from a column in Excel, especially for lists with many duplicate entries, the built-in Advanced Filter feature is a reliable choice.
1. Begin by selecting the column that contains the values you wish to copy uniquely. Then, go to the Data tab and click Advanced in the Sort & Filter group. See screenshot:
2. In the Advanced Filter dialog box, follow these steps with care:
- Choose the Copy to another location radio button in the Action section—this ensures that the results appear elsewhere without affecting the original data.
- In the Copy to box, click the
button and select a blank cell where you want the unique values to be placed. Selecting an empty destination helps avoid overwriting any existing data. - Check the Unique records only box to instruct Excel to exclude any duplicates from the extracted results.
- Click the OK button to complete the operation. See screenshot:
button and select a blank cell where you want the unique values to be placed. Selecting an empty destination helps avoid overwriting any existing data.
After completing these steps, the specified blank cell location will display a list containing only the unique values extracted from your original column. Please note that the filtered list does not maintain any specific order and the process doesn’t dynamically update if the source data changes. If you modify your original list, you'll need to repeat these steps as needed.
Tips:
- If your original data includes a header, ensure that this is included in the selection for accurate filtering.
- If you encounter an error stating "List range must be a single row or a single column," confirm that your selected range is consistent.
- This method is ideal for one-off extractions but less suited for lists that frequently update, as the filtering result is static.
Paste only the unique records from a list with Kutools for Excel
If you prefer a streamlined process or your workflow often involves managing unique and duplicate records, Kutools for Excel provides a specialized utility that simplifies these tasks. The Select Duplicate & Unique Cells utility enables users—without deep Excel knowledge—to quickly identify, select, and copy unique values from any column list. This is especially useful for business data cleanup, preparing mailing lists, or organizing survey responses.
Kutools for Excel: includes more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now for 30-days free trial
1. First, highlight the list where you want to extract and paste only the unique values. Then, navigate to Kutools > Select > Select Duplicate & Unique Cells from the top menu.
2. In the dialog box that appears, select All unique (Including 1st duplicates). This option ensures that first occurrences of duplicate values are retained as unique, while repetitions are ignored. Click OK. In the next confirmation dialog that pops up, click OK again. See screenshot:
Note: If you want to extract only values that never repeat in the list (true uniques), select the Unique values only option. This is helpful if you need to find items that occur once and eliminate all duplicates entirely.
3. After confirming, Kutools will automatically select all the identified unique values in your original list. You can then copy them by pressing Ctrl + C and paste the results into a new column or sheet using Ctrl + V .
Important reminders:
- This method is suitable for both small and large datasets and provides instant visual feedback of selection.
- Be careful when copying and pasting—ensure no other cells are selected to avoid unintentional overwriting.
- If you have multiple columns and each row is considered as a unique entry, the utility also supports selection by rows instead of just columns.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Paste only the unique records using the Excel UNIQUE function
For users working with Excel 365, Excel 2021 and later versions, the UNIQUE function provides a dynamic and highly efficient way to extract unique values from a column. This method is ideal if you want the results to automatically update whenever your original data changes, such as in dashboards or regularly-updated summary tables.
1. In the first cell of your destination column (for example, cell B1), enter the following formula:
=UNIQUE(A1:A100)Here, replace A1:A100 with the range of your actual data. You can adjust the range to fit your list as needed.
2. Press Enter. Excel will instantly spill all unique values from your original column into the adjacent cells below your formula. The result updates automatically whenever the source data changes.
Tip: If you wish to extract unique values from rows instead of columns, or vice versa, ensure you set the formula range accordingly.
- Pros: Dynamic updating, easy to use, and no need for manual filtering or copying.
- Cons: Only available in Excel 365, Excel 2021 and later versions. The result will overwrite any existing data located below the formula cell, so plan your layout to avoid data loss.
If you prefer to have a static copy of the unique values after extraction (for sharing or further editing), simply copy the spilled list and use Paste Special > Values in a new location.
Paste only the unique records from a column with VBA code
If you frequently need to extract and copy unique records, or you want an automated way to perform this task on large datasets, writing a simple VBA macro can complete this process quickly. This approach suits users comfortable with basic programming or in scenarios where manual filtering would be too time-consuming.
1. Click Developer Tools > Visual Basic. This will open the Microsoft Visual Basic for Applications editor window. In the editor, click Insert > Module, and input the following code into the module:
Sub ExtractUniqueRecords()
Dim rng As Range
Dim dest As Range
Dim uniqueCol As Collection
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the source range:", xTitleId, rng.Address, Type:=8)
Set dest = Application.InputBox("Select the destination (top cell):", xTitleId, "", Type:=8)
Set uniqueCol = New Collection
For Each cell In rng
If Len(cell.Value) > 0 Then
uniqueCol.Add cell.Value, CStr(cell.Value)
End If
Next
For i = 1 To uniqueCol.Count
dest.Offset(i - 1, 0).Value = uniqueCol(i)
Next
End Sub2. Click the 
button (or press F5) to run the macro. You will be prompted to select the source range to extract unique values from, and then specify the destination cell (where the results will be pasted). Once done, the macro will fill the destination column with the unique records.
Tips and troubleshooting:
- Before running the macro, make sure to select the intended source range and ensure the destination range has enough space for the results.
- This VBA code skips blank cells and treats text/numbers the same. If your data type varies, review results for accuracy.
- If you receive a “subscript out of range” error, check that your selection matches what’s expected (single column, no merged cells).
VBA automation is highly flexible for batch operations, and you can assign this macro to a button for even easier repeated use.
Additional suggestions and troubleshooting
- Always back up important data before performing copy-paste operations that potentially overwrite cells.
- When using formula-based solutions, especially the UNIQUE function, be mindful of Excel version compatibility.
- Combining methods (for example, using UNIQUE first and then Kutools for additional formatting) can help optimize workflow for complex tasks.
- Sometimes, formatting issues (hidden rows/columns, merged cells) may affect results—always review the extracted list for completeness.
- If you encounter unexpected outcomes or system errors, restart Excel to clear any temporary glitches, and review your range selection steps.
Related article:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in