Excel’s Merge Cells feature offers a simple way to organize expand the space a single cell contains. If you have a summary page, for example, you may want to create a title for the display. Or on your data sheet, maybe you want to show that two columns belong to a specific group of data. Either way, Merge Cells in Excel can help you create a nice layout. Let’s take a look at how to merge cells in Excel.
Merge Cells Explained
Merge Cells in Excel will take a range of cells that you select and combine them into a single cell. That is, whether you select a bunch of rows, a single row with multiple columns, or an area of rows and columns, Merge Cells will take the top-left-most cell and expand its area across all the other cells you selected.
For example, say I wanted to take my data:
And I want to show that columns
C is a group called “Part Info.” I would start by inserting a row above the data by right-clicking row
1 and choosing Insert. (Note: be sure to click on the 1 in row
1 to Insert an entire row)
After you have the new row inserted, next select the range
With the two cells selected, go to the ribbon and click “Merge and Center” as shown:
Now the previously two cells have been combined into one, single cell. Notice that the reference for the cell points at
B1. This will be important later.
Now you can treat the two cells as a single cell. Here, I have added text and formatted it.
And that’s basically the gist of it. Before we get to the other Merge Cells options, I want to take a moment to discuss the formatting that takes place when you merge cells.
Merge Cells and Formatting
Let’s take a quick look at what happens when I have different formatting applied in the cells I want to merge. Check this out:
I want to merge cells
A1:B2. What will happen with the colors? Let’s try it out. I’m going to select cells
A1:B2 and use Merge and Center:
Notice it took the blue color in the end. That’s because when Excel merges cells, it uses the top-left cell of the range selected as the formatting guide. Even if you selected the cells starting with
B2 and moved to
A1, it will still use the top-left-most cell as the guide. In other words, it’s not based on which cell you selected first. Don’t believe me? Try it 🙂
Ok, so it takes the color, what about other formatting?
Great question. Not all formatting is treated the same. Let’s look at what happens to other formatting. Take a look at this example:
Here, I have taken
A1 and applied the following formatting:
- Bold, italic, and underline text
- Thick border
- Green cell color
- Red text color
- Indent text to the right
- Top text alignment
- Left text alignment
Now I will select cells
A1:B2 and use Merge Cells (not Merge and Center):
- Thick border
Now, we lost the border from cell A1. Let’s try something else.
Here, I have set the border around the area I want to merge cells. Now I’ll merge cells A1:B2 and see what happens.
The border was kept! Nice.
Here’s what I think we learned here:
When you merge cells, the formatting from the top-left cell will be used, with the exception of borders. However, if there is a border applied on the outskirts of the merged cell area BEFORE you merge cells, that border will be preserved.
Merge Cells and Data
We have covered formatting, now what about data? What happens to data if I have multiple cells selected to merge?
Great question! Let’s try it out:
Let’s merge cells
Well, we lost data. Bummer. What if we wanted to concatenated that and put it in the merged cell?
How to Conserve Data While Merging Cells
If you have a lot of cells to combine (more than, say, 20), then I think you might want a VBA solution for that. Let me know in the comments if this is something you’d like to see explained in a later post. For now, let’s do something simple and quick.
Follow these steps:
- Copy the following text:
- Now, go to an empty cell, and start typing the formula:
- If you have more cells, repeat steps 3 and 4 for each cell
Your finished formula should look like:
=A1&" "&B1&" "&A2
Now you have all the combined cell contents:
Now, copy the cell with the formula and right-click on
A1 and choose Paste Values Only (highlighted below):
A1 has all the text concatenated. Nice. Now you can merge the cells and keep your data:
I’ll admit, that is a bit tedious, but it gets the job done. Hopefully, you don’t have to do that too much 😉
Merge Cells Options
Let’s look at the different Merge Cells options Excel offers. If you look at the Merge & Center button, there is a little dropdown arrow that you can click for more options like so:
Let’s explore each option in more detail.
Merge & Center
This is the default selection of the dropdown list. This will take the cells you select, merge them, and apply a center alignment and middle alignment to the text it contains. If you have formatted the text for a different alignment (such as right align), then that formatting will be overwritten.
This option will take multiple rows of cells and merge the cells row-by-row. Take the following example:
I’m going to highlight cells
A1:C3 and choose Merge Across. Here is the outcome:
Now there are 3 merged cells – each spanning 3 columns.
Unlike Merge & Center, this will merge the cells and not affect the text formatting. Very basic.
This option will take a merged cell and break it apart completely. Let’s look at an example real quick to demonstrate what happens to the formatting of the cells the merged cell ends up breaking apart to.
Here we have a merged cell consuming the range
A1:B3. It contains some simple formatting. For fun, I also applied the border “All Borders” which, if these cells were not merged, all the borders of cells
A1:B3 would be applied (meaning each cell would be bordered). Let’s unmerge and see what happens:
Well would you just look at that! Each cell has been bordered. I did some further testing and found out that you have to apply that type of border after you merge cells and before you unmerge them. This only works with the “All Borders” border type, not just an “Outside Borders” border type.
The text formatting has also been preserved in each cell:
Unmerge Cells will preserve all formatting and apply it to each cell that it breaks apart into; even the border format! And if you apply “All Borders” to the merged cell BEFORE you unmerge, it will apply the “All Borders” to the range even after you unmerge.
I’d like to make one last note about unmerging cells: you cannot take a single, unmerged cell and try to split it up into more cells.
Merged Cells And Formulas
The last piece I want to look at before I close this post up is if you need to reference a merged cell in a formula. Basically put, you must reference the top-left-most cell of the merged cell range. For example, say the cells A1:B2 have been merged and it contains a value of 33. Now, what if we created a formula in D1 like so:
Notice how the formula shows that it actually is referring to B1. What do you think the formula will result to?
Yup, zero. Even though at the moment it seems that B1 should equal 31, it’s really A1 that contains the value. You would have to use:
Notice this time that the formula selects the entire merged cell area. This will return the correct result of 33.
Does that mean I can hide values in B1?
No. Even if you try to set it with VBA, it won’t let me do it. Sad, I know.
Range("B1").Value = "test"
This code will run without errors, making it seem like it set the value, but it doesn’t. I think this is odd. Excel should at least issue a warning about that.
If you made it this far, I commend you! Thanks for sticking it through and I hope to see you again soon!
By the way, what do you use merge cells for? Let me know in a comment below!
Are you ready to master Excel Dashboards?
Still not convinced? Check out my review of the course!
Suggest the next post!
I would love your help to know what I should post about next, so if you enjoyed this content and would like to see more, please let me know what you'd like me to talk about in the comments below. Thank you!