Combine Multiple Excel Workbooks Into One Sheet

 admin

Select the worksheets in your source workbooks which you want to copy. If there are several sheets within one file, hold the Ctrl key and click on each sheet tab. Alternatively, go to the first worksheet you want to copy, hold the Shift key and click on the last worksheet. That way, all worksheets in between will be selected as well.

  1. Combine Multiple Excel Sheets Into One Sheet Vba
  2. How To Combine Excel Workbooks
  3. Multiple Spreadsheets Into One Workbook

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

Reading Time: 7minutes

Insert a new sheet and name it “Master” in the workbook. Rename it later if you want. Insert a module in VBA editor and copy above VBA code. You will be asked to select headings. Select the heading and hit OK. All the sheets are merged in master. I recently got a question from a reader about combining multiple worksheets in the same workbook into one single worksheet. I asked him to use Power Query to combine different sheets, but then I realized that for someone new to Power Query, doing this can be tough.

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.

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:

Combine Multiple Excel Workbooks Into One Sheet

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.

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

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.

Step 2: set options

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

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.

Combine Multiple Excel Workbooks Into One Sheet

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.

Combine Multiple Excel Sheets Into One Sheet Vba

Merging Other Workbook File Formats

Excel

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.

Stop me if you’ve heard this one before:

I need to make a pivot table but the data is spread out in lots of different Excel files…

Damnit!

This situation blows, since your analysis depends on a pivot table and combining a bunch of workbooks together by hand is terrible.

Fortunately, VBA will make short work of this pain in the ass. Here goes:


Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Let’s break this challenge down step-by-step using the 4-step VBA process as our guide:

Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup

Our Step 1 – Setup is covered by lines 16-18 — short and sweet.

First, on line 16, we assign the folder name where the individual Excel files are stored. (You’ll want to change this to your folder, but in this example we are targeting C:blogexample_data_in_here.)

Then, on lines 16-17, we create a new Workbook (where Dst is short for “destination”, i.e. output) to store the data from each individual file, then assign the first Worksheet in that Workbook as the Dst Worksheet.

Boom! One down, three to go.

Step 2 – Exploration begins on line 21, where we take advantage of the Dir function to loop through the directory we set up moments ago (StrDirContainingFiles) and identify every file that ends in “.xlsx”. (That’s what the asterisk character, “*”, is doing there at the end of the line.)

Lines 22 through 25 store each file name inside a Collection (named colFileNames), which will make it SUPER easy to iterate through each file a little bit later in the code.

Umm why not do the whole thing in this Dir loop?

Yes, we COULD have conducted the bulk of the code inside this Dir loop, but I prefer using a Collection here because it reduces the number of nested loops in our subroutine. Each nested loop you add is another layer of complexity for you to mentally keep track of… fuck that. Programming is hard enough — avoid deeply-nested loops whenever you can.

The “use a Collection” strategy also makes it really easy to verify that the loop worked and pulled in the data we expected, which you can check for yourself by un-commenting lines 27-31.

Let’s keep it moving though 🙂

With those file names stored neatly in colFileNames, we begin looping through it on line 35.

On line 38, strFilePath is assigned to be the original source folder string (strDirContainingFiles, which is “C:blogexample_data_in_here” in this example), a backslash (“”), and the file name from colFileNames.

We’ll immediately take advantage of that full file path to the Excel file on line 41, where we open that Workbook and save a reference to it as wbkSrc (where “Src” is short for “Source”). Line 42 assigns the target worksheet, named “data” in this example, to wksSrc.

Exploration continues on lines 46-47, where we take advantage of the LastOccupiedRowNum and LastOccupiedColNum functions (which are defined at the very bottom as well as in the VBA Toolbelt, which you should be using) to easily identify the last-occupied row and last-occupied column on the source Worksheet.

This is critical here! By dynamically determining the last column and last row on each loop, we can be confident that we’re getting all the data from each Worksheet.

With the last-occupied row and last-occupied column numbers stored in lngSrcLastRow and lngSrcLastCol respectively, we store the full data range on lines 48-51, starting from the top-left corner and extending to the bottom-right.

How To Combine Excel Workbooks

This is another great checkpoint: we can verify that all the data has been correctly identified and stored in rngSrc by un-commenting out lines 53-55 and calling rngSrc.Select to highlight all the cells. Smooth!

Here’s where things get a bit more interesting…

On lines 60-62, we check to see if this is NOT the first iteration.

Why are we checking to see if this is the first loop?

Why? Good question!

On the first loop, we want to include the headers, but each subsequent time we do NOT want to include the headers.

Each loop after the first, we adjust rngSrc to skip the first row like this:

Set rngSrc = rngSrc.Offset(1, 0).Resize(rngSrc.Rows.Count – 1)

Here’s the two-step process in slow-motion:

  • .Offset(1, 0): this shifts the whole range down one row, meaning the first row is no longer included, but also means we now have a blank row at the bottom of rngSrc
  • .Resize(rngSrc.Rows.Count – 1): this adjusts the bottom row of rngSrc up one row by reducing the total count of rows that are included in the Range

Nice!

This puts us at another good checkpoint, where we can verify that the header is no longer part of rngSrc by un-commenting out lines 64-68.

Phew — with that, Step 2 – Exploration is complete!

Let’s dive into Step 3 – Execution, which kicks off on line 73.

Here, we again check the iteration. If this is the first loop, our target cell is easy — it’s A1, since the Worksheet is empty. (That’s what we handle on lines 74 and 75.) On the other hand, if this is NOT the first loop, then we:

  • Again take advantage of LastOccupiedRowNum, which comes with the VBA Toolbelt and works on ANY Worksheet, to identify the last-occupied (on line 77)
  • Set the target Range to be one cell down from the last-occupied row (on line 78)

The actual copy / paste step happens on line 80, where we call rngSrc.Copy and pass in rngDst (which we just set on line 78) as the Destination.

Almost there you guys, stay with me!

The last challenge within Step 3 – Execution is to add a column identifying which data file a given row came from by writing the Worksheet name into a far-right column. Let’s get to it!

Lines 86-89 cover another first loop special case — if this is the first time through, then we need to make sure we add a header name! By taking advantage of LastOccupiedColNum, which, again, is implemented for you both below AND in the VBA Toolbelt, we know that lngDstLastCol + 1 gives us the column right next to the last-occupied column.

We name this column header “Source Filename” on line 88.

Since we know that each row of data from the last paste (which happened on line 80) came from one of the different Excel files, we can take advantage of the Range.Value property to quickly write the file name to each of those rows.

First, we need to identify the first row of data that was just pasted in.

We used this same exact row number back on line 78, so we essentially copy that logic and assign lngDstFirstFileRow to be lngDstLastRow + 1.

Multiple Spreadsheets Into One Workbook

Next, we need to figure out the last row that will get this file name data, and on line 103 we do just that.

Guess which function rides in to our rescue? Yep — it’s our old standby LastOccupiedRowNum.

Now that we know the first row and the last row of the range of cells that will need to be populated with the file name, all that’s left to do is get the right column number!

And wouldn’t you know it, LastOccupiedColNum, on line 104, assigns lngDstLastCol that exact value 🙂

Now that we know the three critical components for a Range:

  1. The first row, which is stored in lngDstFirstFileRow
  2. The last row, which is stored in lngDstLastRow
  3. The column those rows need to be applied in, which is stored in lngDstLastCol

We can write the file name easy peasy!

On lines 107-108, we use the values from #1, #2, and #3 above to store the target Range.

Damn son! That brings us to a great checkpoint — by un-commenting lines 112-113, we can easily verify (using Range.Select) that the correct Range is defined.

Finally, the last Execution task occurs on line 117, where we actually do the file name writing. Calling wbkSrc.Name returns the file name (in the first case, it will be “AT_Apr_16.xlsx”), which is why we assign the rngFile.Value to it.

Jackpot — Step 3 – Execution is all done!

And of course, we wrap up with Step 4 – Cleanup.

Nice and short here too: on line 122, we close the source data Workbook (with SaveChanges set to False, since we do not want to modify those files at all), and on line 127 we throw a quick “Data combined!” message box to let the user know the job is done!

Want to see this code in action? Here’s an 14-minute video guide:

Combining many individual Excel files into a single file with VBA smoothly? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.

Get the VBA Toolbelt!

Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.