WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to combine excel files into one workbook

July 22, 2022
5.6K Views

Spreadsheet generally is used worldwide and because of their various functions and program, it makes work very easy for this generation of people and every type of financial model can be performed under excel supervision. But due to the several excel files, it creates a harder situation to access data and this process takes a lot of our time. So to save our precious time we must aware of the easiest ways by which we can merge and combine excel files into one. There are several ways to combine excel files.

In this tutorial, we will find ways to combine excel files into one.

NOTE: To merge files there are several steps, which are below.

1. How to Simply Merge Multiple Excel Files

WPS Spreadsheet's "Tools" menu allows you to easily merge numerous Excel files. Users may utilize this sophisticated capability to merge data from many files into a single, coherent document. Users may save substantial time and effort by utilizing this capability, which would otherwise be spent manually copying and pasting data. Here’s how users can simplify merging multiple excel files with just a few clicks:

Step 1: Open WPS Spreadsheet on your computer.

Step 2: Browse the excel sheet which contains your data.

WPS Spreadsheet file



Step 3: On the ribbon menu, go to the Tools tab and click on the “Split or Merge”

WPS Spreadsheet Tool



Step 4: A Document Split and Merge will window will pop-up, click on “Merge”

WPS Spreadsheet Merge



Step 5: Users can now simply click on “ Add more files” to add the documents they want to merge.

WPS Spreadsheet add more files



Step 6: Once the documents have been added to the process, users can then click on “Next”.

WPS Spreadsheet start merge



Step 7: Now, a preview will be displayed. This window will show what documents are being merged and their data. Users can also set the document name for their merged Excel file on this window. Click on “Merge” to start the process.

WPS Spreadsheet preview



Step 8: WPS Spreadsheet will notify once the merging is complete. Users can view their merged files.

WPS Spreadsheet merge file preview



2. How to Merge Excel Files Using Power Query

Using Power Query in WPS Spreadsheet, you can easily merge Excel files. Users may effortlessly merge data from various Excel files, even if they have different formats or structures, using Power Query. Here’s a step by step guide on how to do merge documents using Power Query:

Step 1: Open the Excel file on WPS Spreadsheet

Step 2: Go to the Data tab, located on the ribbon menu, and click on “Import Data”

WPS Spreadsheet data tab



Step 3: In the Import Data drop down menu, click on “Import Data…”

WPS Spreadsheet Import Data



Step 4: Users will be asked if they would like to continue with exporting data from 3rd party sources. Simply click “OK”

Data from external sources



Step 5: Using the Power Query on WPS Spreadsheet is a simple step by step process. The first step is click on “Select Data Source”

Select Data source



Step 6: Browse your computer to find the file that needs to be merged with the existing document

Browse for files


Step 7: Once the data source is selected, click on “Next”

Data source


Step 8: In this step, users can specify the columns they want to merge from the selected data source. The single arrow “>” is to select one column, whereas the double arrow “>>” is to select all the fields present in the data source. Click on “Next”

Select table and fields


Step 9: In this step, users can add advanced queries such as sorting the data in an ascending or descending order. Once done specifying your desired filters and sorting, click on “Next”

Data filtering and sorting


Step 10: Power query will then display a preview of the imported data. Users can go back if the results are not according to their or click on “Finish”.

Preview by power query


Step 11: Select the cell where you want your data to be imported.

Select cell to import data


Step 12: The data will be imported and displayed on your current Excel file

Merging excel files using Power Query


3. How to Split Combined Excel files

The Tools tab in WPS Spreadsheet makes it simple to split numerous Excel files. Large Excel files may be easily divided into smaller, more manageable ones, enhancing data organization and accessibility. Users can save time and effort by using the splitting option instead of manually removing and organizing data. Here’s how users can take advantage of this tool:

Step 1: Launch WPS Spreadsheet on your PC

Step 2: Open the excel file for the process

WPS Spreadsheet file


Step 3: In our example, we have an excel file that contains 3 sheets. We will split them in to 3 separate Excel files

Step 4: Navigate to the Tools tab on the ribbon menu and select the "Split or Merge" option.

WPS Spreadsheet Tool


Step 5: “Document Split and Merge” dialog will appear, click on “Split”

WPS Spreadsheet Split


Step 6: Select the Excel file you want to split and click on “Next”

Select files to split


Step 7: WPS Spreadsheet will show the details of the split document. In our example document, we have 3 separate sheets, therefore, we will have 3 separate documents after the split process. After reviewing the details, click on “Split” to start the process

WPS Spreadsheet split preview


Step 8: Once the process is complete, users can access their separate documents on their systems

WPS Spreadsheet split document


Frequently Asked Questions (FAQs)

Q1. Can I merge Excel files with different formats or structures?

Yes, you may use techniques such as copy and paste, Power Query, or third-party tools to merge Excel files with different formats or structures.

Q2. Can I merge Excel files on different sheets or tabs within a single file?

Yes, you may merge Excel files on separate sheets or tabs into a single file by copying and pasting the data or consolidating the information with formulae and functions.

Q3. Are there any limitations to merging Excel files?

Merging Excel files may have constraints such as compatibility concerns, data size restrictions, and the possible loss of formatting or formulae during the merging process. To guarantee accuracy and the intended outcomes, the combined data must be tested and reviewed.

Effortlessly Merge Excel Files

Managing multiple Excel files might be difficult. However, you may quickly combine Excel files and optimize your data management process using the approaches mentioned in this article. You can combine files with various formats or structures by using copy and paste, Power Query, or third-party tools. Additionally, many ways exist for combining Excel files on separate sheets or tabs into a single file. Remember the constraints, such as compatibility concerns and possibly formatting or formulas loss, and properly evaluate the merged data. You may simplify the merging process and gain new insights from your data by utilizing the correct technique, such as WPS Office and WPS Template. Download WPS Office to simplify Data Management, merge excel files Effortlessly with WPS Office


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.