Merge & Center (Home tab, Alignment group) combines the selected cells into one cell and centers the content, keeping only the upper-left value and deleting the rest. If the range will ever be sorted, filtered or pivoted, use Center Across Selection instead, which looks the same but merges nothing.
The truth is messier. Use merge and center in excel on data you'll later sort, filter, or feed into a pivot table, and you'll spend an afternoon hunting down errors you didn't know you created. The button looks innocent. The damage isn't.
Here's what nobody tells you. Excel keeps the value of only the top-left cell when you merge a range. Every other cell in that selection? Wiped. And once merged, your data behaves differently than the rest of your worksheet — sorts misalign, formulas break, filters refuse to play along.
That said, merging isn't evil. For a report title spanning columns B through G, or a header cell that needs visual weight, it's perfect. The problem is people use it on rows of actual data, then wonder why VLOOKUP returns #N/A across the board.
This guide walks you through every merge option Excel offers — including the one you should use instead, which doesn't actually merge anything. We'll cover keyboard shortcuts, the four merge modes hidden behind the dropdown arrow, what breaks when you merge (pivot tables, sorting, copy-paste), how to unmerge imported data and fill the resulting blanks in seconds, and the VBA + Power Query alternatives for people working at scale.
Open Excel. Look at the Home tab on the ribbon. You'll see the Alignment group — that's where Merge & Center sits, usually between the indent buttons and the text wrapping controls.
The button is a small icon: two squares fusing into one, with a tiny letter centered inside. Click it and Excel performs three actions at once. It combines the selected cells into a single cell. It centers the text horizontally. And it discards the contents of every cell except the top-left one.
That last part trips up almost everyone. If cell A1 contains "Q1 Sales" and B1 contains "Q2 Sales," merging the two gives you a single merged cell containing only "Q1 Sales." The Q2 data — gone. Excel does flash a warning dialog before it happens, but the dialog appears so often that most people click through it without reading.
Notice the small dropdown arrow next to the button. That arrow is the entire point of this guide. Click it instead of the button itself, and you'll see four options that behave very differently.
When you merge a range, Excel keeps only the value in the top-left cell. Everything else is discarded. Excel shows a warning dialog when more than one selected cell holds data, but it is easy to click through without reading. Copy any data you need from the other cells before merging, because Microsoft's documentation states the contents of the other merged cells are deleted.
Most people think Merge & Center is the only choice. It isn't.
The default behavior. Combines all selected cells into one, centers the content horizontally, keeps only the top-left value. Use this for report titles, dashboard headers, and any single-row banner that spans multiple columns. Avoid it for anything you'll later sort or filter.
This one's underused. Merge Across merges cells within each selected row separately. So if you select A1:D3, you get three merged rows (A1:D1, A2:D2, A3:D3) — not one giant merged block. Useful when you have multiple section dividers stacked vertically and need each one to span the same columns. Saves clicking three separate times.
Same as Merge & Center, minus the centering. The text stays left-aligned (or wherever it was). Helpful when you need the merge but the centering would look wrong — long-form notes that read better left-aligned, for instance.
Reverses any merge. Select the merged cell, click Unmerge Cells, and the original cell boundaries return. The value stays in the top-left position; the rest of the formerly-hidden cells come back as blanks. We'll cover what to do with those blanks in a minute — that's where the real work begins for anyone cleaning up imported data.
Default behavior. Combines the entire selection into one cell, centers content horizontally, discards every value except the top-left.
Use for: report titles, dashboard headers, single-row banners spanning columns.
Avoid for: any column or row of data you'll later sort, filter, pivot, or reference in a formula.
Per-row merging. Merges cells within each selected row independently. Selecting A1:D3 produces three merged rows, not one giant block.
Use for: stacked section dividers, multi-row banners where each row spans the same columns.
Saves manual clicks compared to merging each row separately.
Merge without centering. Same as Merge & Center but preserves the original text alignment (usually left for text, right for numbers).
Use for: long-form note cells, sidebar text, anywhere centering would look awkward.
Reverses a merge. Restores original cell boundaries. The value stays in the top-left position; the rest come back as blank cells.
Use this when cleaning imported data — then combine with Go To Special and the Ctrl+Enter fill trick to populate the blanks.
The four layout options behave very differently once the sheet is sorted, filtered or used as pivot source data. This table compares what each one does to the cells and the data.
| Option | Where to find it | What it does | Data kept | Effect on sorting and filtering |
|---|---|---|---|---|
| Merge & Center | Home > Merge & Center (Alt+H, M, C on Windows) | Joins the selection into one cell and centers the content | Upper-left value only; other values deleted | Can block sorting; avoid in data ranges |
| Merge Across | Merge & Center drop-down (Alt+H, M, A) | Merges each selected row separately, without centering | Upper-left value of each row only | Can block sorting; avoid in data ranges |
| Merge Cells | Merge & Center drop-down (Alt+H, M, M) | Joins the selection into one cell, keeps existing alignment | Upper-left value only | Can block sorting; avoid in data ranges |
| Unmerge Cells | Merge & Center drop-down (Alt+H, M, U) | Splits a merged cell back into separate cells | Value moves to the left cell; others blank | Restores normal sorting after you fill blanks |
| Center Across Selection | Ctrl+1 > Alignment > Horizontal | Centers text across the selected cells without merging them | All cells keep their own values (text sits in the left cell) | No merged cells, so sorting and filtering are unaffected |
Source: Microsoft Support, "Merge and unmerge cells" (support.microsoft.com), which states that only the upper-left cell's contents survive a merge.
Forget reaching for the mouse. Excel exposes every merge option through the ribbon accelerator: Alt + H + M, followed by a single letter.
The Alt key activates ribbon shortcuts. H opens the Home tab. M opens the Merge dropdown. Then the final letter picks the option. Press the keys one at a time, not held down, and confirm the sequence on your own ribbon KeyTips because they can differ between Excel versions.
These Alt-key sequences are Windows ribbon access keys (press Alt to show the KeyTips on the ribbon). Microsoft documents access keys for Windows only; its Excel for Mac shortcut list is built on Cmd-key combinations, and no Alt+H+M+C equivalent is documented for Mac Excel. On a Mac, use Home > Merge & Center with the mouse, or press Cmd+1 to open Format Cells for Center Across Selection.
If you find yourself merging the same range often, record a quick macro — covered in the VBA in Excel guide. Three lines of code, mapped to Ctrl+Shift+M, beats clicking the ribbon every time.
Merge & Center — combines selection into one centered cell.
Merge Across — merges each row independently.
Merge Cells — combine without centering.
Unmerge Cells — restore original cell boundaries.
Merged cells break four things that Excel users rely on constantly. Knowing which features break — and how — saves hours of debugging later.
Select a column that contains merged cells. Try to sort. Excel typically refuses to sort a range that mixes merged cells of different sizes, and a partly merged range can leave rows orphaned from their original headers. The fix is always the same: unmerge first, then sort.
Pivot tables work best on clean tabular source data: one value per cell, a single header row and no merged cells. Merged cells leave blank cells in all but the upper-left position, so the pivot can show a (blank) group or fail to build as expected. For a deep dive on cleaning data for pivots, see pivot tables in Excel.
Try copying a merged 2×1 cell into an unmerged single cell, and Excel either rejects the paste or merges the destination cell silently. Copying between merged ranges of different sizes is even worse — you'll get a popup demanding identically sized ranges. The shortcut to delete row in Excel becomes complicated when merged cells span row boundaries (see keyboard shortcut to delete row in Excel for clean row deletion tactics).
This is the silent killer. A formula like =A2 referencing a merged cell that spans A2:A4 returns the value as expected. But =A3 or =A4 returns blank, because those cells technically contain nothing. Now imagine a VLOOKUP scanning down a column where every fourth row is merged. It'll find some matches, miss others, and you won't know why your totals look wrong.
Almost every situation where you'd reach for Merge & Center, there's a better option. It's called Center Across Selection, and it's buried in the Format Cells dialog. Once you find it, you'll wonder why Microsoft hides it.
Here's how to use it. Select the range you want to "merge" visually. Press Ctrl+1 to open Format Cells. Click the Alignment tab. Open the Horizontal dropdown. Choose Center Across Selection. Click OK.
The result looks identical to Merge & Center — text appears centered across the selected columns. But under the hood, nothing is merged. Each cell retains its individual identity. Sorts work. Filters work. Pivot tables read the data cleanly. Formulas referencing the underlying cells return the right values.
The only "catch" is that the text must live in the leftmost cell of the selection. If you put text in the middle of the range, Center Across Selection still aligns it as if it were in the left cell.
For 90% of header-row use cases — dashboard titles, report banners, section dividers — Center Across Selection is the right tool. Use Merge & Center only when you genuinely need a single cell (for example, when applying a borders-only frame to a quadrant of a layout).
You inherit a spreadsheet. Someone exported it from a legacy system, or downloaded it from a vendor portal. The header rows are merged. The category columns are merged. Half the data has gaps because the original author merged "Region" cells across three rows, expecting it to mean "this region applies to all three."
You can't pivot. You can't filter. You can't analyze. Time to unmerge — and fill the blanks correctly.
Select all (Ctrl+A). Click Merge & Center once to unmerge any active merges in the selection. The merged ranges revert to individual cells, with the value stuck in the top-left of each former merge zone. The rest are blank.
Select the column with the now-broken data. Press F5 (or Ctrl+G) to open Go To. Click Special. Choose Blanks. Click OK. Excel selects every blank cell in the column.
With the blanks still selected, type =, press the Up arrow once, then hit Ctrl+Enter. Ctrl+Enter fills the formula into every selected cell, each referencing the cell directly above it. The blanks now display the same value as the row above them.
Select the column again. Copy (Ctrl+C). Paste Special as Values (Ctrl+Alt+V, then V, then Enter). The formulas convert to static text. Now you can sort, filter, and pivot freely.
This four-step process takes about 15 seconds once you've done it twice. It's the single highest-ROI Excel skill for anyone working with imported data.
Use Ctrl+A or click the column header above the merged data.
All merges collapse. Values land in the top-left; the rest become blanks.
Press F5 (or Ctrl+G), click Special, choose Blanks, click OK. Excel selects every blank cell in the range.
With blanks selected, type = then press the Up arrow once. The formula references the cell above.
Hitting Ctrl+Enter (not just Enter) writes the formula into every selected blank simultaneously.
Copy the column (Ctrl+C), then Paste Special as Values (Ctrl+Alt+V then V then Enter). Formulas become text.
The data is now clean tabular content. Pivot tables, AutoFilter, and formula references work normally.
If you import the same merged-cell spreadsheet weekly, Power Query removes the manual labor. Load the file via Data → Get Data → From File. In the Power Query Editor, select the column with merged-cell artifacts. Right-click → Fill → Down. Power Query propagates the top value into every blank cell beneath it. Save the query. Next week's file refreshes automatically with the fill-down baked in.
Power Query never reads merged cells the way Excel does — it sees the raw underlying values. Merged headers become single-row headers with the rest of the row blank, and Fill Down handles the rest. For a deeper look at Power Query, see Excel Power Query.
For automation, VBA exposes two methods on the Range object:
Range("A1:D1").Merge
Range("A1:D1").UnMerge
Range("A1:D1").Merge Across:=TrueThe third line — Merge Across:=True — produces the same result as Merge Across in the dropdown. Useful inside macros that build report templates programmatically. The full VBA reference lives in our Excel VBA practice test PDF guide.
One trap to know: Range.Merge raises a runtime warning dialog if the selection contains data in cells other than the top-left. To suppress it inside a macro, set Application.DisplayAlerts = False before the merge, then restore it afterward.
Merged cells hold their value only in the upper-left cell, so any rule or formula that reads the other cells in the merged area sees blanks. Test conditional formatting on a small sample before relying on it across merged ranges, and avoid merging where you need reliable banding or data bars.
Convert a range to an Excel Table (Ctrl+T), and Merge & Center is disabled for cells inside the table, which Microsoft notes in its merge instructions. Tables expect uniform rows, so unmerge the range before converting it. If you must preserve a visual merged header above a Table, place the merged cell outside the Table range.
The web version of Excel (Excel Online, part of Microsoft 365) has a Merge & Center command on the Home tab, and Microsoft's merge guide covers merging and unmerging in the web app. Keyboard sequences and some options can differ from the desktop client, so check the ribbon menu in your own version.
Copy a merged 3×1 cell. Try to paste it into a single unmerged cell. Excel offers two outcomes: it merges the destination to match the source size, or it refuses and shows a dialog. Try to paste a merged 3×1 into a 2×1 selection, and Excel rejects it outright. The workaround: unmerge the source first, copy the unmerged values, paste them into the destination, then re-merge if needed. Tedious — and an argument for using Center Across Selection from the start.
Before you click Merge & Center, ask one question. Will this data ever be sorted, filtered, copied, pivoted, or referenced by a formula?
If yes — even probably yes — don't merge. Use Center Across Selection, freeze a header row, or restructure the layout so visual hierarchy comes from font weight and color rather than cell geometry.
Merging is for static presentation only. Report covers. Dashboard titles. Printed handouts that nobody will analyze. For everything else, the merge button is a trap dressed up as a convenience.
Master the unmerge-and-fill workflow (Go To Special → Blanks, then = then Up arrow then Ctrl+Enter), and you'll handle any merged-cell mess somebody hands you. Add Power Query to the toolkit and you'll handle them at scale. That's the real skill — not knowing how to merge, but knowing when not to.
If you're already deep in a workbook full of merged cells that someone else built, don't try to fix it all at once. Pick the single sheet you actually need to analyze. Unmerge only that sheet. Fill the blanks. Convert formulas to values. Then build your pivot or chart on top of the clean version, leaving the original mess untouched for whoever inherits it next.
This staged approach matters because some merged-cell layouts are intentional — print layouts, signature blocks, executive summaries. Stripping them globally breaks formatting somebody spent hours building. Targeted cleanup respects that work while still letting you do yours.
The same principle applies when you're building a sheet from scratch. Separate your data area (no merges, ever) from your presentation area (merges fine, as long as nothing reads from them). A two-zone layout — clean data on Sheet1, formatted report on Sheet2 with formulas pulling values — gives you the visual polish without sacrificing the analytical foundation.
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
Each Excel file is a workbook with a variety of sheets. Which of the following can't be a workbook sheet?
Answer: C. Data sheet
An Excel workbook can contain various types of sheets, including 'Worksheets' (for data entry and calculations), 'Chart Sheets' (dedicated sheets for displaying charts), and 'Macro Sheets' (for VBA code, though less common in modern Excel). 'Data sheet' is not a standard, distinct type of sheet within an Excel workbook; data is typically stored within a regular 'Worksheet'.
After creating an Excel table from a range of data, you want to provide users with a set of visible, clickable buttons to filter the 'Region' and 'Product Category' columns without using the drop-down arrows in the headers. Which feature should you insert?
Answer: A. Slicer
Slicers are interactive controls that provide buttons for filtering data in tables, PivotTables, and PivotCharts. They can be inserted from the 'Table Design' tab and offer a user-friendly way to see and change the current filter state.
You are working with a large dataset and want to quickly select all the blank cells within a specified range to either delete them or fill them with specific data. Which feature would be most efficient for this task?
Answer: C. Go To Special
The Go To Special feature allows you to select cells that meet specific criteria, such as being blank. By selecting 'Blanks' in the Go To Special dialog box, you can highlight all empty cells in a range at once for further action.
What is the default file extension for Excel workbooks in Excel 2016 and later?
Answer: A. .xlsx
The .xlsx format is the default XML-based format introduced in Excel 2007 and used in all modern versions.