Gain a comprehensive understanding of how to use the Merge and Center feature in Excel, crucial for creating a well-organized worksheet with clear formatting of titles and data. Learn how to merge cells both horizontally and vertically, ensuring the content is aligned and formatted as desired for an easy-to-read presentation.
Key Insights
- The Merge and Center button, located on the Home tab in the Alignment section of the ribbon, allows users to merge a series of cells in a row, which can be particularly useful for centering titles that span the width of the data in the worksheet.
- The Merge and Center function merges selected cells into a single cell and centers the content within it.
- Besides Merge and Center, the feature offers additional options like Merge Across, which creates a merged cell but doesn't realign the text; Merge Cells, used for merging cells not running horizontally; and Unmerge Cells, which restores the merged cells to their individual pre-merged state.
- Merge Cells can be used to merge a block of cells both horizontally and vertically, and it also allows the adjustment of the text alignment within the merged cell.
- It's crucial to note that when cells are merged, only the content in the uppermost cell in the range is retained.
- The feature can be valuable for centering titles across cells they refer to or vertically alongside relevant data, enabling the creation of a single formatted cell with shading and borders, and alignment of the content within it as desired.
Should You Merge Cells?
Pros
- Visual centering across columns for headers.
- Quick way to create banner-style titles.
- Familiar pattern most users recognize.
Cons
- Breaks sorting and filtering on the merged range.
- Causes issues with copy/paste operations.
- Center Across Selection achieves same look without merging.
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. 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.