Wondering how to merge cells in Excel? It is an easy thing to do which you can learn in no time. Here are some simple ways to do so.
For beginners using spreadsheets, Merged cells are one of the best features that they can use. In this article, we will dive deep into Merge cells in excel and how one can do it. Let us first know, what a Merged Cell is.
What is a Merged Cell
In Excel, a merged cell is a feature where you can combine two or more cells into one large cell. But one can only merge contagious cells that make a rectangular. The example below shows one single cell merged with two more in the range B2:C4
Why You Might Merge a Cell
Merging cells is one of the most common features and techniques that is used when a label or a tile is required for a group of columns, rows or cells.
Upon merging cells, only the formula or value in the top-left cell of the range is preserved and shown in the final merged cell while all other values are discarded.
The above example shows two merged cells in B2:C2 and D2:F2 that shows the category of information in the columns below. For example, the First and Last name columns are organized with a Name merged cell.
Why is the Merge Command Disabled?
On some occasions, you might see the Merge & Cell command is greyed out and you are not able to use it.
The two reasons for the command being unavailable can be:
- You are trying to merge cells in a protected sheet.
- You are trying to merge cells inside an Excel table.
Always remember that the cells inside an Excel table can not be merged and you cannot enable this.
In case the sheet is protected, all the other commands would be greyed out too and if you want to access them, you must unprotect the spreadsheet.
To do this, go to the Review tab and click the Unprotected Sheet command.
In case the sheet is protected with a password, you would have to enter it to unprotect it. Once done, you will be then able to merge cells inside the sheet.
Merged Cells Only Retain the Top Left Values
One important thing to note before you start merging cells is that if the cells consist of formulas and data, you will lose everything which is not in the upper left cell.
Upon giving the command, you will get a dialogue box which will say – Merging cells only keeps the upper-left value and discards other values.
To understand it better, here is an example of the same. In the above picture, merging cells B2:C4 which contain text data. Only the data from cell B2 remains in the resulting merged cells.
In case there is no information or data then there would be no warning box.
How To Merge Cells In Excel – Different Ways
This is an easy thing to do and you can do it in various ways.
Merge Cells with the Merge & Center Command in the Home Tab
This is one of the easiest ways to merge cells in Excel and here is how to do it.
- Select the cells you want to merge together.
- Go to the Home tab.
- Click on the Merge & Center command found in the Alignment section.
Merge Cells with the Alt Hotkey Shortcut
An easy way to access is to use the Home tab Merge & Center command with the help of the Alt key.
Simply press Alt H M C in sequence on your keyboard to use the Merge & Center command.
Merge Cells with the Format Cells Dialog Box
You can also merge cells from the Format Cells dialogue box which is a menu that consists of all formatting options and also includes merging cells.
Here is how you can do it. Open the Format Cells dialogue box a few different ways.
- Go to the Home tab and click on the small launch icon in the lower right corner of the Alignment section.
- Use the Ctrl + 1 keyboard shortcut.
- Right-click on the selected cells and choose Format Cells.
Go to the Alignment tab in the Format Cells menu, check the Merge cells option and click the OK button.
Merge Cells with Copy and Paste
Another quick and easy way to merge cells in your worksheet is with the help of copy and Paste.
In case you already have a merged sheet, you can copy-paste it to make more merged cells. They would be of the exact same dimensions.
Here is how you can do this: Copy a merged cell using Ctrl + C then paste it into a new location using Ctrl + V. Make sure the paste area doesn’t overlap an existing merged cell.
Merge Cells with the Quick Access Toolbar
This hack is for everybody who uses the Merge & Centre command a lot. In case of this, you can simply add it to the Quick Access Toolbar which would make it easy for you to use.
To do this, Go to the Home tab and right-click on the Merge & Center command. Choose the Add to Quick Access Toolbar from the options.
Now the command is added to your QAT which is always available to use.
The best part about this quick access command is that it also gets its own hotkey shortcut based on its position in the QAT. Press the Alt key, Excel will show you what key to press next in order to access the command.
In the above example, the Merge and Center command is the third command in the QAT so the hotkey shortcut is Alt 3.
Merge Cells with VBA
To merge cells in VBA is one of the simplest things to do. The below image shows how to do it. The code below will merge cells B2:C4.
Merging cells sure makes the work very easy to do. There are many ways one can use Merge & Centre command in their daily life. If you are someone who uses it thoroughly then you can use it in one way or the other in case of any trouble.
Maximize Your Online Business Potential for just ₹79/month on Lio. Annual plans start at just ₹799.