Geek Dashboard

How-To's, Smartphones, News and Reviews

  • Home
  • News
  • Smartphones
    • Android
    • iOS
  • Computers
    • Windows
    • macOS
  • Internet
  • Reviews
You are at Home » Computer

How To Merge Columns in Microsoft Excel Without Data Loss

Last Updated on March 27, 2020 by Abhishek Babbar Leave a Comment

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

Page Contents

  • Method 1: Merge Columns In Excel Using Concatenation Formula
  • Method 2: Merge Columns In Excel Using Notepad
  • Method 3: Shortcut For Merging Cells Using Flash Fill
  • Method 4: Merge Cells In Excel Using Third-Party Plugins

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”. merge columns in excel using concatenate
  • 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.

merge columns in excel

  • 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.

merge multiple columns in excel

  • 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).

merge multiple columns in excel

  • 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.

merge cells in excel

  • 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.

merge data in excel using notepad

  • 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.

merge columns in excel using notepad

  • 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.

merge columns in excel with notepad

  • 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.

merge columns in excel

  • 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.

shortcut for merging cells in excel

  • 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.

merge columns in excel using kutools

  • Click on “Combine” option under Kutools tab

merge multiple columns in excel

  • Follow simple on-screen actions to justify your merged column location, separator symbol, and other short actions.

merge cells in excel

  • Clicking Ok will instantly merge the selected cells and it is independent of parent cells.

merge columns in excel

  • 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.

#Excel#macOS#merge columns in excel#Microsoft#Windows
Posted inComputer

Spread the Word!

Avatar for Abhishek Babbar

Abhishek Babbar

Abhishek is an electronics engineer who is loves drafting technology related articles. He is good at strategic planning and event management.

Related articles

News

MacBook Pro 16-inch (2021) with M1 Max Chipset Model to Feature ‘High Power Mode’ for Performance Boost

MacBook Pro M1 Max
News

Windows 7 Dies: Here’s All You Need To Know and How-To Upgrade to Windows 10 For Free

News

Asus TUF Dash F15 Gaming Laptop Launched at Rs. 1,39,990 in India

Comment Policy:

The comments section is aimed to help our readers in case of any questions or you can even appreciate us for our hard work. Every comment is strictly moderated before approving it.

Your name and comment will be visible to the public. Never share your personal information in the comments section.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Over 1,30,286+ Followers

Join to get latest updates from Geek Dashboard

Facebook Twitter Telegram Pinterest YouTube Instagram

Recently Published

  1. Tecno Spark Go (2023) Launched with MediaTek Helio A22 SoC and Dual Cameras in India

  2. Creating Social Media Visuals: 9 Tips, Tricks, and Tools

  3. Hum to Search: 5 Best Music Recognition Apps to Find a Song by Humming

  4. Fire-Boltt Talk Ultra With Bluetooth Calling and 123 Sport Modes Launched in India

  5. Wikolo Super App: Bringing Countless Benefits for Students

Geek Dashboard Placeholder

Download the apps and never miss a story from us

We put a lot effort and resources in writing our articles and we believe it is our responsibility to satisfy your tech hunger. We will keep you filled forever!

  • Get Geek Dashboard App from Google Play
  • Get Geek Dashboard App from Chrome Web Store
Geek Dashboard Logo

At Geek Dashboard, we are dedicated to bringing you the latest and greatest in technology news, reviews, and how-to guides. From smartphones to laptops, and everything in between, we've got you covered.

Got a Tip? Write In tip@geekdashboard.com

© 2012 - 2023 · Geek Dashboard, product of ikva eSolutions

Blog Advertise About Jobs Contact Privacy Policy Write For Us T&C Office Setup

No dogs were injured while working on this website because we love them