If you are also struggling in combining a set of data in Excel and looking for a solution to merge cells or columns in MS Excel without losing data, then you have stumbled upon the right place. I’ll demonstrate few handy ways to merge columns in excel row-by-row into one.
For purpose of illustration, we have taken a sample table with First name, Last Name, and Grade. We will be merging the ‘First Name’ and ‘Last Name’ to single column ‘Full Name’.
4 methods to merge cells in Microsoft Excel without any data loss
If you don’t like reading the entire article, you can watch this YouTube video
Method 1: Merge Columns In Excel Using Concatenation Formula
- Firstly, to Insert a new column ‘Full Name‘ select the desired column header (in our case it is column D),
- Right click on it and select ‘Insert‘ option. We will rename this column as per requirement, in our case it is ‘Full Name‘.
- Now we will use the concatenation formula: =CONCATENATE(B4,” “,C4) where B4 is the “4th row of B Column” and C4 is the “4th row of C Column”.
- This formula will add both cells (B4 and C4) separated by a <space>
- To apply the same formula to the entire column, just drag and drop the formula to the bottom of the column.
- To merge multiple columns in excel, you just need to add more column header names (cells) in the concatenation formula in the similar fashion as described. For example =CONCATENATE(B4,” “,C4, ” – “, E4) which will result as Brijesh Sharma – B+
- The new merged data column created is a result of formula iteration. If you remove the obsolete columns (First Name or Last Name), then the data in the merged column (Full Name column in our case) will be lost.
- In order to prevent this, select the full column by selecting the column header (Column D in our case) and press CTRL + C in Windows or Cmd + C on Mac to copy entire column.
- Now right click on the merged column and select ‘Paste Special’ option
- Choose ‘Values‘ option from the next dialog box. This operation will just convert the formula to the actual value set.
- Now you can remove the two parent columns (First Name and Last Name) which are obsolete now.
And Done! You learned one method to merge multiple cells in Microsoft Excel.
Also Read: 21 Best KPI Dashboard Templates for Microsoft Excel
Method 2: Merge Columns In Excel Using Notepad
This is a little bit faster way to merge data in excel than using concatenation formula. However, the previous method is used to merge any columns, no matter if there is any space or column in between. Merging columns using notepad requires both the merging columns to be placed adjacent to each other.
Follow these steps to merge columns in excel using notepad.
- Hold Shift and select both the parent column headers you need to merge (First Name and Last Name in our case).
- Press CTRL+C on Windows or Cmd + C on Mac to copy data in both columns.
- Now open Notepad or TextEdit on your desktop and hit CTRL+V.
- Now at any blank space, hit ‘Tab‘ key and copy the space created by this Tab operation. This operation is called copying of a Tab character. Alternatively, you can Press Tab and then hit CTRL+SHIFT+LeftArrow and then CTRL+X to copy a Tab character as well.
- Press CTRL + H to open ‘Replace‘ dialog box in Notepad or Fn + Cmd +F in TextEdit of Mac.
- In ‘Find What‘ field you have to paste the copied Tab character and just add your desired separator (space in our case) in the ‘Replace With‘ box. The separator can be space, a comma or any other symbol as per your requirement.
- Press CTRL + A / Cmd + A to copy entire new data in Notepad or TextEdit.
- Go to your spreadsheet and select the entire column and paste the newly merged data by pressing Ctrl + C or Cmd + C.
- You can now delete previous obsolete columns and it won’t affect the merged column.
Voila! You have successfully seen a better way to merge cells in excel. This method seems tedious, but you will literally fall in love with the beauty of this method once you try it!
Method 3: Shortcut For Merging Cells Using Flash Fill
Microsoft Excel has built-in learning and adaptive systems. It keeps a track of work you do in a spreadsheet and can automatically suggest you auto-fill for the subsequent data field. By using Excel Flash Fill (Enable it if you haven’t already), you can easily merge multiple columns in excel. This can also be considered as “shortcut for merging cells in excel” in some lay-man terms.
- Insert a new column in which you want to add the merged values to two columns.
- Start typing the merged data manually in the first cell of the new column.
- In our case, I wanted to club the ‘First Name’ and the ‘Last Name’ in the ‘Full Name’ column. So I did it in the first row (D3) of the Full Name column.
- Proceed to next cell and enter the data as required. By this time, you will see that Excel has understood what you intend to do and will suggest you an auto-fill. Hit Enter and your data from both the merging columns will be merged into one column.
- Delete the obsolete columns and you will be left with a single column with merged data in it.
If Flash Fill fails to suggest the matching pattern, then you can manually trigger it by pressing Ctrl + E or at Data -> Flash Fill.
This method is a lot simple since it doesn’t require any long copy-paste instruction set nor the use of any secondary application to carry out merging operation. Needless to say, you still have one more method to merge columns in excel and this time we’ll make use of third-party plugin.
Method 4: Merge Cells In Excel Using Third-Party Plugins
Alternatively, you can also use some third-party add-ons or plugins which can add a plenty of extra functions to your existing version of Microsoft Excel. I recommend “Kutools for Excel” which has various features to ease up your work and optimise productivity. You will be able to merge multiple cells in excel without losing data.
- Download “Kutools For Excel” from here.
- Install and add the plugin to your Microsoft Excel by following simple on-screen options.
- Open your spreadsheet, select the columns you wish to merge and click on “Kutools” tab present next to View option.
- Click on “Combine” option under Kutools tab
- Follow simple on-screen actions to justify your merged column location, separator symbol, and other short actions.
- Clicking Ok will instantly merge the selected cells and it is independent of parent cells.
- You can now delete the obsolete data and keep only the merged cells for better readability.
Kutools For Excel has a wide variety of capabilities which can help you save time while working with Excel.
Also Read: How To Sort Gmail Inbox By Sender, Subject, and Label
Final Words
That’s it! You now know 4 efficient ways to merge multiple columns in Microsoft Excel without losing data. Do let us know which method worked best for you.
