Excel Consolidate List of Names: 10 Practical Methods

For a quick master list in Excel for Microsoft 365, stack the source ranges with a formula. If A2:A4 contains Ada, Ben, and Cara, while C2:C3 contains Diego and Eva, enter =VSTACK(A2:A4,C2:C3) in an empty cell; the result is the five names in one column. The methods below cover formulas, Power Query, and built-in tools, so Excel users can choose an approach that suits one-time cleanup or a list that needs to stay current.

1. Stack two ranges with VSTACK

VSTACK places one range below another and spills the combined result into neighboring cells, so you do not have to insert rows or copy each name by hand. It is useful when two lists have the same kind of information in separate columns or sections of a worksheet, and you want a simple master column that updates when source entries change. For example, it can combine an employee list from one department with a second department’s list while preserving the order within each source. VSTACK is available in Microsoft 365 and Excel 2024; older Excel versions will not recognize it.

2. Combine lists from different worksheets

VSTACK can also refer to ranges on separate sheets, which is convenient when each month, office, or team maintains its own tab. Use clear, stable sheet names and matching column layouts; if a sheet name contains spaces, Excel requires single quotation marks around it in the reference. This method keeps a formula-based master list linked to its source sheets, but adding a new sheet later does not automatically add that sheet to the formula—you must edit the formula or choose a more scalable method such as Power Query.

3. Flatten a multi-column block with TOCOL

When names are spread across several columns instead of sitting in separate clean lists, TOCOL can turn a rectangular range into one column and ignore blank cells. With A2:C4 containing Ada, Ben, and Cara in column A; Diego in B3; and Eva and Fran in column C, enter =TOCOL(A2:C4,1) in an empty cell; it returns Ada, Ben, Cara, Diego, Eva, and Fran, scanning down each column. This is helpful for rosters pasted into a grid, but it follows column order, so confirm that this is the order you want. TOCOL requires Microsoft 365 or Excel 2024.

4. Remove repeated names with UNIQUE

Consolidating names often leaves duplicates, especially when different teams submit overlapping lists. If A2:A4 contains Ava, Ben, and Ava, and C2:C4 contains Ben, Cara, and Ava, enter =UNIQUE(VSTACK(A2:A4,C2:C4)) in an empty cell; the result is Ava, Ben, and Cara, keeping the first-seen order. UNIQUE is worth using when the goal is one entry per name rather than a record of every occurrence. It removes exact duplicate text values for this purpose, but it does not standardize spelling, extra spaces, or different forms such as “Alex Smith” and “Alex S.”

5. Create an alphabetical, duplicate-free list

For a directory or lookup list, a sorted result is usually easier to scan than names left in the order they were submitted. If A2:A4 contains Maya, Ben, and Asha, and C2:C4 contains Ben, Liam, and Asha, enter =SORT(UNIQUE(VSTACK(A2:A4,C2:C4))) in an empty cell; it returns Asha, Ben, Liam, and Maya in ascending order. Combining SORT, UNIQUE, and VSTACK makes a compact formula-driven workflow, and edits to the source ranges recalculate the list. Leave enough empty space below the formula, because a dynamic-array result cannot spill into occupied cells.

6. Append Excel tables with Power Query

Power Query is a strong choice when several structured lists need to be combined repeatedly, particularly when the source data has headers or requires cleanup. Convert each range to a table with Insert > Table, then use Data > Get Data > From Table/Range to load each table into Power Query; in the editor, choose Home > Append Queries and select the tables to stack. Make sure the name columns have matching headers and compatible data types, then choose Home > Close & Load to return the result to Excel. When source tables change, use Data > Refresh All; the query output is refreshed rather than being a live formula spill.

7. Combine name files from a folder

If names arrive as multiple Excel or CSV files, importing the folder avoids opening and copying each file separately. In Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From Folder, browse to the folder, and use Combine & Transform Data to inspect the files before loading the result. This works best when the files share a consistent structure, such as the same column heading and layout; inconsistent headers or extra title rows can produce messy results that need correction in Power Query. Save new source files in the selected folder and refresh the query to bring them in.

8. Use Advanced Filter to copy unique names

Advanced Filter is a built-in option for a one-time list of unique values when you do not want a dynamic formula. Put the names in a single column with a header, select the data, and choose Data > Advanced; select Copy to another location, specify the destination under Copy to, and check Unique records only. Excel copies one instance of each distinct entry to the chosen location, leaving the original source intact. This option is useful in older Excel versions too, but it does not create a refreshable connection: repeat the filter after the source list changes.

9. Display distinct names in a PivotTable

A PivotTable can display each distinct name once as a row label, making it a practical way to inspect a consolidated source list or count how often each name appears. First place the names in one column, select the range or table, choose Insert > PivotTable, and drag the Name field into the Rows area; adding Name to Values can show a count when the field is summarized by Count. A PivotTable does not automatically append unrelated worksheets, so combine the source lists first or prepare them in Power Query. Refresh the PivotTable after source data changes, and check its source range if new rows are not included.

10. Copy and paste for a small, one-time merge

For two short lists that will not need updating, copying one column and pasting it below the other is often the quickest solution. Copy the first list, paste it into a blank destination column, then paste the second list directly below the last used name; include the header only once and inspect the join for blank rows. This manual approach works in almost every Excel version and needs no formula knowledge, but it will not update when the originals change. If duplicates matter, sort the combined column and review repeated entries, or use Data > Remove Duplicates on a copy of the merged data.

How to Choose

Start with how often the lists change. For a small, one-time merge, copy and paste or Advanced Filter may be enough; for a linked list from a few known ranges, VSTACK with UNIQUE and SORT is faster to maintain. If you receive recurring files or several source tables, Power Query is generally more robust because it records the transformation steps and can refresh from updated data. For a folder of monthly exports, the folder connector can bring in matching files as a batch.

Next, decide whether every occurrence matters. Use VSTACK or Power Query append when you need to preserve every submitted row, including repeated names; use UNIQUE or Advanced Filter when you need each name only once. Consider whether the order is meaningful: VSTACK keeps source order, whereas SORT alphabetizes the result. Also check what “same name” means for your task. A formula will not automatically resolve spelling variants, leading or trailing spaces, different punctuation, or names with different formatting. Clean those issues before deduplicating if the records refer to the same person.

Finally, consider compatibility and maintenance. Dynamic-array functions such as VSTACK, TOCOL, UNIQUE, and SORT require Microsoft 365 or Excel 2024; recipients using older Excel may see an unsupported-function error. Power Query is more suitable for repeatable imports, but it requires a refresh and a consistent source structure. If your master list feeds other formulas, tables and clearly labeled columns make the workbook easier to audit. Keep a separate copy of source data before applying destructive actions such as Remove Duplicates.

Frequently Asked Questions

Can Excel combine names from multiple worksheets automatically?

Yes. In Microsoft 365 or Excel 2024, a VSTACK formula can reference ranges on several sheets and spill their names into one column. If new worksheets are added later, update the formula or use a Power Query process designed to include those sources.

How do I consolidate names and remove duplicates?

Use UNIQUE around the stacked ranges, or use Advanced Filter with Unique records only for a one-time copy. Check for spelling differences and unwanted spaces first, because those can make entries that look similar remain separate values.

Why does my VSTACK formula show a #SPILL! error?

The cells where the result needs to appear may not be empty, or the formula may be inside an Excel Table that blocks a spilled array. Clear the obstructing cells or place the formula in a blank area outside the table.

Will the consolidated list update when I edit the source names?

A formula-based list recalculates when referenced cells change. Power Query and PivotTables generally need a refresh, while a copied-and-pasted list does not update automatically.

Can I combine names from multiple workbooks?

Yes. Power Query can import individual workbooks or combine files from a folder, provided the source files have a sufficiently consistent layout. For just a few files, you can also copy their name columns into one workbook and then use a formula or filter.

Final Thoughts

For a quick, change-responsive list, use VSTACK; add UNIQUE when repeats should disappear and SORT when alphabetical order matters. Choose Power Query for recurring sources or batches of files, and keep manual copying for small jobs that will not need refreshing. Whichever method you use, verify the result against the original lists and decide how to handle spelling variations before treating names as duplicates.

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.