Microsoft Excel is an exceptional tool for analyzing and managing large amounts of data. However, there are situations when you might want to combine cells into one value so as to make it more manageable.
Thinking about How to merge cells in Excel? Merging cells is all about combining one or more adjacent cells either horizontally or vertically. One of the most widely cited reasons to use the Merge Cells function of Excel is for Formatting purposes.
For example, you want to merge the First Name & Last Name columns into one or join several columns such as Street, City, Zip and State into a single “Address” column. Unfortunately, Excel does not provide any built-in tool to achieve this.
Of course, there is the Merge button (“Merge & Center,” etc.), but if you select 2 adjacent cells in order to combine them, as shown in the screenshot below, you will get the error message-
“Merging cells only keeps the upper-left cell value and discards the other values.”
(Excel 2013)
“The selection contains multiple data values. Merging into one cell will keep the upper-left most data only.” (Excel 2010, 2007).
Why does this happen? And how to rectify it? All such questions are answered in this post. Read on…
Table of Contents
Merging Columns in Excel without Losing Data
To merge cells in Excel without losing any data, you should use the following methods –
Method 1: Using the CONCATENATE Function
If you use the Merge cells in Excel like the above, it will only keep the text at the leftmost cell (A1 in this case) and remove the text from all other cells. However, If you don’t want to lose the text from the other cells, use the CONCATENATE Function (pre-Excel 2019) or the CONCAT Function (Excel 2019 & Office 365). The CONCATENATE function combines cells in Excel quickly.
There may be times when you are dumped with data in Excel, and it is not formatted quite the way you want. Say, you have the First Name and Last Name in different columns, and you want to merge them into a single column containing the Full Name. Now, if you use Merge & Center or Merge Across Selection, it will delete the text in the second column and only display the First Name as a merged cell. But this is not what you were looking for.
It is here that the Concatenate Function comes in handy. It will help in cleaning, transforming, and analyzing data in Excel. The Concatenate Function will merge multiple cells into a single cell and keep both values. You can merge or join more than two cells together, whether it contains text, numbers, or both.
In the SALES REPORT below, you have the First Name in Column A and Last Name in Column B. For reporting and further analysis, you may need them to be combined into one column, so it’s best to use the Concatenate function.
To merge columns, follow the steps below:
- Select Columns you wish to Merge.
- To the right of this column, click on the right-click option. Then proceed to select Insert Column. This will create a new column.
- In the cell at the top of this new column, type =CONCATENATE(A3,“ ”, B3). The double quotes symbol “ ” in the middle adds a space between the text from the two cells.
- Press Enter after typing the above function.
- You will now see the text from two columns merged in a single cell in the new column Full Name.
- After that, you can enter the usual concatenation formula in the first cell, and then copy it down to other cells by dragging the fill handle (the small square that appears in the lower right-hand corner of the selected cell).
- Remove the “First Name” & “Last Name” columns, which are not needed anymore.
Method 2. Using the & (Ampersand) Sign
You can also use the ampersand sign (&) to combine cells in Excel. The “&” operator works just like the CONCATENATE function, where you can combine text, numbers, individual cells, etc. Both CONCATENATE and & produce the same results.
Pro Tip: By simply adding CHAR(10) in between your formula, you can enter a line break. You will need to select WRAP TEXT in order to see each text on a separate line.
The formula to combine text with a line break and using Ampersand in the previous example with & sign will be:
=A2 & CHAR(10) & B2 & CHAR(10) & C2 & CHAR(10) & D2
Method 3. Using TEXTJOIN function
This function is available in Excel 2019 & Office 365 only. Instead of selecting individual cells and adding a CHAR function again and again in the formula, you can use the advanced versions of the concatenate function – TEXTJOIN.
This function can be used to concatenate a range of cells using a delimiter. You will need to select WRAP TEXT in order to see each text on a separate line. So, the TEXTJOIN function in our example will look like this:
=TEXTJOIN(CHAR(10), TRUE, A2:D2)
In the TEXTJOIN function, you use space as a delimiter, TRUE to ignore blank cells and the entire range in a single argument.
Method 4. Using Flash Fill in Excel
Flash Fill is a special tool that analyses the pattern from the existing cells and then automatically extracts the data to the pattern that you set. It is probably the simplest way to combine cells in Excel and is available in Excel 2013 or later.
Excel’s Flash Fill feature is a versatile tool that can be used to merge, clean, or split data. We can combine the FIRST NAME and LAST NAME from the previous example using Flash Fill.
Step 1: You should establish a pattern by typing the FULL NAME in cell E2, say Name and Department- Homer Ronny, Finance.
Step 2: Highlight the remaining cell and go to the ribbon menu, and select: Data > Flash Fill.
Step 3: Excel will sense the pattern you provided in C2 and will fill in the empty cells below, merging the FIRST NAME and the DEPARTMENT.
To turn Flash Fill on, go to Tools > Options > Advanced > Editing Options > check the “Automatically Flash Fill” box. You need to type the first entry for Excel to sense the patterns, and then Excel fills in the rest of the data based on the pattern you provided.
Also Read
How to unhide columns in Excel
How to unmerge cells in Excel
How to Merge Rows in Excel?
Merging rows in Microsoft Excel is one of the most common tasks that all of us need to perform every now and then. The problem is that Microsoft Excel does not provide a reliable tool to do this. For example, if you try to combine two or more rows using the built-in Merge & Center button, you will end up with the following error message:
To merge several rows into one, you can use either the CONCATENATE function or concatenation operator (&). In both these functions, you have to supply cells as references and type the desired delimiters in-between.
Let’s consider an example where you have a database in which each row contains certain specific details such as product name, product key, customer name, and so on. Suppose we want to combine all the rows related to a particular order as shown below:
Let us now look at different methods to merge rows.
Method 1. Using the CONCATENATE function
We will be using the same CONCATENATE function to merge rows so as not to lose data. To merge rows using this method, follow the steps below:
- Place the cursor where you would like the data from multiple rows to fall under.
- Then proceed to type =CONCATENATE(B2,”, “,B3,”, “,B4,”, “,B5,”, “,B6,”, “,B7,”, “,B8)
- Select Enter, and you will see the new merged row appear.
Method 2. Using the Ampersand Symbol (&)
We can also use the Ampersand function in Excel to merge multiple rows into a single cell. The formula in our above example will be:
=B2&B3&B4&B5&B6&B7&B8
If you want to separate your rows content using comma (,), space, or any character, insert those marks in between the space of double quote (“ ”).
Method 3. Using the TEXTJOIN Function
Here we will use the TEXTJOIN function to combine multiple rows into one cell. The syntax for this function is
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
A delimiter is the text separator such as comma, space, character. Ignore_empty will use TRUE or FALSE, where TRUE will ignore the empty value, and FALSE will include the empty values.
In our example, the function is used as the following:
= TEXTJOIN(“ “,TRUE,B2:B8)
Method 4. Using the Consolidate Option to Merge Duplicate Rows
The Excel Consolidate option is used to combine information from multiple rows, worksheets, or workbooks into one place. It helps you to summarize your information from your data table from its different locations.
1. Select your data headers, copy and paste them into the location (D4:E11) where you want to show the consolidated data.
2. Select the Cell D4 located just under the left-most header of the new table. Then go to the Data tab and select Consolidate.
3. A dialogue box for Consolidate will appear. In the Functions dropdown box, select Sum (it should already be there by default). Don’t forget to mark the Left Column tick box.
4. Click the Reference box and, using the mouse, select the cells without headers and click Ok.
In the end, you will get a Consolidated Sales Report having a unique list of the Salesperson along with their Total Sales from the initial data set.
How to Merge Two Cells in Excel?
By using the merge function in Excel, we can convert two cells into one cell. This function is usually performed as a means to format and organize information. You can create clear and precise headers and titles for cells.
Combine cells using Excel’s Merge and Center feature
The fastest and easiest way to combine two or more cells in Excel is to use the built-in Merge and Center option. The whole process takes only 2 quick steps:
- Select the contiguous cells you want to combine.
- On the Home tab > Alignment group, click the Merge & Center
Once you click Merge and Center, the selected cells will be combined into one cell, and the text will be centered.
Other Merging Options in Excel
To access a couple more merge options provided by Excel, click the little drop-down arrow next to the Merge & Center button and choose the option you want from the drop-down menu:
- Merge across in Excel: This merges across, implying that the selected cells of the same row are joined together. It merges the values of one row at one time. It retains the text of the upper-leftmost cell only.
- Merge cells in Excel: This merges all the selected cells into one single cell. The cells can be selected either horizontally or vertically or both. It retains the text of the upper-leftmost cell only.
Pro Tip: To change the text alignment after merging, simply select the merged cell and click the desired alignment in the Alignment group on the Home tab.
How to Merge Cells in Excel without Losing Data?
As already mentioned, the standard Excel merge features keep the content of the top-left cell only. However, there is a workaround around this issue.
Method 1. Combining cells within one column (Justify feature)
This is a quick and easy method of merging cells keeping all their content. However, it requires that all the cells be merged inside one area in one column. Here are the steps to do the same.
- Select all the cells you want to combine.
- Make the column wide enough to fit the contents of all cells
- On the Home tab, in the Editing group, click Fill > Justify. This will move the contents of the selected cells to the top-most cell.
- Click Merge and Center or Merge Cells, depending on whether you want the merged text to be centered or not. If the combined values spread across two or more rows, make the column a bit wider and repeat the process.
This merging technique is easy to use, but it does have a number of limitations:
- Using Justify, you can only join cells in a single column.
- It works for text only; numerical values or formulas cannot be merged in this way.
- It does not work if there are any blank cells in between the cells to be merged.
Also Read
How to find Average in Excel
How to enable Macros in Excel
Method 2. Using the CONCATENATE function to Combine Two or Multiple Cells
Users who feel more comfortable with Excel formulas may like this way to combine cells in Excel. You can employ the CONCATENATE function of Excel or & operator to join the cells’ values first and then merge the cells if needed. The detailed steps follow below.
Supposing you want to combine two cells in your Excel sheet, A2 and B2, and both cells have data in them. In order to not lose the value in the second cell during merging, concatenate the two cells by using either of the following formulas:
- =CONCATENATE(A3,”, “,B3)
- =A3&”, “&B3
The formula, however, inserts the concatenated values in another cell.
In a similar manner, you can merge multiple cells in Excel; the CONCATENATE formula will be just a little bit longer in this case. An advantage of this approach is that you can separate values with different delimiters within a single formula, for example:
=CONCATENATE(A2, “: “, B2, “, “, C2)
Method 3. Combining cells with Numbers and Text
If you wish to merge cells in Excel with other forms of data such as numbers or formulae, you can follow the below method-
- Choose the cells you would like to merge and group them under the “What to merge” option.
- Select the delimiter if you wish to separate values in the cell, such as a comma, space, etc
- Select the cell where you want to place the merged cell under the “Place results to” tab
- Ensure you have selected the “merge all areas in the selection” button
- Proceed to click on the Merge button
- You will now be able to view all the selected data in a singular button that contains multiple forms of data along with the selected space.
Since date and time are stored as numbers in Excel, when they are merged, the combined cells indicate the numbers, as shown in Column C. The use of the TEXT function helps in formatting the merged columns.
Excel’s Merging Features – Tips
When using Excel’s built-in features to combine cells, there are a few things to keep in mind:
- Make sure that all the data you want to include in a merged cell is entered in the left-most cell of the selected range. This is because only the content of the upper-left cell will survive after merging. Data in all other cells will be deleted.
- If the Merge and Center button is grayed out, then most likely, the selected cells are in Edit mode. Press the Enter key to cancel the Edit mode, and then try to merge cells.
- Inside an Excel Table, you will have to convert a table to a usual range first (right-click the table and select Table > Convert to Range from the context menu), and then combine the cells.
- It’s not possible to sort a range containing both merged and unmerged cells.
- You cannot turn a range containing at least one merged cell into a full-fledged Excel table or a Pivot Table.
Conclusion
MS Excel is a Microsoft software program that uses spreadsheets to organize numbers and data using formulas and functions. We can perform a variety of tasks, from simple ones like calculating subtraction to more complex ones like the VLOOKUP function.
The next time you are trying to effectively organize and format data in your Excel, you can do so using the Merge Cells function. This function will help you to –
- Combine multiple cells into a singular cell
- Merge Data across two or more column
- Merge Data across two or more rows
- Merge Cells
Easysheet is a blog that features incredible Excel hacks, tips, and tricks. Our goal is to make Excel accessible to beginners and provide them with solutions to all of their problems using the EasySheet.
We are the best place to go for anyone who needs assistance with Microsoft Office products such as Excel, Word, PowerPoint, Publisher, and more!
