Excel Consolidate Lists With Duplicates: Keep or Remove?

To combine lists and keep repeated items in Excel for Microsoft 365 on Windows, enter =VSTACK(A2:A5,B2:B4) in an empty cell, replacing the ranges with your lists. To combine them and return each item only once, use =UNIQUE(VSTACK(A2:A5,B2:B4)). The right method depends on whether duplicates are meaningful records to retain, repeated labels to remove, or values whose numbers should be added together.

What “consolidate” means in Excel

People use “consolidate a list” to describe several different jobs. You might want to stack lists from separate columns or worksheets into one longer list, remove repeated names from that combined list, or summarize matching records such as sales by product. Those jobs call for different Excel tools.

Excel’s built-in Data Consolidate command is primarily a summary tool: it combines values from multiple ranges using a calculation such as Sum or Average. It is not a general-purpose command for appending text entries into one list. For stacking lists, use a dynamic-array formula or Power Query. For a summary by matching labels, Data Consolidate or a PivotTable may be appropriate.

Before choosing a method, decide what counts as a duplicate. Two rows might have the same customer name but different dates or order numbers. Removing one of those rows simply because the name repeats could discard a valid transaction. A repeated value is not automatically an unwanted record.

Combine lists and keep every duplicate

VSTACK places one range below another and spills the combined result into cells beneath the formula. For example, with names in A2:A5 and another list in B2:B4, enter this in an empty cell:

=VSTACK(A2:A5,B2:B4)

If A2:A5 contains Ana, Bo, Ana, and Chen, and B2:B4 contains Bo, Daria, and Chen, the result contains seven entries in that order. Ana, Bo, and Chen each appear twice. This is useful when you are combining attendance records, order lines, or other data where repeated entries may represent separate events.

VSTACK is available in Microsoft 365 and Excel 2024. It is not available in Excel 2021 or older perpetual versions. If you do not have it, copy the second range and paste it beneath the first, or use Power Query for a repeatable process. When using a formula, leave enough empty cells below it; otherwise Excel displays a spill error because something blocks the output.

Combine lists and remove repeated items

UNIQUE returns distinct entries from an array. Combined with VSTACK, it creates one list from several ranges and removes repeated values:

=UNIQUE(VSTACK(A2:A5,B2:B4))

With the example lists above, the output is Ana, Bo, Chen, and Daria, in the order each value first appears. UNIQUE is available in Microsoft 365 and Excel 2021 or later; because this formula also uses VSTACK, the complete formula requires Microsoft 365 or Excel 2024. To sort the distinct result alphabetically, with the same source ranges, use:

=SORT(UNIQUE(VSTACK(A2:A5,B2:B4)))

That returns Ana, Bo, Chen, and Daria in ascending order. Sorting changes the displayed order, so omit SORT if first-seen order carries meaning. These formulas update when source values change, making them preferable to a one-time cleanup when the source lists are regularly updated.

Remove duplicates from an existing combined range

If the lists are already together and you want to clean a fixed result, Excel’s Remove Duplicates command changes the selected data rather than creating a live formula result. Make a copy of the data first if you may need the original. Select the range, then choose Data > Remove Duplicates. In the dialog, select the columns that define a duplicate and confirm.

When you select one column, Excel treats rows with the same value in that column as duplicates, even if other columns differ. If you select several columns, it removes rows only when the values match across all selected columns. Excel keeps the first occurrence it encounters and removes later matching rows. This is why sorting or keeping a backup first can matter if the first record is not the one you want to retain.

For example, if a table contains Customer, Date, and Amount, selecting only Customer may remove later purchases by the same person. Selecting all three columns removes only rows where the customer, date, and amount all match. This command is convenient for a one-off cleanup, but it does not automatically refresh a result when new records are added. On Windows, Ctrl+Z can undo the removal immediately; on Mac, use Command+Z. Do not rely on undo after closing or making other changes.

Append lists with Power Query

Power Query is a good choice when you regularly combine lists from tables, files, or other sources, or when the combined result needs cleanup steps that can be repeated. In Excel for Microsoft 365 on Windows, format each source range as a table with Insert > Table, then create a query from each table using Data > From Table/Range. In the Power Query Editor, choose Home > Append Queries > Append Queries as New, select the two tables, and confirm.

Append stacks rows; it does not match and add records with the same label. Power Query aligns fields by column name, so check that corresponding columns have consistent headers and compatible data types. When one source has a column the other lacks, the appended result can contain null values for that source. Review the preview, then choose Home > Close & Load to return the result to Excel. Later, use Data > Refresh All to rerun the query after the sources change.

To remove repeats in the query, select the column or columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Selecting just Product removes repeated products even if other fields differ; selecting Product and Region retains separate entries for the same product in different regions. Power Query records the cleanup as a step, so refresh applies it again. Check which row is retained when duplicate records contain different details, rather than assuming the remaining row represents the most recent or most accurate information.

Power Query features and available sources can vary by Excel platform and update channel. The menu path above is for Microsoft 365 on Windows. Excel for the web and Mac may not offer the same query-editing experience or every connector; if a menu is missing, use the formula or manual method available in that version.

Use Data Consolidate for summaries, not list stacking

Choose Excel’s Consolidate command when you need to calculate a combined summary from similarly arranged ranges. For example, if several worksheets contain monthly product totals, consolidation can sum matching product labels across the sheets. On Windows, select the destination cell and choose Data > Consolidate. Select a function such as Sum, add each source range, and, if the ranges include labels, select Top row and/or Left column under “Use labels in.”

With label matching enabled, Excel can combine totals for the same label even when the source ranges are not laid out in exactly the same positions. If you select Create links to source data, Excel creates a linked summary structure rather than simply placing fixed results. Consolidate is most useful for numeric summaries; it does not make a clean, distinct text list or preserve each underlying transaction as a separate appended record. For a list of names, use VSTACK, Power Query, or copy and paste instead.

Choose the method that fits the job

Keep every row and make a live combined list: use VSTACK in Microsoft 365 or Excel 2024.
Show each value once in a live result: use UNIQUE with VSTACK where both functions are available.
Clean a fixed range quickly: use Data > Remove Duplicates, after making a backup if needed.
Repeat the same combination and cleanup on refreshed data: append and transform the sources in Power Query.
Add or otherwise summarize matching numeric values: use Data > Consolidate or consider a PivotTable.

The amount and structure of your data also matter. A formula is easy to inspect and recalculates as its source cells change, but it needs compatible Excel functions and clear space for spilled results. Power Query is more suitable for repeatable imports and several cleanup steps, but requires an initial setup and refresh. Remove Duplicates is quick, but it edits the selected data. Consolidate calculates summaries, not a row-by-row union.

Common duplicate and formula problems

Values that look identical may not be identical to Excel. A trailing space, different punctuation, or a number stored as text can make two entries behave differently. For example, “North” and “North ” may appear alike but contain different characters. Inspect source data if UNIQUE returns entries that seem repeated; clean or standardize the source first, and make sure all lists use the same conventions.

Blank cells can also appear in a combined result. If blanks are unwanted, remove them in the source or filter the finished list; do not assume stacking automatically ignores empty rows. Another frequent issue is selecting the wrong duplicate key. If the goal is one row per email address, choose that field as the key, but consider whether the other columns contain information that would be lost when multiple records share an address.

Finally, distinguish duplicate removal from aggregation. If two rows both say “Pens” and contain quantities of 5 and 8, removing one row is not the same as combining the quantities into 13. Use a summary method when the intended result is a total, and retain the detailed rows if you need an audit trail.

Frequently Asked Questions

Does Excel Consolidate remove duplicates?
No. Data > Consolidate summarizes matching labels using a selected calculation. To create a distinct list, use UNIQUE or remove duplicates from the combined range.

How do I combine two columns and keep duplicates?
In Microsoft 365 or Excel 2024, place =VSTACK(A2:A20,B2:B20) in a clear cell, adjusting the ranges to fit. It stacks the values and retains repetitions.

How do I combine lists but show each name once?
Use =UNIQUE(VSTACK(A2:A20,B2:B20)) in Microsoft 365 or Excel 2024. The result updates when the source values change.

Why does Remove Duplicates delete rows I wanted to keep?
The selected columns define what Excel considers a match. If you select only a name column, rows sharing that name can be treated as duplicates even when their dates or amounts differ. Undo immediately if needed, then repeat with the correct columns.

Can Excel combine duplicate product rows and add their quantities?
Yes, but use a summary method rather than simply removing rows. Data > Consolidate can sum matching labels when the source ranges are structured appropriately; a PivotTable is another option for grouping products and totaling quantities.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.