How To Merge Or Combine Cells In Excel Without Losing Data?

Microsoft Excel offers its users the feature of combining cells together. However, if it’s your first time on excel, it can get little confusing. And if you do the merge wrong, only the contents from one cell are kept while the rest is lost. This has reasonably caused great confusion among many users.

If you’re looking to combine two cells together while keeping their data intact, we’re here to help. Keep reading this article to learn more about how you can combine cells in Excel.

How to Merge Cells in Excel?

There are specifically two ways you can combine cells in Excel without losing data. You may use either the Ampersand symbol (&) or the CONCAT function to combine data between two cells into one. 

Using Ampersand (&)

You can use the ampersand symbol to combine data into a single cell. The ampersand symbol combines data in multiple cells in an Excel file.  Although this might look tricky initially, it is very simple. Follow each step closely to use the ampersand symbol to combine data:

1. Open Microsoft Excel and then the workbook you want to combine cells. 
2. On an empty cell in a different row (where you want to combine cell data), type =.
3. Using your pointer, click on the first cell with the contents you want to combine. 
4. On the new cell, you will see the name of the first cell you selected. On your keyboard, select Shift + 7 to enter the ‘&’ symbol. 
5.  If you want to add space between the two combined data, you must add “ ” after the Ampersand symbol. On your keyboard, press Shift + ‘ to enter the “ symbol. Hit the spacebar, then close it with the ” symbol.
6. Use the & symbol again, then use your pointer to select the second cell you want to combine.
7. After you see the name of the cell on the empty cell, on your keyboard, hit Enter. 

Here is an example of what the formula should look like with space between the data:
=A2&“ ”&B2

CONCAT Function

You can add additional elements using the CONCAT function in MS Excel. If you’re familiar with the older versions of Excel, this function is the improved version of the CONCATENATE function. Unlike CONCATENATE, you can pass arguments as a range in this function. This makes merging a larger number of cells easier for users.

Like Ampersand, CONCAT combines all types of data in the string. The only contrast between the two functions is that this function has a limit of 255 strings. 

Follow these steps to use the CONCAT function to combine cells in Excel:

1. Open MS Excel to open your document.
2. On a new cell where you want to combine the cells, type in = CONCAT (
3. Click on the first cell with the data you want to combine, then separate it with the , (comma) symbol. If you want to add space in between, enter “ ”. 
4. Click on the second cell with the next data you want to combine. Add the , (comma) symbol to separate your data. Repeat this step until you’re done adding data. 
5. If you have additional information, enter it inside “ ”. 
6. Close the formula with the ) symbol.

This is how the code should look with added space in between:
= CONCAT (A2, “ ”, B2 “ ”, “AddedText”)  

Flash Fill

Flash fill is an amazing feature on MS Excel that senses a pattern and fills the remaining cells similarly when prompted. You can use Flash Fill to merge data in a single cell by creating a pattern. Many users find flash fill convenient as they do not have to repeatedly use formulas to merge data on multiple cells.

Follow these steps to enable Flash Fill on Excel:

1. Open Microsoft Excel to open your workbook.
2. On an empty cell in a different row, type in the content of the first cell you want to merge. You can add a symbol next to separate the data.
3. On the same cell, enter the contents of the second cell you want to merge. Repeat this step for at least three cells you want to combine.
4. On your keyboard, hit Ctrl + Enter to lock the cell. 
5. Press Ctrl + E on your keyboard to use the flash fill feature

The remaining cells on the row should follow the pattern of the data you registered in the first few cells and replicate it in the remaining cells.

Error While Combining Cells

Did you encounter an error while combining the data from different cells? When you’re just expecting combined cells after hitting enter, you may actually see error codes like #NAME and #SPILL! 

Don’t worry; they’re not that big of a problem. We’ll help you work your way around them as well.

• #NAME: Excel displays this error message when it cannot recognize the data you entered in the formula. If you see this message on your cell, you most likely have made a typo while entering your formula. Double-check your formula for any typing or syntax error.
• #SPILL!: Did you mistakenly select a row instead of a cell while entering the formula? You will encounter this message when the action you requested gives multiple results. Excel is not able to display these results in a single cell. Hence, it’ll show you this message.