How to Merge Cells in Excel

Excel is one of the widely used spreadsheet Programs today. Very often, the need to combine cells in rows and columns is inevitable. Dive into our step by step guide on multiple methods to merge cells in Excel. Also, grab some shortcut tips and formulas to merge cells without losing your data.

Let us look into a few handy points before getting started. There are 4 types of merge options in excel to combine your cells. They can be defined as follows,

1. Merge and Center – Combines selected cells as one cell and aligns text to the center.
2. Merge Across – Combines only selected cells as a row and aligns the text to the left. This feature also combines multiple columns in a row.
3. Unmerge Cells – Reverses the merge operation and decomposes combined cells to original individual cells.
4. Merge Cells – Combines selected cells and aligns text to the left.

Another important fact to keep in mind is that only the upper cell value will remain when merging cells with data will remain, and other values will be discarded.

Method 1: Merging cells from Home Ribbon

1.Select a cell and then drag your mouse across all the cells you want to combine for merging.

2. Now go to the Home ribbon. Select your preferred type of merge in the alignment tab.

Sometimes if you try to merge several cells with data, you will receive a warning dialog box. Remember, if you continue to proceed with the merging, all values in the selected cell range will be removed except the upper-left cell's value, as shown below.

Method 2: Merging cells with Format Cells

1. Select the cells you want to merge.
2. Click on Ctrl + 1. Now a format cells dialog box will appear.
3. Tap on the Alignment Tab.
4. Click on the merge cells checkbox. You can also make necessary alignments to the text of your merged cell.

Method 3: Shortcut Keys to Merge Cells in Excel

1. Highlight the cells to be merged.
2. Next, press the Alt key and then release it.
3. Then press on H and then click on M.
4. Now press on one of the following letters based on your preference.

• C – to Merge and Center
• A – to Merge across
• M – to Merge cells
• U – to Unmerge cells

Merge cells without losing Data

We have already discussed that merging several cells with data will cause you to lose them during the merging process. However, we have got a few tricks up the sleeve to merge cells without losing data.

Merge cells with CONCATENATE function

Let us look into the following table where you wish to merge the first name and last name column without losing data.

1. First, select the cell and right-click on it. Then tap on insert and select the entire column. This will insert a new blank column to the left of it.

2. Tap on the first cell of the column and type in the function =CONCATENATE(C26," ", D26). You can replace C26 with D26 to cells with which you wish to merge.
3. Next press Enter. Now you will see the new column with the merged data.
4. Next, drag down the fill handle in the first cell to copy the formula and get similar results for the remaining cells.
5. Now, you can remove the two columns used for merging if unnecessary.

Merge Cells with other Formulas

We have also got another simple formula to merge cells without losing data.

1. Right-click on a cell that is to the right side of the cells you wish to merge. Then tap on insert and click on the entire column radio button. Now a new column will appear.
2. Click on the first blank column and type = [cell reference] & [cell reference] &[cell reference]. This formula will merge your cells and the data in the new column as in the figure below. You can merge any number of cells using the '&' symbol.

I hope you were able to get a clear understanding of merging cells. Keep in touch for more posts on how to use various other features in excel.