Microsoft’s Excel spreadsheet program is one of the most well-known on the market. One of the reasons it remains so popular is that there are so many things you can do with it. Merging and unmerging cells is a perfect example of the multi-functional nature of the software; but how do you do it?
Merging cells in Excel
Before we begin the process of merging and unmerging cells, let’s take a little look at what happens during.
The purpose of merging cells is to join one or more adjacent cells into a larger cell that fits across multiple columns or rows. The main reason for doing this is for presentation purposes. A sales report, with a series of columns featuring data, would be a perfect example. At the top is the header which is in one cell. To tidy up the report, merge all the cells in this top row and then centre the header.
Another example would be a list of contacts. Let’s say you have a list of contacts where one column is their first name and the next is their last name, but what you want is a single column that contains their full name in one. To achieve this, you would merge the two cells – first and last name to create one new cell.
How to merge cells in Excel
The most common way to merge cells in Excel is through the ‘merge and centre’ option found in the Home tab. The problem with this is it merges the cells, but not the text within them. So, if you have two columns with the first name in cell A1 and last name in B1, you will now have a new column, twice the width, but featuring only the text from A1 (the upper left cell). Excel does warn you of this this when you select this option.
If you want to merge the cells and keep the text from both, then you need to use the CONCATENATE Formula. In this, you would use the following formula and place it in the first cell of the column next to the two you wish to merge (let’s say it is C1 for this example):
C1: =CONCATENATE(A1,” “,B1)
It tells Excel to merge cells A1 and B1 with a space in between them.
If you don’t want a space between the text, you can leave out the two speech marks. You can create a formula that uses each of the cells, that you want to join, to form a single newly merged cell containing all the data. So, in the brackets, you would enter A1&B1, for example, to merge the first two cells on row 1.
How to unmerge cells in Excel
Like merging, there are different ways to unmerge cells, depending on what you want to do. If you use the simple ‘unmerge’ option on the Home tab, then you will see the first cell data return to its former place. However, the data from the second cell will be lost.
If you are unmerging cells that you have previously had in two columns, there is another option. It is where you split cells across contents of a spreadsheet. Find the cells you want to unmerge. Go to the ‘data’ option on the top menu and look for ‘text to columns’ to unmerge the cells across multiple cells. Select the cell, range or column to convert and choose the delimiter such as space.
Follow the remaining instructions to tell Excel how you want to show the data you are unmerging then check the data preview. Hit finish and Excel will do the work for you.
Conclusion
Excel’s options for ‘merge’ and ‘unmerge’ allow you to combine and separate data in a way that allows you to keep all the information while also getting the format you require. You do need to be aware of the merge option’s habit of removing some of the data, and make sure you use the right option to keep your data intact.
