How To Merge Cells in Excel Without Losing Data

Merging cells in Excel is a simple but powerful way to organize and format your spreadsheets. Whether you're looking to combine two cells, center text across a group of cells, or merge multiple rows or columns, knowing how to merge cells in Excel is a must-have skill. In this guide, we’ll walk you through different methods to merge cells, from using the "Merge and Center" feature to combining cells with functions like CONCATENATE or the ampersand symbol.

We’ll also cover how to unmerge cells, troubleshoot common merging issues, and offer helpful tips for working with combined cells in Excel. No matter what your Excel needs are, this guide will help you merge cells with ease and efficiency.

Understanding Merging Options in Excel

When you merge cells, Excel offers a few different options to suit your needs. These include:

• Merge & Center: Merges the selected cells into one and centers the content within the merged cell.
• Merge Across: Merges cells across rows but not entire columns.
• Merge Cells: Merges the selected cells into one, but the content will remain left-aligned (no centering).
• Unmerge Cells: Reverts any previously merged cells back to their original state.

Step-by-Step Methods to Merge Cells in Excel

Merging cells in Excel can help you combine multiple cells into one, making your data look cleaner and more organized. Here are three simple methods to combine cells in Excel:

Method 1: Using Merge & Center

Users appreciate that spreadsheet titles are merged and centered in Excel, making them stand out in marketing materials and publications. Follow these steps to merge and center in Excel data for better presentation.

Step 1: Open the Excel spreadsheet

To start with, open Microsoft Excel on your device and select the spreadsheet you want to merge cells in.

Step 2: Select the cells to merge

Drag the mouse pointer over the two cells and hold down the left mouse button to highlight them. 

Step 3: Merge the selected cells

In the Home tab of the Excel window, tap the "Merge & Center" option. From the drop-down menu, choose the "Merge & Center" option. After the cells you decide to combine, the data will be moved to the middle.

Alternatively, You can use Merge and Center Keyboard Shortcut

• Windows: Use the keyboard shortcut Alt + H + M + C to merge and center cells.
• Mac: Use the keyboard shortcut Command + Option + M followed by C to merge and center cells.

Point to Remember: Some features like Merge Across, Merge Cells, and Merge & Center allow you to create multiple cells per your desired output requirements.

Method 2: Merge Without Centering

Merging cells is straightforward, but there's a risk of data loss if you're not careful. Here's a step-by-step guide to combine text from two cells in excel without losing data.

Step 1: Open the Excel

To start with, open Microsoft Excel on your device and select the spreadsheet you want to merge cells in.

Step 2: Select the cells to merge

Drag the mouse pointer over the two cells and hold down the left mouse button to highlight them. In the Below example, we need to merge the A1, B1 and C1 Cells, so we will select those cells:

Quick Tip: To choose the cells you want, you can also find the starting cell, hold down the shift key, and press the right arrow key to pick all the cells next to it.

Step 3: Go to the Home Tab and Select Merge the selected cells

• Click on the "Merge and Centre " option in the Home tab. Further select Merge Cells Option.
• A popup with the warning "Merging cells only keeps the upper-left value and discards other values." will appear on the screen. Click OK to merge the cells.

Alternatively, You can use Merge Without Centering Keyboard Shortcut

• Windows: Use the keyboard shortcut Alt + H + M + M to merge cells without centering the content.
• Mac: Use the keyboard shortcut Command + Option + M followed by M to merge cells without centering.

Step 4: Preview Result

Now you can see that the cells (A1, B1,C1) are merged.

Note: Remember that the process only saves the information in the left cell. If data is present in any neighboring cells, copy and paste them somewhere else before combining them to prevent data loss.

Methods 3: Merge Across Rows

The Merge Across option is helpful when you want to merge cells in multiple rows but keep the merging limited to each row, not the entire selected area. Follow the below steps to Merge Cells using Merge Across Feature:

Step 1: Select the Cells

Highlight the cells in the same row that you want to merge. These cells should be next to each other in the same row (e.g., cells A1:E6).

Step 2: Go to the Home Tab

On the Home tab in the ribbon, locate the Alignment group.

Step 3: Click Merge & Center and Select Merge Across

Click the Merge & Center drop-down arrow to reveal more merge options. From the drop-down menu, choose Merge Across. This will merge the selected cells horizontally within each row, leaving the content in the leftmost cell.

Alternatively, You can use Merge Across Keyboard Shortcut

• For Windows: Press Alt → H → M → A
• For Mac: Highlight the Cells and Press Command + Option + M

Step 4: Preview Results

Now you can see that the cells are Merged.

Advanced Methods to Merge Multiple Cells

When you merge multiple cells, only the content from the top-left cell will remain visible in the merged cell, and the content from the other cells will be deleted. There are two ways to to merge multiple cells without losing any data:

Using CONCATENATE Function

The CONCATENATE function is a great way to combine text from two or more cells into one cell. This function allows you to merge text from different cells while keeping all the original information.

Step 1: Open Excel and Select the cells

Launch Microsoft Excel and select the spreadsheet you want to work on.

Step 2: Choose an Empty Cell for the Combined Result

Click on an empty cell where you want the merged result to appear. This will be the cell where the combined text from other cells will show up.

Step 3: Enter the Formula

For users using older versions of Excel (before 2016), use the CONCATENATE function:

=CONCATENATE(A2, " ", B2)

This formula will combine the contents of cells A2 and B2, with a space between them.

For users using Excel 2016 and later, you can use the newer CONCAT function (which is more flexible and allows you to combine a range of cells as well):

=CONCAT(A2, " ", B2)

This formula works the same way as CONCATENATE but is part of the updated Excel function set.

Step 4: Customize the Separator (Optional)

You can adjust the separator between the combined values. Instead of a space (" "), you can use any separator, such as:

• A comma: =CONCATENATE(A2, ", ", B2) or =CONCAT(A2, ", ", B2)
• A dash: =CONCATENATE(A2, " - ", B2) or =CONCAT(A2, " - ", B2)
• No separator: =CONCATENATE(A2, B2) or =CONCAT(A2, B2)

Note:

• The CONCATENATE function is still supported but will eventually be replaced by CONCAT in future Excel versions.
• CONCAT can handle ranges of cells, making it more efficient for combining multiple cells (e.g., =CONCAT(A2:A5)).

Using Ampersand (&) Formula

Another approach for combining cells in Excel without losing data is using the Ampersand (&) method. It works similarly to the CONCATENATE function.

Step 1: Open the Excel Spreadsheet

To start with, open Microsoft Excel on your device and select the spreadsheet you want to merge cells in.

Step 2: Select the cells where you want to merge the data

Select the cells where you want to add the data and type "=."

Step 3: Enter the formula in the cells

Choose the first cell you want to merge and enter "&." Make sure to type " " to leave space between the current data. Again enter "&" and choose the second cell. Complete the formula and hit "Enter." Here in the below example, we want to merge A5 and B5 cells.

=A5 & " "&B5

Step 4: The data is merged

The output shows up in the next cell after the values are combined. The two data points will also be combined with a space between them.

How to Unmerge Cells in Excel

Follow the below steps to Unmerge Cells in Excel:

Step 1: Select the Cells you want to Unmerge

Highlight the Cells you want to Merge

Step 2: Go to the Home Tab, Click on the 'Merge' Icon and Choose Unmerge cells

Go to Home Tab, Click on Unmerge Option and Choose Unmerge Cells

Step 3: You have Successfully Unmerged Cells

Your Cells will go back to the original formatting.

Common Issues and Solutions for Merging Cells in Excel

Data Loss: Only the data in the upper-left cell is retained when merging multiple cells.

Solution: Use CONCATENATE or Ampersand to combine cells in Excel without losing any data.

Formula Errors: Merged cells can cause issues with some formulas and functions.

Solution: Avoid merging cells within data ranges used in calculations.

Formatting Inconsistencies: Merging cells may disrupt the formatting of your spreadsheet.

Solution: Reapply formatting after merging or unmerging cells.

Conclusion

Merging cells in Excel offers a range of possibilities for improving the appearance and structure of your data. From combining two columns to consolidating cells across rows, these techniques enable you to make your spreadsheets more organized and visually appealing. Remember, using features like Merge & Center, shortcuts, and other merging options will help you quickly achieve your desired results, whether you’re merging fields, centering text, or handling large datasets. By applying these methods, you can effortlessly merge cells, optimize your data presentation, and take your Excel skills to the next level.