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.
Reasons for Merging Cells Merge 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.
Merge Across:
This option combines cells in columns without changing their alignment.
Merge Cells:
It is the simplest method of merging the selected cells
Unmerge Cells:
It is the opposite of merging because it unmerges or splits the cells.
Let’s get started on how to merge cells in Excel
Method 1: Merge & Center Option in Excel
The Merging command is located on the Excel Home Tab.
Afterward, maneuver to the
Merge & Center
button located under the
Home Tab
.
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.
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.
Take note that selecting the
Merge & Center
button will provide you with one large cell that has only one value.
Method 2: Merging Multiple Cells using the Format Method
Another easy method to merge cells is using the format menu.
Highlight
the multiple cells to be merged.
Locate the Alignment Tab and check on the “
Merge Cells
” option.
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 cells. 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.
The first step is to
select the text
you want to join across.
Alternatively, press
CTRL+1
to launch the
Alignment Group Dialogue box
.
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 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.
First of all, select cell “
C2
” and apply the
CONCATENATE
formula
(A2,” ” B2)
to get the desired results.
A2
refers to the first cell to be merged, whereas
B2
is the last cell to be merged.
The space between the first name and the last name is represented by the two quotations (“) 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.
First of all, select
Cell 2
titled “
Full Name
”.
After that, apply the formula
=A2&“ “&B3
to give the desired result as illustrated below.
How to Unmerge Cells In Excel
If you need to split the previously merged cells, then you can unmerge them.
Select the
merged cells
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.
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.
That’s our 360 Degree SoftwareKeep Guarantee. So, what are you waiting for? Call us Today on +1 877 315 1713 or email [email protected]. As well, you can reach us via Live Chat.