Can We Again Combine Already Combined Pages

If you lot ask people who piece of work with information, you will get to know that combining Excel files or merging workbooks is a part of their daily work.

Hold?

A elementary an example: Let's say you want to create a sales report and you take data of 4 different zones in four different files.

Now:

The very first affair you need to do is to combine those files in one single workbook and simply then yous can create your written report further.

The bespeak is: You lot have to have a method which you can use for merging these files. Say "Yes" in the comment section if you want to know the best method for this.

Today in this mail service, I'm going to share with you lot the best fashion to merge information from multiple Excel files into a SINGLE workbook.

But, here'due south the kicker.

This post will teach you lot something you need to acquire to utilize in the real world information trouble and then make sure to read the unabridged post.

The Best Possible Way for Combining Excel Files by Merging data into ONE Workbook - POWER QUERY

Ability Query is the best way to merge or combine data from multiple Excel files in a single file. You demand to store all the files in a single folder and and so use that folder to load data from those files into the power query editor. It likewise allows you to transform that data along with combining.

It works something similar this:

• Saving All the Files into a Single Folder
• Combining them using Ability Query
• Merging Data into a Single Tabular array

Make sure to download these sample file from here to follow along and check out this tutorial to acquire ability query.

Annotation: For combining data from different Excel files, your data should be structured in the same way. That means the number of columns and their gild should be the same.

To merge files, you can use the following steps:

1. First of all, extract all the files from the sample folder and save that folder at the desktop (or wherever you want to salvage information technology).
   
2. Now, the next matter is to open up a new Excel workbook and open up "POWER Query".
3. For this, become to Data Tab ➜ Go & Transform Data ➜ Get Data ➜ From File ➜ From Folder.
   
4. Here you need to locate the folder where you take files.
   
5. In the end, click OK, and in one case y'all click OK, you'll go a window listing all the file from the folder, just similar beneath.
   
6. At present, you need to combine data from these files and for this click on "Combine & Edit".
   
7. From hither, the next affair is to select the table in which you lot accept data in all the workbooks and yeah, you'll get a preview of this at the side of the window.
   
8. Once you select the tabular array, click OK. At this bespeak, you have merged data from all the files into your power query editor and, if you look closely you can see a new column with the name of the workbooks from which data is extracted.
   
9. So, correct-click on the column header and select "Supervene upon Values".
   
10. Hither in the "Value to Replace" enter the text ".xlsx" and leave "Supplant With" blank (hither idea is to remove the file extension from the name of the workbook).
    
11. After that, double click on the header and select "Rename" to enter a name for the cavalcade i.e. Zone
    .
12. At this point, your merged data is prepare and all you lot need is to load it into your new workbook. So, get to the Home Tab and click on the "Close & Load".
    

Now you have your combined information (from all the workbooks) into a single workbook.

This is the moment of JOY, write "Joy" in the annotate department if you love to apply "Ability Query for combining data from multiple files".

Important Indicate

In the above steps, we have used the tabular array name to combine data from all the files and add together all of it into a single workbook. Just not all fourth dimension you will have the aforementioned table name in all the Excel files and at that point, you can apply the worksheet name as a key to summarizing all that information.

Ane more than thing:

As I said, you tin can use a worksheet name to combine data with the power query but there are few more things which I want to share with you lot and y'all need to accept care of those. Ability Query is case sensitive, so when combining files make sure to accept the name of worksheets in all the workbooks in the same messages.

The next thing is, to have the same name for the column headers, simply here the kicker: The order of the columns doesn't matter. If column1 in the north.xlsx is column2 in the west.xlsx, Power Query will match it, but you have to have cavalcade names the same.

And so at present, while combining files using power query you can use the worksheet proper noun instead of the table name, and here you lot take "SalesData" as the worksheet name in all the files.

You select information technology and click on the "Combine & Edit" and follow all the steps which I have mentioned in the in a higher place method.

Why Ability Query is the Best Way to Merge Information into a Single File?

Yes, I'm a huge fan of Power Query and you'll be if you get to know this:

It's Real Fourth dimension:

In one case yous create a query which combines data from multiple workbooks into one, you don't need to do it again and over again.

Let's Say:

If you add together new files to that your binder, you just demand to refresh the tabular array,

or, refresh the query,

or, use the refresh button.

And the power query will update your table and you'll get all the data into the table from all the workbook you have in that binder.

It'southward Fast:

If combine files manually or even by any other method then it takes time for you to copy-paste data into a single table.

But power query does it in the background and you don't need to spend a 2nd actress.

Merge Data from Multiple Workbooks When you lot don't take the Same Proper name for Worksheets and data in Tables

This is the hard truth…

…that in some situations, you won't have the aforementioned name for worksheets and not all the data in tables all the time.

Now, what you lot should exist doing in that case?

Well…

…in this case, you lot must know how you tin combine data from all the files and I don't desire to miss to share with this matter with you.

...so without any farther ado, let's get started.

• First of all, open the "From Folder" dialog box to locate the folder where you lot accept all the files.
• Now in this dialog box, locate the folder and click OK.

• After that, click on the "Edit" to edit the tabular array.

• At this point, yous will accept a table like below in your power query editor.

• Next, select the first two columns of the table and click on the "Remove Other Columns" from the correct-click menu.

From here, nosotros need to add a custom column to fetch data from the worksheets of the workbooks.

• For this, become to Add Cavalcade Tab and click on the "Custom Column" push button. This will open the "Custom Cavalcade" dialog box.

• In the dialog box, enter =Excel.Workbook([Content]) and click OK.

…at this time you have a new column in the table but next, you need to extract data from information technology.

• Now, open the filter from that newly added custom column and click OK to expand all the data into the table.

• Hither you accept the newly expanded tabular array with some new columns.
• Now from this new tabular array, delete all the columns except tertiary and fourth.

• And so, open up the filter for the column "Custom.Information" to expand it and click OK.

The moment you lot click OK, y'all'll go all the data from all the files into a single table…

… you need to make some changes into it to get in PERFECT.

If y'all discover, all the heading of the column are into data itself...

...and so you need to add together the column headings.

• To practice this, you lot demand to double click on the header and add a name, or you tin can right click and select rename it.

The next you need to exclude the headings which you have in the data tabular array.

• Now open any column'south filter option and unselect the heading proper name which you have in the column information and click OK after that.

Now our information is prepare to load into the worksheet, so, become to the Home Tab and click on the close and load.

Congratulations! you have just combined information from the different workbooks (with different worksheets name and without any table).

This is as well important:

At this point, y'all have merged the information into one table.

But there's one thing you lot need to practice…

…and that'due south applying some formatting to it and making certain that it won't go away when you update your data.

Here's what you lot need to do…

• Beginning of all, select the column where you have dates (as it is formatted as number correct now) and format it as dates.
• Subsequently that, make all the columns wide equally per the data you have in them.
• Here you lot tin besides format amount and price every bit "Currency".

But the adjacent affair is to make this formatting ready.

• For this, become to "Design Tab", and open properties.
• Untick "Adjust Column" width and tick marking "Preserve Jail cell Formatting".
• Yes, that's it.

Now you have a query in your workbook which can combine data from multiple files...

...and merge it into a single workbook...

...even if the worksheet proper name is not the same or if y'all don't have tables.

And yep, you have also made the formatting fix. ?

In the end,

Every bit I said, Power QUERY is real and if y'all frequently apply to combine data from multiple files and then you must use this method…

…every bit information technology's a ONE-TIME setup.

The most important matter is yous when you utilize power query you can even clean the data from those files too.

I hope this tutorial will aid you to Go Improve at Excel. But now, you need to tell me one matter.

Which method do you utilise to combine data from multiple files?

Make certain to share your views with me in the comment section, I'd love to hear from yous. And please, don't forget to share this post with your friends, I am certain they volition capeesh it.

Yous must Read these Next

• Consolidate Information From Multiple Worksheets: This option can help y'all to combine data from multiple worksheets into a single 1...
• Unpivot Information using Ability Query: In this situation, you need to put some efforts and spend your precious fourth dimension to brand it re-usable...
• Create a Pivot from Multiple Files: In this mail, I'd like to show you lot a three steps process to create a pivot tabular array by using data from multiple...

Almost the Author

Puneet is using Excel since his college days. He helped thousands of people to sympathize the power of the spreadsheets and learn Microsoft Excel. Yous tin find him online, tweeting almost Excel, on a running track, or sometimes hiking up a mount.

turnerexion1958.blogspot.com

Source: https://excelchamps.com/blog/merge-excel-files-one-workbook/

Share this post