Search Not Just Numbers

Tuesday 22 October 2013

Excel Tip: Centre titles without merging

Before we get into today's post, just a little reminder that the 20% discount offer on Mynda Treacy's Excel Dashboards course expires on Thursday. So if you've been thinking about it, now's the time to do something about it, if you want to save some money.

Today's post is a follow-up to an earlier post, "Do you really need to merge those cells?" where I highlighted the dangers of merging cells.

It became clear in some of the comments on forums that I had highlighted the problems but had been less than clear on alternatives.

This post is intended to address that.

Just to recap, the problems highlighted in that earlier post were:

  • 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.
Now, by far the most common reason cells tend to be merged is when we are looking to centre titles across multiple columns.

This is usually done be selecting the range of cells in the title row that we wish to centre the text across and clicking Merge & Center.

Fortunately, Excel provides a very simple alternative to Merge & Center, but instead of placing a button for it prominently on the Home ribbon (Merge & Center is slap-bang in the middle of the Home ribbon), you need to go into the Alignment tab of the Format Cells dialogue box.

You can access this by either clicking the little expansion arrow of the alignment section, just under Merge & Center, or by right-clicking the selected cells, selecting Format Cells and then the Alignment tab.

However you get there - once you are on the Alignment tab, the first drop-down box under text alignment is labelled Horizontal. There is an option on this drop-down of Center Across Selection. Visually, this does exactly the same as Merge & Center but crucially, it does not merge the cells.

It still leaves the content in the leftmost cell, but all of the other cells remain intact.

One point to note, however, is that this is only possible horizontally, i.e. across the cells, unlike Merge & Center which will work vertically too. However the horizontal centring is far more common and this approach provides one less reason to merge cells.


Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

4 comments:

  1. Glen

    Thanks for this and others have said before your site is very helpful and Excel is a never ending source of good things.
    Regards
    Keith

    ReplyDelete
  2. Awesome tip, love the emails!

    ReplyDelete
  3. I am using excel since 1992, on both Mac and windz, so I experience the goods and the bads, lately more bads ; I feel pain seeing Excel degrading more and more. Up to around excel 2003 if I am not wrong, Center Across Columns was very visible and Merger was a bit not that important; little by little MS hid CAC and brought in front Merge, which I hate; even if now you can do more things when have merged cells, but before you couldn't do inserting/deleting rows/columns, sorting etc. What I don't understand, what is the purpose of Merge, because if the cells merged have info, you would expect to merge, add, put together the info from all cells merge...but no, it takes only info from cell one. to center a title/header across more columns, you use Center Across Columns, so you don't lose any of excel functionalities. The only usage, logical usage for Merge if you need to center VERTICALLY across more cells, but even for that you can go around, select the middle cell in the given column and enter info there. MICROSOFT, bring back Center Across Columns, and bury the Merge function.

    ReplyDelete
  4. This tutorial is very helpful..
    http://tutorialway.com/how-to-centre-text-and-numbers/

    ReplyDelete