How to merge Excel files Click inside the file drop area to upload first Excel file or drag & drop one Excel file. Likewise, upload second Excel file or drag & drop it. Check 'Merge into one sheet' option if merging Excel files into one sheet. Combine multiple sheets or workbooks into one workbook After free installing Kutools for Excel, please do as below: 1. Activate Excel, click Kutools Plus Combine, a dialog pops out to remind you the workbooks you want to combine needed be closed. Oct 05, 2020 If you need to combine multiple sheets into one automatically on a custom schedule, or you just don’t want to deal with formulas, use the Google Sheets importer. It allows you to import a data range from multiple sheets and merge them together. Google Sheets importer is a part of Coupler.io, a product to import data from different data sources.
We can copy data from multiple worksheets into one by following the simple steps outlined below. In this tutorial, we will learn how to merge Excel sheets into one. We will also explore how to merge two Excel sheets, merge multiple sheets and use a VBA to combine sheets.
Figure 1 – How to merge excel documents
The Power Query tool sheets provide a quick and easy way to combine a lot of worksheets into one. When we have just a few sets of workbooks we want to merge, we can use these steps:
Figure 2 – Sheet 1 for merge table from different sheets
Figure 3 – Sheet 2 for merging excel sheets into one
Figure 4 – Sheet 3 for merging excel sheets into one
Figure 5 – How to merge spreadsheets in excel
Figure 6 – Merge two spreadsheets
Figure 7 – How to merge two Excel Sheets
Figure 8 – combine excel files
Figure 9 – merge excel documents
Figure 10 – Merge multiple excel files
Figure 11 – How to merge two Excel sheets
Figure 12 – Merge Excel files using Power Query
Figure 13 – Combine excel files into one
Figure 14 – How to combine multiple excel files into one worksheet.
When we have multiple Excel files, we can merge them in a swift manner using a VBA macro. We can easily use the macro displayed below.
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook
fnameList = Application.GetOpenFilename(FileFilter:='Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm', Title:='Choose Excel files to merge', MultiSelect:=True)
If (vbBoolean <> VarType(fnameList)) Then
If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbkCurBook = ActiveWorkbook
For Each fnameCurFile In fnameList
countFiles = countFiles + 1
Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox 'Processed ' & countFiles & ' files' & vbCrLf & 'Merged ' & countSheets & ' worksheets', Title:='Merge Excel files'
MsgBox 'No files selected', Title:='Merge Excel files'
Figure 15 – How to use a macro to combine excel files
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
One of the common headaches I have come across in the course of my career as a Trainer in Excel Dashboardsis the question of how to combine data from multiple worksheets into one master worksheet, so that you can analyze smartly.
Many general users of Excel retire to the time consuming method of copy and pasting, which works, if you have few worksheets.
It doesn’t have to be that way!
Learn at least 3 ways at your disposal to combine multiple worksheets.
Consider the following scenarios that can necessitate combining various worksheets include:
Please note that the challenge at hand is not to perform a lookup of additional columns that are stored in different mapping tables. Rather, we would want to have an appended data set (that is, additional rows).
This is a 3 partseries that will equip you with all that you need to combine data coming from multiple worksheets or external sources into a single worksheet. You can use the links below to jump to your preferred starting point.
[ctt template=”3″ link=”434Ib” via=”no” ]Learn 3 ways to combine data from multiple worksheets: Use Power Query, Use consolidate feature, Use a macro (VBA) #DataCleansing[/ctt]
Power Query is a powerful way to combine data from multiple sheets into a master list for further analysis. It enhances Business Intelligence for Excel by helping you extract, combine and shape data coming from a wide variety of sources.
With MS Excel 2010, Power Query is not inbuilt but comes as a free add-in that you download from the Microsoft website. For MS Excel 2013 Microsoft Office Professional Plus, the Power Query comes inbuilt but you need to enable it among the Add-ins.
Excel 2016 comes pre-installedwith Power Query. With this version of Excel, it has been named as Get & Transform.
If you want a deep dive into some of the “power” hidden in Power Query, Jon Acampora from Excel Campus has written a comprehensive article here.
In the example at hand, we have sales data for a fictitious company that operates in two regions: Nairobi and Mombasa. As seen in figure 2 below, the data is stored in 2 worksheets for each region.
In a third worksheet, the Finance Manager of the company seeks to analyze the data but first, he has to combine the data sets.
The method discussed in this post uses the Get & Transform feature in MS Excel 2016. The steps (similar to Excel 2013/2010) are as follows…
There’re more approaches to using Power Query to do this similar task, but we’re keeping this to the basics.
First, ensure the source worksheets are formatted as Excel tables -that is, click any cell inside the data, then press CTRL+T (or CTRL+L). Similarly, you can select a cell in the data, and from the Home tab, select Format as Table and choose your preferred style.
Having formatted the data as an Excel table, you will notice an extra contextual Design menu appears on the Excel Ribbon. From this menu, you can rename each of the tables to make it easy to work with.
We shall name the Mombasa and Nairobi tables as MombasaSalesand NairobiSalesrespectively.
From the Data menu, in the Get & Transform group, click From Table. If you’re using Excel 2010/2013 and you’ve enabled Power Query, go to the Power Query menu on the ribbon and locate “From Table”
Step 2 above will open up the Query Editor window. In this step we shall create a custom column that will be used to identify the dataset you have loaded. The custom column is added from the “Add Column” tab in the Query Editor.
If you need to do further manipulation on the data, you can take advantage of the rich features in the Query Editor.
For this example, adding the region column is all we needed to do.
The Close & Load To… option opens the Load To dialog box shown below. In this window, we will choose Only Create Connection. The “Table” option would load a duplicate table in your Excel worksheet. We do not want this!
Press Load to complete the process.
In this example, we shall repeat the steps above for the Mombasa sales data.
In step 3 though, the formula to create the custom column will be = “Mombasa”. The column heading need to be similar, i.e. Region.
As mentioned earlier, we’re using a basic approach. A better method would be to load all the tables in the workbook at once using the Excel.CurrentWorkbook() Power Query function. We shall look at this in another article as the steps will be totally different.
This is the step that helps us combine the individual worksheet datasets into a single worksheet.
Go to Data menu (or Power Query menu if working with earlier versions of Excel) and select New Query. In the short-cut menu, choose Combine Queries >> Append.
This opens the dialog box shown below. Choose the Three or more tables option.
Select each of the tables and press Add to move the table to the right side of the dialog box. Once through click OK.
When you press OK, Excel opens the Query Editor window to allow you make further adjustments.
On the Query Editor, we shall rename the newly created table from the default “Append1” to an appropriate name. In this example we have called this “Combined Sales”.
Next, press Close & Load To… option from the Home tab on the ribbon.
The dialog box below will pop up.
This time round, we shall load the combined dataset into a new Table, and in a New Worksheet.
When you press Load, Excel will create a new worksheet with the original datasets combined.
You can now go on and do your analysis on based on this consolidated worksheet.
One of the advantages of using the Power Query approach to data consolidation is that you can access the Query Editor and make adjustments that you may have omitted as you did the first process.
For instance, you may realize in this example that the sales date column that was loaded into the combined worksheet comes with the time component which you may not need.
To avoid displaying the time component, select a cell in the combined worksheet.
On the Excel ribbon, you will see a contextual Query menu. Click Edit.
This will open the Query Editor window. On the Query Editor, ensure you have selected the Combined Sales query and the Sales Date column is selected.
Select Transform menu >> Date >> Date only.
Having done this, press Close & Load from the Home menu of the Query Editor window.
The sales date column is updated accordingly.
Further, when you make changes on the original worksheets and click Refresh from the Data menu, the changes are immediately reflected on the combined worksheet.
There is a revolution in the self-service Business Intelligence that is coming as a result of the Power BI Suite (Power Query, Power Pivot, Power View, Power BI Desktop, Power BI Web version) provided by Microsoft. I personally believe these features are a must know for all Excel Analysts as well as anyone interested in making smarter business decisions.
There are a lot of resources to learn these tools and it pays off. Mynda Treacy’s article is such a resource that will solidify your understanding of the Append feature.
[ctt template=”3″ link=”6qKHT” via=”no” ]The beauty of Power Query is that once set, you only need to refresh and not repeat the process! #DataCleansing #ExcelDashboards[/ctt]
To me, the Power Query combined with VBA form powerful data shaping tools. I would choose Power Query any day!
The best way to learn is to practice yourself, so download the workbook used to for this post to test your understanding.
Share this with your friends!