How to Merge Cells in Excel - Microsoft Excel

Do you want to spruce up your report? Spreadsheets don’t have to be boring, disorganized, and hard to understand. Instead, you can learn how to merge cells in Excel to make your spreadsheet layout attractive.

Microsoft Excel is the sought-after spreadsheet system that allows you to organize your data in rows and columns.

This system boasts of a merge-cells add-in that will enable you to combine data from multiple cells into a single cell in a row or column. 

You can organize and manipulate the data by merging the rows and columns to suit your report. What’s more, Excel allows you to unmerge the cells.

Learning how to merge cells in excel is easy. If you want a well-organized and attractive spreadsheet, follow the steps below to merge cells in Excel and how to unmerge cells.

Read: Microsoft Excel Cheatsheet.

Reasons for Merging Cells

If you have Microsoft Office, then you probably use Microsoft Excel a lot.

Merging cells not only gives you a well-organized document, but it also customizes your spreadsheet giving it a clean look. You should learn how to join cells in excel to center your title across all columns in the given document or report. Also, merging organizes and combines several sections under a single heading.

What’s more, if you accidentally merge rows and columns, then you get to split or unmerge them. It is wise to learn the ropes of merging first because merged cells can be split. Therefore, merging cells organizes your document making it easy to read and understand.

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.

Merged cells can be the change in your improved productivity. 

Merge Cells in Excel Options

There are four primary merge options available in Excel.

• Merge & Center: This alternative merged cells and aligns text at the center while retaining the top and left most data. This also applies when you want to merge multiple cells.
• Merge Across: This option combines cells in columns without changing their alignment. This also applies when you want to merge multiple cells.
• Merge Cells: It is the simplest method of merging the selected cells. This also applies when you want to merge multiple cells.
• Unmerge Cells: It is the opposite of merging because it unmerges or splits the cells. This also applies when you want to unmerge cells in multiplicity.

Let's get started on how to merge cells in Excel and unmerge cells as well.

How to merge cells in Excel

The following are different ways to merge cells in Excel:

Method 1: Merge & Center Option in Excel

The Merging command is located on the Excel Home Tab. 

1. First, highlight the cells to be merged by clicking on the first cell to be highlighted, hold down the Shift Key, and then click on the last cell range. 
2. Afterward, maneuver to the Merge & Center button located under the Home Tab. 
3. Click on the Merge & Center to merge your preferred cells. However, a warning window appears, cautioning you that proceeding with the merging process will not only discard other values, but it will also retain the left-values only. 
4. Click OK to proceed if you are comfortable with such changes.  

How to Merge Columns in Excel

It is easy to merge columns in Excel. Select the multiple columns you wish to join and repeat the above process. 

1. The first step is to highlight the two columns you want to merge. For instance, you may want to combine the “First Name” and “Last Name” like in the example below. 
2. Take note that selecting the Merge & Center button will provide you with one large cell that has only one value. 
3. Click on the Merge & Center drop-down menu and choose Merge Across. Instead of a single warning, Excel will give you several warnings depending on the number of rows selected. 
4. Click OK for each row to get a newly merged column, as shown below.

Method 2: Merging Multiple Cells using the Format Method

Another easy method to merge cells in Excel is using the format menu. 

1. Highlight the multiple cells to be merged. 
2. Then, On the Home Tab, click on “Alignment”. 
3. Alternately, Right Click on the selected text. 
4. From the menu, click on Merge Cells. In both options, a Format Cells window will be displayed. 
5. Locate the Alignment Tab and check on the “Merge Cells” option. 
6. Then click OK to combine your multiple cells into a single cell.

Disadvantages of Using the Merge Cells in Excels

Although 53% of Excel users utilize the Merge Cells Feature, merging cells creates multiple data problems. First of all, it is difficult to copy and paste data. Secondly, it is impossible to highlight a single column that contains numbers as data. Thirdly, the option of Autofill is disabled, which makes it challenging to save on time on Excel. Lastly, since merged cells are not similar to the original cells, you cannot use essential Excel features such as COUNTIFS and SUMIFS. Therefore, the ideal alternative to counter these problems is using the “Center Across Selection” merging option.

How to Merge Cells without Losing Data

Method 1: Center Across Selection

Center Across Selection does not modify and combine or merge cells in Excel. Instead, it only aligns the relevant text at the center. Therefore, when merging cells, you don’t lose any functionality such as copy, paste, or Autofill. 

1. The first step is to select the text you want to join across. 
2. Next, click on the Alignment Tab in Alignment Group Dialogue and locate the Format Cells found in the dialog box. 
3. Alternatively, press CTRL+1 to launch the Alignment Group Dialogue box. 
4. After that, a dialog box will appear, click on the Horizontal Menu, locate, and select the “Center Across Selection” option. 
5. After clicking OK, the text will appear merged just like in the “Merge Cells”. 

However, the only difference is that the cells are intact, including their functionality. Please note that this option only works for horizontal groups. Therefore, you’ll need to merge the cells vertically. What’s more, ensure that you join cells with single entries because data from multiple entries may be unsuccessful to emerge.

Method 2: Concatenation Formula

The Concatenation formula is the best option for merging cells if you don’t want to lose your data. What’s more, this formula is the best for joining multiple cells, such as “First Name” and “Last Name” into a single cell. However, a new cell will be created for the result.

1. First of all, select cell “C2” and apply the CONCATENATE formula (A2,” ” B2) to get the desired results. 
2. A2 refers to the first cell to be merged, whereas B2 is the last cell to be merged. 
3. The space between the first name and the last name is represented by the two quotation (“) marks. 

Method 3: How to Merge cells using Ampersand (&) Operator

The Ampersand (&) Operator is similar to the Concatenation formula. However, whereas ampersand uses “&” operator function, the latter use the CONCATENATE function. 

1. First of all, select Cell 2 titled “Full Name”. 
2. After that, apply the formula =A2&“ “&B3 to give the desired result as illustrated below.

Merge cells in Excel Shortcuts

Now that we’ve discussed the methods to merge cells in Excel, let’s look at some shortcut options.

First, clicking “Merge” repeatedly in a spreadsheet with multiple cells can be time-consuming and monotonous. It is tiring and mostly tedious.

So, 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

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.

How to Unmerge Cells In Excel

If you need to split the previously merged cells, then you can unmerge them. 

1. Select the merged cells 
2. Click on “Unmerge Cells” on the Merge & Center Ribbon under the Home toolbar. 

However, unmerging cells always place the data on the top-left cell leaving all the other cells blank. What’s more, data, once lost during merging, cannot be recovered after unmerging the cells. Nevertheless, unmerging solves the vast spaces in your spreadsheets caused by merging rows and columns.

Limitations of Merging Cells using Excel

However, Excel’s primary weakness is that only the upper-left value of cells is retained while all other data is discarded. Although data from one cell is retained, the contents of two or more cells cannot be merged. Consequently, only the data from the upper-left will be kept after merging.

Secondly, Excel only merges cells that form a rectangular shape. For instance, it is possible to combine data from cells C1, C2, D1, and D2. However, it is impossible to merge cells from C1, C2, and B1 only. Lastly, the sort command does not function on the already merged cells.

With that said, Excel is the best spreadsheet system to present or report data. Particularly, learning how to merge the cells customizes your document giving it a clean, highly organized, and easy to understand look.

Remember that only data in the top and left-most cell is used when using Excel. Data from other cells is automatically deleted. Although merged data can be split, it is advisable, therefore, to back up relevant data for future reference.

TL; DR

If you’re looking for a software company you can trust for its integrity and honest business practices, look no further than SoftwareKeep.

We are a Microsoft Certified Partner and a BBB Accredited Business that cares about bringing our customers a reliable, satisfying experience on the software products they need.   We will be with you before, during, and after all the sales.

COnclsuion

Have you merged cells in Excel? Merge cell can man a big difference in your productivity. 

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

Related Articles

» How to Merge Multiple Tables in Excel for Better Data Management
» Excel Add Text to Cell Made Easy: Quick Tips and Tricks
» How to Limit Rows and Columns in an Excel Worksheet
» Excel Convert Table to Range and Vice Versa: A Comprehensive Guide
» Expert Tips, Tricks, and Shortcuts to help you Maneuver Microsoft Office
» How To Use NOW Function In Excel To Get Current Date And Time
» Ways To Alternate Row Colors in Excel [Guide]
» How to Create Excel Header Row
» How to Insert Pdf into Excel
» How to Split Column in Excel