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
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
- In the Horizontal setting, click the arrow, and select Center Across Selection
- 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.
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.
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.
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