Center Headings Without Merging Cells

You know that merged cells are evil, and should be avoided at any cost. Those merged cells can make it almost impossible to do simple tasks on a worksheet, such as sorting or filtering. Merged cells can even make it difficult to select a range of cells – and that’s annoying, as you probably know!
But sometimes it’s tempting to merge cells, and I often see them used to centre headings across several columns. Sure, it might look pretty when you’re done, but those merged cells can come back to haunt you.
Instead, let’s create the multi-column headings without merging cells. It takes a couple of extra clicks, but it’s well worth the effort.

Enter the Heading Text

In this example, we’ll add “Qtr 01” and “Qtr 02” headings over the monthly columns.
First, type the headings in row 3, above the monthly headings.

  • Qtr 01 is in cell B3
  • Qtr 02 is in cell E3

centerheadings01

Center the First Heading

To center the Qtr 01 heading over the first 3 months:

  • Select cells B3:D3 – these are the cells where the heading should be centered.
  • Then, click the Alignment Settings button on the Ribbon,
    OR press Ctrl + 1, then click the Alignment tab

centerheadings02

  • In the Horizontal setting, click the arrow, and select Center Across Selection

centerheadings03

  • Click OK

NOTE: If you have a copy of my Contextures Excel Tools Add-in,there is a Center Across command on the CTX TOOLS tab.
centerheadings05

Centered But Not Merged

The heading is centered over the Jan, Feb and Mar columns. The cells aren’t merged though – I can still click on cell C3 individually.
And even though it looks like that’s the cell where the heading is, the formula bar shows that there’s nothing in that cell.
centerheadings06

Finish the Formatting

To finish the formatting:

  • Select cells B3:D3 and apply an outside border.
  • Then, with those cells still selected, click the Format Painter on the Home tab, to copy the formatting
  • Click on cell E3, to apply the formatting to the Qtr 02 heading cells.

centerheadings04

Add Grand Totals

To add SUM formulas in all the Total cells:

  • Select cells B5:H7 – the values and the blank cells for totals
  • On the Ribbon’s Home tab, click the AutoSum button

The Total cells are automatically filled with SUM formulas for the row and column totals.

Download the Sample File

To download the sample file, go to the Worksheet Tips page on my Contextures site, and look for the Download section.

Video: Center Without Merging

To see the steps for formatting the headings, and adding totals, please watch this short video tutorial.

Or, watch on YouTube: Center Headings Without Merging Cells in Excel
_________________