How to merge cells in Excel with data

Microsoft Excel is by far the most used tool for analytics, data management and information collection in the world. Knowing the nooks and corners, such as how to use formulas correctly, are a necessity today. Offices worldwide are using Microsoft Excel as a primary source to collect and analyze data. In this article, we will look specifically at how to merge cells in Excel.

Excel helps you boost marketing efforts and collect data, among other things. It helps you spread data, work on calculations and make a presentation using detailed columns and rows in a spreadsheet. Further, it allows businesses to integrate new applications into Excel to keep data organized.

Therefore, having sufficient knowledge about shortcuts can make life easier. Wwe’re going to cover the tricks on how to merge cells in Excel. Merging cells is handy for organizing information for writing materials such as marketing campaigns and blogs.

This Article Covers:

Benefits

Merging cells in Excel helps the user catalog multiple strands of data in a uniform format. It allows you to merge both horizontal and vertical cells. By doing that, the spreadsheet displays data in one large cell rather than multiple columns. It makes the spreadsheet look clean and presentable.

How to Merge Cells in Excel

As mentioned above, merging cells is about turning two or more cells into a large one. Let’s look at the steps to merge cells in Excel on the Windows operating system.

Step 1: Open Microsoft Excel and populate the spreadsheet with data.

Step 2: Highlight the two cells by dragging the mouse over them while holding the left click. Another way of selecting the desired cells is to determine the starting cell, hold shift and press the right arrow key to select all the adjacent cells. For example, the user is merging A1 and B1 in the image.

A1 and B1 are selected and ready to merge.

Step 3: Locate the “Merge” icon below the “Home” tab and click on “Merge Cells.”

Locate Merge and click on Merge Cells.

Step 4: It’s as simple as three steps and cells A1 and B1 are merged.

Cells A1 and B1 are merged.

Note: Keep in mind that the process only saves the data in the left cell (in this case, A1). If B1 or any adjacent cells have data, make sure to cut and paste them elsewhere before merging the cells to avoid data loss.

One of the aspects of handling an Excel sheet and knowing the shortcuts to get things done is data loss. One way to avoid data loss while merging cells is to follow the CONCATENATE method.

If we go by the above example, the CONCATENATE formula must be entered in the C1 cell. Type =CONCATENATE(A1,” “,B1).

The CONCATENATE method requires a separator, in this case, a space. However, one can also use the command without a separator. In that case, enter =CONCATENATE(A1,B1).

Merge and Center Cells

When it comes to marketing campaigns and brochures, users want the titles of the spreadsheets in the center. Merge and center data by following these steps.

Step 1: Select the cells that need to merge.

Step 2: On the “Home” ribbon, locate the “Merge & Center” menu and select “Merge & Center” from the dropdown list.

Select Merge & Center from the dropdown list.

Step 3: The selected cells will merge, and the data will be placed in the center.

The text is placed in the center after merging the cells.

If you have content that is in separate cells, note that apart from the left cell, data from all other cells will be erased.

Now that we’ve discussed some of the methods of merging cells in Excel, let’s look at some shortcut options. Clicking “Merge” repeatedly in a spreadsheet with multiple cells can be time-consuming and monotonous. These shortcuts will help reduce time and effort for users. To begin, highlight the cells and enter one of the following combinations.

Windows

• Merge Cells: ALT H+M+M
• Cancel Cells: ALT H+M+U
• Merge Across: ALT H+M+A
• Merge & Center: ALT H+M+U

Here is a final sales report created and segregated by year, manager location and region.

Final sales report of a company.

Although the “Year” text is in the center of the cell, it will look more presentable to place it in the middle cell rather than the left.

In order to do that, highlight the cells to merge. In this case, we will be merging B1 to E1.

Highlight B1 to E1.

Press ALT H+M+M. The cells are merged, and the text is in the center.

Merge the cells by pressing ALT+H+M+M.

macOS

The procedure book of how to merge cells in Excel on macOS differs from windows. As Apple keyboards lack the ALT key, the shortcuts for merging cells vary slightly.

1. Launch Excel. Locate the navigation bar and click on “Tools.”
2. Click on “Customize Keyboard.”
3. A dialog box with the header “Specify a Command” will appear. In the box, there are two respective columns, Categories & Commands. Select the “Home Tab” from Categories and “Merge Cells” from Commands.
4. Type the new shortcut combination in the text box that appears and click “OK” to set up the shortcut you want.

You may have wondered about a question, “what if I want to merge multiple cells without losing data?” We have an answer to that too. There are two popularly accepted methods to merge multiple cells: the ampersand and CONCATENATE methods. As we’ve already talked about the CONCATENATE formula, let’s look at the ampersand approach.

1. Highlight the cells where the combined data will be placed and type “=”.
2. Select the first cell to combine and type “&”. Ensure to enter ” ” to keep space between the existing data.
3. Select the second cell. Enter the complete formula and press “Enter.”
4. The data are merged, and the output comes in the next cell. Further, the two data points will be combined with a space between them.
5. Hold the left mouse button and drag the formula down to obtain the same result for subsequent cells.

Merge Multiple Cells

Now that you’ve uncovered the mysteries and popularly accepted procedures of merging cells in Excel, time for an advanced lesson. Do you know that you can choose more merging options depending on the type of the spreadsheet and the desired output? Buckle your seat belts and learn.

Merge Across

With the “Merge Across” option, users can combine multiple cells in a row. It’s beneficial if a row has a long string of data that’ll be better represented in a single cell. However, this command is unable to merge cells across multiple rows.

Step 1: Hold the mouse’s left button and drag it from the start of the row to where you want to end. In this instance, it will be A2 to R2.

Hold left click and select the cells that will merge.

Step 2: Go to the “Home” tab. Find the “Merge” icon and click. A dropdown will appear. Select “Merge Across.”

Select Merge Across.

The cells in the row will merge.

The selected cells merge.

Merge Cells Option

With “Merge Cells” options users can combine multiple cells next to each other. It can merge cells across rows and columns as well. The command works similar to the “Merge & Center” option in Excel. However, the data aligns towards the left after merging.

Use Merge Cells option to combine adjacent cells.

Merge & Center

As mentioned above, “Merge & Center” allows users to merge cells across rows and columns while centralizing the text in the left-most column. Keep in mind to move all the other data apart from the left-most cell.

Now that we’ve covered all the tricks of how to merge cells in Excel, it’s time to look at ways to unmerge cells. Follow the steps below to unmerge cells without a hitch.

Step 1: Highlight the cells that need to unmerge.
Step 2: Head over to the “Home” tab, click on “Merge” and select “Unmerge Cells” from the dropdown list.

Select Unmerge Cells to restore the cells back to their original form.

The cells will be unmerged and switch back to the regular shape and size. However, the data will be lost.

Limitations

While the knowledge of how to merge cells in Excel is essential, users must keep in mind the limitations that follow. Merging cells can create problems, especially if there is a need to copy, paste or move data. It can also cause confusion and data loss.

• Only the data in the left cell will be retained after merging. The remaining are deleted.
• The “Merge & Center” option only merges the Excel cells. This provides a challenge for users as the values are not merged.
• After the cells merge, Excel’s “Sort” command doesn’t work on ranges within merged cells.

Now that you have cracked the riddle of merging cells in Excel, save time and get work done faster. This shortcut will help you avoid sorting rows manually, which ends up being time-consuming. We hope that this guide helped you learn something new while saving time and accelerating work in Microsoft Excel.

Which way helped you the most? Let us know in the comments below!