Taming merged cells
Spreadsheet full of merged cells? Here's how to avoid common issues caused by merged cells.
This is a great tip that your future self will thank you for, whenever you use it!
The Quick Tip shows how to use Center Across Selection instead of Merge Cells.
The Bonus Tip helps you center, justify or distribute content within cells vertically.
With the Expert Tip you can find and un-merge all merged cells in the worksheet.
We hope you find these tips useful.
Excel Quicker!
Center Across Selection as an alternative to merged cells
It is often preferable to use Center Across Selection in stead of Merge Cells.
Merged cells may be convenient, but can cause issues with copying & pasting, importing data, conversion to and from tables or pivot tables, charting, and inserting or deleting rows or columns.
If you use Center Across Selection instead, it looks the same but behaves more nicely.
Center Across Selection is a horizontal alignment format that you apply to the first cell in a contiguous group.
Here’s how to quickly apply Center Across Selection formatting:
Center Across Selection using keyboard shortcuts
For some reason, Center Across Selection is hidden away in the alignment settings in the Format Cells dialog.
It does not have a shortcut button on the ribbon toolbar, nor a native keyboard shortcut.
This makes it difficult to access quickly with the keyboard, unless you add a custom button or macro.
Without modifying any settings, about the simplest way to apply this formatting with the keyboard is like this:
Action | Shortcut |
---|---|
Center Across Selection | Ctrl + 1 then A then Tab then C then C then Enter then Enter |
Don’t forget to first select the cells that you want your content centered across. The content should be in the first or leftmost of those cells.
As you can see, this is quite involved; not ideal at all!
What’s more, you can’t just add this command to the Quick Access Toolbar, as there is not a seperate item for it in the available commands list.
If you need a simpler shortcut, you could write a macro to apply this formatting, then put your macro in your personal macro workbook and assign a keyboard shortcut to it.
Hopefully one day we will see a version of Excel that includes a Center Across Selection button on the ribbon toolbar, with a keyboard shortcut assigned.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
View our plans to see what else you are missing, or join for free on our Standard plan.
Delayed schedule
This tip is on the Delayed Schedule. It works just like normal, but it’s old news.
To view the current Tip of the week, log in with an account that has access to our Advance Publication schedule.
Don’t have a login?
For more information about publication schedules and access to tips, see our plans.