Merge Across Background
Today we will talk about how to merge cells across multiple rows/columns.
Merge cell is a very popular feature in Excel.
When people try to center a cell across multiple columns, they usually go for “Merge & Center“. “Merge & Center” allows users to center text horizontally.
I personally do not prefer merging cells. Read How To Center Cells Across Multiple Columns to see why I am against merging cells and another workaround. However, sometimes we have no choice but to use it because of certain reasons like “My boss insists on it” or whatever.
If you have the following questions, I suggest reading this until the end.
- How to merge multiple group of cells simultaneously in Excel?
- How to merge two cells in multiple rows in Excel?
- How to merge cells across multiple rows and columns?
- How to merge every two cells in Excel?
In this article, I will show you how to merge cells across multiple rows and columns without doing merge and center one by one. Instead, there are 2 ways to make the merging thing a lot more efficient.
After reading this article, you will be able to merge multiple group of cells simultaneously.
Sample Workbook
As always, feel free to download our sample workbook to work through the examples with us.
You may be interested in How To Remove Digits After Decimal In Excel?
Merge Cells Across Multiple Columns by Format Painter
Step 1: Select one set of range you would like to merge
We only need to select one set of cells. In this example, I am gonna select Cell A1 and Cell B1 only.
Step 2-3: On “Home” tab, select “Merge & Center”
Until this step, you are still going through the same steps as you normally do.
Now your cell ranges should become like this.
Step 4: On the same tab, press “Format Painter”
Step 5: Select remaining cells to apply the Format Painter
The magic happens here.
Cell A2 is merged with Cell B2; Cell A3 is merged with Cell B3; Cell A4 is merged with Cell B4; Cell A5 is merged with Cell B5.
Isn’t it amazing? Most of the Excel users only use format painter for cell colour or font size but they lose sight of other usages of format painter. Can you think of other usage of format painter other than this?
You may be interested in How to Sum and Count Cells by Color in Excel?
Merge Cells Across Multiple Rows by Format Painter
In the previous section, we learnt how to merge every two cells on the same row across multiple columns.
Next we are going to learn how to merge every two cells on the same column across multiple rows.
Step 1: Select one set of range you would like to merge
We only need to select one set of cells. In this example, I am gonna select Cell A1 and Cell A2 only.
Step 2-3: On “Home” tab, select “Merge & Center”
Until this step, you are still going through the same steps as you normally do.
Now your cell ranges should look like this.
Step 4: On the same tab, press “Format Painter”
Step 5: Select remaining cells to apply the Format Painter
The magic happens here.
Cell A3 is merged with Cell A4; Cell A5 is merged with Cell A6; Cell A7 is merged with Cell A8.
You may be interested in How to Sum and Count Cells by Color in Excel?
Merge Cells Across Multiple Rows/Columns Option 2: Merge Across
Step 1: Select the range you would like to merge
In the first option, we only need to select one set of cells. In the second option, we do the opposite. Make sure you select every cells you would like to merge, instead of only selecting one set of cells.
Here I am gonna select cell A1 through cell E2.
Step 2-3: On “home” tab, press the small triangle next to “Merge & Center”
The small triangle opens up other options of merging cells – “Merge Across”, “Merge Cells” and “Unmerge Cells”.
Step 4: In the drop-down list, select “Merge Across”
Very few people has noticed “Merge Across” feature but it’s super useful.
“Merge & Center” merges cells across columns and rows, both horizontally and vertically. This command merges Every selected cells and only keep the text in the left upper corner. If you select one row, it keeps the value in the leftmost cells. If you select more than one row, it still only keeps the left upper cell and erase the value in the remaining cells.
“Merge Across” merge horizontally but not vertically. This command merges selected cells row by row instead of merging them as a whole. If you select one row, it keeps the value in the leftmost cells. If you select more than one row, each cells are merged with the cells in the same row. For example, Cell A1 is merged with Cell B1; Cell A2 is merged with Cell B2.
Do you find this article helpful? Subscribe to our newsletter to get regular Excel tips and exclusive free Excel resources.
