For a visual heading, select the cells and choose Home > Merge & Center; to combine information without losing cell contents, use a text formula or Power Query instead. This guide covers 11 ways to handle Excel “zones”—individual cells, ranges, or tables—and explains which option suits formatted reports, joined text, or data from separate lists.
1. Merge & Center for a Single Heading
Select the cells across which a heading should sit, then use Home > Merge & Center in Excel for Microsoft 365 on Windows. Excel turns the selected block into one larger cell and centers its contents, which can make a report title appear above several columns. The important limitation is that only the value in the upper-left cell is retained; Excel warns if other selected cells contain data. Use this for presentation, not for a table’s working data, where merged cells can interfere with sorting, filtering, and selecting individual records.
2. Merge Cells Without Centering
Open the arrow beside Home > Merge & Center and choose Merge Cells to combine a rectangular selection while keeping the text’s existing alignment. This is useful when a label should span several columns but remain left-aligned, or when vertical centering is not appropriate for the layout. Like other merge commands, it keeps only the upper-left cell’s content, so check the selection before confirming. It changes the sheet’s structure rather than joining the contents of multiple cells; if every value matters, concatenate them into a separate cell instead.
3. Merge Across for Separate Row Headings
Choose Merge Across from the Merge & Center dropdown when each row in a selected block needs its own merged area. For example, selecting A1:C3 creates three horizontal merged cells—one for each row—rather than one large cell covering all nine cells. This can help format grouped labels in a designed report, but it still retains only the leftmost value in each row and removes the other selected cell contents. It is not a way to combine three columns of records into a single useful data field.
4. Center Across Selection Without Merging
For a centered heading that should visually span columns while leaving every cell intact, select the range, open Format Cells with Ctrl+1 on Windows, choose the Alignment tab, and set Horizontal to Center Across Selection. The text in the leftmost cell appears centered across the selected cells, but the cells remain separate, which avoids many sorting and selection problems caused by merged cells. This option works best when the heading is in the first cell of the selected range and the cells to its right are empty. On Mac, open Format Cells with Command+1; the setting is also on the Alignment tab.
5. Combine Text with CONCAT
When names, codes, or labels are in separate cells and need to become one value, CONCAT joins their contents without changing the source cells. With a location in A2 and an identifier in B2, enter =CONCAT(A2," ",B2) in C2; if A2 contains North and B2 contains 42, the result is North 42. The space is included as a text argument so the values do not run together. CONCAT is available in Microsoft 365 and Excel 2019 or later; for older Excel versions, the ampersand operator is a broadly compatible alternative.
6. Join a List with TEXTJOIN
TEXTJOIN is designed for combining a range of text values with a chosen separator, and it can skip empty cells. With Red, Blue, Green, and Gold in A2:A5, enter =TEXTJOIN(", ",TRUE,A2:A5) in B2 to return Red, Blue, Green, Gold; TRUE tells Excel to ignore blanks. This is handy for a one-cell summary of tags, product options, or selected labels, particularly when the number of filled cells varies. It does not merge the original cells or preserve each value as a separate sortable field, so keep the original range when you may need to analyze the entries individually.
7. Join Two Values with the Ampersand Operator
The ampersand (&) joins text values and works in older Excel versions as well as current Microsoft 365. With a region in A2 and a code in B2, enter =A2&" - "&B2 in C2; North in A2 and 42 in B2 produce North – 42. The quoted separator can be a space, slash, or other text, so it is easy to create readable labels without altering either source cell. For a long list of values, CONCAT or TEXTJOIN is usually easier to maintain than a chain of ampersands.
8. Stack Ranges Vertically with VSTACK
VSTACK places one range below another in a spilled result, making it useful for appending monthly or departmental lists that share the same columns. With a January block in A2:B4 and a February block in D2:E4, enter =VSTACK(A2:B4,D2:E4) in G2; the result is six rows by two columns, with the February rows following the January rows. Keep the source ranges the same width and leave enough empty space below the formula for the result to spill. VSTACK requires Microsoft 365 or Excel 2024, and it combines the displayed ranges rather than creating a permanent Power Query output.
9. Combine Columns Side by Side with HSTACK
HSTACK puts ranges next to one another in a spilled array, which can be convenient when two aligned lists are stored in separate areas. With names in A2:A4 and cities in C2:C4, enter =HSTACK(A2:A4,C2:C4) in E2; Excel returns a two-column result pairing each name with the city on the same row. Check that the source rows are already in the correct order: HSTACK matches by position, not by a shared ID or other key. It requires Microsoft 365 or Excel 2024, and unequal range heights can produce padding values in the shorter range’s result column.
10. Match Tables with Power Query Merge Queries
Power Query’s Merge Queries joins two tables using matching key columns, such as Customer ID, rather than placing one range beside another by row position. Convert each source range to a table, select a table cell, and use Data > From Table/Range to open it in Power Query; in the editor, choose Home > Merge Queries, select the second table, and click the matching key column in both previews. The default Left Outer join keeps all rows from the first table and adds matching values from the second; expand the resulting column to choose which fields to include. This is a strong choice for repeatable data preparation, and Refresh can update the result when source tables change.
11. Append Tables with Power Query
Use Power Query’s Append Queries when two lists have the same kind of records and should become one longer table, such as current and prior-year transactions. Load each source table through Data > From Table/Range, then in the Power Query editor choose Home > Append Queries and select the tables to combine. Power Query aligns fields by column name, not simply by their position, so check spelling and headers first; columns present in only one table receive null values in rows from the other. Unlike manually copying and pasting, an append query can be refreshed after the source tables are updated.
How to Choose
First decide whether “merge” means changing the layout or combining information. Use Merge & Center, Merge Cells, or Merge Across only for visual formatting, and only when losing all but the upper-left value in each merged area is acceptable. For a heading that should look centered but remain compatible with a normal grid, Center Across Selection is often the safer layout choice.
For text, choose CONCAT when you are joining a few cells, TEXTJOIN when you need a separator and want to skip blanks, or the ampersand operator when you want a simple formula that works in older Excel versions. These formulas return a combined text value; they do not make a new relational table. Keep the underlying values in separate columns if you expect to sort, filter, or calculate using each field.
For ranges, VSTACK is suited to a quick vertical combination and HSTACK to a quick side-by-side arrangement, provided the rows or columns already correspond correctly. Choose Power Query when you need a repeatable process, especially for combining refreshed files or joining tables by a key. Before choosing a method, also check your Excel version, whether source data must remain unchanged, and whether the result needs to refresh when that source changes.
Frequently Asked Questions
Does merging cells delete data in Excel?
Yes. The standard merge commands retain the value in the selection’s upper-left cell and discard contents in the other selected cells. Excel normally displays a warning before proceeding.
Can I merge cells and still sort a table?
Merged cells can prevent normal sorting when the merged areas do not match the sort range. Avoid merging cells inside a data table; use Center Across Selection for a visual heading instead.
What is the difference between Merge Queries and Append Queries?
Merge Queries matches rows using one or more key columns and brings fields together. Append Queries adds rows from one table below rows from another, aligning fields by column names.
Why does VSTACK show an error or unexpected values?
Check that the ranges have compatible widths and that the formula’s spill area is empty. VSTACK needs Microsoft 365 or Excel 2024; Excel versions without it will not recognize the function.
Will a formula-based merge update when source cells change?
Yes. CONCAT, TEXTJOIN, ampersand, VSTACK, and HSTACK recalculate when their referenced cells change. Power Query results update when you refresh the query, while a manual copy-and-paste result does not.
Final Thoughts
Excel offers several different meanings of “merge,” from combining the appearance of cells to joining text or matching entire tables. For most working data, keep cells unmerged and choose a formula or Power Query method that preserves the values you need. Use visual merging sparingly, and verify the source data before applying a command that discards cell contents.
Related
- Excel Merge Multiple Cells Into One Cell: A Clear Guide
- Excel: Merge Data From Multiple Cells Into One Cell
- Excel Merge Cells Option Not Available? Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Conditional Formatting: Highlight Dates 1 Year LaterTo highlight a date exactly one year from today in Excel, select the date cells and create a…
- Excel Merge Blank Cells Below: Steps and AlternativesTo merge blank cells below a value in Excel for Microsoft 365 on Windows, select the cell containing…
- Excel Color Codes: How to Apply, Save, and Reuse ThemTo give a cell a specific color code in Excel for Microsoft 365 on Windows, select the cell…
- Google Sheets: Extract a Date from a TimestampTo extract the date from a timestamp in Google Sheets, use =INT(A2) when A2 contains a real date-time…
- Add Page Numbers in Word on Mac: Step-by-Step GuideTo add page numbers in Word on a Mac, open the Insert tab, choose Page Number, select where…
- Excel Shortcut to Edit a Cell: F2 and Other MethodsIn Excel for Microsoft 365 on Windows, select the cell you want to change and press F2 to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.