Do you need to Merge that?

There are a number of “basic errors users make in Excel” that results in their spreadsheet life being that much more challenging when it doesn’t necessarily need too. One of these, which we are discussing today, is the use (or unnecessary use) of merged cells in an Excel spreadsheet.

Why Merge Cells?

Merging cells in a spreadsheet is a process that allows you to join one or more adjacent cells (horizontally or vertically or both) into one larger cell that is then displayed across multiple columns or rows. One of the main reasons to do this is for reporting or presentation purposes – or simply put, to make a spreadsheet look nice.

If we take a quick look at the example below, you’ll see we have a simple Sales Results spreadsheet, with raw data from a number of different Departments and Divisions. A typical formatting decision for this type of information is to have the heading centred across the top of the table. To achieve this, you could merge all the heading cells to create one large cell and then centre the text. As the information in our example is raw data, we’d discourage you from doing any formatting to it at all.

The purpose of this article is not to teach you how to Merge Cells in Excel, but to provide some practical advice on some issues surrounding using merged cells unnecessarily throughout your spreadsheets.

Sample Sales Report Raw Data

Now when it comes to creating a spreadsheet, we at ExcelSuperSite, use what we call the D.A.R.E Methodology.

This methodology breaks the design of a spreadsheet down into separate functions:

Following this methodology, merged cells won’t be used in any the Data areas nor Analysis areas of a spreadsheet, but may occasionally be used in the Reporting areas. It is generally not an issue to use merged cells in the Reporting areas of a spreadsheet as these areas are simply presenting the information obtained or analysed elsewhere and should not be used for calculation or analysis of raw data.

So What are the Issues with Merging Cells?

So let’s assume that you don’t follow our D.A.R.E. Methodology for creating your spreadsheets and that your spreadsheets are a mix of data and analysis and presentation/reporting tables. Then just for the fun of it, we also through some merged cells into the mix. Now, when trying to make changes to a spreadsheet that is constructed like this, we are faced with all sorts of complications. These include:

• 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 cell ranges is restricted to cells that are merged EXACTLY the same way as the cells being copied;
• Fill down doesn’t work if any of the cells in the range to be filled are merged;
• If you unmerge a range of cells, the merged cell contents will be placed in the top left cell of the unmerged cell range – which may not be where you want the contents to be. As a result column headings and data underneath it may now be misaligned;
• Excel won’t apply formats to a merged cell unless you select all the columns or rows that comprise the merged cell range;
• Columns with merged cells can’t be sorted;
• You can not select a single-column range if there is a merged cell in it;
• You cannot put a filter on a column with a merged cell in it;
• Formulas and Functions that refer to merged cells will not work;

The issues mentioned above can result in large amounts of additional effort when working with a spreadsheet that is not well constructed. Our advice is to only use merged cells in sheets that are purely for presentation/reporting purposes and NEVER EVER use merged cells in the Data or Analysis areas of your spreadsheets.
Our advice - only use merged cells in sheets that are purely for presentation purposes and NEVER EVER use them in the Data or Analysis areas of your spreadsheets. Click To Tweet

How Do I Achieve the Same Effect Without Merging Cells?

So how then do you achieve a similar effect without merging the underlying cells? Use the Center Across Selection cell format option.

To apply the Center Across Selection format, select the cells you want to appear merged (cells A1 through to F1 in our example) and then launch the Alignment group dialogue and click the Alignment tab. Center Across Selection is in the Horizontal: drop-down box.

Alignment Group Dialogue

Format Cells – Center Across Selection

Using Center Across Selection will achieve a similar formatting effect on your cell selection without having to Merge cells and without any of the complications mentioned above.

Raw Data Heading Centered without Merging Cells

Continue the Discussion

So do you use merged cells in your spreadsheets? If so, do they cause any of the complications mentioned above? How do you get around these issues? Continue the discussion and add your thoughts in the comments section at the bottom of this article.

Please Share

If you liked this article or know someone who could benefit from this information, please feel free to share it with your friends and colleagues and spread the word on Facebook, Twitter and/or Linkedin.

Some related posts you might also be interested in:

D.A.R.E to be different What are the 56 ColorIndex colors in Excel Navigate your worksheets with ease Declutter your Spreadsheets by Hiding Zero Values