Excel Tip: Do you really need to merge those cells?

Just a short one this week, along the lines of my cautionary post a couple of weeks ago on the dangers of cut and paste.

When working on a spreadsheet that someone else has built, there are a number of things that can make life particularly difficult. One of these, which is rarely necessary, is the use of merged cells.

Merging cells allow you to combine one or more adjacent cells into one, usually to allow the cell contents (text or numbers) to be centred, either horizontally or vertically, in the larger combined cell. This is purely a presentation tool, which you can use to make your spreadsheet look better.

To access Merge Cells, you can select the range you want to merge, then either choose format cells and tick the box on the Alignment tab, or use the drop-down in the middle of the ribbon on the more recent versions of Excel.

The purpose of today's post, however, is not to teach you how to use Merge Cells, but to warn of the dangers of using them unnecessarily, and in the wrong place.

Merging cells in a table in Microsoft Word is great and gives you far greater flexibility in your layout, however Excel has one fundamental difference to a table in Word - these cells contain data and/or calculations, linking them to other cells in the worksheet/workbook. Excel treats the contents of the merged cell as being contained in the top left cell of the merged range.

Regular readers will know of my OAP approach, which breaks your Excel spreadsheet design down into three distinct functions:

• O - Obtain the data
• A - Analyse the data
• P - Present the results

If your spreadsheet follows this approach, then there won't be too many problems using Merge Cells at the P stage, as this worksheet (or area of a worksheet) will simply be presenting data obtained and analysed elsewhere -and will not be being used as data itself.

Unfortunately, most spreadsheets do not follow this approach and each worksheet tends to include all of these steps.

Now, when making changes to a spreadsheet that includes merged cells, among the data and analysis, we are faced with all sorts of complications, including the following:

• Data containing merged cells can not be treated like a normal data table - meaning that we can't use all of the tools that we might want to use for referring to a properly formatted data table, such as pivot tables, SUMIF, etc;
• Copying and pasting ranges is restricted to those with cells merged in the same way;
• Fill down doesn't work if any of the cells in the range to be filled are merged;
• Even if we unmerge all of the cells, this rarely solves the problem, as this action will assume that the merged cell contents should be placed in the top left cell of the unmerged range - which may not be where you want them to be. Also, having done this, it is often not clear where your data is, as the labels may now be in a completely different place.

These problems can cause huge amounts of extra work when working with a spreadsheet like this. My advice would be to only use merged cells in a sheet that you know is purely for presentation, or that is so simple that it is very easy to see what is going on.

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".