What is Merge Cells in Excel?
Merging is an Excel function that can be applied to several adjacent cells to transform them into a single cell on a spreadsheet. When you merge cells in Excel, they become one larger cell that spans multiple columns or rows. The resulting cell:
- Saves content of the upper-left cell
- Deletes the content of the other cells
Why do you need to Merge Cells?
Merging cells might be useful in different situations, for instance:
- A single header for several columns or rows. Merging is often used when one title is to be centered over several sub-headers to group visually several columns/rows on a spreadsheet
- Large field for inputting longer text data. If your table includes considerable chunks of text, you sometimes don’t want to increase column width to fit the text, because it shifts other fields in a table. Instead, you can merge several adjacent cells to create enough space for the text without affecting other fields
Overall, merging cells helps make the spreadsheet look more professional and easy to read, and so this function is widely used to create user-friendly report forms in Excel.
Let’s say we have a table with prices, the number of items sold, and revenue by product line for two different years (see Figure 1). So, three columns in our table – price, # items, and revenue – are similar for each year. To make the table easier to understand, we can merge:
- Cells C2:E2 to make a header for the year 2014
- Cells F2:H2 to make a header for the year 2015
- Cells B2:B3 to span the Product Line header across two rows
How to do it?
Here is how you can merge cells C2:E2 (‘2014’) from the example above:
- Select the cells you want to merge – see Figure 2
- Go to menu Home >> Merge & Center (in Alignment) – see Figure 3
Follow the same steps to merge cells F2:H2 (‘2015’) and B2:B3 (‘Product line’).
Different Merge options in Excel
There are several ways to merge two cells in Excel:
- Merge & Center (available by default): merge cells and align the text at the center of the resulting larger cell
- Merge Across: when selection includes several rows of data, this function will merge each row of selected cells into one separate cell (Figure 4)
- Merge Cells: simple merge, without applying center alignment
- Unmerge Cells: reverse the previous merging; the contents of the previously merged cell will appear in the upper-left cell of the resulting range of split cells
Merge cells without losing data
Merge function in Excel only saves the content of the upper-left cell and deletes the content of all the remaining cells in the selection. However, sometimes there is a need to merge several cells while concatenating the data from each of them.
While Excel doesn’t provide a separate function for it, there are several workarounds to achieve the same result. The easiest one is to create a separate column or row with the combined data by using CONCATENATE formula or &-operator.
Here is an example of how you can do it (see Figure 5):
- Create a formula to combine several cells
- Select an empty cell (F2 in our case)
- Type =
- Select a cell containing the first part of the text you want to combine
- Type &” “&
- Select a cell with the next part of the text you want to combine
- Repeat steps 4 and 5 until all parts of the target text are included
- Press Enter
- Copy the formula to the remaining cells
- Select the initial cell with the formula (F2 in our case)
- Press Ctrl + C
- Select the remaining cells where you want similar formula (F3:F4 in our case)
- Press Enter
Note 1: When creating a formula, you can just type & instead of &“ “&, but, in this case, the combined text string will not include spaces between words
Note 2: You can use CONCATENATE formula the same way. In this case, you need to type =CONCATENATE( instead of = and ,” “, instead of &” “& (or ;” “; if your system uses semicolon as a list separator)
Shortcuts for merging cells
- Alt + H + M + C – Merge & Center
- Alt + H + M + A – Merge Across
- Alt + H + M + M – Merge Cells
- Alt + H + M + U – Unmerge Cells
- F4 – repeating the previous action. If you need to merge cells multiple times, you can do it once – either by selecting an appropriate command in the Ribbon or by using the shortcuts above – and then just press F4 every time you need to apply it again