Excel Consolidation Without Duplicates: Practical Methods

Yes. In Microsoft 365, use UNIQUE to create a duplicate-free result, or use Data > Remove Duplicates to delete repeated records from a list. For lists on multiple sheets, stack matching ranges with VSTACK and wrap the result in UNIQUE; choose the method based on whether you need a live result or a one-time cleanup.

What consolidation means in Excel

People use “consolidate” to mean two different things in Excel. One is combining records from several lists into a single list. The other is summarizing numbers from separate ranges, such as adding monthly sales totals. These are different jobs, and the built-in Consolidate command is designed mainly for the second.

If you have customer, inventory, or transaction rows to combine and want repeated records removed, first decide what makes a record a duplicate. It might be an identical row, a repeated customer ID, or a repeated email address. Excel cannot make that business decision for you: the result depends on which columns you compare.

For instance, two rows with the same customer ID but different phone numbers are not identical rows. They may nevertheless represent one customer if the ID is your chosen key. Conversely, two people with the same name may be separate customers. A useful consolidation process therefore starts with a clear key and keeps the original data intact until the result is checked.

Choose what counts as a duplicate

There are two common definitions. An exact-row duplicate has the same values in every compared column. A key-based duplicate has the same value in one or more identifying columns, even if other fields differ. A list containing names, departments, and email addresses might be deduplicated by email if each person should have only one record.

Before cleaning, check whether the source lists use consistent formats. These entries may look equivalent to a person but not match as expected: a number stored as text versus a number, a trailing space after an email, or different date formats. Similar-looking names can also conceal spelling variations. Decide how to resolve those cases rather than assuming Excel can infer which value is correct.

Use all columns when only fully identical rows should disappear.
Use a stable ID column when one entity should appear once, regardless of changes in other fields.
Use multiple key columns when uniqueness depends on a combination, such as product ID plus warehouse.

If two rows share a key but disagree in other fields, removing one can discard useful information. Compare or reconcile those differences first, or use a summary or merge process that makes the conflict visible.

Use UNIQUE for a live, duplicate-free result

The UNIQUE function returns distinct values or rows and recalculates when its source data changes. With names in A2:A100, the following formula returns each distinct name once:

=UNIQUE(A2:A100)

With records in A2:C100, where each row is one record and all three columns should be compared, use:

=UNIQUE(A2:C100)

In both examples, put the formula in a clear cell outside the source range. Excel spills the results into neighboring cells, so those cells must be empty; otherwise, the formula can return a #SPILL! error. The formula does not delete or alter the original records. It creates a separate result that updates as the source range changes, subject to the range you included.

UNIQUE is available in Microsoft 365 and Excel 2021 or later. If new rows may be added, an Excel Table is often more convenient than a fixed range because structured references can expand with the table. For example, if a table is named Records and you want distinct values from its Email column, use =UNIQUE(Records[Email]). The table name and column name must match your workbook.

By default, UNIQUE returns values that occur at least once, with duplicates represented once. Its optional exactly-once setting has a different meaning: it returns only values that appear once in the source. That is not the same as keeping one copy of every value. For example, if an email appears three times, the default result includes it once, while the exactly-once option excludes it.

Combine worksheets with VSTACK and UNIQUE

When two worksheets have the same columns in the same order, Microsoft 365 can stack their records and then return distinct rows. Assume Sheet1 and Sheet2 each contain records in A2:C100, with no headers or unused blank rows in those ranges. Use:

=UNIQUE(VSTACK(Sheet1!A2:C100,Sheet2!A2:C100))

VSTACK places the second range below the first, and UNIQUE removes repeated rows from the combined result. The columns must mean the same thing in both sheets; if one sheet has email in column B and the other has email in column C, align the columns before stacking. Do not include header rows in the ranges, or the header may appear as a record in the result.

Keep the ranges limited to actual data where possible. Oversized ranges can add blank rows to the spill result, and a blank row is still a row for the formula to process. For regularly updated lists, convert each source range to an Excel Table and maintain consistent column names and order. If the sheets have different layouts or many changing sources, Power Query is usually easier to maintain than editing a long formula.

VSTACK is available in Microsoft 365 and Excel 2024. Excel 2021 includes UNIQUE but not VSTACK, so use Power Query, copy the source ranges into one table, or use another compatible method there.

Remove duplicates from a list permanently

For a one-time cleanup, select the data range or click inside its table, then choose Data > Remove Duplicates. In the dialog, confirm whether the first row contains headers and select the columns that define a duplicate. Click OK after checking the selection.

If every column is selected, Excel removes rows whose values match across the full record. If you select only Email, Excel treats rows with the same email as duplicates and removes entire repeated rows, not just the repeated email cells. It keeps one row for each value. This can be useful, but it may discard different information from the other columns, so inspect conflicts first.

This command changes the selected data rather than making a linked output. Make a copy of the worksheet or workbook before using it on important records. Excel displays a summary of how many duplicate values were removed and how many unique values remain. The command can be run again after new data is added, but it does not automatically run whenever the source changes.

In Excel for Microsoft 365 on Windows, the ribbon path is Data > Remove Duplicates. Excel for Mac has a Remove Duplicates command on the Data tab as well, though ribbon organization can differ by release. In Excel for the web, command availability and editing options can depend on the workbook and current web features; if the command is not available, use a supported desktop version or a formula-based result.

Use Power Query for repeatable consolidation

Power Query is useful when you regularly combine files, worksheets, or tables and want to repeat the same cleanup on refreshed data. It keeps the source data separate from the transformation steps, so you can refresh the query instead of manually combining and cleaning the lists again.

Turn each source range into a table, or import the relevant files through the Data tab.
Load the tables or files into Power Query and confirm that their column names and data types match.
In the Power Query Editor, choose Home > Append Queries > Append Queries as New to combine tables with the same general layout.
Select the columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates.
Review the preview, then use Close & Load to return the result to Excel.

When you select only key columns for removal, Power Query uses those columns to identify duplicate rows. If the remaining columns contain conflicting information, do not assume that the row retained will be the one you prefer. Decide how to resolve conflicts explicitly—for example, choose the latest dated record or group records and calculate a summary—before relying on the output.

Power Query authoring options and connectors vary between Windows, Mac, and Excel for the web. The steps above describe the Windows desktop experience in Microsoft 365. If your version does not offer the same import or editing options, a UNIQUE formula or the Remove Duplicates command may be a simpler alternative.

Why the Consolidate command is different

Excel also has a Data > Consolidate command. It combines or summarizes values from separate ranges using a function such as Sum, Average, or Count, optionally matching labels in rows or columns. It is useful for totaling several monthly reports that share a structure.

That command is not a general-purpose way to append records and remove duplicate rows. If you use Sum on overlapping source data, repeated amounts can be counted more than once. Use UNIQUE, Remove Duplicates, or Power Query for record lists; use Consolidate when the goal is a numerical summary across ranges.

Common problems and checks

A result that still appears to contain duplicates often reflects inconsistent source values or a different definition of uniqueness than expected. Before repeating the cleanup, check the selected columns, the headers, and the actual cell contents. A space at the end of a text value can make two entries different, and dates or IDs may be stored in inconsistent formats.

Unexpectedly few rows: You may have selected only a key column in Remove Duplicates, causing rows with that key to be treated as repeats even when other fields differ.
Unexpectedly many rows: Extra spaces, spelling variations, or inconsistent number and date formats may prevent apparent matches.
Formula will not spill: Clear the cells where the result needs to appear. A nonempty cell in the spill area causes #SPILL!.
Blank results appear: Check for blank rows included in the source ranges, especially in a VSTACK formula.
Different details disappear: Repeated keys may have conflicting non-key values. Review those records before removing anything.

For text cleanup, Excel’s TRIM function can remove ordinary extra spaces at the beginning and end of text, but it does not resolve spelling differences or every type of invisible character. Cleaning source data before deduplication is often safer than trying to guess which similar entries should be merged afterward.

Which method should you choose?

Choose based on how the result will be used, how often the inputs change, and whether duplicates are defined by a full row or a key:

UNIQUE: Best for a live, formula-driven list that should update without altering source data.
VSTACK with UNIQUE: Best for a small number of similarly structured ranges in a Microsoft 365 workbook.
Remove Duplicates: Best for a direct, one-time cleanup when you have already chosen the comparison columns and backed up the data.
Power Query: Best for recurring imports or several sources that need repeatable combining and cleanup steps.
Consolidate: Best for summarizing matching numeric ranges, not for eliminating duplicate records.

Whichever method you choose, retain the source or a backup until you have checked the output. Compare the row count, test a few known repeated keys, and review records where the same key has different details. A duplicate-free result is only reliable when the definition of a duplicate fits the data and the purpose of the report.

Frequently Asked Questions

How do I combine two Excel lists and remove duplicates?
In Microsoft 365, if both lists have matching columns and the records are in A2:C100 on Sheet1 and Sheet2, enter =UNIQUE(VSTACK(Sheet1!A2:C100,Sheet2!A2:C100)) in an empty area. Exclude headers and unused rows. For a repeatable import, append the tables in Power Query and remove duplicates there.

Does Remove Duplicates delete the original data?
Yes. It removes repeated rows from the selected range or table. Make a copy first if you may need the original, or use UNIQUE to create a separate result without changing the source.

How do I remove duplicates based on one column but keep the other data?
Use Data > Remove Duplicates and select only the key column. Excel removes entire repeated rows, so first check whether the other columns disagree. If they do, decide which record to keep or how to combine the details before deleting rows.

Can Excel remove duplicates automatically when I add new rows?
A UNIQUE formula can recalculate as its source expands, especially when the source is an Excel Table. Remove Duplicates is a one-time action, and Power Query updates when you refresh the query rather than immediately changing the source.

Why does Excel still show records that look identical?
They may differ in spaces, spelling, data type, or another column included in the comparison. Inspect the underlying values and normalize the data where appropriate; also confirm that you chose the intended key columns.

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.