Excel Merge Sheets By Column

Combining data from many sheets into a single sheet? It’s something you’ll do all the time, and you can check out tutorials on how to accomplish this task when all the sheets have the same column order as well as when you need to skip certain sheets but combine others. But what about when you have different columns on each sheet? Jun 14, 2018 Here are the steps: Select Data, Get Data, From File, From Workbook: Load data from a file. Browse to the first workbook and click OK. In the Navigator dialog, choose the worksheet on the left. (Even if there is only one worksheet, you have to select it.) You will see the data on the. Merge and combine rows without losing data in Excel Excel only keeps the data in the upper-left most cell, if you apply 'Merge & Center' command (Home tab Merge & Center on the Alignment panel) to merge rows of data in Excel. Users have to use another method to merge multiple rows of data into one row without deleting data. Feb 25, 2021 And here's how you can accomplish the task: On your Excel ribbon, go to the Ablebits tab Merge group, and click the Combine Sheets button: Select all the worksheets you want to merge into one. If you'd like to combine just one table, not all data, hover over. Choose the columns you want to.

  1. Excel How To Merge Two Columns
  2. Excel Merge Sheets By Column Free
  3. Excel Merge Sheets By Column Excel
  4. Excel Merge Sheets By Column Chart
  5. Excel Merge Column Data

Excel How To Merge Two Columns

June 14, 2018 - by Bill Jelen

David from Florida asks today's question:

I have two workbooks. Both have the same data in column A, but the remaining columns are different. How can I merge those two workbooks?

I asked David if it is possible that one workbook has more records than the other. And the answer is Yes. I asked David if the key field only appears once in each file. The answer is also yes. Today, I will solve this with Power Query. The Power Query tools are found in Windows versions of Excel 2016+ in the Get & Transform section of the Data tab. If you have Windows versions of Excel 2010 or Excel 2013, you can download the Power Query add-in for those versions.

Here is David's workbook 1. It has Product and then three columns of data.

Here is David's workbook 2. It has Product Code and then other columns. In this example, there are extra products in workbook2, but the solutions will work if either workbook has extra columns.

Here are the steps:

  1. Select Data, Get Data, From File, From Workbook:

  2. Browse to the first workbook and click OK
  3. In the Navigator dialog, choose the worksheet on the left. (Even if there is only one worksheet, you have to select it.) You will see the data on the right.
  4. In the Navigator dialog, open the Load dropdown and choose Load To...
  5. Choose Only Create a Connection and press OK.
  6. Repeat steps 1-5 for the second workbook.

    If you've done both workbooks, you should see two connections on the Queries & Connections Panel on the right of your Excel screen.

    Continue with the steps to merge the workbooks:

  7. Data, Get Data, Combine Queries, Merge.

  8. From the top drop down in the Merge dialog, choose the first query.
  9. From the second drop down in the Merge dialog, choose the second query.
  10. Click on the Product heading in the top preview (this is the key field. Note you can multi-select two or more key fields by Ctrl + Clicking)
  11. Click on the Product Code heading in the second preview.
  12. Open the Join Type and choose Full Outer (All Rows From Both)

  13. Click OK. The data preview does not show the extra rows and only shows 'Table' repeatedly in the last column.

  14. Notice there is an 'Expand' icon in the heading for DavidTwo. Click that icon.
  15. Optional, but I always unselect 'Use Original Column Name As Prefix'. Click OK.

    The results are shown in this preview:

  16. In Power Query, use Home, Close & Load.

Here is the beautiful feature: if the underlying data in either workbook changes, you can click the Refresh icon to pull new data in to the results workbook.

Note

The icon for Refresh is usually hidden. Drag the left edge of the Queries & Connections pane to the left to reveal the icon.

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2216: Combine Two Workbooks Based on a Common Column.

Hey, welcome back to MrExcel netcast, I'm Bill Jelen. Today's question's from David, who was in my seminar in Melbourne, Florida, for the Space Coast Chapter of the IIA.

David has two different workbooks where Column A is in common between both of them. So, here's Workbook 1, here's Workbook 2-- both have product code. This one has items that the first one doesn't have, or vice versa, and David wants to combine all the columns. So, we have three columns here and four columns here. I put both of these in the same workbook, in case you're downloading the workbook to work along. Take each one of these, move it out to its own workbook and save it.

Alright, to combine these files, we're going to use Power Query. Power Query's built into Excel 2016. If you're in the Windows version of 10 or 13, you can go out to Microsoft and download Power Query. You can start from a new blank workbook with a blank worksheet. You're going to save this file-- Save as, you know, maybe Workbook, to show the results of combined files .xlsx. Alright? And what we're going to do is, we're going to do two queries. We're going to go to Data, Get Data, From File, From Workbook, and then we'll choose the first file. In a preview, select the sheet that has your data, and we don't have to do anything to this data. So just open the load box and choose Load To, Only Create Connection, click OK. Perfect. Now, we're going to repeat that for the second item-- Data, From File, From a Workbook, choose DavidTwo, choose the sheet name, and then open the load, Load To, Only Create a Connection. You'll see over here in this panel, we have both connections present. Alright.

Now the actual work-- Data, Get Data, Combine Queries, Merge, and then in the Merge dialog, choose DavidOne, DavidTwo, and this next step is completely unintuitive. You have to do this. Choose the column or columns in common-- so Product and Product. Alright. And then, be very careful here with the join type. I want all rows from both because one might have an extra row and I need to see that, and then we click OK. Alright. And here's the initial result. It doesn't look like it worked; it doesn't look like it added the extra items that were in file 2. And we have this column 5-- it's null now. I'm going to right click column 5 and say, Remove that column. So open this expand icon and uncheck this box for Use original column name as prefix, and BAM! it works. So the extra items that were in File 2, that aren't in File 1, do appear.

Alright. Now in today's file, it looks like this Product Code column is better than this Product column, because it has extra rows. But there might be a day in the future where Workbook 1 has things that Workbook 2 doesn't have. So I'm going to leave both of them there, and I'm not going to get rid of any nulls because, like, even though this row at the bottom appears to be completely null, there might be in the future a situation where we have a few nulls in here because something's missing. Alright? So, finally, Close & Load, and we have our sixteen rows.

Now, in the future, let's say that something changes. Alright, so we'll go back to one of those two files and I'll change the class for Apple to 99, and let's even insert something new and save this workbook. Alright. And then, if we want our merge file to update, come over here-- now, watch out, when you do this the first time, you can't see the Refresh icon-- you have to grab this bar and drag it over. And we will do Refresh, and 17 rows loaded, the watermelon appears, the Apple changes to 99-- it's a beautiful thing. Now, hey, do you wanna learn about Power Query? Buy this book by Ken Puls and Miguel Escobar, M is for (DATA) MONKEY. I'll get you up to speed.

Wrap-up today: David from Florida has two workbooks that he wants to combine; they both have the same fields in Column A, but the other columns are all different; one workbook might have extra items that are not in the other and David wants those; there's no duplicates in either file; we're going to use power query to solve this, so start in a new blank workbook on a blank worksheet; you're going to do three queries, first one-- Data, From File, Workbook, and then Load to only Created Connection; the same thing for the second workbook, and then Data, Get Data, Merge, select the two connections, select the column that's common in both--in my case, Product-- and then from the Join Type, you want to full join all from the File 1, all from File 2. And then the beautiful thing is if the underlying data changes, you can just refresh the query.

To download the workbook from today's video, visit the URL in the YouTube description.

Well, hey, I want like David for showing up for my seminar, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.

Download Excel File

To download the excel file: combine-based-on-common-column.xlsx

Power Query is an amazing tool in Excel.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

'Always press F4 when you read range or matrix in a function'

Summary:

Does merging rows and columns in Excel seems a tough task for you to perform? Read this tutorial to learn different ways to merge rows and columns in Excel.

Microsoft Excel is a very useful application and can be used for performing various tasks. This is the reason Excel provides various useful functions to make the task easy for the users.

One of the most common tasks that everyone needs performing now and then is merging rows and columns.

But the problem is that performing this is not an easy task and Excel does not provide any tool to do this.

This is quite complicated as merging rows and columns in some cases causes data loss.

Excel Merge Sheets By Column Free

As while trying to combine two or more rows in the worksheet by making use of the Merge & Center button (Home tab > Alignment group), you will start getting the error message:

“The selection contains multiple data values. Merging into one cell will keep the upper-left most data only.”

And if you click OK, merged cells would contain just the value of the top-left cell and as a result, entire other data will be removed.

So this is what leads you to Panic situation!!!

To get rid of this, today in this article I am sharing different ways to easily merge rows and columns in excel without losing any data.

Below check out the fixes on how to merge rows in Excel or how to merge columns in Excel.

To recover Excel data without any data loss, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

How To Merge Rows & Columns In Excel Without Losing Data?

There are different methods for combining row and columns text in Excel. Here check the ways one by one to merge data without losing it. First, check how to merge rows in Excel.

Part 1# How To Merge Rows in Excel

When it comes to merging the Excel rows there are two ways that allow you to merge rows data easily.

  1. Merge Excel rows using a formula
  2. Combine multiple rows using the Merge Cells add-in

1. How to Merge Multiple Rows using Excel Formulas

Excel provides various formulas that help you combine data from different rows. Possibly the easiest one is the CONCATENATE function. So here checks out some examples for concatenating numerous rows into one:

Excel Merge Sheets By ColumnMerge
  • Merge rows with spaces between data: For example =CONCATENATE(B1,” “,B2,” “,B3)
  • Combine rows without any space between the values: For example =CONCATENATE(A1,A2,A3)
  • Merge rows > separate the values with comma: For Example =CONCATENATE(A1,”, “,A2,”, “,A3)

Now check how the CONCATENATE formula works on the real data.

  • On the sheet choose an empty cell and type the formula into it. Type the formula as per the data rows
  • And copy the formula across entire other cells in the row.
  • Now, simply you are having several data rows merged into one row.

2. How to Combine Rows in Excel using the Merge Cells Add-in

The Merge Cells add-in is used for merging various types of cells in Excel. This allows you to merges the individual cells and also combines data from entire rows or columns.

Please Note: You need to download a merge cell add-ins for third-party sites available online. Search in Google for add-ins.

Follow the given steps to combine two or more rows in your table:

  1. Choose rows you are looking to merge > click on the Merge Cells icon.
  2. Now the merge cells dialog window opens with a table or range selected already. And in the upper part of the window, you can see the three basic things:
    • How you want to join cells– For combining rows of data > choose “column by column“.
    • How to separate merged values with – an array of standard separators is available to choose from > comma, space, semicolon, anda line break. So select the separator as per your desire.
    • Where you need to place the merged cells > either the top cell or bottom cell.
  1. Now check the lower part of the Windows to check if you need any additional options:
    • Clear the content of selected cells – Choose this if need data to remain in the merged cells only.
    • Merge all areas in the selectionThis option allows you to merge rows in two or more non-adjacent ranges.
    • Skip empty cells and Wrap text – Well, these are self-explanatory.
    • Lastly, Create a backup copy of the worksheetThis option is checked by default. It is just a precaution that keeps you on the safe side and prevents the risk of data loss.
  1. Click the Merge button > to check the result – possible the merged rows of data separated by line breaks.

So, these are the two ways that allow you to merge rows in Excel without any data loss. Now, check out the ways on how to combine two columns in Excel.

Part 2# How To Merge Columns In Excel

Here check out the 3 ways to merge data from several columns into one without using VBA macro.

  • Merge two columns using formulas
  • Combine columns data via NotePad
  • The fastest way to join multiple columns

1. Merge Two Columns using Excel Formulas

1. Into your table > insert a new column > in the column header place the mouse pointer > right-click the mouse > select Insert from the context menu. Name the newly added columns for eg. – “Full Name”

2. In the cell D2, write the formula: =CONCATENATE(B2,” “,C2). The B2 and C2 are the addresses of First Name and Last Name. And in the formula, the quotation marks “” is the separator that will be inserted between merged names any other symbol can be used as a separator e.g. a comma.

3. Just like this, join data from several cells into one by making use of any separator of your choice.

4. Simply, copy the formula to other cells of the Full Name column. If the First name or the Last name is deleted, then the corresponding data in the Full name Column will also be gone.

5. Next, try converting the formula to a value so that you can remove the unnecessary columns from the Excel worksheet. Choose entire cells with data in the merged column (choose the first cell in “Full Name” Column > press Ctrl +Shift + Arrow Down)

6. Now copy the contents of the columns to clipboard > right click on the cell in the same column (“Full Name”) > choose “Paste Special” context menu > choose “Values” radio button > click OK.

Excel Merge Sheets By Column Excel

7. Now remove “First Name” & “Last Name” columns that are not required. Click the column B header > press and hold Ctrl > click column C header.

8. After that make a right-click on any selected columns > select Delete from the context menu.

9. This is it, now you have successfully merged the names from 2 columns into one.

2. Combine columns Data via Notepad

This is another way that allows you to merge several columns. Here you don’t need any formulas. This is suitable for combining adjacent columns to make use of the same delimiter for all of them.

For Example: If looking for combining 2 columns with First Names and Last Names into one:

  1. Choose both columns you need to merge: Click B1 > press Shift + ArrrowRight for choosing C1 > then hit Ctrl + Shift + ArrowDown for choosing entire data cells with data in two columns.
  • And copy data to clipboard > open Notepad > insert data from the clipboard to the Notepad
  • Then copy tab character to clipboard > hit Tab right in Notepad > hit Ctrl + Shift + LeftArrow > press Ctrl + X.
  • After that Replace Tab characters in Notepad with the separator, you require.
  • Hit Ctrl + H for opening the “Replace” dialog box > paste the Tab character from the clipboard in Find what field > type the separator Space, comma etc in “Replace with” field. Hit the Replace All button > to close the dialog box press Cancel
  • Now select the entire text in the Notepad and copy it to Clipboard.
  • Then switch back to Excel worksheet (press Alt + Tab) > choose B1 cell and paste text from Clipboard to your table.
  • And rename column B to Full Name and remove the “Last name” column.

So, this is the second way that allows you to merge columns in Excel without any data loss.

3. Join Columns Using Merge Cells Add-in For Excel

This is the easiest and quickest way for combining data from numerous Excel columns into one. Just make use of the third party merge cells add-in for Excel.

And with the merge cells add-in you can merge data from many cells by using any separator you like (for example carriage return or line break). With this, you can join row by row, column by column, or merge data from the selected cell into one without any loss.

There are many third-party add-ins online sites that allow you to download the add-ins and merge the cells easily in just a few clicks.

Conclusion:

So this is all about merging rows and columns in Excel without any data loss.

Follow the given steps to combine text in rows and columns easily.

Hope the given different steps will allow you to perform the task easily in the rows and column. Here I have described different methods of merging rows and columns data in Excel without any data loss.

So make use of anyone that you find easy for you.

However if in case you come to face any issue or data loss situation in Excel then make use of the MS Excel Repair Tool. This is the best tool that allows you to repair and recover data from the corrupted, damaged Excel file.

Additionally, you can learn advanced Excel to become more productive and easily utilize Excel functions and formulas.

Excel Merge Sheets By Column Chart

5 Easy Methods To Merge Rows and Columns in Excel Without Losing Data

Excel Merge Column Data

Description
Merging Excel rows and column is a very important task but if facing problem then read this article to rows and columns in Excel without losing any data...
Author
Repair MS Excel Blog
Publisher Logo

Margret

Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.