Navigating large datasets in Excel can be a daunting task, but knowing how to merge and unmerge cells in Excel can significantly streamline your workflow. Imagine creating clean, organized spreadsheets that effectively convey information without the mess. Whether you’re dealing with extensive financial data, organizing project timelines, or simply tidying up your personal records, mastering the art of merging cells in Excel is essential..

In this article, we will walk you through the quick and easy steps to merge and unmerge cells without losing any data. You’ll learn about various methods, including the CONCATENATE function and the Merge & Center option, to enhance your Excel skills. Say goodbye to data management headaches and hello to a more efficient, professional-looking spreadsheet

How to Merge Cells in Excel

Here are Quick and Easy Steps to merge Cells without losing data/text in Excel:-

Step 1: Open the Excel

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

Open the Excel spreadsheet

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.

Select the cells to merge

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.

Go to Home tab >> Merge & Centre >> Merge Cells

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

How to Merge Cells using the CONCATENATE Method in Excel

The risk of losing data is an issue when working with Excel sheets and learning their shortcuts. Using the CONCATENATE technique is one approach to merge cells without losing data.

• According to the previous example, the D3 cell is where you should enter the CONCATENATE formula. Enter the formula “=CONCATENATE(A13” “,C3)“.
• A space, as a separator, is necessary for the CONCATENATE technique. But it’s also possible to use the command without the space. So, put “=CONCATENATE(A3,C3)” into the code.

Enter the formula =CONCATENATE(A1,” “,B1).

How to Merge and Center Cells in Excel

Users appreciate that spreadsheet titles are front and center in marketing materials and publications. Follow these steps to merge and center 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. 

Select the cells to merge

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.

Merge the selected 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.

How to Merge Cells without Losing Data

There are two widely used techniques for merging cells: ampersand and CONCATENATE. Now that we’ve covered the CONCATENATE formula, we can look at the ampersand method.

Step 1: Open the Excel

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

Open the Excel spreadsheet

Step 2: Select the cells to merge

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

Select the Cells

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

Enter the Formula

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.

Data merged

Step 5: Select other Cells

If you press the left mouse button and drag the formula down, it will work the same in all cells.

How to Unmerge Cells in Excel

Finally, we’ll talk about how to undo the merging of cells in Excel. Here are the steps to unmerge cells without any problems:

Step 1: Open the Excel

First, open Microsoft Excel on your device and select the spreadsheet in which you want to merge cells.

Open the Excel spreadsheet

Step 2: Select the merged cells

Mark the cells that you want to unmerge. Now, click on the “Merge & Center” option in the Home tab of the window. From the drop-down menu, select Unmerge Cells.

Select the merged cells

Step 3: Go to the Home Tab, Select Merge and Centre Option and Select Unmerge Cells

Select unmerge cells

Thing To Remember: After joining, the cells will separate and return to their original shape and size. But unfortunately, all of the data will disappear.

How to Use the Excel Ribbon to Merge and Unmerge Cells

Step 1: Open Your Excel Sheet

Launch Microsoft Excel and open the spreadsheet where you want to merge cells.

Step 2: Select the Cells to Merge

Click and drag your mouse over the cells you want to merge. Alternatively, hold down the Shift key and use the arrow keys to select the cells.

Step 3: Go to the Home Tab

Navigate to the “Home” tab in the Excel ribbon at the top of the screen.

Step 4: Click Merge & Center

In the Alignment group, click on the “Merge & Center” button. This will merge the selected cells and center the content.

Step 5: Choose the Merge Option

If you want different merge options, click the dropdown arrow next to “Merge & Center” and select from:

Merge & Center: Merges the cells and centers the content.

Merge Across: Merges cells in each row individually.

Merge Cells: Merges cells without centering the content.

Unmerging Cells Using the Excel Ribbon

Step 1: Open Your Excel Sheet:

Launch Microsoft Excel and open the spreadsheet where you want to unmerge cells.

Step 2: Select the Merged Cell

Click on the merged cell you want to unmerge.

Step 3: Go to the Home Tab

Navigate to the “Home” tab in the Excel ribbon.

Step 4: Click Merge & Center

In the Alignment group, click on the “Merge & Center” button. This will unmerge the selected cells.

Step 5: Choose Unmerge Cells

If you want to unmerge specific cells, click the dropdown arrow next to “Merge & Center” and select “Unmerge Cells.“

Conclusion

Mastering how to merge and unmerge cells in Excel is crucial for creating organized and professional spreadsheets. Whether managing financial data or compiling reports, these techniques enhance your data presentation and productivity. Use the Merge & Center option and the CONCATENATE function to combine cells without losing information. Understanding these Excel skills will streamline your data management and improve your spreadsheet’s readability. Start applying these strategies today to grow your Excel proficiency!

How to Merge Cells in Excel – FAQs

How do I merge and Unmerge cells in sheets??

For merging the cells in sheets follow the steps given below:

• Open Your Google Sheet
• Select the Cells to Merge
• Go to Toolbar >> Merge the Selected Cells

For unmerging the cells follow the steps given below:

• Open Your Google Sheet
• Select the Merged Cell
• Unmerge the Selected Cells

How to Merge Multiple Cells in Excel?

To merge multiple cells in Excel you can use different options:

1. Merge using Merge Across.
2. Merge using Merge and Centre.
3. Merge using Merge Cells Option

How to unmerge cells in excel without losing data?

Follow the steps to unmerge cells in Excel without losing data:

• Open Your Excel Sheet
• Select the Merged Cells
• Copy the Data
• Paste Data to Adjacent Cells
• Unmerge the Cells
• Adjust Cell Content

What is the shortcuts for Merge and Unmerge cells?

The Shortcut keys for Merging Cells in Excel for Windows are:

1. ALT + H + M + M: It allows you to merge the cells.
2. ALT + H + M + C: It allows you to merge and center the cells
3. ALT + H + M + A: It allows you to merge the cells across.
4. ALT + H + M + U: It allows you to unmerge the cells.

What are the Shortcut keys for Merging Cells in Excel for Mac?

The Shortcut keys for Merging Cells in Excel for Mac are:

1. Command + Option + M: It allows you to merge the cells you select
2. Command + Option + M again: It allows you to unmerge the merged cells.