Excel Combine Unique Lists: 3 Ways

Formulas & functions · Updated November 22, 2026

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.

  1. Go to Data > Get & Transform Data > From Table/Range for each list (select the range, then click).
  2. In the Power Query Editor, use Append Queries (Home > Append Queries > Append Queries as New) to stack the two queries.
  3. Select the column(s) you want to deduplicate, then go to Home > Remove Rows > Remove Duplicates.
  4. 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 + VSTACK for 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: UNIQUE treats blanks as a value and will return a blank row. To ignore blanks, wrap with FILTER: =UNIQUE(FILTER(VSTACK(A2:A10, C2:C8), VSTACK(A2:A10, C2:C8)<>"")).
  • Case sensitivity: UNIQUE is case-insensitive by default (it treats “Apple” and “apple” as the same). If you need case-sensitive, use UNIQUE with the exact argument? Actually, UNIQUE does 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.