How to Merge and Split Cells in Excel? – The Step-By-Step Guide
Microsoft Excel is a spreadsheet program that analyzes and stores numerical data. It is used in Microsoft applications for systematically organizing and manipulating data. This data is divided into rows and columns, which, when combined, make cells.
From its list of various features, Excel offers an option to merge columns and rows to make data look more efficient and presentable. There are different techniques used to merge columns in Excel and to merge rows in Excel. In this blog, we will get to know these techniques. So, let’s start!
How to Merge Cells in Excel?
The merging of cells in Excel provides many advantages. These include better visual appearance, clear labels, centralized content, and a designed layout. The answer to how to merge two cells in Excel is simple. You can follow these steps to merge cells in Excel:
- Select the cells you want to merge. Multiple cells can be selected by clicking and dragging.
- Or you can use the “Shift” key. Press the “Shift” key and use the arrow keys to extend the selection.
- Then, right-click and choose “Format Cells” from the menu.
- In the dialog box, choose the “Alignment” tab.
- Check the “Merge Cells” box and click “OK”.
This is how two or more cells will be merged into a single cell. You can enter text or numbers into the merged cells and it will be displayed in one. Remember that the merged data can be deleted at once.
Output:
How to Split Cells in Excel?
Splitting cells makes working in Excel easier and more efficient. The cells are split to separate the contents of a single cell into two or more cells. It helps with the sorting, filtering, and formatting of data. You can follow these steps to split cells in Excel:
- Select the cells you want to split.
- Go to the “Data” tab in the Ribbon.
- Click on “Text to columns”.
- Then, in the “Text to columns” dialog box, select the type of data you are splitting.
- After selecting the type, click “Next”. You can select other options according to your data.
- Click “Next”.
- You can also choose the data format for the destination cells.
- Click “Finish”. The selected cell range will now be split.
Output:
Alternative Method for Splitting Text in Excel
Other than the “Text to columns” feature, you can use certain formulas to split text in Excel. These formulas include:
- =LEFT(text, number of characters)
- =RIGHT(text, number of characters)
- =MID(text, starting position, number of characters)
How to Merge Two Columns in Excel?
The technique of merging columns in Excel is used to make the spreadsheet look more presentable to the viewers. The following are the steps explaining how to merge two columns in Excel:
- Select the cells where you want to merge columns.
- Apply the formula =CONCATENATE(A1,B1).
- Then, press “Enter” to apply the formula.
- You may drag the fill handle down to apply the formula to the remaining cells if you want.
This process will replace the original two columns with the merged columns. You can also know basic and advanced formulas in Excel to get a better understanding of using this tool.
How to Split Columns in Excel?
Splitting columns makes the content easier to understand for the viewers in the Excel spreadsheet. Here is how to split columns in Excel using a few simple steps:
- Select the column that you want to split.
- Click on “Data” from the top menu bar.
- Then, click on the “Text to Columns” option.
- Choose the delimiter character (character sequence used to specify boundaries between different regions on the sheet).
- Click “Next”.
- You can preview the results in the “Data Preview” section.
- Click “Finish”.
The columns will be split into separate columns according to the selected settings. To learn more about the splitting and merging of cells, you can take an advanced Excel course.
Uses of Splitting and Merging Cells in Excel
Merging of cells is used when you want to combine the data while splitting of cells is used when you want to separate data. Both techniques are used to organize data and make it visually attractive.
The following are the common uses of merging cells in Excel:
- It helps create table headers as two or more cells can be merged to create one single cell.
- You can merge cells if you have relatable data in adjacent cells. This way all the data can be contained in one single cell.
- The merged cells can be used to create one single larger cell in which the title and subtitles can be written.
The following are the common uses of splitting cells in Excel:
- It can be used to divide the data into multiple cells to make it look more presentable instead of being stuffed into a single cell.
- It can help adjust the size of the cells, which helps the data in those cells to fit better.
- It is useful when you want to apply different formatting in the cells, like font size and color.
An Alternative to Merge Cells in Excel
Though merged cells help improve the appearance of your spreadsheet, they have some limitations when it comes to functionality. You may come across issues while using sort and filter, pivot tables, and formulas. So, instead of merging cells, you can use an alternative in Excel called the ‘Center Across Selection’.
Here are the steps to use the ‘Center Across Selection’ feature:
- First, you have to undo the merged cells. Select the merged area and go to the ‘Home’ tab. Here, in the ‘Alignment’ group select the drop-down for ‘Merge & Center’, and click on ‘Unmerge cells’.
- Now, select the unmerged cells in the spreadsheet and right-click to select ‘Format cells’ from the menu.
- A pop-up will appear. Select the ‘Alignment’ tab and click the drop-down below ‘Horizontal’. Then, select ‘Center Across Selection’ and click ‘OK’.
- This will give your cells the appearance of merged cells without merging them.
How to Unmerge Cells in Excel?
If you wish to unmerge cells, you can follow these simple steps:
- Select the merged cells and go to the ‘Home’ tab.
- In the ‘Alignment’ group, select the ‘Merge & Center’ dropdown arrow.
- Click on ‘Unmerge Cells’.
Output:
Conclusion
This blog covered how to merge cells in Excel, how to unmerge cells, and how to split cells in Excel. While merging helps create cells that span several columns and act as a label, splitting helps in filtering information based on specific criteria. These techniques are used for formatting and organizing the data in a spreadsheet, making data look more efficient and effective.
Did you find this blog informative? Let us know in the comments section below. Also, check out MS Excel interview questions to revise basic Excel concepts before appearing for an interview.
FAQs
Select the cells you want to merge. Go to the “Home” tab and select the “Alignment” group. Click the “Merge” button.
Alt+H+M+M is the shortcut key to merge cells. It will show the data in the center of the merged cells.
To Split Tables in Word: Select the row from which you want to split your table. Then, from the “Layout” tab, go to the “Merge” group and select “Split Table”.
To Merge Tables in Word: Select the tables you want to merge, right-click on one of the selected tables, and ‘Merge Tables’ in the context menu.
Alt+Shift+↓ (Down Arrow) is the shortcut key to split the table.
You can use the CONCATENATE function or ampersand operator (&) to merge names in Excel.
Select the columns you want to split. Click on ‘Data’ from the top menu bar and select the “Text to Columns” option. Choose a delimiter character and click on “Next”. Preview the results and click on “Finish”.
