Learning how to merge in Excel is one of the most practical skills you can build for spreadsheet work, whether you are creating polished financial reports, formatting invoices, or consolidating customer data from multiple sources. Merging combines two or more cells, columns, rows, or even entire worksheets into a single cohesive view, and Excel offers several distinct methods depending on what you need. From the classic Merge & Center button on the Home ribbon to powerful formulas like CONCAT, TEXTJOIN, and Power Query, every approach has a specific use case worth mastering.
Most beginners discover merging when they want to create a single header that stretches across multiple columns in a budget or report. They click Merge & Center, and the result looks clean, professional, and ready to print. However, the same feature that beautifies a title row can wreak havoc on sorting, filtering, and pivot tables when applied carelessly to data cells. That is why understanding when to merge, when to use Center Across Selection, and when to combine values with a formula matters more than the click itself.
This guide walks through every realistic scenario you will encounter. You will learn how to merge cells in Excel using the ribbon, how to merge data without losing values using TEXTJOIN, how to combine columns containing first and last names, and how to consolidate multiple worksheets into one master sheet using Power Query. We will also cover keyboard shortcuts that save hours each week, plus the troubleshooting steps for when merge buttons appear grayed out or when merged cells break your VLOOKUP formulas.
Excel has evolved dramatically since merging was introduced in Excel 97, and modern versions including Microsoft 365, Excel 2021, and Excel for the web all support the techniques discussed here. The screenshots, ribbon paths, and shortcuts in this article reflect the current interface as of 2026, with notes where older versions behave differently. If you use Excel on Mac, the keyboard shortcuts swap Ctrl for Cmd in most cases, though the menu locations remain identical.
By the end of this tutorial, you will know exactly which merge technique to reach for in any situation. You will understand the trade-offs between visual merging and data merging, how to preserve information when combining cells, and how to undo a merge without losing the underlying values. We have included a downloadable practice workbook walkthrough so you can follow along with real examples rather than abstract theory, and every formula is tested in Excel 365 as of the current release.
One important note before we dive in: merging cells in Excel is fundamentally different from merging tables or merging workbooks, even though the words sound similar. Merging cells joins visual space within a single sheet, while merging tables combines data from multiple ranges, often using lookup formulas or Power Query. We will touch on both, but the bulk of this guide focuses on the cell-level operations most users search for when they ask how to merge in Excel.
Finally, if you are preparing for an Excel certification exam or a job interview that tests spreadsheet skills, merging questions appear frequently. Interviewers love to ask candidates how to combine first and last name columns into a full name column, because the answer reveals whether you know formulas, Flash Fill, or just the Merge button. We will cover all three approaches so you walk into any test confident and ready.
The classic option on the Home ribbon. Combines selected cells into one and centers the content. Best for titles and headers that span columns. Only keeps the value from the upper-left cell.
Merges cells row by row within a selection. Useful when you have several rows that each need their own merged header. Saves clicks compared to merging each row separately.
Merges without centering, preserving the original alignment. Ideal when you want a left-aligned merged label or when the data inside is already formatted with custom alignment.
A hidden gem in Format Cells. Visually centers text across columns without actually merging cells. Preserves sort, filter, and pivot functionality while looking identical to a merge.
Formula-based merging that combines values from multiple cells into one cell. Unlike visual merge, this preserves every value and creates a true data merge that downstream formulas can reference.
The most common reason people search for how to merge cells in Excel is to create a title bar across the top of a report or invoice. The process is genuinely simple once you know where the button lives.
Start by selecting two or more adjacent cells in a row or column, navigate to the Home tab on the ribbon, and locate the Alignment group near the middle. You will see a dropdown button labeled Merge & Center with a small arrow next to it. Click the main button to merge instantly, or click the arrow to see all four merge options at once.
The selection step matters more than beginners realize. Excel only merges cells that are directly adjacent, meaning you cannot merge A1 with C1 while skipping B1. The cells must form a contiguous rectangle. If you accidentally select a non-contiguous range using Ctrl+click, the merge buttons will gray out, and Excel will refuse to proceed. Always verify your selection forms a clean block before clicking, especially when merging across multiple rows and columns simultaneously to create a large header zone.
For users who prefer keyboard shortcuts, Excel offers a fast sequence: Alt, H, M, and then C for Merge & Center, A for Merge Across, M for Merge Cells, or U for Unmerge. Press each key in sequence rather than holding them down together. With practice, the Alt+H+M+C combo becomes muscle memory and lets you merge selections in under two seconds without ever touching the mouse. On Mac, the equivalent uses Control+Option+Return after selecting cells, though the ribbon path remains the same.
When you merge cells that contain data, Excel pops up a warning: only the upper-left value will be preserved, and all other values will be discarded. This warning exists for good reason because the operation is destructive. If you accept and click OK, every value except the top-left disappears permanently unless you press Ctrl+Z immediately. Power users develop the habit of copying important data to another location before merging, then pasting it back after the merge completes if needed.
Unmerging works the same way as merging. Select the merged cell or range, click the Merge & Center button again, or choose Unmerge Cells from the dropdown. The original value reappears in the upper-left cell of the unmerged range, while the other cells return empty. This is useful when you inherit a workbook full of merged cells and need to convert it back into a sortable, filterable table. Excel 365 also adds a Find feature that locates every merged cell in a worksheet with a single click.
For more nuanced layouts, the Format Cells dialog gives you finer control. Press Ctrl+1 with your selection active to open the dialog, then navigate to the Alignment tab. Toward the bottom you will find a Merge cells checkbox and a Horizontal dropdown that includes Center Across Selection. Many Excel professionals prefer Center Across Selection over actual merging because it produces the same visual effect without breaking any of Excel's built-in features like sorting, filtering, and pivot table grouping.
Merging interacts with cell borders, fill colors, and conditional formatting in predictable but sometimes surprising ways. The merged cell inherits the formatting of the upper-left cell, which means you should set your colors and borders before merging rather than after. If you apply a fill color to a merged range, the entire merged area receives the color. Conditional formatting rules that reference individual cells continue to work, but they only evaluate against the single retained value, not the entire visual span.
Merging cells is the simplest operation in Excel and the one most users encounter first. Select your range, click Merge & Center on the Home ribbon, and Excel combines the cells into one. The result is a single visual area that takes the formatting of the upper-left cell. This works perfectly for report titles, section headers, and form labels that need to span multiple columns or rows in a clean, presentation-ready layout.
Behind the scenes, Excel still recognizes the merged area as the upper-left coordinate. If your merge starts at A1 and covers A1 through D1, formulas elsewhere will reference the merged value as A1. Cells B1, C1, and D1 effectively become empty placeholders. Knowing this saves headaches when you write formulas that need to pull from a merged title or when you copy a merged cell to a new location and notice the data shifts unexpectedly.
Merging two columns of data, like first name and last name into a full name, is best done with a formula rather than the Merge button. Use the formula =A2 & " " & B2 to combine cells with a space between them, or use =CONCAT(A2," ",B2) for the modern equivalent. For larger ranges with delimiters, TEXTJOIN is unbeatable: =TEXTJOIN(" ",TRUE,A2:C2) skips empty cells automatically and adds a space between each value seamlessly.
Once your merged column is created, you typically want to convert the formulas to static values. Select the formula column, press Ctrl+C to copy, then right-click and choose Paste Special with Values. This removes the dependency on the original columns and lets you safely delete the source data. Flash Fill, accessed via Ctrl+E, often achieves the same result instantly by recognizing a pattern after you type one merged example.
Merging rows is less common than merging columns, but it appears in scenarios like consolidating duplicate customer entries or summarizing daily transactions into a single line. The Merge & Center button works on row selections just like column selections, but again it discards data. For row-level data merging, formulas like SUMIFS, GROUPBY, or pivot tables produce far better results because they preserve and aggregate values rather than throwing them away.
If you genuinely need to visually merge cells across rows for a layout reason, such as a side label that spans three rows in a form, select the vertical range first, then click Merge & Center. The text rotates correctly and centers vertically as long as you also set the vertical alignment to Center in the Format Cells dialog. Merging rows behaves identically to merging columns from the data-preservation perspective, so the upper-left rule applies equally.
If you need a header that visually spans multiple columns but you also want sorting, filtering, and pivot tables to work, skip Merge & Center entirely. Open Format Cells with Ctrl+1, go to the Alignment tab, and choose Center Across Selection from the Horizontal dropdown. The result looks identical to a merge but leaves every cell independent. This single trick has saved countless analysts from rebuilding broken tables.
Even experienced Excel users hit problems with merged cells, and the symptoms often look mysterious until you trace them back to the merge. The most common issue is the grayed-out Merge & Center button. When this happens, look at your selection first. The button disables itself if you have selected a non-contiguous range, if you are inside an Excel Table created with Ctrl+T, or if the worksheet is protected. Each scenario has a specific fix that takes seconds once you know what to look for.
If your selection is non-contiguous, simply reselect a single rectangular range. If you are inside an Excel Table, you have two choices: convert the table back to a normal range using Table Tools, Design, Convert to Range, or accept that you cannot merge cells inside structured tables. Microsoft made this restriction intentionally because merged cells break the table model. For protected worksheets, unprotect the sheet via Review, Unprotect Sheet, then merge and reprotect with appropriate permissions for collaborators.
Another frequent problem is sorting failures. When you try to sort a range that contains merged cells, Excel displays an error: To do this, all the merged cells need to be the same size. This message means at least one merged cell in your range spans a different number of rows or columns than the others. The fix is to unmerge every cell in the sort range using Home, Merge & Center, Unmerge Cells, then perform the sort. If you absolutely need merged formatting, reapply it only to header rows after sorting completes.
VLOOKUP and INDEX/MATCH errors caused by merged cells are subtle because the formulas appear to work but return wrong values. Remember that Excel treats a merged range as the upper-left coordinate only. If you VLOOKUP against a merged cell that visually spans rows 2 through 4, the formula only finds the value when matching row 2. Rows 3 and 4 register as blank to the lookup. The solution is to unmerge the lookup column and fill down the value, or restructure the data so lookups target unmerged cells exclusively.
Pivot tables refuse to work with merged source data. The error PivotTable field name is not valid often points to a merged header in the source range. Excel needs every column to have a unique, non-blank header in a single cell. Unmerge your headers, add unique names to any blank cells, and refresh the pivot table. Many analysts who inherit messy workbooks find that simply unmerging the top row solves seventy percent of their pivot table problems immediately without further changes.
Copy and paste behaves strangely around merged cells too. Copying a merged range and pasting it into an unmerged area sometimes leaves blanks where the merge ended. Copying an unmerged range and pasting over a merged area can either unmerge automatically or trigger a warning, depending on the Excel version. The safest approach is to unmerge both source and destination before bulk paste operations, then reapply formatting manually after the data lands where it belongs.
Finally, watch for hidden merged cells in downloaded templates and email attachments. Open the Find and Replace dialog with Ctrl+F, click Options, then Format, and check Merge cells under the Alignment tab. Click Find All to see every merged cell in the active sheet. This audit takes seconds and reveals merges you never knew existed, often hiding inside header rows or summary blocks created by previous workbook authors who left the file in your hands.
Beyond merging cells within a single sheet, Excel offers powerful tools for merging entire worksheets and workbooks. Power Query, built into Excel since 2016 and now called Get & Transform Data on the Data tab, lets you merge data from multiple sheets, files, folders, or databases into one consolidated query. This is the modern replacement for the older Consolidate feature and handles far more complex scenarios with refresh capability that updates the merged result whenever source data changes without manual rework.
To merge two tables in Power Query, click Data, Get Data, From Other Sources, Blank Query, then load each table using From Table/Range. Once both queries appear in the Queries pane, choose Home, Merge Queries, select the matching columns, and choose a join type: Left Outer, Right Outer, Inner, Full Outer, or Anti Joins. Power Query handles the lookup logic automatically and creates a new merged table that you can load back into Excel as a worksheet or pivot table source for downstream analysis.
For merging multiple workbooks stored in a folder, Power Query offers the From Folder option. Point it at a folder containing dozens of monthly sales files, and Power Query reads every file, combines them into one master query, and refreshes automatically when new files arrive. This is the killer use case that justifies learning Power Query for any analyst who deals with recurring data from multiple sources. The time investment pays off within a week of building your first folder-based merge.
Older Excel users sometimes still rely on the Consolidate feature found under Data, Consolidate. This tool combines data from multiple ranges using Sum, Count, Average, or other aggregations based on row labels. It works for simple cases but lacks the flexibility and refresh capability of Power Query. Most Excel professionals have migrated away from Consolidate entirely, though it remains useful for quick one-off summaries when you do not need an ongoing connection between source and result.
VBA macros offer yet another approach for repetitive merge operations. A simple macro can loop through every worksheet in a workbook, copy specific ranges, and paste them into a master sheet. While VBA requires programming knowledge, it shines when the merge logic is too complex for Power Query or when you need to integrate with other Office applications like Outlook for automated reporting. Recording a macro while you manually perform the merge once is the fastest way to get started with VBA-based automation.
For Microsoft 365 subscribers, the newer LAMBDA, LET, and dynamic array functions like VSTACK and HSTACK make formula-based merging dramatically easier. VSTACK(Sheet1!A1:C100, Sheet2!A1:C100) stacks two ranges vertically in a single formula, creating a true merged dataset without copy-paste. HSTACK does the same horizontally. These functions are revolutionary because they update automatically when source data changes, behaving like a lightweight Power Query for users who prefer formulas over the query editor and just want results fast.
Finally, third-party add-ins like Ablebits, Kutools, and ASAP Utilities extend Excel's native merging capabilities with batch operations, fuzzy matching for combining slightly different records, and shortcuts for common workflows. These tools cost between thirty and one hundred dollars and pay for themselves quickly for power users who merge data daily. Free alternatives exist on the Microsoft AppSource marketplace, though their quality varies widely, so read reviews carefully before installing and granting any add-in access to your sensitive workbooks.
With the technical knowledge behind you, the next step is developing habits that make merging serve your work rather than complicate it. Start by adopting a personal rule: never merge cells inside a data table. Reserve merging exclusively for titles, headers, and form labels above or beside your data. This single discipline eliminates the vast majority of problems that frustrate Excel users and keeps your spreadsheets compatible with sorting, filtering, pivot tables, and Power Query without exception or workaround.
When you receive a workbook from a colleague that violates this rule, take five minutes to unmerge everything and rebuild the layout properly. Use Find and Replace with the Merge cells format filter to locate every offender, then unmerge them in batches. Replace the visual merges with Center Across Selection where the look matters, or simply remove the merge entirely if the cell was merged for no functional reason. Your future self and every collaborator will thank you for the cleanup investment.
For data merging tasks like combining first and last names, default to Flash Fill before reaching for formulas. Type the first merged example in a new column, press Ctrl+E, and Excel often completes the pattern automatically across thousands of rows. Flash Fill recognizes capitalization changes, delimiters, partial extractions, and many other transformations. It is the fastest way to merge columns of text data when you do not need the result to update dynamically when source values change in the future.
When Flash Fill cannot handle the pattern, TEXTJOIN is your next stop. The formula =TEXTJOIN(", ",TRUE,A2:E2) combines five columns into one with commas separating values and automatically skips empty cells thanks to the TRUE argument. This is far more robust than ampersand concatenation because you do not have to write =A2&", "&B2&", "&C2 manually for every additional column. TEXTJOIN also handles up to 252 ranges, making it suitable for merging dozens of columns at once into a single text result.
For ongoing merge tasks where source data changes regularly, invest the time to build a Power Query workflow. The initial setup takes twenty to thirty minutes, but every subsequent refresh happens with a single click. Power Query queries are documented automatically through Applied Steps, so you can hand the workbook to a colleague who has never seen it and they can understand exactly how the merge works. This makes Power Query ideal for team environments and recurring monthly or quarterly reports.
Test your merges thoroughly before sharing the workbook. Sort the data, apply a filter, build a quick pivot table, and run a few representative formulas. If anything breaks, the merge is the likely culprit. Fix it before the workbook reaches your audience, because broken merges shake confidence in your work even when the underlying analysis is excellent. A clean, merge-free data area combined with thoughtfully merged headers communicates professionalism instantly to anyone who opens the file fresh.
Finally, keep learning. Excel adds new merge-related features almost every year, from dynamic arrays to LAMBDA functions to Copilot AI suggestions. Subscribe to a few Excel YouTube channels, follow Microsoft's Excel blog, and try one new technique each month. The investment compounds rapidly because every saved hour each week translates to dozens of hours saved over a year, and merging is a daily operation for most office workers. Mastering it well puts you ahead of ninety percent of Excel users in your organization.