Guide to Merging Cells in Excel: Techniques and Alternatives

How to Merge Cells in Excel

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

Merge Center

This is the most common and easiest way to merge two 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, for example 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. Make sure you don’t need that information before merging.

Merge Center Shortcut:

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

Merge Cells without Centering

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

Click the arrow next to Merge Center and select Merge Cells from the dropdown.

Merge Cells Shortcut:

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

Additional Techniques

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 some alternatives you can use instead:

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, for example, A1, B1, C1, and D1. 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 can be beneficial, but there are also alternatives that allow you to keep the data in individual cells while achieving similar visual effects.

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. 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. 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.

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 also need a combined view.

Using the Ampersand Method

The ampersand is the simplest way to combine text from different cells. It concatenates the text with the specified separator (in this case, a space).

Steps:

Click on an empty cell where you want the combined data, for example, 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, for example, B2, that lists all names separated by commas.

Steps:

Click on an empty cell where you want the combined data, for example, 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 names into one cell with each name separated by a comma and a space.

Note: TEXTJOIN is available in Excel 2019 and above and Microsoft 365.