Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Merge Excel Files? 5 Proven Ways

How to Merge Excel Files? 5 Proven Ways

  • Facebook
  • Twitter
  • YouTube

(Note: This guide on how to merge Excel files is suitable for all Excel versions including Office 365)

Imagine you are working in Excel. You have entered, stored, formatted, and performed operations or functions with a wide variety of data. You would have done the same for different data in different workbooks. But, there might come a situation where you would want all your files in one place as a single file. What would you do?

In such a case, Excel offers you the ability to combine data from multiple worksheets or workbooks into one single workbook.

In this article, you will learn how to merge Excel files into one single file. We’ll see how to merge files as separate worksheets and also how to merge data from different workbooks into a single worksheet. 

You’ll Learn:

  • Why Merge Excel Files?
  • How to Merge Excel Files as Separate Worksheets?
    • By Copying and Pasting the Data
    • By Copying and Moving Whole Sheets
  • How to Merge Data into One Sheet?
    • Using Power Query
    • Using the Consolidate Data Option
    • Converting Files to CSV

Related Reads:

How to Merge Cells in Excel? 3 Easy Ways

How to Unmerge Cells in Excel? 3 Best Methods

How to Save an Excel File? 4 Different Ways

Why Merge Excel Files?

There are a variety of reasons why you would want to merge Excel files. 

  • It is easy to look up, search through the data, and reference them when you combine multiple worksheets into a single file. 
  • Merging files also helps you to access data from a single place without having to search or locate it, thereby increasing the efficiency of your work.
  • Merging files makes them more presentable. Also, it facilitates the easy sharing of data. You will only have to send one file instead of multiple files.
  • Gives you a clutter-free way to store data. You would not have to store files under different names in folders. Also, merging files takes less space.

How to Merge Excel Files as Separate Worksheets

Merging Excel files is not as complicated as it seems. There are a few methods that we can use to easily merge them. Let us see how to merge Excel files from different workbooks into a single worksheet.

By Copying and Pasting the Data

This is one of the easiest and simple methods to understand and merge Excel files. Just by copying the data from the sheet in one book and pasting the data into another book, you can consolidate all the data into a common workbook. 

For example, consider we have a workbook called Book1 which has 5 sheets namely Sheet A, Sheet B, Sheet C, Sheet D, and Sheet E. Now, we want to copy the data from this sheet into another workbook, Book2.

  • To copy all the data, first, select all the data from the worksheet (say, Sheet A from Book1) either by pressing Ctrl+A or by clicking on the small triangle which intersects the row and column headers in the top left corner.
  • Right-click on the selected data and click Copy or press Ctrl+C.
  • Now, open the workbook Book2.
  • Navigate to a new sheet and press Ctrl+V or click on the Paste button from the Home menu. 
Merge Excel Files by Copying and Pasting
Merge Excel Files by Copying and Pasting
  • This pastes the data onto the new sheet in the new book. 

Now, we have copied and pasted data from one workbook to another. In the same way, you can copy and paste any number of worksheets into different workbooks and consolidate the data into a single Excel file. 

This method is simple and helps you combine a couple of books or sheets. But when there are more worksheets or workbooks, this can be a little inefficient and time-consuming. Also, the formatting might be a little disrupted when you copy data between worksheets.

By Copying and Moving Whole Sheets

This is another easy method that is similar to the copy-and-paste method. But, instead of copying the data, you will copy the sheets together and copy or move them to another workbook. 

  • First, open the workbook to which you want to merge all the files. This will act as the destination.
  • Then, open the source workbook from which you want to move/copy. In this case, let us copy sheets B, C, and D from Book1 to Book2.
  • To copy/move the books, first, select the sheets. You can either hold Ctrl and select non-adjacent worksheets or hold the Shift key to select adjacent sheets. Once you select the sheets, they will appear in bold.
  • Now, right-click and select Move or Copy.
Select Move or Copy
Select Move or Copy
  • This opens the Move or Copy dialog box. From the To book: dropdown, select the workbook to which you want to move the sheets. In this case, we will be moving the sheets to Book2.
  • In the Before sheet: section, select (move to end). This will move the copied sheet to the end. 
  • Additionally, check the checkbox for Create a copy, if you want to create a copy of the selected sheets onto a new workbook. This leaves the data in the source workbook intact. 
  • Click OK.
Select the book and click OK
Select the book and click OK

This will copy the selected worksheets onto a new workbook. That is, the selected sheets will be moved from Book1 to Book2. 

Merge Excel files by using Move or Copy method
Merge Excel files by using Move or Copy method

In the same way, you can copy/move multiple worksheets from one workbook to a destination workbook easily. 

Suggested Reads:

How to Insert Radio Buttons in Excel? A Step-by-Step Guide

How to Change Row Height in Excel? 5 Easy Ways

How to Split Cells Diagonally in Excel? 2 Easy Ways

How to Merge Data into One Sheet?

The above-mentioned methods help you to merge worksheets from different Excel workbooks. However, there might be some instances where you would want to merge Excel files into a single worksheet. In such cases, you can use the below-mentioned methods to arrive at the desired result. 

Using Power Query

Power Query is a very useful tool in Excel that helps perform a variety of operations. Using Power Query, you can merge Excel files into a single workbook and help alter the data with ease.

Prerequisite: When using this method, store all the files you want to combine in a single folder. Also, take precautions to make sure the data are structured and in the same format as each other. 

  • Now, open the Excel workbook in which you want to combine all the other workbooks.
  • Navigate to the Data tab. Under the Get&Transform Data section, click on the dropdown from Get Data. Click in From File and select From Folder. 
Select Folder from the Get Data option
Select Folder from the Get Data option
  • A dialog box opens. Select the folder which contains the files you want to merge and click OK.
  • This inserts all the data from the selected folder and shows you a preview of the selected files.
  • Click on the dropdown from Combine and select Combine & Load.
Click on Combine & Load
Click on Combine & Load
  • This in turn opens the Combine Files dialog box. 
  • From the Sample File dropdown, select any file and select any sheet under the Display Options Parameters. This file will act as a template based on which the data from other sheets will be formatted.
  • You can see the preview of the data in the corresponding sheet. Check the checkbox for Skip files with errors, if you want Power Query to ignore the errors.
  • Once you have verified the data, click OK.
Click OK
Click OK
  • After you click OK, the data from the workbooks are combined and reflected in a new sheet.  You can see that the data from Book1 and Book3 are combined and reflected in a new Excel sheet in the current workbook. 
Merge Excel files using Power Query
Merge Excel files using Power Query

Note: You can see that only Sheet A from Book1 is imported and combined with Sheet E from Book2. This method is better suited when you have to combine a single worksheet from different workbooks.

Using the Consolidate Data Option

This is another useful tool in Excel to combine data into a single worksheet from different workbooks. Using the Consolidate option in the Data section, you can combine multiple worksheets into one.

Remember: While using this method to merge files, make sure the files you want to merge are opened.

  • First, open a new worksheet in a new workbook. This will act as the destination where all the files will be merged.
  • Navigate to Data. Under the Data Tools section, click on the Consolidate button.
Select the Consolidate option
Select the Consolidate option
  • This opens the Consolidate dialog box. 
  • Since we will be adding data one after the other, select Sum from the Function: dropdown.
  • In the References textbox, enter the cell range you want to consolidate and click on Add. You can add data from different worksheets in different workbooks by using the up arrow or by clicking on the Browse button. Here, we have selected the data from Sheet A in Book1 and Sheet E in Book3.
  • Once you click on Add, the references will be added to the All references: tab. From here, you can remove any references you don’t want to consolidate using the Delete button.
  • If you want the consolidated data to be dynamic, i.e., the data in the destination changes when the source is changed, click on the checkbox for Create links to source data.
  • Finally, click OK.
Select Sum and choose reference
Select Sum and choose reference
  • This consolidates the data from the selected source references into a single sheet. You can use the [+] and [-] buttons on the left of the sheet to view the data. 
Merge Excel files using Consolidate option
Merge Excel files using Consolidate option

Converting Files to CSV

Another effective way to merge Excel files is by changing their format and then reverting to their original format. In this case, let us convert the Excel files Book1 and Book3 to CSV file format and then combine them.

  • The first step is to convert the two Excel files into CSV format. To do that, open the Excel file. Navigate to File and click on Save As.
  • Click on More Options. This opens the Save As dialog box. Select the storage location and rename the file. Under the Save as type: dropdown, select the CSV (Comma delimited).
  • Click on Save.
Change the Save As type
Change the Save As type
  • This saves the Excel files(.xlsx) in the .csv format.

The next step is to use the Command Prompt to merge files. 

  • While using this method, make sure you have placed the files you want to combine in the same folder.
  • Now, right-click in the empty space and select Open in Terminal.
Open in Terminal
Open in Terminal
  • By default, Windows opens Powershell Editor. Click on the small dropdown next to the new tab or press Ctrl + Shift + 2 to open the Command Prompt.
Select Command Prompt
Select Command Prompt
  • This opens the Command Prompt Editor. 
  • In the Command Prompt window, enter the command cd (space) the folder path in double quotes. In this case, the command will be cd “C:\Users\Adam\Documents\Merged CSV File”.
  • Press Enter.
Enter the Command
Enter the Command
  • This shows that any command you enter is specified to the current folder.
  • Enter the command copy *.csv mergedfile.csv in the prompt and press Enter. The “mergedfile” is a variable and it denotes the file name. You can change the file name by changing this section. 
  • Once the execution is complete, you can see the names of the workbooks combined in the Command Prompt. 
  • In the folder, you can see the combined CSV in the name of “mergedfile”.
The execution is complete
The execution is complete
  • Let us now convert the CSV file back to an Excel file. 
  • First, open the file in Excel.
  • Again, navigate to File and click on Save As.
  • Choose the location and rename the file. More importantly, change the file type back into Excel Workbook (.xlsx) from the Save as type dropdown and click on Save.
Merge Excel files using Command Prompt
Merge Excel files using Command Prompt

Also Read:

How to Add Page Numbers in Excel? 2 Easy with Options

How to Hide Formula Bar in Excel (and Show)? 3 Easy Ways

How to Find and Replace in Excel? A Step-by-Step Guide

Frequently Asked Questions

Why am I getting an error when I merge files using Power Query in Excel?

When merging files using Power Query, there are two prerequisites. First, make sure the files you want to merge are in the same folder. Second, make sure the data in all the worksheets have the same data sections, structure, and formatting. Sometimes, this may also be a cause for the error.

Why isn’t the command prompt working in Windows 11?

To open the Command Prompt in Windows 11, open any folder and right-click to open the options dialog box. In the pop-up, select the option Open in Terminal. By default, Microsoft opens Windows PowerShell. To open Command Prompt, click on the dropdown next to the new tab and select Command Prompt or press Ctrl + Shift + 2.  

How to merge Excel files as separate worksheets?

To merge the Excel files with the data in the corresponding worksheets, you can either copy and paste the data from one workbook to another in different worksheets. Another method is to select the worksheets, right-click on them, and select Move or Copy. You can then select the workbook you want to move to and move the worksheet with the data intact.

Closing Thoughts

Merging Excel files provides a sure way to access all the data from a single file, and proves to be greatly efficient when storing or sharing data.  

In this article, we saw how to merge files in Excel in 5 proven ways. You can either merge files with data in their corresponding worksheets, or you can consolidate all the data from different workbooks into a single worksheet. You can choose the method that suits your purpose the best. 

If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Sharpen & Blur a Photo Using Photoshop Elements 15

Managing CSS Rules in Dreamweaver CS6

How to Use Items in QuickBooks 2019 – Part 1 and 2

Using SkyDrive in Windows 8

How to Hide and Unhide Columns in Excel? (3 Easy Steps)

5 Best JIRA Alternatives

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)