An ultimate guide on how to combine cells in excel
We use Excel sheets for different kinds of projects. Combining text from one or more cells is vital when we are editing it. Sometimes we may lose data in this process. Here we guide you with simple steps on merging data cells without losing any data. Let us get started.
How to Combine Text from Multiple Cells into One Cell?
Text from multiple cells can be combined into one cell by using ampersand (&) operator.
To combine the text in two cells A1 and B1, steps to be followed are as follows:
- We have to select the cell in which we want to place the combined data.
- To start the formula we have to type an equal sign =
- Then we have to click on the first cell i.e., A1, then type the ampersand operator (&)
- If space is needed between the text of multiple cells, type the text string ” ” (double quote, space, double quote).
- Now click the second cell i.e., B1 and press enter to complete the formula.
Don't use plagiarised sources.Get your custom essay just from $11/page
How to Merge Cells in Excel?
To merge the cells in excel, Merge and Center option can be used in the home tab. But using this option cells can be merged but not the text. When we select the cells and use the “merge and Center” option, it will keep the text from the leftmost cell and remove the text from all other cells. To merge the cells without losing text we can use “CONCATENATE” function. To merge cells without losing text we can use “Center across selection” option as an alternative to merge and Center option.
How to combine Cells in Excel?
To combine the cells in excel we have various options.
We can use merge and center option
We can use ampersand (&) operator.
We can use CONCATENATE function.
Excel concatenates multiple cells.
Concatenate function helps us to combine the content from multiple cells into one destination cell without retyping all the text. Steps to be followed to concatenate are:
Initially we have to select the cell in which combined text to be placed. Then type the function into the cell which starts with an equal sign (=) followed by concatenate.
=CONCATENATE.
Arguments can be entered in two ways
First we have to open the parenthesis, type the cell references (A2, B2) separated by commas and then close the parentheses at the end.
=CONCATENATE (A2, B2). Otherwise we can click on the A2 cell, type a comma then click on B2 cell and then type the closing parenthesis.
Now we have to press ENTER after adding the cell references to the concatenate function.
No space is found between the combined texts as the concatenate function combines exactly what is in the arguments.
If we need the space between the combined texts, we have to add space as another argument in between the cell references. To perform this we need to type a space surrounded by double quotes between the cell references.
=CONCATENATE (A2, ” “,B2)
In order to fill sequential data into Excel with the fill handle we need not type the function in every cell in the column. We can quickly copy the function to the other cell. Steps to be followed to quickly copy the function are as follows:
First we should select the cell in which we have entered concatenate function. The small square found on the lower right corner is called the fill handle. We have to move the cursor over the fill handle until it turns into black plus sign and then click it and drag down by which function is copied down to the rest of the cells.
Excel merge cells vertically without losing data
The quick and simple way to combine two or more cells vertically is to use the built in merge option combined with center option.
First select contiguous cells you want combine
Select the alignment group from the home tab.
Now click on the merge and Centre button.
When we click the button the cells start to combine into a single text
There are other options called Merge across Merge cells which work in a similar way.
Align the text after merging.
First select the text you wish to align.
Now select the desired alignment from the alignment group.
This is found on the home tab.
Why can’t I merge cells in excel
There are several reasons that make cells unable to be merged
The most common reasons are
The cell is in a table
The cell is in a protected sheet
When you are cell is in a table you have to merge it before creating the table.
When your cell is in a protected cell editing is denied
In such cases we need to first unlock the sheet and then go for merging option.
There are many other problems which will not allow to merge the cells
They are:
When the cells have data in the form of image
When the entered data is huge merging and aligning becomes a major issue.
Merge data in excel
Combining the cells using & symbol
First you need to select the cell in which you want to put the combined data
Now type = and select first cell that you wish to combine.
Now & with the help of quotation marks enclosing space
Now select the next cell you wish to combine and press enter.
Combining using the concatenate function
Here you have to select a cell where you wish to put the combined data.
Now type = CONTACT(
Now you need to select the second cell which you wish to combine by using quotation marks, spaces, commas or other text.
There are some problems in which merging become an issue.
We need to take care of the problems accordingly and later merge them.
You can merge cells horizontally and vertically into a single cell.
Aligning is the key point here and do not forget to get the data to the center.