How to Merge Cells in Excel – Easy Steps 2024

What is Merge Cells function in Excel ?

Merging cells combines two or more cells into one larger cell. This is useful when you want a title or label to stretch across multiple columns. Merge cells in excel help to make your spreadsheet look clean and professional. You can do it with just a few clicks.

How to Merge Cells in Excel

Let’s start with the basics. Here’s how you can combine cells in Excel:

1. Merge & Center

This is the most common & easiest way to merge 2 cells in Excel.

This option combines the selected cells into one single cell and centers the content in that new merged cell.

Steps:

• Click and drag to select the cells you want to merge (e.g., A1 to D1)
• Go to the Home tab at the top of the screen.
• Click on Merge & Center.

💡 When you merge cells, only the content in the top-left cell stays. The rest of the data will disappear. So make sure you don’t need that information before merging.

Merge and Center Shortcut

Use the keyboard shortcut ALT + H + M + C to merge and center.

2. Merge Cells without Centering

You can also merge cells but keep the original text alignment:

• Click the arrow next to Merge & Center.
• Then, select Merge Cells from the dropdown.

Merge Cells Shortcut

Use the keyboard shortcut ALT + H + M + M to merge without centering.

3. Merge Across

Merge Across merges cells in each row separately, rather than merging all selected cells into one large cell.

Steps:

• Select the cells in the row you want to merge, e.g.,
• Go to the Home tab.
• Click the arrow next to Merge & Center and choose Merge Across from the dropdown.

This will combine the selected cells across each row without affecting other rows.

Merge Across Shortcut

Use the keyboard shortcut ALT + H + M + A to merge without centering.

Alternatives to Merging Cells

Merging cells in Excel can be helpful for formatting purposes, such as creating headers or organizing data in a visually appealing way.

However, merging cells can also create problems, especially when it comes to sorting, filtering, or performing calculations. Let’s explore the alternatives you can use instead.

1. Center Across Selection

Centers text across a selection of cells without actually merging them.

Use this when you want the visual effect of merged cells but still need the data in individual cells for sorting, filtering, or calculations.

Steps:

• Select the range of cells where you want to center the text.
• On the Excel ribbon, go to the Home tab.
• In the Alignment section, click the small angled arrow in the bottom-right corner to open the Format Cells dialog box.

• In the Format Cells dialog box, go to the Alignment tab.
• Under Horizontal, select Center Across Selection and click OK.

This will center the title “Employee Rating” across columns A to D while keeping each column separate.

2. Combining Data Using Formulas

Sometimes, you need to combine data from multiple cells in Excel without actually merging them. This is especially useful when you want to keep the data in individual cells for sorting or filtering, but you also need a combined view.

Here’s how you can do it using simple formulas.

Using the Ampersand (&) Method

The ampersand (&) is the simplest way to combine text from different cells.

Steps:

• Click on an empty cell where you want the combined data, e.g., cell C2.
• Enter a formula that combines the content of the cells. For example, if you want to combine a first name in cell A2 and a last name in cell B2, you would use:

=A2 & " " & B2

This formula will join the first name and last name with a space in between.

Using the TEXTJOIN Function for Lists

TEXTJOIN is perfect for situations where you need to combine multiple cells with a separator, such as creating a list of items.

Scenario:

• Suppose you have a list of names in cells A2 to A7.
• You want to create a single cell, e.g., B2 that lists all tasks separated by commas.

Steps:

• Click on an empty cell where you want the combined data, e.g., B2
• Use the TEXTJOIN function. For example, if your names are in cells A2 through A7

=TEXTJOIN(", ", TRUE, A2:A7)

This formula will combine all tasks into one cell, with each task separated by a comma and a space.

💡 Note: TEXTJOIN is available in Excel 2019 (and above), and Microsoft 365.

How to Unmerge Cells in Excel

Sometimes, you might need to undo a merge in Excel to get back the original layout of your data.

Unmerging cells is simple and helps you restore the original structure without losing important information.

Steps:

• Select the Merged Cell: Click on the cell that has been merged.
• Navigate to the Home Tab: Go to the Home tab on the Excel ribbon.
• Unmerge: Click the arrow next to Merge & Center and choose Unmerge Cells from the dropdown menu.

💡 Your cells will return to their original state. Note that only the content from the top-left cell remains, while the other cells will be empty.

How to Find Merged Cells in Excel

Finding merged cells, especially in a large spreadsheet, can be tricky. Excel provides a quick way to locate all merged cells in your worksheet.

Steps:

• Use the Find & Select Tool:
  • Go to the Home tab.
  • Click Find & Select in the Editing group, then choose Find.

• Expand the Search Options:
  • In the Find dialog box, click on Options.
  • Search within the sheet or the entire workbook by choosing from the Within dropdown.
  • Click the Format button.

• Specify the Search Format:
  • In the Format Cells dialog, go to the Alignment tab.
  • Check the box for Merge cells and click OK.

• Search and Highlight Merged Cells:
  • Click Find All to generate a list of all merged cells in your selected range (sheet or workbook).
  • Click on any result in the list and then use the shortcut Ctrl + A to highlight all the merged cells found.
  • With all merged cells selected, apply a background color or border to easily spot them in your data.

For more visit EasyExcel

for visit our channel EasyExcel