How to Merge Excel Files

• Facebook
• Twitter
• YouTube

Merging Multiple Files

Managing and analyzing large volumes of information is crucial in today’s data-driven world. Microsoft Excel, a powerful tool for data manipulation, offers several methods to merge files from multiple worksheets or workbooks into a single, cohesive document.

Whether you are a business analyst consolidating financial reports, a researcher combining datasets, or a project manager organizing task lists, knowing how to merge Excel files can significantly enhance your productivity and data management capabilities.

This tutorial explores five proven methods: simple copy and paste, moving or copying sheets, using Power Query, leveraging the Consolidate function, and utilizing the command prompt with CSV files. Each method caters to different needs, from manual data consolidation to more automated processes, allowing you to choose the best approach for your workflow.

By mastering these techniques, you can streamline your data management, reduce file clutter, and improve your ability to share and analyze information, making your work more efficient and effective. This guide provides clear, step-by-step instructions to help you confidently merge Excel files, regardless of the complexity of your data or your familiarity with Excel’s advanced features.

• Merging Multiple Files
  • Method 1: Copy and Paste Data
  • Method 2: Move or Copy Sheets
  • Method 3: Using Power Query
  • Method 4: Using the Consolidate Function
  • Method 5: Using Command Prompt with CSV Files

Method 1: Copy and Paste Data

This method consolidates data from multiple worksheets into a single workbook by manually copying and pasting.

Steps

1. Select Data: Open the source worksheet, select all the data, right-click, and choose “Copy” or press Ctrl+C.
2. Paste Data: Open the destination workbook, navigate to a new sheet, and paste the data by pressing Ctrl+V or using the paste button from the Home menu.

Method 2: Move or Copy Sheets

This method allows you to move or copy entire sheets from one workbook to another.

Steps

1. Open Workbooks: Open both the source and destination workbooks.
2. Select Sheets: In the source workbook, select the sheets you want to move or copy.
3. Move/Copy Sheets: Right-click on the selected sheets, choose “Move or Copy,” and select the destination workbook from the drop-down menu. Optionally, check “Create a copy” to retain the data in the source workbook.
4. Finalize: Click OK to complete the transfer.

Method 3: Using Power Query

Power Query enables merging multiple Excel files stored in a single folder into one workbook, making it easier to manipulate and analyze data.

Steps

1. Prepare Files: Ensure all files to be merged are in the same folder and structured similarly.
2. Load Data: In the destination workbook, go to the Data tab and select “Get Data” > “From File” > “From Folder.”
3. Select Folder: Choose the folder containing the files, then preview and combine the data using the “Combine and Load” option.
4. Template Selection: In the Combine Files dialog box, select a sample file and sheet to format the combined data.
5. Finalize: Verify the data and click OK to merge into a new sheet.

Method 4: Using the Consolidate Function

This method consolidates data from different worksheets into a single sheet using Excel’s Consolidate function.

Steps

1. Open Destination Workbook: Create a new workbook for the consolidated data.
2. Navigate to Consolidate: Go to Data > Data Tools > Consolidate.
3. Configure Consolidation: In the Consolidate dialog box, select “Sum” from the function drop-down menu.
4. Add References: Enter the cell ranges to consolidate by using the up arrow or the browse button, and click “Add.”
5. Dynamic Links: Optionally, check “Create links to source data” to make the consolidated data dynamic.
6. Finalize: Click OK to combine the data, with the ability to expand and collapse details using plus and minus buttons.

Method 5: Using Command Prompt with CSV Files

This method combines Excel files by converting them to CSV format, using the command prompt, and then reconverting them to Excel format.

Steps

1. Convert to CSV: Open each Excel file, go to File > Save As, select “CSV (Comma delimited)” from the save-as type, and save.
2. Open Command Prompt: Place the CSV files in the same folder and open a terminal or command prompt in that folder.
3. Execute Command: Use the command CD “folder path” to navigate to the folder, then use a command to merge the CSV files.
4. Combine Files: The merged CSV file appears in the folder. Open it and save it as an Excel file to convert it back to Excel.

Also read:

How to Use VLOOKUP to Compare Two Lists

How to Split Cells in Excel

Best Excel Formulas: Part 1

Please visit our free resources center for more high-quality guides and training.

Ready to dive deep into Excel? Click here for basic to advanced Excel courses with in-depth training modules.

Simon Sez IT has been teaching Excel and other business software for over ten years. You can access 200+ IT training courses for a low monthly fee.

Simon