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.

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