Merging and Centering Tiles

Merging and Centering Titles

Merge and Center is a very handy button, found on the Home tab, in the Alignment section of the ribbon.

It’s used mainly for merging a series of cells in a row, typically the cell containing a title and the cells next to that that span the width of the data within that worksheet.

For example, on this worksheet, note that the Title, Sales Report, is centered in one long cell, spanning the cells in Row 2 from column B through O.

Other cells have been merged and centered here – the title of each of the 4 sections of the report are merged to span the columns in each section. Sales Rep spans cells B4 through D4, and Quarterly Totals spans cells E4 through I4. And so on, for the next 2 sections.

How did I do this?

Let’s switch to a version of the worksheet with no Merge & Center applied.

Now, let’s select the title in cell B2 and select all the cells next to it, out to column O.

When I click the Merge & Center button, that’s what happens – the cells are merged in a single cell and the contents in the left-most cell is centered within the new, long cell.

I’ll repeat that for the Sales Rep heading, and then for the remaining 3 headings in Row 4, each heading merged into a cell that spans the columns in that section.

Last, I’ll do it one more time for the Division Totals cell and adjacent cells in B18 through D18.

Now, you’ve probably noticed there’s a drop arrow to the right of the Merge & Center button, and that’s for the other options this feature offers.

We have, in addition to Merge & Center:

Merge Across – which creates the merged cell but doesn’t re-align the text in the left-most cell in the range

Merge Cells – which you’ll use to merge cells not running horizontally

And Unmerge Cells, which returns the merged cells to their individual, pre-merged state, the text in the left-most cell returned to its original alignment.

Going back to Merge Cells – let’s demonstrate that here in cells B18 through D23. This block could be merged into a single cell and the title – Division Totals – merged both horizontally and vertically.

First, let’s Unmerge it, and then select the range from B18 through D23.

Now, I’ll click Merge Cells, and the cell is one long cell spanning 3 columns and 6 rows. It’s also inherited the fill color from the first cell in the selected range.

Then, to re-align the text so it’s not looking odd there in the corner of the box, I’ll click the Middle Align and Center buttons, to place it both horizontally and vertically in the center of the merged cell.

An important note – when you merge cells, only the content in the upper-most cell in the range is kept. So if I were to try to merge, say, the Division titles in cells D19 through D22, only the word East would remain.

Luckily, Excel warns me of this if I select the range and click any of the Merge options, but to show you how it works, I’ll ignore the warning. So I’ll undo that, and restore the titles.

So – you can use this feature to center titles across the cells they refer to, or vertically alongside relevant data. This creates a single cell you can then format with shading and borders – and re-align the content within it as desired.