How to Consolidate Multiple Excel Files into One? Complete Guide
This write-up will explain all the possible methods to consolidate data in Excel from multiple worksheets, including the simple Copy and Paste method, the built-in Power Query, Formula-Based Consolidation, and an automatic merger tool for multiple data files. All the methods with pros and cons are mentioned in the upcoming section one by one.
If you are also one of them who uses Excel regularly and has multiple sheets to manage monthly reports, department-wise data, or files created by different team members. And not able to manage them smoothly. Then you are not alone. There are a number of users who face the same situation daily and want to resolve it.
Most users think that the copy and paste technique is the easiest way to consolidate data in Excel from multiple worksheets. But they don’t know when they should use this technique. So, let’s start the process without wasting time.
How to Consolidate Data in Excel from Multiple Sheets?
Ok, so there are three manual techniques available, which allow users to consolidate multiple Excel files into one, but support limited data:
- Copy and Paste Method
- Built-In Power Query
- Formula-Based Consolidation
- Automatic Excel Consolidation Tool
Instant Way to Consolidate Multiple Excel Files into One
If you have multiple Excel spreadsheets that contain large amounts of data, then the SysTools Excel Merger Tool is the perfect way to consolidate data in Excel from multiple worksheets easily and smoothly. This software ensures data integrity, including header details, colors, font style, size, bold words, etc. Also, it maintains your data properly in columns and cells without messing up your information. Download the free trial edition to analyze the features and working steps:
Method 1: Copy and Paste Method
Copy and paste is the popular method to consolidate multiple Excel files into one, and many users prefer it. But it’s not true, yes, it is easy to use, but not in all situations. So, let’s learn the steps and then the pros and cons of this method.
Useful for:
- Very small data.
- One-time task.
- No formulas or hidden rows
Steps:
- Open a new Excel spreadsheet.
- And all your Excel files, whose data you want to consolidate.
- Now copy your data and paste it one by one into the new sheet.
- Repeat the same steps for each workbook one by one.
- Data goes into the wrong columns
- Formatting breaks
- High chances of duplicate entries
- Hidden rows and formulas don’t copy.
- Missing rows
- Duplicate headers
- High chance of manual error
- Have to repeat the same steps
- Can’t work with large data
This is the reality of the copy-paste method. If you have medium to large data, then go for the next method, which is in-built Power Query method.
Method 2: Consolidate Multiple Excel Files into One Power Query
Power Query is a built-in technique to consolidate data in Excel from multiple worksheets without having technical knowledge.
Use this method if:
- If all your worksheets have the same headers.
- Data volume should be medium to large.
- Best for clean and reliable data
Steps:
- Open a new Excel spreadsheet.
- Go to Data → Get Data → From Workbook options.
- Select the workbook which is containing multiple worksheets.
- Choose the Combine button.
- Now load the data into a new worksheet.
Advantage:
- No need to copy and paste data
- Smoothly handle a large dataset.
- Reduce human errors
- No coding required to consolidate multiple Excel files into one
- Basic knowledge of Power Query required.
- Doesn’t retain the original formatting.
- Does not work with hidden data.
Method 3: Formula-Based Consolidation (Advanced Users)
When to Use:
- Your sheets have the same headers.
- And you want total, merged values, or comparison.
Use this Formula (Excel 365):
=VSTACK(Sheet1!A1:D100, Sheet2!A1:D100, Sheet3!A1:D100)
- Formatting break if structure changes.
- Performance issue with large and multiple data files.
Also Read:
- How to Open Password Protected Excel File without Password?
- What to Do When Excel File is Too Large to Open?
- How to Break Excel File into Multiple Files?
Method 4: Best Way to Consolidate Multiple Excel Files into One
If you want to maintain the original data as it is, and don’t want to lose information and mess up data, then use the mentioned software. It is created by a well-known brand called SysTools to consolidate data in Excel from multiple worksheets. This software is two in one tool that allows you to merge and split large Excel files into smaller parts easily. This is the only software in the market that provides an end number of advanced features. Simple steps and capabilities are mentioned below for better knowledge:
How to Consolidate Data in Excel from Multiple Worksheets?
- Download and install the software and choose the “Merge Excel” option.
- Select your Excel files using the “Add Files/Folders” option.
- Locate your Excel sheets from your machine using the “Open” icon.
- Choose the “Next” button to consolidate multiple Excel files into one.
- After that, select the “Merge” Options according to you and choose a location.
- Select the “Merge” button to consolidate data in Excel from multiple worksheets.
- After completing the consolidation, choose the “OK” button.
Why Only SysTools Excel Consolidation Tool?
- There is no need to follow the same steps to consolidate data in Excel from multiple worksheets in bulk.
- This software maintains the hidden data from rows, sheets, and columns without losing any information.
- Also, there is no need to install Excel or any other application as a supportive tool.
- Maintaining header details is important, so SysTools Excel consolidation tool maintains the header information like color, font style and size, bold text, etc.
- There is no need of internet connection, it’s an offline software.
- You can easily create a master sheet from multiple sheets in Excel without modifying the original file data.
- It is 100% safe and secure, and doesn’t save your personal information.
Time to Say Goodbye
Handling multiple Excel spreadsheets is not an easy task. So, you can consolidate data in Excel from multiple worksheets using copy and paste, Power Query, and formulas. You can use these techniques according to the situation, because manual methods don’t work with all types of data. And if you have multiple and large data files, then simply go for the automatic solution. For any query, you can contact us through live chat support.