Taming merged cells

Spreadsheet full of merged cells? Here's how to avoid common issues caused by merged cells.

EQ

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!

Quick Tip

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.

Center Across Selection in the Format Cells dialog
Center Across Selection 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:

ActionShortcut
Center Across SelectionCtrl + 1
then A
then Tab
then C
then C
then Enter
then Enter
Keyboard shortcuts to Center Across Selection

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.

 

Bonus Tip

Please log in to see the bonus tip.

No login?
Get one for free.

Don’t have a login?

Get a login for free on our standard plan, and get a new tip each week.

Get started >>

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

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.

 

Expert Tip

Please log in to see the expert tip.

Don’t have a login? Get one.

Download files for this tip

Get Quicksheet

Quicksheet

Download example

Example file

No login?
Get one for free.

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?

Get one >>

For more information about publication schedules and access to tips, see our plans.

Share this tip