Microsoft Excel is a spreadsheet program that is primarily used for storing and organizing data, runs on Windows, macOS, Android, and iOS. It’s extremely valuable for businesses of all sizes to perform financial analysis. 

When creating or managing a spreadsheet, often you have to merge multiple cells to create data. For example, you want to merge the columns of data named ‘First name’ and ‘Last name’, but going cell by cell to type and delete to combine data would take forever to complete. There are many ways you can merge cells in Excel within minutes. In this article, we will explain the various ways you merge cells easily.

Merging and Combining Cells in Excel

Excel allows you to merge cells, columns, and rows to combine numbers or texts, or other data to organize your data effectively. Merging cells organizes your information making it easy to read and understand.

Using Merge & Center

First, create your data in individual cells in the spreadsheet as shown in the sample image below. For example, one of the main reasons to merge multiple cells is to create a title row in your worksheet.

Select all of the cells that you like to merge and center to fit the width of your data table. Then, go to the ‘Home’ tab and click the ‘Merge & Center’ drop-down menu and select the first ‘Merge & Center’ option.

As you can see all your cells are merged and the tile ‘Shipments’ is centered in the middle at the top of the table. You can also merge cells vertically as well.

If you try to merge multiple rows or multiple columns or multiple rows and columns, only the data on the upper-left cell of the select area will remain and the rest will be deleted.

To merge multiple rows and columns, select the cells and open the ‘Merge & Center’ menu, and click the ‘Merge & Center’ option.

All that, cells in the select area will be merged into one single cell and the data/value on the first cell will be placed in the middle of the cell.

If you select multiple columns and select ‘Merge cells’ from the ‘Merge & Center’ menu, all the data except the leftmost cells will be lost.

Excel will issue a warning before that happens.

As you can see, now all the columns are joined into single cell without centering the text.

The ‘Merge Across’ option has the same function as ‘Merge Cells’ but it combines the selected cells in each row individually. It only works on the horizontal cells. To do that, maneuver to ‘Merge & Center’ menu on the ‘Home’ tab and select ‘Merge Across’.

Excel will give you multiple warnings before combining each row separately and you will get the following result.

Unmerging Cells in Excel

Select the merged cells that you wish to unmerge and click the ‘Merge & Center’ drop-down menu and select the ‘Unmerge Cells’ option.

Now all the cells will regain their individual cell functions but at the cost of their contents. Only the first cell in the unmerged area will retain its data.

How to Merge Cells in Excel without Losing Data

Unfortunately, all the above methods will result in data loss. But when you’re are organizing some important financial data, you can’t afford to lose your data. There are many 3rd party add-ons you can add to excel to merge cells without losing data, but they are unnecessary expenses. So, we’ll show you how to merge cells without data loss using few simple Excel formulas.

You can join data from multiple cells into one cell using the Ampersand (&) operator or the CONCAT function.

Syntax for Ampersand Operator

=cell address&cell address

Syntax for CONCAT Function

=CONCAT(cell address,cell address)

Cell address represents the location of the cell in the spread sheet.

How to Combine Data with the Ampersand (&) Operator

First, we’ll combine cell ‘A3’ and ‘B3’ and add the combined data to cell ‘E3’. To do that, type the following ‘&’ formula in the cell ‘E3’ and you will get the merged data. You can combine multiple cells without losing data by using this formula. The following is an example formula, make sure to replace cell addresses with whatever cells you want to merge.

=A3&B3

You can also apply the formula to multiple cells by dragging the filler icon below the combined cell.

Now, the formula is applied to the whole column and ‘Item’ and ‘Rep’ cell is merged and added to the ‘E’ column.

Next, we’ll add space between the ‘Desk’ and ‘Smith’ by adding a space between two quotation marks in the formula.

=A3&" "&B3

You can also combine data with a specific separator in between, it could be a space, a comma, colon, or any other character. We’ll use a hyphen to separate the ‘A3’ text and ‘B3’ text.

=A3&"-"&B3

If you combine text cell and date cell, you’ll get a messed up result like it is shown in the picture. It’s because Excel stores date and time as serial numbers, and if you merge cells that have date or time, as shown below, it combines the data with the serial number.

To combine date and text correctly, use the following formula. 

=A3&"-"&TEXT(D3,"dd mmm yyy")

You can add your own text with the merged cell data with the next formula.

=A3&"in"&B3

How to Combine Data with the Concatenation Formula

The only difference between CONCATENATE and Ampersand (&) operator is the 255 strings limit of the CONCATENATE function and the ampersand doesn’t have that limitation. But CONCATENATE formulas are easier to read and remember.

CONCAT function replaces CONCATENATE function in Excel 2016 and newer versions. Although CONCATENATE function will stay available for compatibility with all versions of Excel, the CONCAT function is only available in Excel 2016 and later versions.

You can use the CONCATENATE or CONCAT formula the same way as the (&) operator, the only difference is the syntax.

Type the following CONCATENATE formula in the cell ‘E3’ and it will combine ‘A3’ and ‘B3’ data. You can combine as many cells as you want by using CONCATENATE or CONCAT function. 

=CONCATENATE(A3,B3)

You can also combine data with a specific separator or character in between. 

=CONCATENATE(A3," ",B3)

You can concatenate any text with the combined cell data using this formula.

=CONCATENATE(A3," in ",B3)

The CONCATENATE formula to combine date and text properly is:

=CONCATENATE(A3," ",TEXT(D3,"dd mmm yyy"))

That’s it, now you successfully learned how to merge and combine cells in Excel.