Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Shortcut to Merge Cells.
Written by Allen Wyatt (last updated February 11, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Excel allows you to merge cells together in two ways: using the Merge and Center tool on the ribbon or toolbar, or by using the controls in the Alignment tab of the Format Cells dialog box. If you merge cells quite a bit, you might long for a keyboard shortcut that will merge whatever cells you've selected.
Unfortunately, Excel doesn't include such a shortcut. You can, however, create one using a macro. The following is a simple macro to merge whatever cells you've selected:
Sub MergeCells1() Selection.Merge End Sub
After you create the macro, you can assign it to a keyboard shortcut and you are set to go. If you instead want a macro that is a shortcut for the Merge and Center tool, then you can use the following:
Sub MergeCells2() With Selection .HorizontalAlignment = xlCenter .Merge End With End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12458) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Shortcut to Merge Cells.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When your macro is processing information in a worksheet, do you need to periodically make the contents of a cell bold? ...
Discover MoreIf you need to change fonts used in a lot of different workbooks, the task can be daunting, if you need to do it ...
Discover MoreExcel allows you to perform math using times as operands. If you subtract a later time from an earlier time, you should ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-02-13 07:15:30
Steve J
This message from the Ribbon merge button should be re-written to "This is a diabolical way of creating a title that spans multiple columns"
(see Figure 1 below)
How this button has been allowed to remain on the main ribbon is a mystery, in IMHO it should be replaced with a Centre Across Selection button & the merge facility relegated to the Alignment dropdown menu.
Listen to any MVP & they advise against using merge in most circumstances.
Figure 1. The worst Excel advice ever !
2023-02-12 10:29:19
J. Woolley
For more about Center Across Selection, see https://excelribbon.tips.net/T012183_Creating_a_Center_Across_Selection_Button.html
2023-02-11 14:47:46
Mark Watson
Please DON'T Merge cells. They make doing more advanced activities very difficult if not impossible. Instead use "Center Across Selection" on the Alignment tab of the Format Cells pop-up.
If I had a nickel for every time I had to undo Merges (usually Merge and Center) to be able to even simple things like Insert Column or Copy and Paste I'd be retired already ;-)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments