Merge Cells In Depth

Joseph Basic, Dos and Don'ts, Excel, Formatting 3 Comments

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:

Merge Cells different formats

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:

Merge Cells and formatting

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:

Merge Cells more formatting

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):

Merge Cells more formatting

Formatting lost:

  • Thick border

Now, we lost the border from cell A1. Let’s try something else.

Merge Cells Border Format

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.

Merge Cells Border Format

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:

Merge Cells and data

Let’s merge cells A1:B2:

Merge Cells warning

…uh oh.

Merge Cells data lost

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:

  1. Copy the following text:ย &" "&
  2. Now, go to an empty cell, and start typing the formula: =
  3. Click A1
  4. Press CTRL+v
  5. Click B2
  6. Press CTRL+v
  7. Click A2
  8. Press CTRL+v
  9. If you have more cells, repeat steps 3 and 4 for each cell
  10. Press Enter

Your finished formula should look like:

=A1&" "&B1&" "&A2

Now you have all the combined cell contents:

Merge Cells concatenated

Now, copy the cell with the formula and right-click on A1 and choose Paste Values Only (highlighted below):

Merge Cells concatenated

Now A1 has all the text concatenated. Nice. Now you can merge the cells and keep your data:

Merge Cells concatenated

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:

Merge Cells Options

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:

Merge Across

I’m going to highlight cells A1:C3 and choose Merge Across. Here is the outcome:

Merge Across Applied

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.

Unmerge cells

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:

Unmerge cells

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

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:

Merge Cells and Formulas

Notice how the formula shows that it actually is referring to B1.ย What do you think the formula will result to?

Merge Cells and Formulas

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:

Merge Cells and Formulas

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!

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

  • Alicia

    Hi,
    I know this is not related to this post, but I have a question about Excel and thought you could help me. I have a spreadsheet that I use at work, and I need to add a drop down box so users can select one option in the list. How could I do that?

    Thanks!

    • Hi Alicia,

      I’ll make that my next blog post ๐Ÿ™‚ Can you please subscribe to get notified of the new post?

      Thanks,
      Joseph

  • Armin

    This example was absolutely perfect for the problem I was working on. Thank you ever so much!!! PS. I’m Canadian, eh.