Image used with permission by copyright holder
Microsoft Excel is a powerful application, capable of acting as a full-featured spreadsheet and data analysis tool. While Excel is fully capable of some powerful data manipulation tasks, sometimes you need to do something simple like merge and unmerge cells. That essentially means placing the value of one into multiple cells or two cells into a single cell. That’s exactly what we’re going to cover in this how-to.
Check out our other articles for more Excel tips and tricks.
Using the Merge and Center command to merge cells
The easiest way to merge a single cell into multiple cells is using the Merge and Center command from the main menu. This command takes the value in the left-most cell in left-to-right languages or the right-most cell in right-to-left languages and places it into however many cells are selected. It’s particularly useful when you want a column header to cover multiple columns, such as in the sample spreadsheet where we would like the “Q1 Sales” title to refer to all of the first quarter months.
(This data might later be used to create a graph in Excel.)
Step 1: Select the cells you want to merge into and click on the Merge and Center command.
Mark Coppock/Digital Trends
Step 2: If you simply click on the Merge and Center button, then the “Q1 Sales” will be spread across the three cells and centered. Note that any data in the cells to the right will be overwritten.
Mark Coppock/Digital Trends
Step 3: You can also select Merge Across and the cells will be merged but the content will be moved to the left. The Merge Cells function will do the same in most cases.
Mark Coppock/Digital Trends
Using the Merge Cells command to unmerge cells in Microsoft Excel
It’s easy enough to unmerge cells that you’ve merged using the Merge Cells command.
Simply select the cells you wish to unmerge, and select Unmerge Cells from the drop-down menu.
Mark Coppock/Digital Trends
Using the Concatenate function to merge cells in Microsoft Excel
Sometimes we want to take two columns and merge them into one, keeping the information from both columns. In our sample spreadsheet, we’d like to take the first name and last name and combine them into a cell containing the full name.
This is easily done by using the Concatenate function. We create the formula in the cell where we want the full name to populate. The formula is in the format “=CONCATENATE(A2,” “,B2), where A2 refers to the left-most cell in the second row, B2 refers to the right-most cell in the second row, and the middle section indicates that we want the data separated by a space. Of course, the cell references will change as you move down your table. We could use any symbol we like here. Note that any data in the column where the data is to be merged will be overwritten.
Mark Coppock/Digital Trends
Using these steps you can easily merge, unmerge, and generally manipulate the layout of your spreadsheet. It has possibilities beyond a simple table, as you’ll discover with regular use.
Recording important data that you don’t want changing? Learn how to lock cells in Microsoft Excel and protect your data.
