To combine two or more lists in Excel and keep only unique values, use the UNIQUE function with the VSTACK function: =UNIQUE(VSTACK(range1, range2)). This works in Excel 365 and Excel 2024. For older versions, use a Power Query merge or a pivot table.
Why You Might Need to Combine Unique Lists
Combining lists while removing duplicates is a common cleanup task. You might have a list of customer emails from two different campaigns, product SKUs from separate suppliers, or employee IDs from different departments. The goal is to merge them into one master list where each item appears only once. Doing this manually with copy-paste and Remove Duplicates works for small datasets, but it’s error-prone and slow when lists change frequently. Excel’s dynamic array functions make the process automatic and recalculating.
The Modern Way: UNIQUE + VSTACK (Excel 365/2024)
If you have Microsoft 365 or Excel 2024, this is the simplest method. VSTACK stacks ranges vertically, and UNIQUE removes duplicates.
Example: Suppose you have list A in cells A2:A10 and list B in C2:C8. In cell E2, enter:
=UNIQUE(VSTACK(A2:A10, C2:C8))
This returns a single column of all unique values from both ranges. If your lists are in rows, use HSTACK instead of VSTACK.
Important: VSTACK requires that the columns match in number. If your lists have headers, include them in the range and then filter them out with FILTER if needed. For example, if both lists have a header in row 1, use:
=UNIQUE(VSTACK(A2:A10, C2:C8))
and the header will be included as a value—so it’s better to exclude headers from the ranges.
Using Power Query (Excel 2016 and Later)
Power Query is a robust tool for combining and deduplicating data, and it works in all Excel versions from 2016 onward (including Excel 2019 and 2021). It’s especially useful when you need to refresh the result when the source lists change.
- Go to Data > Get & Transform Data > From Table/Range for each list (select the range, then click).
- In the Power Query Editor, use Append Queries (Home > Append Queries > Append Queries as New) to stack the two queries.
- Select the column(s) you want to deduplicate, then go to Home > Remove Rows > Remove Duplicates.
- Click Close & Load to put the result in a new worksheet.
Power Query creates a connection that you can refresh (right-click the result table > Refresh) when the source data changes. This is more manual than UNIQUE but works in older versions.
Using a Pivot Table (Any Version)
A pivot table is another way to get a unique list, though it’s less direct. Place both lists in a single column (manually or with a formula), then create a pivot table from that combined column and put the field in the Rows area. The pivot table automatically shows only unique values.
Example: If you have lists in A2:A10 and C2:C8, first combine them in a helper column, say in E2:
=IFERROR(INDEX($A$2:$A$10, ROW()-1), INDEX($C$2:$C$8, ROW()-1-ROWS($A$2:$A$10)))
But this is clunky. A simpler approach: copy both lists into one column (e.g., paste A2:A10 into E2, then paste C2:C8 below), then select that column and go to Insert > PivotTable. Drag the field to Rows. The pivot table shows unique values, and you can copy the row labels and paste as values if you need a static list.
How to Choose the Right Method
Your choice depends on your Excel version and whether you need a dynamic result.
- Excel 365/2024: Use
UNIQUE+VSTACKfor a formula that updates automatically when source cells change. - Excel 2016–2021: Use Power Query if you want a refreshable result; use a pivot table if you prefer a manual but simple approach.
- All versions: If your lists are small and static, the manual copy-paste + Remove Duplicates (Data > Data Tools > Remove Duplicates) is the quickest.
For one-off tasks, the manual method is fine. For ongoing reports, dynamic formulas or Power Query save time.
Common Problems and Troubleshooting
Here are issues you might hit and how to fix them.
- #VALUE! error with VSTACK: This usually means the ranges have different column counts. Ensure both ranges have the same number of columns.
- UNIQUE not available: You’re on Excel 2019 or earlier. Use Power Query or a pivot table instead.
- Blank cells in lists:
UNIQUEtreats blanks as a value and will return a blank row. To ignore blanks, wrap withFILTER:=UNIQUE(FILTER(VSTACK(A2:A10, C2:C8), VSTACK(A2:A10, C2:C8)<>"")). - Case sensitivity:
UNIQUEis case-insensitive by default (it treats “Apple” and “apple” as the same). If you need case-sensitive, useUNIQUEwith theexactargument? Actually,UNIQUEdoes not have a case-sensitive option; you’d need a more complex formula or Power Query with a case-sensitive comparison.
Comparing Dynamic Arrays vs. Power Query
Dynamic arrays (UNIQUE, VSTACK) are formula-based and recalc instantly when source data changes. They are ideal for small to medium datasets (up to a few thousand rows) and for users who prefer formulas. Power Query is better for large datasets (hundreds of thousands of rows) because it doesn’t recalc on every change; you refresh manually. Power Query also preserves data types and can handle complex transformations beyond simple stacking.
One key difference: dynamic arrays spill results that can be referenced by other formulas, while Power Query outputs a table that you can reference with structured references. Both are valid; choose based on your workflow.
Frequently Asked Questions
Can I combine more than two lists?
Yes. With VSTACK, you can stack as many ranges as you need: =UNIQUE(VSTACK(A2:A10, C2:C8, E2:E5)). In Power Query, use Append Queries multiple times or select multiple tables when appending.
What if my lists are in different worksheets?
You can reference ranges from different sheets in the formula, e.g., =UNIQUE(VSTACK(Sheet1!A2:A10, Sheet2!A2:A8)). In Power Query, you’d create queries from each sheet and then append them.
How do I keep the original order of items?
UNIQUE preserves the order of first appearance. If you want to sort the result, wrap with SORT: =SORT(UNIQUE(VSTACK(A2:A10, C2:C8))).
Does this work for text and numbers?
Yes, UNIQUE works with any data type, including dates and mixed types. Just ensure the ranges are aligned in columns.
Can I combine lists horizontally instead of vertically?
Use HSTACK instead of VSTACK to place ranges side by side, then apply UNIQUE to the resulting array. For example, =UNIQUE(HSTACK(A2:A10, C2:C8)) would return unique values from both columns as a single row? Actually, HSTACK stacks columns, so UNIQUE would treat each row as a record. For a single list, vertical stacking is usually what you want.
Why does my UNIQUE formula return a #SPILL! error?
This happens when there isn’t enough space for the result to spill. Clear cells below or to the right of the formula cell, or move the formula to a column with empty space.
Related
- Train-Test Split: Purpose, Methods, and Common Mistakes
- PostgreSQL: How to Execute a SQL String
- MS Access Split Form: Put the Datasheet on the Bottom
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Cell Fill Shortcut: Fill Down, Fill Right, and ColorIn Excel for Microsoft 365 on Windows, press Ctrl+D to fill down from the top cell of a…
- Excel Slicer to Filter Data: How to Set Up and Use ItTo filter data with an Excel slicer, select a table cell and choose Table Design > Insert Slicer,…
- Excel Table Formulas Not Carrying Down? Fixes That WorkIf a formula in an Excel table is not carrying down, first confirm the data is an actual…
- Excel Text Right of Character: Formulas and ExamplesTo return the text to the right of a character in Excel, use TEXTAFTER in Microsoft 365 or…
- Copy and Transpose in Excel: Steps, Formulas, and TipsTo copy and transpose data in Excel for Microsoft 365 on Windows, select and copy the cells, choose…
- How to Add a New Line in Excel CONCATENATETo add a new line between text values joined with Excel’s CONCATENATE function, insert CHAR(10) between the cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.