Merge Multiple Excel Sheets

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.

  1. Combine Worksheets Macro
  2. Merge Multiple Excel Sheets In One Workbook
  3. How To Merge Multiple Excel Sheets Into One

Figure 1 – How to merge excel documents

Combine Excel files into one using the Power Query tool

  • Many office men may need to merge multiple Excel Worksheets into a single master worksheet if they want to analyze or count the data quickly and easily. Sometimes they could merge the worksheets manually, but if there are numerous worksheets, merging sheets manually would be time-consuming.
  • If your data is in different workbooks or worksheets, you have two ways to get a pivot table from it; the first one gets all the data in a single sheet by copy-paste and then make a pivot table from it; another one is to use this feature of MS Excel wizard to make a pivot table from multiple sheets.
  • Feb 03, 2020 Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamically Posted on February 3, 2020 Combining multiple files from one folder is already supported built-in through the graphical interface of Power Query when use you Get Data from Folder.

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:

Understanding readexcel. The readexcel function is a feature packed pandas function. For this specific case, we can use the sheetname parameter to streamline the reading in of all the sheets in our Excel file. Most of the time, you will read in a specific sheet from an Excel file.

  • 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.

Merge multiple excel sheets into one workbook online, free

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

Multiple

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.

Introduction

One of the most commonly used pandas functions is read_excel. This short article shows how youcan read in all the tabs in an Excel workbook and combine them into a single pandas dataframe usingone command.

For those of you that want the TLDR, here is the command:

Read on for an explanation of when to use this and how it works.

Excel Worksheets

For the purposes of this example, we assume that the Excel workbook is structured like this:

The process I will describe works when:

  • The data is not duplicated across tabs (sheet1 is one full month and the subsequent sheets have only a single month’s worth of data)
  • The columns are all named the same
  • You wish to read in all tabs and combine them
Merge Multiple Excel Sheets

Understanding read_excel

Combine Worksheets Macro

The read_excel function is a feature packed pandas function. For this specificcase, we can use the sheet_name parameter to streamline the reading in of all the sheetsin our Excel file.

Most of the time, you will read in a specific sheet from an Excel file:

If you carefully look at the documentation, you may notice that if you use sheet_name=None,you can read in all the sheets in the workbook at one time. Let’s try it:

Pandas will read in all the sheets and return a collections.OrderedDict object. For the purposesof the readability of this article, I’m defining the full url and passing it to read_excel. In practice,you may decide to make this one command.

Merge Multiple Excel Sheets In One Workbook

Let’s inspect the resulting all_dfs:

If you want to access a single sheet as a dataframe:

account numbernameskuquantityunit priceext pricedate
0412290Jerde-HilpertS2-778964376.663296.382018-03-04 23:10:28
1383080Will LLCS1-936832890.862544.082018-03-05 05:11:49
2729833Koepp LtdS1-302481344.84582.922018-03-05 17:33:52
3424914White-TrantowS2-824233850.931935.342018-03-05 21:40:10
4672390Kuhn-GusikowskiS1-509613448.201638.802018-03-06 11:59:00

If we want to join all the individual dataframes into one single dataframe, use pd.concat:

In this case, we use ignore_index since the automatically generated indicesof Sheet1, Sheet2, etc. are not meaningful.

If your data meets the structure outlined above, this one liner will return a singlepandas dataframe that combines the data in each Excel worksheet:

Summary

This trick can be useful in the right circumstances. It also illustrates how muchpower there is in a pandas command that “just” reads in an Excel file. The full notebookis available on github if you would like to try it out for yourself.

How To Merge Multiple Excel Sheets Into One

Comments