Magical merged cells

I don’t think merged cells get much love, nor do I think they deserve any. I much prefer to use centre across selection, but for some reason way back when, MS decided to put that at the bottom of the alignment drop down to ensure people pick the gross spreadsheet breaking Merge mess instead of the simple centre across selection. Oh well..

Anyway I just had a wb with external links that shouldn’t have. Find didn’t find any, no names, charts or objects – all the usual suspects for hard to remove names.

In this case Cells X10:AI10 were merged showing the year once across all 12 cells.

If I unmerge those 12 cells I get the year in X10 and the other 11 cells are links to cells in an external wb.

If I try and remerge them it deletes the links and leaves only the year.

How can you have a merged cell range that has multiple values/formulas?

Anyone else seen that? Anyone know how to do it?

This was an old spredasheet, so its not a 2007 issues

I wondered if it might have been some broken array formula? Any other ideas?



13 Responses to “Magical merged cells”

  1. jonpeltier Says:

    I’ve seen this once or twice, and I’ve never been able to recreate it. Not with the links, but with phantom contents of merged cells.


    I’ve never seen that. Doesn’t make any sense to me unless there was some issues when the first Merged cells came into Excel 4 or 5 way back when and Centered across selections might have been converted to merged like that (??).

    I can’t believe you figured out where the links were hiding – you must be some kinda GOD of Excel or something :-) …….. or damned lucky .. good work!


  3. Simon Says:

    Dick – neither! just good tools
    (XLAnalyst pro edition – due out soon)

  4. Simon Says:

    Good point on xl4 centred to merged
    I might have to fire up an old box and see, I think 5.0 is the oldest I have

  5. Nick Hebb Says:

    I’ve seen that problem before. It was years ago, but I don’t recall which version of Excel.

    +1 for Center Across Selection in lieu of Merge. I was annoyed that it isn’t available in the Commands for customizing toolbars. I had to create a macro.

  6. jonpeltier Says:

    Very early during the 2007 Beta, I suggeted that they put Center Across in the relevant dropdown on the main tab. The person processing he report asked why. I explained about the problems with merged cells, and how Center Across was a “nicely behaved” alternative. The response:

    “Oh. Well, it’s too late anyway.”

    You can bet that won’t get fixed in any later version, if they do’t even know what the issues are with merged cells.

  7. Rob Bruce Says:

    What spreadsheet software do you think Microsoft’s accounts department uses? If it’s Excel, don’t you think that they and the Excel development team ought to have a little chat about Excel use in the real world?

  8. Bob Phillips Says:

    Come on Rob, I’ll bet that they only use it as little more than a glorified calculator, like most of the other Excel users. It is because of this LCD that we get the half-baked ‘improvements’ we have seen.

    At the summit last year I was talking to a guy who was an Excel Help writer, and he did not know about multiple conditional tests, in SUMPRODUCT or array formulae. He thought COUNTIFS and SUMIFS was a first!

  9. Patrick O'Beirne Says:

    Could you send me that sheet with the linked cells so I could have a look?

  10. Harlan Grove Says:

    I guess all that user experience data MSFT has collected PROVED that merged cells were, just, way more popular than center across selection. A perfect marriage of convenient sampling bias and complete cluelessness.

  11. Harlan Grove Says:

    On a tangent, I wish there were an Excel command-line switch AND corresponding session setting that would act the same as Application.DisplayAlerts = False, so that when I do open files with external links Excel doesn’t ask me whether to update them and it doesn’t update them. Fine with me just to get a message in the status bar telling me there were external links. Better still if Excel (all the Office apps for that matter) would work like certain more advanced text editors and appended indicator characters after the filename in the document or application window caption, e.g., an asterisk if the file has been modified since last save, and for Excel a ? if it contains external links that haven’t been updated. Better still if users could select their own indicator characters. Unlikely to happen since it’d eat into the sQAT.

    Going on an even further tangent, I sure with there were a command-line switch to ALWAYS open workbooks in normal view rather than @#$%&*! page break preview.

  12. Harlan Grove Says:

    FWIW, see the following re Windows 7

    which contains this quote:

    ‘Several prominent Windows bloggers saw the list as Microsoft’s response to a groundswell of comments from testers — including those in a small, invitation-only group — that Microsoft was ignoring the feedback they’d provided about Windows 7.’

    Nice to know other Microsoft departments beside the Office development teams start off with a strong desire to ignore the pesky opinions of experienced users.

  13. kati Says:

    I’ve seen this in Excel 2003 – just paste the format from a merged cell over a couple of other cells and you have magically vanished the content.
    I try not to use merged cells, but it would be helpful if one could do the ‘center across’ thing in the vertical as well as in the horizontal direction.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: