There are many Excel functions and formulas that are useful for a wide range of tasks. They can help you make your workflows easier and more efficient. We’ll introduce you to the most important ones here, and you’ll be on your way to being an Excel expert.
When dealing with data, you may often encounter situations in which you know exactly what you want, but don’t know how to tell the program. For example, do you have an extensive table and want to add headings for clarity, but are not sure how to proceed? Using the Excel spreadsheet program from the Microsoft 365 package, you can combine cells, merge columns, and join rows in just a few clicks.
$1 Domain Names
Register great TLDs for less than $1 for the first year.
Why wait? Grab your favorite domain name today!
How to merge Excel cells – three options
In the following steps, you use the menu items accessible via the “Merge & Center” symbol at the center of the Home tab:
Let’s imagine a notional list of monthly revenues for a major company and its branches for the following steps. The table below can be more clearly arranged by adding headings and merging cells.
Method 1: Merge and center
Let’s assume we want to add a heading with the company name in the row above the data and then combine all the cells together.
Select the cells, columns, or rows in which you want to insert the heading. You can combine both columns and rows. Click on the Home tab and then select the central button “Merge & Center.”
Now, the cells have been combined and the heading is automatically aligned in the middle of the new cell.
Bear in mind that only the content of the top left cell is kept and center-aligned when merging cells. Text in adjacent cells is automatically deleted.
Instead of using “Merge & Center” via the Home tab, you can also right-click within the selected cells. The “Merge & Center” symbol then becomes available in the context menu. It’s a good idea to only enter the content into a cell after you’ve combined the cells.
You’ll now combine the cells and automatically center the text.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!
Once you’ve combined all the desired cells, your table should look much clearer:
Method 2: Merge across
The “Merge Across” option can also be found in the Home tab. While this allows you to easily combine Excel cells, the function can only be applied across the same row. It can’t be used within the same column, for example.
Method 3: Merge cells
Excel’s “Merge Cells” function works like “Merge & Center” – without the addition of centering. Select the cells you want to combine and then click “Merge Cells.” The text in the original cell will not be automatically centered, and will remain left or right-justified.
If you want to split the cells again, select the cells to unmerge, and locate “Unmerge Cells” from “Merge & Center” using the right mouse button or selecting it from the Home tab.
The merged cells will be split. However, text contained in the original cells will not be recovered.
The “&” sign: combining cells by formula
Another way to combine cell content into a merged cell is to use the commercial “&” symbol. For instance, if you have separate streets, house numbers, and ZIP codes in a customer list and wish to combine them into merged Excel cells, without losing the content of one of the cells, you can simply apply the formula below:
=[cell reference]&[cell reference]&[cell reference]
Step 1: Follow the process above to merge the cells in which you’d like to add content:
Step 2: Move your mouse cursor to the first cell to be combined and enter the formula provided above in order to merge your cell contents in the desired Excel cell:
Step 3: The content is now combined in a merged cell, but it still looks untidy. Add a comma and space to your formula as follows:
=[cell reference]&” “&[cell reference]”,”&” “[cell reference]
The content will now appear clearer and structured by separators in the combined cell. Don’t forget to combine each element in the formula with the “&” sign:
Step 4: Now, you can automatically combine all the remaining content in the same way by applying the formula to the other rows using the fill box:
The CONCATENATE function allows you to combine cell content in a merged cell in a similar way to the “&” sign.
Microsoft 365 with IONOS!
Experience powerful Exchange email and the latest versions of your favorite Office apps including Word, Excel and PowerPoint on any device!