Import Multiple Excel Files with Multiple Sheets in Excel

In this post, we look at how to import multiple Excel files that contain multiple sheets into one Excel table.

Importing multiple files from a folder is one of the most popular functions of Power Query. A common question I get is “what about if an Excel file had multiple sheets”.

In this post, we answer that question.

You can download the files used in the tutorial to follow along.

Watch the Video

In this scenario, we have a folder that contains four Excel workbooks. Each one with training data for months of the year.

Each workbook has six sheets. Only four of those sheets (London, Birmingham, York and Reading) contain data that we want imported to the Excel table.

Import Excel Files from a Folder

Let’s start by connecting to the folder that contains the Excel files that we need.

1. Click Data > Get Data > From File > From Folder.

2. Click the Browse button to locate the folder that contains the files. In the files provided the folder is named Data.

3. Click Transform Data on the next window which lists the contents of the folder.

The Power Query Editor opens and shows the four Excel workbooks.

There are columns displaying the file name, extension, date created and more. We only want the Content and Name columns.

4. Select the Content column, hold Ctrl and select the Name column so they are both selected. Right click and Remove Other Columns.

Create a Custom Column to Get the Sheets Data

We now need to extract the data on the sheets of each of the workbooks in that folder. For this, we will create a custom column and use a small bit of M code. Very exciting!!

1. Click Add Column > Custom Column.

2. In the Custom Column window, enter GetSheets for the column name.

3. Then enter the following formula.

=Excel.Workbook([Content],true)

Content is the name of the header that contains the objects (sheets and tables) of the workbook. True states to use headers.

4. Click Ok.

The additional column is added. This GetSheets column contains a table with information about the different objects of each workbook.

5. Click the double arrow button in the GetSheets column header to extract the columns from the table. Click Ok.

You can now see all of the sheets and tables from each workbook in the list. Information such as the object name, kind and if it is hidden or not is shown.

You can now filter out the sheets or tables that you do not want to use.

We will start by filtering out any tables, as we are only interested in the sheets, in this example.

6. Click the filter arrow for the GetSheets.Kind column and uncheck the Table box. Click Ok.

In this example, we also do not want all of the sheets. We want to exclude the Lists and Summary sheets from the data import.

7. Click the filter arrow for the GetSheets.Item column and uncheck the Lists and Summary boxes. Click Ok.

We can now remove any columns that we do not want. In this example, we will remove the Content, Name, Item, Kind and Hidden columns.

8. Click on the Content column, then hold Ctrl and click the other columns. Right click and Remove Columns.

The Name column is often useful to keep, but in this example we have the date on the sheets, so the Name column offers us nothing useful.

We are left with just the sheet name and data columns.

Extract the Sheets Data

We can now extract the data for the sheets of each workbook.

1. Click the double arrow button on the GetSheets.Data column.
2. We will extract all of the columns but uncheck the Use original column name as prefix box.

We now have all the data from those sheets and for each workbook.

To finish, we will do some final Power Query checks and changes.

3. Change the column header from GetSheets.Name to Venue.
4. Change the data types, Venue to Text, Course to Text, Date to Date and No of Delegates to Whole number.

5. Click Home > Close & Load list arrow > Close & Load To.
6. Choose where you want to load the data. In this example, I will load it to a Table on the Existing Worksheet in range A1.

This completes the mission of importing data from multiple sheets and from multiple Excel files.

We can now continue our analysis on this data easily with formulas, PivotTables and other Excel magic.