How to Merge Rows in Excel and Keep All of Your Data

How-to basics · Updated November 20, 2026

To merge duplicate records without losing values, keep the original table intact, create a list of unique keys, and use formulas to join the matching values into a new summary table. Excel’s Merge Cells command is different: it keeps only the top-left cell’s value and discards the others. The steps below show a safe way to consolidate rows, plus what to do if you meant merging cells visually.

What You’ll Need

  • Excel for Microsoft 365 on Windows. The formula method uses UNIQUE and FILTER, which are available in Microsoft 365 and Excel 2021 or later.
  • A table with a column that identifies which records belong together, such as an order number, customer ID, or product code.
  • A backup copy of your workbook. Keep the source rows until you have checked the consolidated results.

The example assumes columns A through D contain Order ID, Customer, Item, and Note, with headings in row 1 and records in rows 2 through 100. Replace those names and ranges with the ones in your workbook. The example joins values from every matching record, so repeated values are retained too.

Step-by-Step

  1. Decide what “merge rows” means for your data. If you want one record per repeated ID, use the consolidation method below. If you only want a larger-looking cell across a selected range, that is a formatting change, not a data merge. Excel’s Merge Cells command does not combine the contents of multiple cells. Decide which column or combination of columns defines a duplicate group before editing anything. For example, two rows with the same order number may belong together, while rows with different order numbers should usually remain separate.
  2. Save a working copy and inspect the source table. Use File > Save As to create a copy, or duplicate the worksheet by right-clicking its sheet tab and choosing Move or Copy, then selecting Create a copy. Check that every record has an ID in column A and that the headings clearly identify each data column. Look for extra spaces or inconsistent IDs, such as 501 stored as a number in one row and as text in another. Those inconsistencies can stop records from grouping as expected. Do not delete or rearrange the original rows yet.
  3. Sort and review records if it will help you understand the groups. Sorting is optional, but it makes repeated IDs easier to spot. Select a cell in the table and choose Data > Sort. Sort by Order ID, then add a second level such as Item if useful. If Excel asks whether to expand the selection, choose Expand the selection so each row stays together. Sorting does not combine rows; it only helps you inspect them. Make sure the whole table is included, not just the ID column.
  4. Set up a separate output area. Create a new worksheet or use empty columns well away from the source table. Add headings such as Order ID, Customer, Items, and Notes. Keeping the result separate protects the original and makes it easy to compare. Do not place the output formula inside the source range: spilled results can be blocked by existing data, and formulas that refer to the source may become confusing if the same table is overwritten.
  5. Create one row for each distinct ID. In this example, the source IDs are in A2:A100, and the output ID heading is in E1. With IDs in A2:A100, enter this formula in E2 to list each nonblank ID once: =UNIQUE(FILTER(A2:A100,A2:A100<>"")). Press Enter. Excel spills the distinct IDs down from E2. If your table has more than 100 records, change the range in the formula to include them. The formula ignores blank IDs, so records without an ID will not appear in this output and should be reviewed separately.
  6. Join the customer values for each ID. With IDs in A2:A100, customer names in B2:B100, and the first output ID in E2, enter this formula in F2: =TEXTJOIN("; ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2,"")). It returns all customer entries associated with the ID in E2, separated by a semicolon. In the formula, TRUE tells TEXTJOIN to ignore blank cells; the final empty text argument makes FILTER return an empty result rather than an error if there is no match. This example keeps repeated names rather than silently removing them.
  7. Repeat the join for every other field you need to retain. With item names in C2:C100, IDs in A2:A100, and the output ID in E2, enter this formula in G2: =TEXTJOIN("; ",TRUE,FILTER($C$2:$C$100,$A$2:$A$100=E2,"")). With notes in D2:D100 and the same IDs and output key, enter this formula in H2: =TEXTJOIN("; ",TRUE,FILTER($D$2:$D$100,$A$2:$A$100=E2,"")). These formulas preserve the matching items and notes as text in separate output columns. If you have more source columns, make a corresponding output column and use the same pattern with that column’s range.
  8. Fill the join formulas down beside the distinct IDs. Select F2:H2 and drag the fill handle down to the last ID listed in column E, or copy the formulas and paste them into the corresponding rows. The reference E2 will adjust to E3, E4, and so on, while the dollar signs keep the source ranges fixed. Confirm that the last output row matches the last spilled ID. If the ID list changes later, copy the formulas down again if new rows have been added beyond the existing formula area.
  9. Check that the summary preserves the information you need. Pick several IDs with more than one source row and compare their output against the original. Confirm that every expected item and note appears, and that delimiters make separate values readable. If the order of values matters, sort the source table before creating the summary; FILTER returns matches in source order. For a small table, you can also count the matching source rows for a specific ID by filtering the original table and comparing the number of records with the number of entries shown.
  10. Freeze the result if you want a standalone table. Once the summary is verified, select the output cells, copy them, and use Home > Paste > Paste Values. This replaces the formulas with their current results. Keep the untouched source worksheet as your audit copy. If you want the summary to update when the source changes, leave the formulas in place instead. Do not remove the original data until you are satisfied that the output is complete and useful.

For example, if order 501 appears on two source rows, one with Notebook and Rush and the other with Pen and Gift wrap, the consolidated row for 501 can show Notebook; Pen in Items and Rush; Gift wrap in Notes. The information is kept, but joined into text rather than stored as two separate item records. That distinction matters: the summary is convenient for review, but it is not equivalent to a normalized transaction table for later calculations.

If you meant joining cell contents side by side rather than consolidating repeated records, you can use the same TEXTJOIN idea on the cells you want to combine. Once the combined text is safely stored in a separate cell, you may format or reposition cells as needed. Avoid using Merge Cells as the method for collecting values.

Common Mistakes

  • Using Merge & Center to combine data. When a selected range contains multiple values, Excel retains only the value in the upper-left cell and warns that other values will be discarded. Cancel that warning if you need the other contents. Merging is for appearance, not consolidation.
  • Choosing the wrong grouping key. Grouping only by a customer name can combine separate orders from the same person. Use a unique ID or a deliberate combination of fields that actually defines one group. If no single column identifies a group, add a helper key or choose a different consolidation approach.
  • Leaving some source rows outside the formula range. If the formulas use rows 2 through 100 but the table extends to row 125, those later records will be omitted. Extend every range consistently. Consider converting the source range to an Excel table with Ctrl+T before you build a larger workflow; structured references can make expanding data easier to manage.
  • Assuming joined text stays as separate records. A result such as Notebook; Pen is one text value, not two item rows. It may be unsuitable for inventory totals, pivot tables, or calculations that expect one item per row. Preserve the original transactional table for those tasks.
  • Deleting duplicates before checking the output. Remove Duplicates removes entire repeated records based on the columns you select; it does not gather their different values into one row. It can discard useful information if applied to the wrong columns.

Tips

  • Use a delimiter that is unlikely to appear in your data, such as a semicolon or a line break. A semicolon is easy to read and can be split later with Data > Text to Columns, though splitting may create more columns than expected when values themselves contain semicolons.
  • Keep the key in the first output column and give each joined field a clear heading. Someone reviewing the workbook should be able to tell which values were grouped and what the separators mean.
  • If the source contains dates or numbers that need exact formatting, check their display in the joined text. Joining values can turn them into text and may not preserve the original date or number format. Keep a separate typed column or format the value deliberately before joining.
  • If you need to retain the relationship between values from the same source row, join a row summary rather than joining each field independently. For instance, combine the item and its note together in each source record, then join those summaries by ID. Otherwise, separate joined columns can make it unclear which note belongs to which item.
  • For an ongoing process or a large dataset, consider Power Query via Data > From Table/Range. Power Query can group records and offers more repeatable transformations, but decide how each column should be aggregated before loading the result. A plain Group By operation does not automatically preserve every detail in a reader-friendly format.

Troubleshooting

The formula shows #NAME? Check that the formula is entered with the function names shown and that your Excel version supports UNIQUE and FILTER. Those functions require Microsoft 365 or Excel 2021 and later. In an older edition, use Power Query or a carefully designed helper-column workflow instead of expecting the dynamic-array formula to work.

The formula shows #SPILL!. The distinct-ID formula needs empty cells below E2 to display its results. Clear anything in the spill area, including cells that look blank but contain spaces, or move the output formula to an empty area. Merged cells in the spill range can also prevent the result from expanding.

An ID is missing or appears as two groups. Check that the ID is not blank and that repeated IDs use the same underlying value. Leading or trailing spaces, different number-versus-text formats, and small spelling differences can create unexpected groups. Clean or standardize the source values in a copy, then recalculate the output.

Some values appear missing from a joined cell. Confirm that the source range covers all rows and that the output formula refers to the correct source column. Check for blanks, filters that hide rows, and text that may be hard to distinguish because the chosen separator also occurs in the data. TEXTJOIN can also be limited by Excel’s maximum cell text length, so an unusually large group may not fit in one cell. For large groups, keep the detail in separate rows or use a data model rather than forcing everything into one cell.

Excel warns that merging cells keeps only the upper-left value. Choose Cancel if you need all values. Use the formula consolidation method to combine contents first, then apply visual formatting only after confirming that the data is present. If you already accepted the merge, use Ctrl+Z immediately if possible; otherwise restore the saved copy or recover the discarded values from the source.

The result does not update as expected. Confirm that calculation is enabled under Formulas > Calculation Options > Automatic. Also check whether you pasted values over formulas, whether new source rows fall outside the ranges, and whether the output formulas were filled down for newly added IDs.

Frequently Asked Questions

Can I merge rows in Excel without losing data?
Yes, if you mean consolidating records: use formulas or a data transformation to combine the values, and keep the source table. The Merge Cells command itself does not preserve every selected cell’s value.

Does Merge & Center combine the text in selected cells?
No. It keeps the upper-left cell’s contents and discards other values in the selected range. Cancel Excel’s warning if those values matter.

Can I merge rows based on a matching name or ID?
Yes. Create one output entry per key, then join values from each source column where the key matches. Choose a key that correctly defines which records belong together.

Can I do this in Excel for the web or on a Mac?
The formula approach works in Microsoft 365 when UNIQUE, FILTER, and TEXTJOIN are available. Excel for the web uses the same formula syntax. On Mac, menu and ribbon layout can differ slightly, but the formulas work the same way in supported versions. Keyboard shortcut equivalents also differ: on Mac, use Command+C and Command+V to copy and paste rather than Ctrl+C and Ctrl+V.

Can I undo a merge after saving and closing?
Ctrl+Z may undo a recent merge while the workbook remains open, but it is not a dependable recovery method after closing. Restore a backup or retrieve the original values from an unchanged source sheet whenever possible.

Should I remove repeated values from the joined result?
Only if you have confirmed that repeats are redundant. The formulas shown deliberately keep every matching value. Removing repeats can make a summary shorter, but it can also hide a real repeated event or entry.

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.