Geek Dashboard

How-To's, Smartphones, News and Reviews

  • Phone Finder
    Phone
    Finder
  • Compare Phones
    Compare
    Phones
  • Viwe All Phone Comparisons
    View All
    Comparisons
  • Home
  • News
  • Smartphones
    • Android
    • iOS
  • Computers
    • Windows
    • macOS
  • Internet
  • Reviews
  • Phone Finder
    • Brands
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!

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

Computer

How to Recover Deleted Files from Pen Drive

recover deleted files from pen drive
Computer

How to Reset Windows 10 Login Password If You Forgot It

how to reset windows 10 login password
Computer

How to Recover Unsaved, Deleted or Lost PSD Files on Mac

Different Ways to Recover PSD Files from Mac

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 *

Phone Finder

Brands

  • Samsung 43
  • Xiaomi 38
  • Nokia 33
  • Motorola 33
  • Huawei 28
  • Asus 25
  • Oppo 25
  • Apple 19
  • Meizu 18
  • Vivo 17
  • Microsoft 12
  • BlackBerry 12
  • OnePlus 9
  • Google 4
  • Acer 2
  • Realme 1

Latest Phones

  • Asus Zenfone 6 (2019) Asus Zenfone 6 (2019) ₹39,999.00
  • Samsung Galaxy A70 Samsung Galaxy A70 ₹28,500.00
Notification History Log for Android

Trending Topics

  1. JioBook: An Upcoming Low-Cost 4G Android Laptop from Jio for India

  2. Nubia Red Magic 6 Series Launched with 18GB RAM and 165Hz Display

  3. Realme GT 5G unveiled with Snapdragon 888 and 120Hz screen in China

  4. Redmi Note 10 Series Officially Launched Starting at Rs. 11,999 in India

  5. Samsung Galaxy A32 with 90Hz sAMOLED Display Launched in India

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

Geek Dashboard, a technology blog strives to produce high-quality tech for our readers. Here you will find the latest updates on trending tech news, unbiased product reviews, and how-to guides on various gadgets.

Got a Tip? Write In [email protected]

© 2012 - 2020 · 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