Excel Consolidate Cells: Merge or Combine Data?

How-to basics · Updated November 19, 2026

In Excel, “consolidate cells” can mean merging cells into one larger cell, joining the contents of several cells, or summarizing matching ranges with the Consolidate tool. To merge a selection, use Home > Merge & Center; to preserve and combine its text, use a formula such as =TEXTJOIN(" ",TRUE,A2:C2); to summarize separate ranges, use Data > Consolidate.

What “consolidate cells” means in Excel

Excel does not have one command called “Consolidate Cells” that does all three jobs. The word is often used loosely, so first decide what result you want. Merging changes the shape of the worksheet by making a selected area act like one cell. Combining contents creates a value containing information from multiple cells. The Consolidate command, by contrast, calculates a summary from several ranges, such as a total across monthly worksheets.

These operations are not interchangeable. A merged cell does not add or preserve all the values in its original cells. Joining text does not create a summary calculation. Consolidating ranges does not make them into one larger cell. Choosing the right operation matters especially when the worksheet will later be sorted, filtered, copied, or used in formulas.

Merge cells with the ribbon

In Excel for Microsoft 365 on Windows, select the adjacent cells you want to combine, then choose Home > Merge & Center. The button’s drop-down offers related choices, including Merge Cells, Merge Across, and Unmerge Cells. Merge & Center merges the selection and centers the remaining entry; Merge Cells combines the selected area without centering it. Merge Across merges cells separately within each selected row rather than making the entire selection one cell.

  1. Select the cells that form one rectangular range, such as A1:C1.
  2. On the Home tab, open the drop-down beside Merge & Center.
  3. Choose the merge option that matches the layout you need.
  4. If Excel warns that merging keeps only the upper-left value, check the selection before confirming.

The key limitation is easy to miss: when multiple selected cells contain data, Excel keeps the value in the upper-left cell and discards the other cell contents from the merged result. For example, if A1 contains “Region,” B1 contains “Sales,” and C1 contains “2026,” merging A1:C1 does not create “Region Sales 2026.” Only the upper-left value remains. Copy or combine the contents elsewhere before merging if you need to retain them.

On Excel for Mac, the merge controls are also on the Home tab, though the ribbon layout can differ slightly by release and window size. Excel for the web may display a more compact ribbon; look for the merge option on Home. The underlying data-loss behavior is the same: merging is a layout operation, not a way to concatenate values.

Combine cell contents without merging

If your goal is to put words, codes, or numbers from multiple cells into one result, use a formula instead. A formula leaves the source cells separate, which is safer for sorting and filtering, and the result updates when its source values change. You can put the formula in an empty cell, then copy it down for other records.

With the words North in A2, a blank in B2, and Store in C2, enter this formula in D2:

=TEXTJOIN(" ",TRUE,A2:C2)

The result is “North Store.” The space inside the first pair of quotation marks is the delimiter placed between nonempty entries. TRUE tells TEXTJOIN to ignore empty cells, so the blank B2 does not produce a doubled space. TEXTJOIN is available in Microsoft 365 and newer supported Excel releases, including Excel 2019 and later.

For a simple join that does not need to skip blanks or insert a delimiter, CONCAT is another option. With a first name in A2 and a last name in B2, this formula in C2 joins their contents directly:

=CONCAT(A2,B2)

If A2 is “Mira” and B2 is “Chen,” the result is “MiraChen.” To include a space, add it as a quoted text argument: =CONCAT(A2," ",B2), which returns “Mira Chen.” The older ampersand operator also joins text, for example =A2&" "&B2. These formulas create a calculated result; they do not combine the original cells physically.

When you need a fixed value rather than a live formula, copy the formula results and use Paste Special > Values. Keep the original columns until you have checked the output. If a source cell contains a date or a number with a special display format, a text-joining formula may show its underlying numeric value rather than the formatting you see on screen. You may need to convert or format that value explicitly before combining it.

Use the Consolidate command to summarize ranges

Excel’s Data > Consolidate command is designed to combine information from multiple ranges into a summary. For example, suppose three worksheets contain monthly sales tables with the same product names and sales figures. Consolidate can add the figures together by matching row labels, rather than merely putting text from cells side by side. It is a calculation and summary feature, not a cell-merging feature.

  1. Choose an empty destination area for the summary, or select its upper-left cell.
  2. Go to Data > Consolidate.
  3. Choose a function, such as Sum, in the Function box.
  4. Select a source range and choose Add. Repeat for each range to include.
  5. If the ranges have labels, select Top row and/or Left column to match entries by those labels.
  6. Select Create links to source data if you want the summary linked to its source cells, then choose OK.

Without label matching, Excel combines entries by position: the first cell in one source corresponds to the first cell in another. That can produce a misleading result if one table has a different row order. With Top row or Left column selected, matching labels help Excel align corresponding categories. Check that spelling and label structure are consistent; “East” and “East Region,” for example, are not the same label.

The function you choose determines what the summary reports. Sum adds values, Average calculates an average, and Count counts entries. The tool is useful for straightforward summaries, but it is not a substitute for cleaning inconsistent source tables or designing a refreshable data model. If you select Create links to source data, Excel builds a linked summary and may display outline controls; without it, the summary is a result rather than a set of links that updates from every source edit.

Choose the right method for the job

  • Use Merge & Center for a heading that spans columns and has only one value to retain. Avoid merged cells in the middle of a data table.
  • Use TEXTJOIN or CONCAT when several text values should appear together in one cell, such as a full name or a combined address. Keep the original fields if you may need to sort or analyze them separately.
  • Use Data > Consolidate when you want to calculate a summary from multiple ranges, such as totals by category across worksheets.
  • Use Power Query when you need to combine tables in a repeatable data-preparation process, especially when the source data will be updated or expanded.

A practical example makes the distinction clear. To show a report title centered above columns A through D, merge a row of otherwise empty heading cells or use Center Across Selection. To create a full-name field from first and last names, join the values with a formula. To total each product’s sales from several monthly sheets, consolidate the ranges or use a suitable data-combination workflow. One task may involve the same worksheet, but it calls for a different operation.

Why merged cells can cause problems

Merged cells can make a report look tidy, but they can complicate ordinary spreadsheet work. Sorting a range that includes merged cells of different sizes may be blocked or behave unexpectedly. Filtering, selecting ranges, filling formulas, and copying data can also be less straightforward because the merged area does not have the same rectangular structure as an ordinary table. For these reasons, avoid merging cells inside a dataset that people need to analyze.

For a centered heading without a merged cell, select the cells and open the Format Cells dialog from Home > Alignment dialog launcher. In the Horizontal alignment options, choose Center Across Selection. This centers the text visually across the selected cells while leaving the cells separate. It is often a better choice for worksheet titles above tables, though it does not work like a merged cell in every respect.

If a merge is already in place, choose Home > Merge & Center > Unmerge Cells. Unmerging restores the individual cells, but it does not bring back values that were discarded when they were first merged. Check version history, a backup, or the original source if those values are needed. Excel’s Undo may help immediately after a merge, but it is not a reliable recovery method once later edits have been made or the workbook has been closed.

Alternatives for combining whole tables

If “consolidate” means bringing several tables together rather than joining a few cell values, consider how the source data is organized. Power Query can append tables by stacking rows with similar columns, or merge queries by matching a key such as a product ID. In Excel for Microsoft 365 on Windows, you can load a table into Power Query from Data > Get Data or Data > From Table/Range, then use the Append Queries or Merge Queries commands in the Power Query Editor. The exact available commands can depend on how the query was opened.

Appending is appropriate when each source contains the same kinds of columns and you want one longer list—for example, transactions from January, February, and March. Merging is appropriate when one table has details that should be matched to another table by a shared field, such as adding product descriptions to sales records using a product code. Neither operation is the same as merging worksheet cells. If the job is a one-time total of similarly laid-out ranges, Data > Consolidate may be simpler; if it needs to be repeated as new data arrives, Power Query is often more suitable.

Frequently Asked Questions

Does merging cells combine their text?
No. Excel retains the upper-left cell’s value and removes the other selected values from the merged result. Join or copy the contents first if you need to preserve them.

How do I combine cells and keep a space between words?
With the source text in A2 and B2, enter =CONCAT(A2," ",B2) in another cell. For a range that may contain blanks, TEXTJOIN can insert a delimiter and ignore empty cells.

Can I undo a merge without losing the original values?
Unmerge Cells restores the separate cell layout, but it cannot restore values discarded by the original merge. Undo may work immediately, while a previous saved version or backup may be needed later.

What is the difference between Merge & Center and Consolidate?
Merge & Center changes the layout of selected cells and centers the retained value. Data > Consolidate calculates a summary from multiple ranges using a function such as Sum or Average.

Why can’t I sort a range with merged cells?
Merged areas can prevent a range from having the consistent row-and-column structure sorting expects. Unmerge the cells or use Center Across Selection for headings, then sort the unmerged data range.

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.