ICAEW.com works better with JavaScript enabled.
Exclusive

Design and format

Why merging cells sucks

Author: David Lyford-Tilly

Published: 03 Feb 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

In this article, we look at the most common pet hate of spreadsheet experts everywhere: merged cells.

This article was originally published in 2016 as Excel Tip of the Week #135.

It has been edited to reference latest Excel developments.

What are merged cells?

Microsoft telemetry suggests that over half of all Excel workbooks contain merged cells, so most likely you know the answer to this already. But just in case you don't:

Screenshot of the Merge & Center menu in Excel

The Merge & Center menu, shown here as part of the Home menu, allows you to merge two or more cells into one large cell. The default setting also horizontally centres the resulting text relative to the new, larger cell. They are often used to centre headings across several related columns.

Why are merged cells so bad?

There are a host of annoying problems that crop up once a workbook has merged cells in it.

Formulas

Formulas that refer to the merged cell will default to using the cell reference for the top-left-most cell in it. Formulas addressing the other cells in the merge can still be written but will return a value of 0. This makes creating nice, flexible formulas much harder, as row/column values become unreliable.

Delete or move rows / columns

Deleting a range that contains a merged cell will remove the data only if the top-left cell of the merge is deleted. Otherwise, the range will be shrunk in size and the data will remain. This behaviour is hard to predict and can be counterintuitive.

Pasting data

You can't paste single-column data into a column that has a merged cell in it, because the destination range is not considered to be the same size and shape as the origin range.

Filtering

Filters don't work properly on ranges with merged cells in them.

Selecting rows / columns

You can't use the keyboard shortcuts / mouse to select a range that contains a merged cell without automatically also selecting any other rows / columns that include the merge. This can be particularly bad if the new rows / columns contain more merged cells, cascading through and making the selection wildly separate from what you originally intended.

So how can you work around these issues?

First of all, if a spreadsheet is 100% presentational, and has no data or formulas, then merged cells can be appropriate. But this is rare.

If you still want to have neat column labels, you can try entering the heading in the first column, selecting the desired range for the headings, and then using 'Center Across Selection' from the Format menu:

Screenshot showing how to select Center Across Selection in Excel

This will centre the text visually but leave the actual data in just the first cell.

Alternatively, use repeated column headings, coloured sections, or other visual cues to identify different batches of related columns.

Finally, if you already have merged cells in a workbook and want to get rid of them, you can select the entire sheet and then use the Merge button to unmerge all cells. Note that the data from the merged cells will be dumped into the top-left cell when you do this, with the other cells being left blank.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250