Do you need to Merge that?

//

Brian Krisanski

Do you need to Merge that?

There are a number of “basic errors users make in Excel” that result in their spreadsheet life being that much more challenging when it doesn’t necessarily need to.

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.

image - merge cells

IMPORTANT

  1. When cells are merged, the contents of only one cell (the upper-left cell) appear in the final merged cell block. The contents of all the other cells, that are being merged, are deleted.
  2. Excel treats the contents of the merged cell block as being contained in the top left cell of the merged block. For example, if you merged cells A1, A2, B1 and B2, Excel would contain the data in this merged block in cell A1. Important to know if you ever write a formula that involves a merged cell.

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:

D – Data – separate your raw data and DO NOT modify its layout

A – Analysis – separate your calculations and analysis

R – Report – create separate summary/presentation sheets in your workbooks

E – Evaluate – does your information make sense

Following this methodology, merged cells won’t be used in any of 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.

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

The Alignment Group Dialogue box can be launched in a number of ways

  1. Press CTRL+1; or
  2. Click the small arrow in the bottom right hand corner of the Alignment Group in the Ribbon Menu – see the image below; or
  3. Right click on your selected cells and select “Format Cells…”
Merge_02a
Merge_02

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.

image - Center Across Selection

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.