Learn how to merge cells in Excel through real datasets.
Combining or merging cells is a vital formatting function in Excel that allows you to have a better-formatted spreadsheet. In the below tutorial you will learn how to merge cells in Excel in different ways.
- Why Should You Merge Cells?
- How to Merge Two Cells in Excel?
- Merge and Center Cells in Excel
- Merge Cells with Values
- Merge cells without losing data
- Why Do Cells Not Merge?
- How to Unmerge Cells
Why Should You Merge Cells?
We can merge cells both vertically and horizontally. Merging cells in Excel presents the data in a larger cell instead of multiple columns.
We merge cells when we give cells a label or a title. It is mainly for formatting and centering headings. While merging, Excel allows saving only the value or formula of the top-left cell of the range, discarding other values.
How to Merge Cells in Excel?
Merging is a way to convert two small cells into one new, big cell. Merging helps you organize information and create headers without resizing the cells. Let’s check out how to merge cells in Excel.
- Highlight the two adjacent cells you want to merge (Merge A1 and B1 in the example).
- Under the Home tab, click the “Merge” icon and select “Merge Cells.”
Done! You joined the cells.
Note: Only the data in the top-left cell is saved when you merge cells. Be sure to keep the information in the other cell, so it doesn’t get lost. If you have data in both A1 and B1, Excel won’t just delete it. This alert will appear –
If you worry that it may delete your data, click “Cancel” and copy/paste the information somewhere else before merging the cells. You need to have the information in the top-left cell; click “OK” to merge if all looks good.
Merge and Center Cells in Excel
You’re creating a report, and you want your titles to appear neatly in a better format. Merge and center the cells by following these steps.
- Highlight the cells you want to merge.
- Under the Home tab, click on the “Merge” icon and select “Merge & Center.”
- Your cells will merge, and the data will appear in the center.
If you decide to merge cells with content, you should remember that the cell merge only keeps the top left value and discards the rest of the values.
Merge Cells with Values
In the example below, merging all the cells will only keep the SUV text in the first upper left position.
Select all the text and apply Merge & Center. You will get this message.
If you are okay with the result, press OK.
Your cells are now merged.
Excel shortcuts for Windows
- Merge cells: ALT + H + M + M/Merge: ALT + H + M + A
- Cancel cells: ALT + H + M + U
- Merge & Center: ALT + H + M + C
Merge Cells without Losing Data
To merge cells without losing any information, use an ampersand (&) or the CONCATENATE function to combine two or more cells.
Here, I am creating a Copy for Instagram and want to combine the headline with the appropriate link.
- Choose the cell where you need to put the merged data. It should be separate from the cells you’re merging.
- Type = in the empty cell. Choose the first cell to merge.
- Type “to leave a space between the cell data.
- Type again before choosing the next cell. Repeat the process for all cells. An example formula is = B4&” “&F10.
- Press Enter. The example shows how the text in cells B4 and F10 merge.
Note – Check there is a space between “.” This ensures that when the two cells merge, they are separated by space.
Read MS Excel Tutorials
CONCATENATE or CONCAT
The CONCATENATE (or CONCAT) function is another way to combine two or more text strings.
CONCATENATE formula in =CONCATENATE(text1, text2, …).
Text1 is the first cell you need to merge and can be a text value, number, or cell reference. Text2 is the next element to bind to. You can combine up to 255 items or 8,192 characters for this feature.
- Highlight a new empty cell you want to merge the data into.
- In the formula bar, type =CONCATENATE(
- A formula builder will appear, allowing you to choose Text1, Text2, etc.
- Select the cells you want to merge.
- To include spaces between cell data, you must include “in the formula. [for e.g. =CONCATENATE(B2, ” “, F2, ” “, F5, ” “, B9, ” “, F11, ” “, B12)]
- Press Enter. The data from the selected cells will be combined into the CONCATENATE cell.
Must Explore – MS Excel Courses
Why Do Cells Not Merge?
If your formula isn’t set correctly, it will affect the merging of cells. You can follow below troubleshooting tips to ensure that your cells are merged in Excel.
- Make sure you’re not actively editing a cell you want to merge. The “Merge” icon will appear grayed out if you are in the middle of changes. Press “Enter” to stop editing or click another cell before clicking the cell you want to merge again to fix the problem.
- Verify that one of the cells is part of an Excel spreadsheet. The tables have a unique format that does not allow merging. You can tell you’re in a table if the “Graphic Layout” or “Format” tabs at the top of the workbook are green. The “Merge” icon should look grey.
- See permissions for sharing Excel workbooks. If you don’t have access, you won’t be able to make changes or merge cells.
How to Unmerge Cells
Maybe you got too excited trying out the new abilities and fused too many cells. Don’t panic. Just break down the cells by following these steps.
- Highlight the cells you want to merge.
- Go to the Home tab, click on the “Merge” icon and select “Unmerge cells.”
- Your cells will return to their original formatting, but the previous data will be lost.
I hope this article helped you understand how to merge cells in Excel.
Top Trending Articles in MS Excel:
Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel
What tool is used to merge cells?
Excel has the CONCATENATE function that precisely joins the contents of the indicated cells in a cell.
How do I join first and last names in Excel?
To combine first and last names, use the CONCATENATE function or the & operator. Important: In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced by the CONCAT function.
How to merge and center fast?
Shortcut to merge cells in Excel Enter ALT+O. Next, we will press ALT+M and we can select the option to combine and center.
How to join the content of two columns in Excel?
To combine columns in Excel Click the cell where you want to move the merged data. Type = Click on the first cell you want to merge. Write & Click on the second cell you want to merge. Press the Enter key.
How to separate names into columns?
To separate First Name and Last Name into two columns from Excel - Select the column you want to split and hit the Data > Split text into columns option. Press the option and the separator option will appear at the bottom of the list of names.