Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

When you should merge cells in Excel – 3 scenarios

No matter where you look, there is a consistent message that it is bad to merge cells in Excel; instead, we should use Center Across Selection.

Whenever my friend Richard Sumner posts about his love of merged cells on LinkedIn, he is always met with many replies stating that merging cells is terrible. But here is the thing… in some scenarios, merging is not only better but the only option.

So, in this post, let’s look at three scenarios when we need to merge cells in Excel.

Watch the video

Watch the video on YouTube

Merge Cells vs. Center Across Selection

Let’s start by understanding the difference between merging and center across selection. They both display values across multiple cells but achieve it in different ways.

What are merged cells?

To merge cells, click Home > Merge & Center.

Home - Merge and centre from ribbon

Merging in this way creates a single cell from the selected range. We can change the alignment and formatting of this single cell.

Merge cells creates one single cell

However, this merged cell can lead to issues with:

  • Copying and pasting
  • Selecting and moving a column of values
  • Charts
  • Calculations with arrays
  • VBA macros

We might even encounter error messages like the following:

Merged cell error

What is center across selection?

Center across selection does not create a single cell; instead, it uses cell formatting to display the left cell in the middle of the range.

Select the cells to center and press Ctrl + 1

In the Format Cells dialog box, click Alignment > Horizontal (drop down) > Center Across Selection.

Alignment - apply centre across selection

This method keeps all cells separate, but the value in the first cell is displayed as centered across all the other cells.

Center Across Selection displays values across multiple cells

This does not lead to any of the error messages caused by merge.

When you should merge cells

If center across selection does not trigger any errors, but merged cells do, why should we ever merge cells again?

Well… center across selection is not perfect either. The following three scenarios demonstrate where merging is the better option.

Scenario #1: Grouping & hidden columns

It’s not uncommon for detailed report breakdowns to be hidden inside a group while leaving a total displayed. Users can expand the group and see the complete information if they wish.

Look at the example below. The years are spread across multiple cells.

Center Across Selection vs Merge

However, what happens if we group or hide the quarterly columns?

Center across selection the values disappear with group

Merge and center correctly displays the year, while center across selection displays a blank cell. So, in this scenario, we need to use a merged cell.

Scenario #2: Merging rows

Center across selection works across columns but not rows. So, let’s suggest we have the following information.

Data with similar reference numbers

The item numbers are so similar that it’s difficult to see where one ends and another begins. We can’t use center across selection to group the reference numbers, but we can merge cells.

Merge works across rows

There we go, much better. Cells merged and aligned top and left.

Before anybody says anything… Yes, we can achieve something similar with conditional formatting. The point I’m trying to make is that merged cells can use rows and columns; while center across selection only uses columns.

Scenario #3: Small grid matrix

Look at the following report; something doesn’t look right, does it? Maybe it’s that enormous amount of unused space. The top table has 4 columns; the bottom table has 6.

Unused space

This problem is caused by Excel’s grid structure; everything in the same row/column must have the same height/width. Unless we use a small grid structure.

Look at the image below. There are narrow columns; each cell is a small square. Now, we can fit a 6-column table neatly below a 4-column table.

Small grid structure

A small grid structure gives the appearance of different columns placed below other columns.

If we still want the freedom to align values how we wish; only merged cells give us this freedom.


All the scenarios in this post involve the presentation of information. This is a crucial distinction to make.

Merged cells should not be used with data or calculations. How data or calculations look is irrelevant; therefore, functionality and avoiding errors is critical.

However, when working on the presentation of a report or input sheet, how something looks is more important. Therefore, we can use merged cells if it provides the options we need.

Related Posts:

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

4 thoughts on “When you should merge cells in Excel – 3 scenarios

  1. Jon Peltier says:

    Merged cells cause lots of problems in your analysis worksheets, with charts, formulas, etc.

    For the display worksheets, the reports, the dashboards, merged cells are just fine, since these should be the final stop on the data to analysis to display journey.

    • Excel Off The Grid says:

      Yep, that is is pretty much my view. The underlying issue is not structuring a workbook properly, but merged cells get the blame.

Leave a Reply

Your email address will not be published. Required fields are marked *