Excel: How to Consolidate Multiple Rows Into One Cell

How-to basics · Updated November 19, 2026

If your goal is to combine values from matching rows into one cell, put the matching key in E2 and use this formula with IDs in A2:A100 and details in B2:B100: =TEXTJOIN(", ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2,"")). It joins every detail whose ID matches E2, separating the results with commas; for other meanings of “consolidate,” such as adding duplicate rows or combining tables, use a different method.

What “consolidate rows” can mean in Excel

Excel users use “consolidate” to describe several different jobs. The right solution depends on whether you want a readable list in one cell, a summary of numbers, or a smaller table with one record per key. These outcomes are not interchangeable: joining text preserves the individual entries, while summing produces a numerical total.

For example, imagine a list with an ID in column A and a product or note in column B. ID 104 appears three times, with “Notebook,” “Pen,” and “Folder” in the detail column. You might want one row for ID 104 and a cell that reads “Notebook, Pen, Folder.” That is a text-combination task. If the rows instead contain quantities and you need their total, you want aggregation. If each row comes from a separate worksheet or file, you may first need to append or combine those tables.

It is also important to distinguish combining rows from merging cells. Excel’s Merge & Center command changes the layout of selected cells; it does not collect all their contents. In most cases, Excel keeps only the value in the upper-left cell and discards the other cell contents. Copy or combine the values before merging if you need to preserve them.

Combine matching rows with a formula

The opening formula is useful when a key, such as a customer ID, project name, or order number, repeats and you want the associated text gathered in one cell. With IDs in A2:A100, details in B2:B100, and the ID to summarize in E2, the formula filters the detail column to matching rows and then joins the results. If E2 contains 104 and the matching details are Notebook, Pen, and Folder, the result is Notebook, Pen, Folder.

FILTER returns only the values whose corresponding IDs meet the test, and TEXTJOIN places the chosen separator between those values. The second argument, TRUE, tells TEXTJOIN to ignore empty cells. The final empty string in FILTER’s arguments means it returns a blank result when it finds no match instead of displaying a no-results error.

To create a summary for several IDs, place each ID to summarize in column E and fill the formula down. For example, E2 can contain 104 and E3 can contain 208; the formula adjusts its reference to E3 when copied. If you have Microsoft 365 or Excel 2021 and want Excel to generate a list of distinct IDs, enter IDs in A2:A100 and use this formula in E2: =UNIQUE(A2:A100). Then place the joining formula beside the first ID and fill it down. UNIQUE creates the list of distinct values; it does not combine the details by itself.

For a list that is not grouped by a key, you can join a whole range directly. With the values to combine in B2:B20, use =TEXTJOIN(", ",TRUE,B2:B20). This returns one comma-separated string from the nonblank cells in that range. It does not remove duplicates, and it does not preserve the original rows as separate records; it simply presents their values together.

Choose a separator and handle duplicates

A comma and space is compact, but it is not always the easiest separator to read. You can substitute a semicolon, a slash, or another text separator in the TEXTJOIN formula. For a vertical-looking list inside one cell, use a line break. With IDs in A2:A100, details in B2:B100, and the target ID in E2, use =TEXTJOIN(CHAR(10),TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2,"")). Turn on Home > Wrap Text for the result cell so the lines display within the cell. The line breaks are part of the cell’s content, even if the row height needs adjusting to show them all.

Repeated detail values remain repeated by default. If ID 104 has “Pen” in two matching rows, the basic formula includes “Pen” twice. To keep each matching detail only once, use UNIQUE around the filtered results. With the same layout—IDs in A2:A100, details in B2:B100, and the target ID in E2—use =TEXTJOIN(", ",TRUE,UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=E2,""))). FILTER, UNIQUE, and dynamic-array behavior are available in Microsoft 365 and Excel 2021; the exact functions available depend on your Excel version.

Consider whether order matters before removing duplicates. UNIQUE retains the first occurrence order from the filtered data, which can be useful when the list represents the sequence in which events occurred. If order should be alphabetical instead, sort the source or use a suitable sorting step before joining. A combined cell is still text, so Excel will not treat the entries inside it as individually filterable records.

Use Power Query for repeatable grouping

Power Query is often a better choice when the source is large, the same cleanup must be repeated, or the result should be refreshed after new rows arrive. It can group records by a key and create one output row per key. Unlike a formula, a query produces a separate result table, so it is a good fit for a recurring data-preparation process.

  1. Make sure the source has clear column headers. Select a cell in the data and choose Insert > Table if it is not already an Excel table. A table can expand as rows are added.
  2. Load the table into Power Query using Data > From Table/Range. In the Power Query Editor, select the column that identifies each group.
  3. Choose Home > Group By. Select the key column as the grouping field, give the new column a name such as AllRows, and choose All Rows as the operation. This creates a nested table for each group.
  4. Choose Add Column > Custom Column. If the nested column is named AllRows and the text field is named Detail, enter Text.Combine([AllRows][Detail], ", ") as the custom-column expression. Adjust the names to match your query.
  5. Remove the AllRows column if you no longer need it, then choose Home > Close & Load to return the result to Excel.

The resulting table has one row for each key and a text field containing the joined details. If the source table changes, refresh the query using Data > Refresh All. Power Query is particularly helpful when the source data has to be cleaned or combined from multiple files before grouping. Its menus and available connectors differ between Windows, Mac, and Excel for the web, so check the options present in your version if a command is missing.

When Excel’s Consolidate command is the right tool

Excel also has a feature literally named Consolidate. On Windows, find it under Data > Data Tools > Consolidate. It combines values from multiple ranges into a summary range using a function such as Sum, Count, or Average. It can be useful when you have similar figures on several worksheets and want one numerical summary.

This command is not a general-purpose way to concatenate text from repeated rows into one cell. For example, if monthly sheets contain sales totals organized by matching row and column labels, Consolidate can summarize the values. If you want to combine “Notebook,” “Pen,” and “Folder” into one text cell, use TEXTJOIN with FILTER or a Power Query grouping step instead. Choosing the Consolidate dialog just because the task uses the word “consolidate” can produce the wrong result.

When you do use the command, select the destination cell, open Data > Data Tools > Consolidate, choose the summary function, and add the source ranges in the Reference box. If the ranges have labels, the options to use labels in the top row or left column can help match corresponding entries. Check the result against a few source values, especially if ranges differ in shape or contain labels inconsistently.

How to choose the best method

Use a formula when you want a live result in the worksheet and the data is already arranged in columns. Use Power Query when you need a repeatable transformation, need to group a large table, or want to load and clean source data as part of the process. Use Data > Consolidate when you need to summarize corresponding numeric ranges, not concatenate text. A pivot table is another option when the real goal is to summarize numbers by category, but it does not create a joined text list of each group’s entries.

  • One-off list from a range: TEXTJOIN can join the cells directly.
  • Matching rows gathered by an ID: FILTER and TEXTJOIN can return the matching text in one cell.
  • Repeated refreshes or multiple source tables: Group and transform the records in Power Query.
  • Totals across labeled numerical ranges: Use the Consolidate command or another numerical summary tool.
  • One row per group for analysis: Keep a structured summary table rather than putting all the detail into a single text cell if you will need to sort, filter, or calculate on individual items later.

Common problems and practical checks

If a formula returns a blank, verify that the key in E2 matches the source values. Extra spaces, inconsistent IDs stored as text versus numbers, and small spelling differences can prevent a match. Check that the ranges in the formula cover all the source rows and that the key and detail ranges are the same size. For data that grows regularly, convert the source range to an Excel table and consider using structured references so new rows are included more reliably.

If the formula displays #NAME?, your Excel version may not recognize FILTER or UNIQUE, or a function name may be misspelled. FILTER and UNIQUE require Microsoft 365 or Excel 2021. TEXTJOIN is available in more recent Excel releases, but older installations may not support it. Update Excel if your license allows it, use Power Query if available, or use a manual/helper-column process suited to your version. Do not assume that changing the formula’s spelling will make a missing function available.

Check for repeated values before using UNIQUE, and decide whether blanks should be ignored or represented. TEXTJOIN’s TRUE setting ignores empty cells, which is generally helpful for incomplete source data. Also watch for very long results: a single cell is not a substitute for a database-style list, and a large joined string can become difficult to review, edit, or use in later analysis. Keep the original rows in a separate sheet or table so the detail remains available.

Frequently Asked Questions

How do I combine duplicate rows in Excel into one row?
First decide what should happen to the other columns. For text entries that should appear together, group by the repeated key with FILTER and TEXTJOIN or Power Query. For quantities that should be totaled, use a summary method such as a pivot table or Data > Data Tools > Consolidate. There is no single command that safely handles every kind of duplicate row.

Can I combine several rows into one cell without a formula?
You can copy and paste values into one cell manually, but that is easy to get wrong and will not update when the source changes. For repeatable work, use a formula or Power Query. Merge & Center is not a substitute because it does not concatenate the contents of the selected cells.

How do I put each matching value on a new line in one cell?
Use TEXTJOIN with CHAR(10) as the separator and turn on Home > Wrap Text for the result cell. For matching rows, combine that separator with FILTER. If the cell still looks like one line, adjust the row height or use AutoFit Row Height.

How can I combine rows but remove repeated values?
In Microsoft 365 or Excel 2021, wrap the filtered values in UNIQUE before passing them to TEXTJOIN. This keeps one copy of each distinct value. Check that removing repeats makes sense for your data; repeated items can represent separate transactions that should not be discarded.

Does Excel’s Consolidate feature combine text?
It is designed to summarize data from ranges using functions such as Sum, Count, and Average, not to join matching text entries into one cell. For text concatenation by group, use TEXTJOIN with FILTER or create a grouped Power Query result.

Related

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.