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.

Table of Contents

Watch the video

3 Scenarios where you SHOULD merge Cells in Excel | Excel Off The Grid

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:

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

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

  1. 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.

    • 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 Comment