Merge Cells In Depth
February 21, 2013
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 B
and 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 B1:C1
:
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):
Formatting lost:
- 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 A1:B2
:
…uh oh.
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:
=
- Click
A1
- Press
CTRL+v
- Click
B2
- Press
CTRL+v
- Click
A2
- Press
CTRL+v
- If you have more cells, repeat steps 3 and 4 for each cell
- Press
Enter
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):
Now 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.
Merge Across
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.
Merge Cells
Unlike Merge & Center, this will merge the cells and not affect the text formatting. Very basic.
Unmerge Cells
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.
Sub test()
Range("B1").Value = "test"
End Sub
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.
Wrap Up
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!