Merge and Center in Excel — Complete Guide with Shortcuts, VBA, and the Better Alternative
Master merge and center in Excel: 4 merge options, Alt+H+M+C shortcut, unmerge workflow, VBA, Power Query, and why Center Across Selection beats it.

Microsoft Excel: upcoming dates and official changes
- Jun 30, 2026: Certiport retired the Microsoft Office Specialist 2016 certifications, including MOS Excel 2016, on June 30, 2026. The Microsoft 365 Apps and Office 2019 Excel exams are still available. (certiport.pearsonvue.com)
- Updated Oct 16, 2026: Delivery is now Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) (previously Proctored live-in-the-application exam via Certiport Authorized Testing Centers).
- Updated Oct 16, 2026: Eligibility is now No formal prerequisites; about 150 hours of instruction and hands-on experience recommended (previously No formal prerequisites; ~150 hours of instruction and hands-on experience recommended (Associate)).
- Updated Oct 16, 2026: Time limit is now 50 minutes (60 minutes seat time) (previously 50 minutes (60 minutes seat time)).
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
- The MOS Excel (Microsoft 365 Apps) exam has 33 to 37 questions. (certiport.pearsonvue.com)
- The MOS Excel Expert (Microsoft 365 Apps) exam has 23 to 27 questions. (certiport.pearsonvue.com)
- Microsoft Office Specialist Excel exams last 50 minutes, with 60 minutes of total seat time. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- The passing score for Microsoft Office Specialist Excel exams is always 700 on Certiport's 1 to 1000 scaled score. (certiport.pearsonvue.com)
- After failing a Microsoft Office Specialist Excel exam, candidates must wait 24 hours before the first retake and 48 hours before each later retake, and there is no annual limit on attempts. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft Office Specialist Excel certifications earned on or after September 23, 2025 are valid for five years; those earned earlier do not expire. (certiport.pearsonvue.com)
- The Microsoft Office Specialist Excel exam is proctored and delivered live in the Excel application through Certiport Authorized Testing Centers, either in person or remote-proctored. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft recommends that candidates for the MOS Excel Associate (Microsoft 365 Apps) certification have about 150 hours of instruction and hands-on experience with Excel. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Candidates get their MOS Excel score report as soon as they finish the exam, and it can also be viewed in their Certiport profile. (certiport.pearsonvue.com)
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Microsoft Excel Exam at a Glance
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1–1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
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. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).
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.
Free Excel Study Tools
Interactive Excel resources on PracticeTestGeeks, free to start:
- Free Excel Questions and Answers — 16 questions with instant scoring and answer explanations
- Excel flashcards — review key terms and concepts card by card
- Excel study guide — topic-by-topic overview of what the exam covers
- Excel cheat sheet — one-page printable summary for last-minute review

Merge & Center by the Numbers
Where the Merge & Center Button Lives
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.
The Four Merge Options Excel Actually Offers
Most people think Merge & Center is the only choice. It isn't.
Merge & Center
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.
Merge Across
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.
Merge Cells
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.
Unmerge Cells
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.
How Each Merge Option Behaves
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.
Merge & Center vs Merge Across vs Merge Cells vs Center Across Selection
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.
The Keyboard Shortcut Nobody Uses
Forget reaching for the mouse. Excel exposes every merge option through the ribbon accelerator: Alt + H + M, followed by a single letter.
- Alt + H + M + C → Merge & Center
- Alt + H + M + A → Merge Across
- Alt + H + M + M → Merge Cells
- Alt + H + M + U → Unmerge Cells
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 Keyboard Shortcuts at a Glance
Merge & Center — combines selection into one centered cell.
- ▸Windows ribbon access keys
- ▸Sequential key presses (don't hold)
- ▸Operates on the active selection
Merge Across — merges each row independently.
- ▸Multi-row banner headers
- ▸Saves clicking each row separately
- ▸Preserves row count
Merge Cells — combine without centering.
- ▸Keeps original text alignment
- ▸Useful for left-aligned notes
- ▸Fastest for long-text merges
Unmerge Cells — restore original cell boundaries.
- ▸Step 1 of any data cleanup workflow
- ▸Value stays in top-left
- ▸Other cells return blank
When Merging Cells Will Ruin Your Day
Merged cells break four things that Excel users rely on constantly. Knowing which features break — and how — saves hours of debugging later.
Sorting and Filtering
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
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.
Copy and Paste
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).
Formulas Reading Merged Cells
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.
Merge & Center Pros and Cons
- +Creates clean visual hierarchy for static report titles and dashboards
- +Available across all Excel versions including Excel Online and Excel for Mac
- +Windows ribbon access keys (Alt+H+M+C) make it fast for power users
- +Merge Across option saves time when building multi-row banners
- +Easy to reverse with Unmerge Cells when cleanup is needed
- −Destroys all cell values except the top-left — data loss is silent
- −Breaks sorting, filtering, and pivot tables on the affected range
- −Copy-paste fails between mismatched merge sizes — common source of errors
- −Formulas referencing non-top-left cells of a merge return blank, not the displayed value
- −Cells other than the upper-left hold no value, so rules and formulas that read them see blanks
The Better Alternative: Center Across Selection
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).
Pick the Right Option in 5 Seconds
- ✓Static report title spanning columns → Merge & Center (or Center Across Selection — better)
- ✓Header row above data you'll sort or filter → Center Across Selection (never merge)
- ✓Source data feeding a pivot table → Never merge any cell in the source range
- ✓Multi-row banner where each row spans the same columns → Merge Across
- ✓Cleaning imported data with merged columns → Unmerge then Go To Special then fill
- ✓Building a report template in VBA → Use Range.Merge with DisplayAlerts disabled
- ✓Recurring import with merged headers → Power Query Fill Down handles it automatically
- ✓Excel Table source range → Don't merge inside it (Merge is disabled inside Excel Tables)

Unmerging Imported Data: The Real Workflow
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.
Step 1: Unmerge Everything
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.
Step 2: Find the Blanks
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.
Step 3: Fill with the Cell Above
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.
Step 4: Convert Formulas to Values
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.
Unmerge-and-Fill Workflow Step by Step
Select the entire range
Unmerge with Alt+H+M+U
Open Go To Special
Type the fill-up formula
Apply with Ctrl+Enter
Convert to static values
Sort, filter, pivot freely
Power Query: The Automated Unmerge
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.
VBA: Merge and Unmerge in Code
For automation, VBA exposes two methods on the Range object:
Range("A1:D1").Merge
Range("A1:D1").UnMerge
Range("A1:D1").Merge Across:=True
The 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.
Range("A1:D1").Merge — basic merge. Range("A1:D1").UnMerge — undo. Range("A1:D1").Merge Across:=True — per-row. Wrap in Application.DisplayAlerts = False to skip the data-loss warning during macro runs.
Edge Cases You'll Hit Sooner or Later
Conditional Formatting on Merged Cells
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.
Excel Tables and Merged Cells
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.
Excel Online Behavior
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-Paste Between Mismatched Merge Ranges
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.
Common Merge Mistakes to Avoid
- ✓Merging cells in a column you plan to sort or filter — always breaks
- ✓Merging headers above pivot table source data — pivot won't build
- ✓Copying merged ranges into mismatched destinations — paste fails
- ✓Using Merge & Center where Center Across Selection would work — data still hidden
- ✓Trying to merge cells inside an Excel Table (Merge & Center is disabled there)
- ✓Trusting that =A3 returns the merged value at A2:A4 — it returns blank
- ✓Applying conditional formatting across merged ranges without testing the result
- ✓Building report templates with merges in code without DisplayAlerts off
The Habit That Saves Hours
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.
One Last Practical Note
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.
Sample Excel Practice Questions
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?
- A. Chart sheet
- B. Work sheet
- C. Data sheet
- D. Marco 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?
- A. Slicer
- B. Timeline
- C. Data Validation List
- D. Advanced Filter
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?
- A. Find and Replace
- B. Conditional Formatting
- C. Go To Special
- D. Flash Fill
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?
- A. .xlsx
- B. .xls
- C. .xlsm
- D. .csv
Answer: A. .xlsx
The .xlsx format is the default XML-based format introduced in Excel 2007 and used in all modern versions.
Excel Questions and Answers
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- learn.microsoft.com — Retake policy, test delivery, time limit, eligibility · verified Oct 2026
- certiport.pearsonvue.com — Exam format, score validity, passing score, score release, exam information · verified Oct 2026