I have been as guilty as many of you when it comes to using the merge utility in a spreadsheet. However, more and more you will read that merging cells is bad for your health. The arguments include the phrase that merging is the work of Satan and others say that once you have merged, it is not obvious which cell has been addressed: that is, if you have merged cells A1, B1 and C1, should you address cell A1 or B1 or C1 in any formula from now on?

When merging cells where each or all cells contain information, the spreadsheet will tell you that merging cells will mean that only the information in the upper leftmost cell in the range will be retained. THAT can be a problem and pressing OK far too quickly must have caught us all out at some time or another! If you are too hasty you could lose a lot of information or cause yourself a lot of extra typing.

Here are the steps you need to take to use Excel’s Centre Across Selection: it LOOKS as if the cells are merged but they are not! What follows is a table of data with a title that initially is left justified in cell A1. What we want to do is to show the titled centred across the whole table but NOT merged!

We have already selected cells A1:F1 ready to do what we want.

one

The Home tab on the ribbon has the Alignment section that we need to use. Click on the icon at the bottom right of the Alignment area:

two

We now need to make our selection in the Text Alignment … Horizontal drop down box where it currently says General:

three

Click on the arrow to open the drop down menu and select Centre Across Selection then click OK

four

And here is your title looking just how you want it!

five

The text is contained in cell A1 and all other cells in your selection, B1:F1 are still available if you want to make changes. However, if you do type in any of the other cells, your results will NOT be so attractive or even predictable. Look at the final image, below, where we have added some information to cell D1:

six

We wanted everyone to know that this is Version 1 of this table. What has happened is that your centre across selection has been updated and changed from being A1:F1 to D1:F1 … not what you wanted but it all goes to show that creating centred titles is fraught with difficulties however you try to do it.

Centre Across Selection is a nice and easy function to work with and it is better than merging cells in just about every way despite its own shortcomings.

Duncan Williamson

ExcelMaster

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: