Merge Excel Worksheets Into One Workbook

 admin

Instead of doing it one at a time for each workbook, you can tweak the above VBA code to get specific sheets from multiple workbooks into the destination workbook. Below is the code that’s will copy and move a sheet (named “Summary”) from all the workbooks into the destination workbook (DBook.xlsx in this example). Easily combine multiple workbooks or specified sheets of workbooks to one workbook 1. Create a new workbook and click Kutools Plus Combine. Then a dialog pops to remind you that all combined workbooks. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into.

  1. Combine Multiple Excel Sheets Vba
  2. Merge Worksheets From Different Workbooks
  3. Merge Excel Spreadsheets Into One Workbook
  4. Combine Excel Worksheets Into One Workbook
  5. How To Combine Excel Workbooks

Last updated on August 8th, 2020 at 08:09 pm

Reading Time: 7minutes

Excel Merger is a free tool for combining multiple Microsoft Excel (XLS/XLSX) files into one file. You can also use it to merge multiple Excel Spreadsheets and Workbooks into a single file or Workbook.

As if that’s not enough already, this tool merges columns and rows in an Excel worksheet. And just as important, it also supports the merging of ODS/CSV/XML files.

The software’s claim to fame is its ability to consolidate data in a single file or Workbook in a few sort clicks. As a result, Excel Merger allows you to quickly summarize and analyze data in a single sheet/workbook.

Benefits of Merging Excel Files into One

Regardless of your field, there are many reasons why you would want to merge multiple excel files into one sheet. For example, if you’re an analyst or manager, you would need to combine budgets and estimates from various departments. A student may need to combine assignments, analyses, research data, etc.

Workbook

In addition to making the information more presentable, excel merging helps you to create one single reference file. You won’t have to move back and forth between multiple documents just to find all the information you need. Instead, you consolidate all the useful data from more than one source and summarize it in one place.

Besides, the best merge Excel files tools allow you to use source data from different worksheets that are not Excel files. For instance, Excel Merger can import data from ODS/CSV/XML and consolidate it in one Excel file. That’s very important because, more often than not, it’s easier to manipulate data in Excel than in other formats.

Excel Merge: The Best Free Tool for Merging Excel Files

There are so many excel combine tools, but Excel Merger packs just enough features to edge them all. Below are all the good things that it brings to the table:

  • Free: Excel Merger is completely free to download and use. It’s a PC-based software that works on all versions of Windows from XP all through Windows 10. With a size of 3.3 MB, the file is small and lightweight enough for new and older computers.
  • Effective Excel combines tool: the software allows you to merge multiple Excel files into a single file in just 3 steps. Please read the step-by-step guide below to find out how you can do that.
  • Create one spreadsheet: in addition to creating one Excel file, this program can pick multiple spreadsheets from multiple Excel files and merge them into one spreadsheet in a single Excel file.
  • Merge workbooks and worksheets: if you have multiple workbooks in a file, you can use Excel Merge to combine them all in a single worksheet for easy analysis. Similarly, the software can merge workbooks from different files into one workbook in one file.
  • Sheet range: this function allows you to select a few worksheets from an Excel file and merge only them instead of merging the entire document. This is a handy feature for when you only need data from a select number of worksheets rather than the entire document. You can specify the range of worksheets yourself.
  • Merge rows and columns: you can pick particular rows and columns from many different files or spreadsheets and merge them in one spreadsheet. For example, you can pick row 2 from one document, row 14 from another document and row 27 from the final document and then combine them into a single row in one spreadsheet. In essence, Excel Merger consolidates data from any number of rows or columns into one row/column for easy analysis.
  • Import data from other file formats: Excel Merger is the best Excel editor because it can import data from ODS (Open Document Sheets) CSV (Comma-Separated Values) and XML (Extensible Markup Language) files.
  • Ultra-fast: merge files, spreadsheets and worksheets in just 3 steps. Excel Merger processes the data lightning-quickly to get the job done.
  • Doesn’t require Microsoft Excel or Microsoft Office: you don’t need to install Office or Excel to use Excel Merger.
  • Multi-lingual: supports English, Chinese, Dutch, French, German, Greek, Italian, Japanese, Korean, Polish, Portuguese, Russian, Spanish, and Swedish

How to Merge Excel Files, Spreadsheets and Workbooks with Excel Merger: A Step by Step Guide

First, download Excel Merger for Windows here.

Once installed you can perform any of the following Excel merging functions:

1. Merge Multiple Excel Files into One Excel File

How do I consolidate multiple Excel files? Below is a complete guide. Note that Excel Merger supports both XLS and XLSX type of files.

Step 1: Add files to Excel Merger

There are two ways of doing that. First you can drag and drop your files to the program’s interface. Secondly, you can use the “Add” button.

Merge Excel Worksheets Into One Workbook

Click on it and then browse your PC to where the files are located. Select all the ones that you want to merger and click “Open” to add them.

Step 2: Set options

Since you are merging files, select “Files” in the Merge dropdown menu.

Head over to the “Output” dropdown menu and choose your preferred output format. It’s XLSX by default but your options are XLS, XLSX, CSV, XML & ODS. Finally, set your output path under the “Save to” option.

Step 3: Merge

Click on “Start Merging Now” to consolidate your Excel files into one.

Excel Merger will automatically open the output folder once the merger is complete.

2. Merge Excel Spreadsheets and workbooks into one

How do you merge two excel spreadsheets together? And how do you merge two Excel workbooks together? Excel Merger can help. Better yet, it can merge more than two spreadsheets or workbooks. Here’s how:

Step 1: Add files to Excel Merger

You can either drag and drop Excel files or use the “Add” button.

Step 2: set options

Since you are merging sheets, select “Sheets” in the Merge dropdown menu.

Go to the “Output” dropdown menu and choose your preferred output format between XLS, XLSX, CSV, XML & ODS. Specify the worksheet range if you only want to merge a few worksheets instead of the entire file. Finally, set your output path under the “Save to” option.

Step 3: Merge

One

Click on “Start Merging Now”.

Excel Merger will combine all the workbooks in all the files that you added and produce one consolidated worksheet.

3. Merge Multiple Excel Files into One Excel Workbook

How do I consolidate multiple Excel files into one workbook? Below is a step-by-step guide. It will help you merge several Excel files into a single Excel workbook in one Excel file.

Step 1: Add files to Excel Merger

Drag and drop Excel files onto Excel Merger or use the “Add” button.

Step 2: set options

In the Merge dropdown menu, select “Files & Sheets”.

In the “Output” dropdown menu, choose your preferred output format. It could be XLS, XLSX, CSV, XML or ODS. Now, set your output path under the “Save to” option.

Step 3: Merge

Click on “Start Merging Now” to combine all the selected Excel files and workbooks into one workbook.in one file

4. Merge Excel Rows and Columns in a Worksheet

How do I combine columns in Excel? And how can I merge rows in a spreadsheet?

Step 1: Add files to Excel Merger

Drag and drop Excel files to the Excel Merger interface or use the “Add” button.

Combine Multiple Excel Sheets Vba

Step 2: set options

Click on the “Merge” dropdown menu and choose either “Rows” or “Columns”.

Excel

Needless to say, choose the latter if you’re merging multiple columns and the former if you’re merging multiple rows.

In the “Output” dropdown menu, select your preferred output format (XLS, XLSX, CSV, XML or ODS). Finally, set your output path under the “Save to” option.

Step 3: specify range

Head to the boxes labelled “Start” and “End” and then type in the first row/column and the last row/column (respectively) that you want to merge.

Remember to specify the delimiter. This is basically a separator for rows/columns. It’s blank by default but you can choose a sign/symbol of your liking.

Step 4: choose what to do with empty rows/columns

By default, Excel Merger will merge rows/columns in the first row/column of the new worksheet and leave the original rows/columns empty. But you can tell it what to do with those empty rows/columns.

Check the “Keep Empty Rows/Columns” option if you want to retain them or uncheck it if you want to remove them.

Step 5: Merge

Click on “Start Merging Now” to merge your rows/columns.

Merging Other Workbook File Formats

You can use the same procedures to merge files that are saved in other workbook formats besides Excel (XLS and XLSX). Specifically, Excel Merger allows you to merge CSV, XML and ODS files.

In the first step – when adding files – simply choose all the files that you want to merge, whether they are XLS, XLSX, CSV, XML or ODS files. Then follow the above procedures for merging files, worksheets, workbooks, rows and columns.

Quickly merge / combine worksheets / workbooks or csv files into one workbook in Excel

Kutools for Excel

When it comes to merge or combine multiple worksheets / workbooks or csv files into one workbook, most of us will think about copying and pasting the worksheets into one workbook. But if there are lots of worksheets need to be merged by such way, it will waste much time.Kutools for Excel augments Excel this powerful feature – Combine. With this utility you can easily:

Merge / combine multiple worksheets or workbooks into one workbook

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Click Kutools Plus >> Combine. See screenshots:

Merge / combine multiple worksheets or workbooks into one workbook

Supposing you have Workbook A, Workbook B and Workbook C, and you want to merge or combine all worksheets of those workbooks into one workbook. You can get it done as follows:

1. Please apply the Combine Worksheets utility by clicking Kutools Plus > Combine. Click OK in the following popping up Kutools for Excel dialog.

2. In the Combine Worksheets wizard, please select Combine multiple worksheets from workbooks into one workbook option, and then click Next button. See screenshot:

3. All the opened workbooks and corresponding worksheets are listed in the Workbook list and Worksheet list boxes, you can click Add > Files… or Folder… button to add the workbooks that you want to merge. See screenshot:

Tips:

A. Add workbooks to merge

Click Files… under Add button, you can add one workbook or multiple workbooks into Workbook list. If you click Folder… and specify a folder, all workbooks in the specified folder are added into Workbook list at once.
If you click Folder…, it will automatically add all workbooks in a specific folder into Workbook list to merge.
If you click OneDrive Files or OneDrive Folder, it will automatically add one or multiple OneDrive files from one folder or different folders as you need.

B. Remove workbooks from the Workbook list box

You can click the button to remove a workbook from the Workbook list box.

Click the Remove all button can remove all workbooks from the Workbook list box at once.

C. Open workbooks which has been encrypted with password:

If you want to combine the workbooks which has been encrypted with passwords, please click the Password button.

In the Password Manager window, click the Add button, enter the workbook password into the Password textbox, fill in the label and click the OK button. Repeat the operation until all workbooks’ passwords are added to the manager, and then click the OK button to close the window. See screenshot:

D. Save or access one scenario of your operation:

If you want to save the settings of Step 2 in Combine Worksheets for future operations, click Scenario > Save… button, and then name the scenario in the popping up dialog. By doing so, you don’t need to add workbooks or specify the worksheets again and again in the future, you just need to click the Open button to choose the scenario item you have saved.
Besides, you can click the Manage button to get into the Manage Scenario window to manage the saved scenarios as you need.

E: Sort all listed workbooks and worksheets in the dialog:

From the Sort drop down lists, you can sort the listed workbooks or worksheets as your need.

F. What’s the Same sheet button? At the bottom of the Worksheet list, there is a Same sheet button. With this button, you can easily select all worksheets with same worksheet name across all the checked workbooks in the Workbook list. For example, there are 3 workbooks that have been checked in the Workbook list, if you click on the Sheet3 as shown in the below screenshot, it will immediately select all worksheets with same worksheet name across the 3 checked workbooks after clicking Same sheet button. See screenshots:


Every Sheet3 in each checked workbook has been checked

Merge Worksheets From Different Workbooks

4. After adding and specifying the workbooks and worksheets you want to combine or merge, please click the Next button. In the Step 3, please configure following options:

1. To merge blank / empty worksheets or not:

If your workbooks contain blank or empty worksheets, you can keep or skip the blank worksheets.

2. Insert worksheet information:

If you check this option, the workbook information will be inserted at first row of each worksheets as comment in the combined workbook.

3. Rename worksheet names after merging:

If you want to insert or add the original workbook name into the worksheet names after merging, please check By inserting workbook name option and specify the location where you want to insert the workbook name. You can insert it before or after the original worksheet name. At the same time, you can specify a separator between the workbook name and worksheet name.

If you don't want to insert or add the workbook name into worksheet names, please uncheck By inserting workbook name option.

5. Click Finish button to merge the worksheets, after combining the sheets, a Specify the file name and location for the combined workbook window is popped out, you can specify a file name and location for your combined file, and then click the Save button. See screenshot:

Note: If you haven't saved the workbook and worksheet settings as scenario, a Kutools for Excel window will pop up to remind you to save the scenario. If you decide to save it, click the Yes button and name it in the next Save Scenario dialog. Otherwise, click the No button. See screenshot:

A new workbook is created and status of the combined workbooks are listed inside. You can click the Output file link to open the combined workbook directly.

Then multiple worksheets or workbooks have been combined into a new workbook. In the new workbook, there will be a worksheet which names Kutools for Excel, and it contains detailed information about the new workbook as shown in the below screenshot.

Note: If you think the Combine feature is useful, you can click button to share it to your friends.

Do you want to know how to merge or combine all same name worksheets from different workbooks into one worksheet? Please refer to Quickly merge / combine all worksheets of same name across workbooks into one worksheet.

Merge Excel Spreadsheets Into One Workbook

Demo: Quickly merge / combine worksheets / workbooks or csv files into one workbook in Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Combine Excel Worksheets Into One Workbook

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

How To Combine Excel Workbooks

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.