How to Merge Two Cells in Excel: Merge vs CONCAT vs TEXTJOIN
How to merge two cells in Excel: Merge & Center, the & operator, CONCAT and TEXTJOIN. Keep values safe, fix sorting issues, and choose the right method.
How to merge two cells in Excel is really two questions with opposite answers. Merging layout (one wide header over columns) is a formatting move with real costs. Combining values ("Sana" + "Kapoor" → "Sana Kapoor") is a data move that Merge & Center actively destroys. Analysts who conflate them lose data on Monday and break sorting on Tuesday. This guide separates both paths, drawing on cell formatting and data cleaning foundations.
On the Data Analyst Roadmap, this is presentation-layer craft: the grid stays machine-readable while the human view stays elegant.
Merge & Center keeps only the upper-left value — every other cell's content is discarded
Excel warns before discarding, but muscle-memory OK-clicking deletes data silently. For values you need, combine with formulas first and merge (or better, Center Across Selection) only the presentation layer.
How to Merge Two Cells in Excel for Layout
Per Microsoft Support: merge: select the cells → Home → Merge & Center (dropdown offers Merge Across, Merge Cells, Unmerge Cells). Variants:
| Option | What it does | When to use |
|---|---|---|
| Merge & Center | Merges + centres content | Single title rows on presentation sheets |
| Merge Across | Merges each row separately in a multi-row selection | Repeated band headers |
| Merge Cells | Merges without centring | Preserving existing alignment |
| Unmerge Cells | Splits back (upper-left value only) | Recovering grid structure |
The costs, stated plainly: merged cells disable Sort for the affected range, confuse AutoFilter boundaries, break pivot table source ranges, and derail VLOOKUP/XLOOKUP fills that land inside merged areas. The professional alternative for headers is Center Across Selection: select the header span → Format Cells (Ctrl+1) → Alignment → Horizontal → Center Across Selection. Visually identical to a merge; structurally still separate cells — sorting, filtering, and formulas keep working.
Step-by-Step Example: Full Names Without Losing Data
First names in A, last names in B, 2,000 employee rows. Goal: full names in C, headers elegant, grid intact:
Step 1 — combine with the ampersand operator in C2:
=A2&" "&B2"Sana" & " " & "Kapoor" yields "Sana Kapoor". The & joins anything — text, numbers, dates (formatted as serials, so wrap dates in TEXT: =A2&" joined "&TEXT(B2, "mmm yyyy")).
Step 2 — scale with TEXTJOIN for multi-part joins. City, state, PIN across C:E:
=TEXTJOIN(", ", TRUE, C2:E2)Per Microsoft Support: TEXTJOIN, the second argument (TRUE) skips blanks — "Pune, Maharashtra" with no dangling comma when PIN is missing. CONCAT (docs) joins without delimiter control: =CONCAT(A2, " ", B2) equals the & version, useful inside larger expressions.
Step 3 — freeze the results. Combined columns depending on live source columns break when sources re-sort independently. Select C → Copy → Paste Special → Values. Now the full names are static text, immune to upstream edits — the standard hand-off state.
Step 4 — dress the header without merging. Select A1:C1 → Ctrl+1 → Center Across Selection for the "Employee Roster" title row. Screenshot-test it: looks merged, sorts fine, filters fine. Reserve real Merge & Center for the final printed cover sheet that no formula will ever touch.
How do you split combined values back apart?
The reverse trip — "Sana Kapoor" in one cell back to first/last — uses TEXTSPLIT in modern Excel:
=TEXTSPLIT(C2, " ")It spills the parts across columns, splitting on the space delimiter (commas for "Kapoor, Sana" after reordering). For legacy Excel, Text to Columns (Data tab → Delimited → Space) does the same destructively — back up first, since it overwrites rightward cells. And for fixed patterns (codes like "REG-2026-0412"), skip splitting entirely: =LEFT(C2, 3), =MID(C2, 5, 4), =RIGHT(C2, 4) extract by position faster and more transparently than any splitter. The standing rule: keep source columns intact and treat combined cells as derived views. Splitting is then a convenience, never a rescue operation — because the atomic parts always survive somewhere.
Common Mistakes and Fixes
Merge & Center on value cells: silent data deletion
Selecting A2:B2 ("Sana", "Kapoor") and merging keeps "Sana" and destroys "Kapoor" after one OK click. Recovery is retyping — there is no undo after save-and-close. Rule: Merge & Center is for empty layout cells and single-value titles only. Values combine via formulas, always.
The second classic is merged headers in a sortable table: one merged title row above the data disables Sort with an opaque error, and analysts rebuild the table before discovering the cause. Diagnose fast — select all, Unmerge, retry Sort. Prevent structurally: titles live above the table (row 1, table from row 3) or use Center Across Selection, never merged cells inside the data rectangle.
Unmerge recovery returns one value — plan for it
Unmerging restores the grid but only the upper-left cell holds content; the rest return blank. If a handover sheet arrives merged, unmerge first, then fill the blanks (Go To Special → Blanks → =↑ → Ctrl+Enter) per the cleaning playbook before any analysis trusts those columns.
How to Merge Two Cells in Excel vs Alternatives
| Feature / Criteria |
|---|
The decision tree: combining values → formulas (&, TEXTJOIN); centring a header → Center Across Selection; and Merge & Center only where no machine will ever read the sheet again. When a colleague's merged sheet lands on your desk, the companion unhide/audit routine plus Unmerge restores a workable grid.
When to Merge (and Combine) in Analyst Work
Name and key construction. Full names, composite keys (region & "-" & year), display addresses — TEXTJOIN pipelines turn fragmented source columns into human-ready fields. Paste-as-values before hand-off so downstream consumers inherit text, not dependencies.
Board-ready headers. Center Across Selection delivers the merged look across section titles while the grid underneath stays sortable for last-minute data fixes — the 9am board pack with an 8:55am correction stops being a crisis.
Print covers and section dividers. The one legitimate Merge & Center territory: title bands on sheets whose only consumer is eyes and printers. Keep them on dedicated presentation sheets, fed by formulas from the unmerged analysis grid — layout and logic in separate places, each optimised for its reader.
Related Excel Tutorials
- Excel Formulas: The Complete Guide
- XLOOKUP Formula in Excel
- SUM Formula in Excel
- Excel Pivot Tables Guide
- Explore All Guides in the Excel Tutorials Hub
Master Excel for Data Analysis
Learn Excel formulas, pivot tables, and dashboards with free, project-based courses.
Start Free Excel CourseFrequently Asked Questions
How do I merge two cells in Excel?
Select both cells, then Home > Merge & Center. Only the upper-left value survives — the other is discarded. For combining text values without loss, use =A2&" "&B2 or =TEXTJOIN(" ", TRUE, A2:B2) instead.
How do I merge two cells in Excel without losing data?
Do not use Merge & Center — it keeps one value. Combine with =A2&" "&B2 (ampersand operator), =CONCAT(A2, " ", B2), or =TEXTJOIN(" ", TRUE, A2:B2), which joins all values while skipping blanks, then Copy > Paste Values.
Why should analysts avoid merging cells?
Merged cells break sorting, filtering, pivot tables, and lookups that expect a rectangular grid — one merged header can disable sort for a whole table. Merge only presentation sheets; keep analysis grids unmerged and use Center Across Selection instead.
What is Center Across Selection in Excel?
A formatting option (Format Cells > Alignment > Horizontal > Center Across Selection) that visually centers text across columns without merging them. The grid stays intact for sorting and formulas while looking identical to a merge.
How do I unmerge cells in Excel?
Select the merged area, then Home > Merge & Center again (toggle) or Merge & Center dropdown > Unmerge Cells. Only the original upper-left value returns — content lost at merge time is not recoverable, so combine-with-formula first for data you need.
Written by
Founder at Topfolio with 6+ years in data & analytics across JPMC, Ultrahuman, and high-growth startups. Sat on hiring panels, reviewed 500+ resumes, and writes practical SQL & data guides.
Related Articles
How to Compare Two Columns in Excel: 5 Methods (with Formulas)
How to compare two columns in Excel: equality checks, IF flags, COUNTIF matching, conditional formatting highlights, and VLOOKUP reconciliation steps.
How to Delete Blank Rows in Excel: 4 Safe Methods (with Checks)
How to delete blank rows in Excel safely: Go To Special Blanks, AutoFilter blanks, helper COUNTBLANK flags, and backup checks every analyst must run first.
Data Cleaning in Excel: How to Remove Blank Rows, Spaces & Duplicates
Master data cleaning in Excel: learn how to remove blank rows in excel, strip stray spaces with TRIM, fix casing, and delete duplicates safely.