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

Combine Excel files into one using the Power Query tool

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:

  • We will open all the worksheets we wish to combine

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

  • In the blank sheet, we will go to the Data Tab
  • Next, we will click on Get External Data or Get Data (depending on Excel version)

Figure 5 – How to merge spreadsheets in excel

  • In the drop-down list, we will go to FromOther Sources option. If we have Excel 2016, we will click on New Query and select Other Sources option.

Figure 6 – Merge two spreadsheets

  • Next, we will click on Blank Query to open the Power Query editor

Figure 7 – How to merge two Excel Sheets

  • In the Editor, we will enter the formula below in the formula bar:

=Excel.CurrentWorkbook()

Figure 8 – combine excel files

  • We will hit the Enter key to show all table names

Figure 9 – merge excel documents

  • To combine tables, we will click on the double pointed arrow in the content header cell

Figure 10 – Merge multiple excel files

  • We will select all the columns we want to combine
  • We will uncheck the Use original column name as prefix option

Figure 11 – How to merge two Excel sheets

  • We will click OK
  • Our worksheets will combine into a single table in the Power Query tab.
  • After combining Excel sheets in the Power Query, we can load it in Excel
    • We will click on the File table
    • We will click on Close and Load To

Figure 12 – Merge Excel files using Power Query

    • In the Import Data dialog box, we will select Table and New Worksheet option

Figure 13 – Combine excel files into one

    • We will click OK

Figure 14 – How to combine multiple excel files into one worksheet.

Using a macro to combine multiple Excel files into one

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.

  • We will click on Alt + F11 and select Visual Basic Editor
  • Next, we will right-click on This Workbook and select Insert, then Modules
  • In the window that opens up, we will enter the code displayed below

Sub MergeExcelFiles()

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

wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

Next

wbkSrcBook.Close SaveChanges:=False

Next

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

MsgBox 'Processed ' & countFiles & ' files' & vbCrLf & 'Merged ' & countSheets & ' worksheets', Title:='Merge Excel files'

End If

Else

MsgBox 'No files selected', Title:='Merge Excel files'

End If

End Sub

  • Now to run Macro, we will click on Alt +F8 to display Macro dialog
  • Next, we will select MergeExcelFiles and tap, Run

Figure 15 – How to use a macro to combine excel files

Instant Connection to an Excel Expert

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.

But

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:

Merge Multiple Excel Sheets Into One Sheet online, free

  • For a teacher, this would mean merging data for various streams of the same grade into one, to help in ranking.
  • You have received budgets from various business units and you want to have a consolidated position.
  • Vehicle sales data coming from different outlets needs to be consolidated into a single worksheet.
  • You have extracted 5 P&Ls from your financial reporting system and your task is to generate a summarized income statement from these individual profit centers.
  • The ministry of health would want to consolidate the number of patients served from various dispensaries from different counties.

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.

  • How to combine data using Power Query in Excel (the rest of this article discusses this method)
How do i combine multiple excel sheets into one

[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]

How to combine data from multiple worksheets using Power Query: Query Editor in Excel

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.

Power Query in MS Excel 2016

Excel 2016 comes pre-installedwith Power Query. With this version of Excel, it has been named as Get & Transform.

Merge All Sheets In One Sheet Excel Online

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.

Combine Data Using Power Query

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.

Step #1: Format the data as an Excel Table

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.

Step #2: Using Get & Transform

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”

How Do I Combine Multiple Excel Sheets Into One

Combine data from multiple sheets

Step #3: Create Custom Column in Query Editor

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.

Step #4: Next, press Close & Load To…

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.

Step #5: Repeat steps 2, 3 & 4 for each individual worksheet

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.

Step #6: Append the Queries

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.

Step #7: Rename Query and Close & Load To…

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.

Step #8: Bonus Step

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.

Merge multiple excel sheets into one sheet online, free

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.

Combine Multiple Excel Sheets Into One Sheet Online

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.

Combine Multiple Excel Sheets Into One Sheet online, free

[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!

Do it yourself

The best way to learn is to practice yourself, so download the workbook used to for this post to test your understanding.

Creating Multiple Sheets In Excel

Share this with your friends!

Coments are closed

Most Viewed Posts

  • 3rd Degree Burn
  • Google File Stream G Suite
  • My Account Amazon Prime
  • A Star Is Born Prime Video

Scroll to top