Excel Combine Multiple Sheets Into One

You can select multiple non-adjacent worksheets with holding the Ctrl key and clicking the sheet tabs one by one. For selecting multiple adjacent worksheets, please click on the first sheet tab, hold the Shift key, and then click the last sheet tab to select them all.

Introduction

Excel Combine Data From Multiple Sheets

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

  1. 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.
  2. To summarize and report results from separate worksheets, you can consolidate data from each sheet into a master worksheet. The sheets can be in the same workbook as the master worksheet, or in other workbooks. When you consolidate data, you assemble data so that you can more easily update and aggregate as necessary.
  3. Combining all Sheets from all Open Workbooks to a single Worksheet in a New Workbook The procedure below combines the information from all the sheets in all open workbooks into a single worksheet in a new workbook that is created. The information from each sheet is pasted into the destination sheet at the last occupied row on the worksheet.
  4. Combine Data from Multiple Worksheets Using Power Query. Click the drop-down and hover the cursor on Text Filters. Click on the Contains option. In the Filter Rows dialog box, enter Data in the field next to the ‘contains’ option.

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

Excel Combine Multiple Worksheets Into One Workbook

Can you combine multiple excel sheets into one

The process I will describe works when:

Excel combine multiple sheets into one pivot
  • 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

Understanding read_excel

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:

Combine worksheets into one tab

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.

Excel Combine Multiple Sheets Into One Sheet

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
Excel Combine Multiple Sheets Into One

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.

Comments