Search Not Just Numbers

Tuesday 16 April 2013

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

6 comments:

  1. Great post
    I often use centre across selection to have cells appear as if they are merged without having the pitfalls of merging that you describe.

    This macro is in my personal macro workbook and on my Quick Access Toolbar.

    Sub WrapAcross()
    With Selection
    .HorizontalAlignment = xlCenterAcrossSelection
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    End Sub

    ReplyDelete
  2. Glen, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion

    ReplyDelete
  3. Dear Glen,
    Good Article. I am impressed with the title of your blog : "Not just numbers". I thought you should be good at number crunching. Then I observed that you are a Chartered Accountant too. As Chartered Accountants, we use spreadsheets extensively but rarely teach the tricks of using the spreadsheets.

    It will be nice if you share your expertise in how to link the data in spreadsheets imported from accounting packages and then update them dynamically with the changes made in the accounting packages. I export the financial statements from Tally accounting package to Excel and use them for proper presentation as per Companies Act. However, the problem arises whenever there are new entries inputted in the Tally. I cannot update the excel file and I have to use new excel file or update the existing file manually. Any solution please?

    Thanks and regards
    CA B Vijaya Kumar

    ReplyDelete
    Replies
    1. I find importing the data using ODBC is more effective than exporting and pasting into Excel, but the key is not to touch the layout of the data as it comes in, so it can easily be replaced with an updated version. As soon as you make changes to the data itself, you can no longer do this.

      If you import the data using ODBC links (I don't know the Tally package, but most packages allow ODBC access to the data).

      You can then create your reports in other sheets of the workbook to report from the data sheet that you pull the data into. All you then need to do when there is new data is to click Refresh on the data sheet, to pull the latest information in from the accounting package.

      I have written a few posts on this kind of thing in the past:

      http://www.notjustnumbers.co.uk/2012/01/it-aint-what-you-do-its-way-that-you-do.html

      Delete