Data Import & Export

How to Combine Multiple Excel Files into One Workbook with Separate Sheets

Anum Mushtaq Oct 13, 2026 869 views

Question details

The user needs to combine multiple individual Excel files (approximately 20) into a single master workbook, ensuring that each original file is placed on its own separate worksheet tab.

Combine Multiple Excel Files into One Workbook with Separate Sheets

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing and consolidating multiple separate data files into one manageable master workbook without losing the individual sheet structures.
Observed behavior: The goal is to load each file into a distinct tab rather than appending all data into a single table or summarizing the values.

Before you start

Ensure all the Excel files you want to combine are saved in an easily accessible local folder and are not currently open in other applications.

Solution 1Recommended

Import Workbooks as Separate Tabs Using Get Data

Use Excel's built-in Power Query (Get Data) function to pull each individual workbook into a separate tab within a new master workbook.

Excel provides several ways to consolidate data. While the 'Consolidate' feature is excellent for summarizing numbers (using functions like Sum, Count, or Average), it does not import whole workbooks as individual tabs. To achieve separate tabs, the 'Get Data' process is the most reliable method.

Open a Master Workbook

Launch Excel and open a new blank workbook that will serve as your master file.

Navigate to Get Data

Click on the 'Data' tab in the ribbon, select 'Get Data', choose 'From File', and then click 'From Workbook'.

Select the Source File

In the file explorer window, locate and select the first Excel file you want to import, then click 'Import'.

Load the Worksheet

The Navigator window will appear. Select the specific worksheet you need from the left panel, and then click the 'Load' button at the bottom. This will create a new tab in your master workbook containing that data.

Repeat for Remaining Files

Repeat the Get Data process for each of the remaining 20 files to populate the master workbook with all the separate tabs.

Import Workbooks as Separate Tabs Using Get Data

Using Macros for Bulk Import: Importing 20 files manually via Get Data can be repetitive. Advanced users often utilize VBA macros to loop through a folder and automatically copy each file's sheets into the active workbook as separate tabs.

Efficient Data Management

Quickly Merge Multiple Excel Files with WPS Office

WPS Spreadsheet offers an incredibly intuitive interface and powerful built-in tools for handling multiple workbooks simultaneously. You can easily manage, import, and organize dozens of worksheets in a lightweight workspace.

1. Install WPS Office: Download and launch WPS Office, then create a new blank Spreadsheet.
2. Access Import Tools: Navigate to the 'Data' tab on the top ribbon and select the data import function.
3. Select Multiple Files: Choose the source files from your local directory that you wish to bring into the current spreadsheet.
4. Organize Tabs: Use the bottom sheet tabs to rename, arrange, and manage your newly imported worksheets within the single master file.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Lightweight architecture ensures smooth performance even with 20+ open workbooks.Familiar user interface requires no learning curve for seamless migration.Advanced document tabs make navigating between multiple files incredibly easy.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the Consolidate feature to put files on separate tabs?

No, Excel's Consolidate feature is specifically designed to summarize numerical data from multiple sheets into a single combined table. To keep files on separate tabs, you must use the Get Data method or manually copy the sheets.

Is there a faster way to import 20 files at once without a macro?

You can use 'Get Data > From File > From Folder', but this method typically appends all the data into one continuous master table. To maintain them as separate worksheet tabs without a VBA macro, importing them one by one is required.

Why does the imported data appear as a formatted table?

When using Power Query (Get Data), Excel automatically loads the imported information into a structured Excel Table. You can remove this formatting by clicking inside the table, going to the 'Table Design' tab, and clicking 'Convert to Range'.

Will the imported tabs automatically update if the original files are changed?

Yes. Because the Get Data method establishes a data connection to the source files, any changes made to the originals can be pulled into your master workbook by simply going to the Data tab and clicking 'Refresh All'.

Excel & Data Problems