How to merge Excel files with the same columns

If you’re reading this then you’ve probably got multiple Excel workbooks which all have the same columns, and you’d like to get them into a single workbook. This is actually a fairly simple task but surprisingly time consuming if you have more than a few workbooks to merge; especially if you need to do it on a regular basis.

The good news is that there are several options you can use to merge Excel files for free:

Each of these options are covered in the following sections, click the item in the list above to jump to that section.

Excel Document StackNot exactly what you need?
Have a look at The Ultimate Guide on how to Merge Excel files for some other ways to merge Excel files.

Copy & Paste the data into one of the spreadsheets from the others

So, this is the easiest of the options but also the most time consuming. It involves opening each spreadsheet and copying the rows into the master spreadsheet.

  1. Choose one of the spreadsheets you want to merge to be the master spreadsheet and open it (Pro tip: pick the one with the most rows!)
  2. Open the next spreadsheet to copy the rows from
  3. Click the row heading to the left of the second row (gray box with the number 2 in it) to select the whole row
    Select Excel Row
    Please note: Each of the spreadsheets we’re using has a header row which we don’t want to copy to the master spreadsheet as it already has the headers. If your spreadsheets don’t have a header row, you should select the first row rather than the second
  4. Press Shift, Control and the down arrow together (Shift, Cmd and the down arrow on a Mac) to select all the rows from row 2 to the end of the data
    All Used Rows Selected
  5. Press the Control and C keys together (Cmd and C on a Mac) to copy the rows
  6. Go back to the master spreadsheet from step 1
  7. Press Control and the down arrow together (Cmd and the down arrow on a Mac) to go to the last row containing data
  8. Right click the row select button to the left of the first empty row after the last row and select ‘Paste’ from the context menu which appears
    Paste Copied Rows
  9. Go back to the spreadsheet you copied the rows from and close it
  10. Repeat steps 2 to 12 for the remaining spreadsheets to copy rows from

As you can see, this is a simple but time consuming process. The next method for merging the spreadsheets is much quicker but a little more technical.

Excel Document Stack
Psst, you could always let us do it for you.

A simple merge where the columns are the same is free!

Click here to see for yourself

 

Use a VBA (Visual Basic for Applications) macro to merge the spreadsheets

Whilst this is a little more technical than the copy & paste method, if you follow the steps below, you should have your files merged in no time.

I’m going to provide 2 methods of doing the merge here:

For either of these approaches, I’ve provided links to spreadsheets with the code in them at the end of the instructions.

 

Merge all Excel files from a specific folder

With this approach, you put all of your Excel files to be merged into a single folder (with nothing else in it). Using the VBA code provided below, all of the Excel files in this folder will be concatenated together.

  1. Create a folder for the Excel files to be merged
  2. Copy & paste all the files into this new folder
  3. Create a new Excel workbook in a location that is NOT the folder created in step 1
  4. In this new workbook, click on the Developer ribbon tab
    Excel Developer Ribbon Tab
    If the Developer ribbon tab is not present, follow these steps to get it
  5. Click the Macros button in the Developer tab (The Macros button on a Mac)
    Excel Macros Button
  6. In the dialog box which opens, type the name MergeExcelFiles and click the ‘Create’ button
    Excel Macros Dialog Box
  7. The Visual Basic Editor will open:
    Excel Visual Basic Editor
  8. Copy & paste (or re-type by hand if you prefer!) the code below between Sub MergeExcelFiles()  and End Sub  (if you’d like to know more about what this code does, see how the code works):
  9. Look for the line shown below and replace <<Full path to your Excel files folder>> with the full path to the folder you created in step 1 (e.g. C:\Users\Martin\Documents\MyExcelFilesToMerge)
  10. We can now run this macro to merge the Excel files. There are 2 ways to do this:
    1. Click the Run toolbar button in the Visual Basic Editor
      Excel VBA Run Button
    2. In the Developer tab in the Excel ribbon, click Macros, select MergeExcelFiles and click the Run button
      Excel Run Macro
    3. If there are any errors reported, check that the VBA code is correct and try again
  11. If you’d like to save this spreadsheet with the macro you just created in it, you will need to save the file as a Macro-Enabled Workbook (an xldm file). Follow these steps to do this:
    1. Click File -> Save as…
    2. Select ‘Excel Macro-Enabled Workbook (.xlsm)’ from the Format list
      Excel File Format
    3. Click the Save button

If you’d like a copy of the spreadsheet which already has this functionality built in, you can download it here.

You can also download some sample files to test the merge with using this link: AllTimeTop2000Albums.zip These samples contain the top 2000 albums from from the All Time Top 3000 Albums list at Acclaimed Music. Each spreadsheet has the same columns and 200 rows, like this:

Excel Sample Data Columns

Psst, me again… seriously, save your time and let us do it for you.

A simple merge where the columns are the same is free!

Click here to check it out

 

Use a form to choose the Excel files to merge

This approach is similar to the previous method but here I’ll show you how to create a form where you can choose which Excel files to merge rather than put them all into a single folder. This makes it easy to re-use if you ever need to do the merge again without creating specific folders.

  1. Create a new Excel workbook
  2. In this new workbook, click on the Developer ribbon tab
    Excel Developer Ribbon Tab
    If the Developer ribbon tab is not present, follow these steps to get it
  3. Click the Visual Basic button in the Developer tab (The Editor button on a Mac)
    Excel Visual Basic Button
  4. The Visual Basic Editor will launch
    Excel Visual Basic Editor
  5. To the left and top of the Visual Basic Editor, there is a window labeled ‘Project’. Right click on the node within that window called ‘VBAProject’ and select Insert->User Form
    Excel VBA Insert User Form
  6. A new window will open with a gray square, this is the blank form. There will also be a toolbox window containing a set of controls which you can add to the form
    Excel VBA New User Form
  7. We need a list box, 3 buttons and a checkbox laid out as shown in the image below:
    Excel User Form With Controls
  8. For the VBA code to work correctly, you will need to change the names of the controls on the form and also update the button labels while we’re at it
    1. Click on the List Box you added
    2. On the left of the Visual Basic Editor, there is a window labeled ‘Properties’
    3. In this window, change the value of the (Name) property to FilesListBox
      Excel Set FilesListBox Name
    4. Click on each button in turn and the checkbox then change the (Name) property and the Caption property so that they match the image shown below:
      Merge Excel Files Dialog
      The (Name) and Caption properties should match those in the table below:

      Caption(Name)
      Add Excel FilesAddFilesButton
      CancelCancelButton
      MergeMergeButton
      First rows are headersFirstRowHeadersCheckBox

      Excel Add Files Properties

    5. Whilst in the properties for the Merge button, set the Enabled property to false
      Excel Merge Button Disabled
  9. We can also give the form a name. Click on the form title ‘UserForm1’ the , in the Properties window, change the (Name) property to MergeFilesForm
  10. Once the controls have been named correctly, right click on the MergeFilesForm node in the Project window and select View Code
    Excel User Form View Code
  11. Copy & paste (or re-type by hand if you prefer!) the code below into the window that opens (if you’d like to know more about what this code does, see how the code works):

  12. To show our new form and perform the merge, we will create a macro. In Excel, click the Macros button in the Developer tab (The Macros button on a Mac)
    Excel Macros Button
  13. In the dialog box which opens, type the name MergeExcelFiles and click the ‘Create’ button
    Excel Macros Dialog Box
  14. The Visual Basic Editor will open:
    Excel Visual Basic Editor
  15. Copy & paste (or re-type by hand if you prefer!) the code below. This will simply show the form which we have just created

  16. We can now run this macro to merge the Excel files. There are 2 ways to do this:
    1. Click the Run toolbar button in the Visual Basic Editor
      Excel VBA Run Button
    2. In the Developer tab in the Excel ribbon, click Macros, select MergeExcelFiles and click the Run button
      Excel Run Macro
  17. If there are any errors reported, check that the VBA code is correct and try again
  18. If you’d like to save this spreadsheet with the macro you just created in it, you will need to save the file as a Macro-Enabled Workbook (an xldm file). Follow these steps to do this:
    1. Click File -> Save as…
    2. Select ‘Excel Macro-Enabled Workbook (.xlsm)’ from the Format list
      Excel File Format
    3. Click the Save button

If you’d like a copy of the spreadsheet which already has this functionality built in, you can download it here.

You can also download some sample files to test the merge with using this link: AllTimeTop2000Albums.zip These samples contain the top 2000 albums from from the All Time Top 3000 Albums list at Acclaimed Music. Each spreadsheet has the same columns and 200 rows, like this:

Excel Sample Data Columns

Of course, if you really want to make your life easier you could let us do it for you (hint, hint!).

Click here to check out how cost-effective it will be

 

Convert Excel files to CSV and and merge the CSV files from the command line

The first thing to do with this approach is to convert all your Excel files to CSV. This can be done easily in Excel as follows:

  1. Open the Excel file to convert
  2. Click File->Save as…
  3. In the Save File dialog box which is shown, choose ‘CSV (Comma delimeted) (.csv)’ on Windows (or ‘Windows Comma Separated (.csv)’ on Mac) from the Format drop-down box <img>
  4. Click the Save button and that’s it!

To merge several CSV files together, it’s best to place them all in a single folder so that’s what we’ll do in the following steps:

  1. Create a folder for the CSV files to be merged
  2. Open each Excel file in turn and convert it to CSV as detailed in the steps above, saving the CSV file into the folder you just created. Whilst the CSV file is open, consider whether you need to delete any header rows. When the CSV files are merged using the method we are describing, the header rows will be included with the rows of data which is probably not what you want. If not, just delete the header rows from the CSV file and save it
  3. The following steps vary on Windows and Mac so look at the appropriate section below for the steps you need:

Windows

  1. Now, open a command prompt:
    • Press the Windows key and R at the same time
    • Or, on Windows older than Windows 8, click Start -> Run, type cmd then hit enter
      Start Run
  2. At the command prompt, change directory to the folder containing your CSV files by typing the following and hitting the enter key (change to the full path of the folder containing your CSV files, e.g. C:\Users\Martin\Documents\CSVsToMerge)
    e.g.
    CD to Merge Folder
  3. This next command will merge the CSV files into one CSV file called Master.csv in the folder containing your CSV files:
    Copy to Merge CSV Files

    Copy to Merge CSV Files Finished

Mac

  1. Open a terminal window by clicking: Applications->Utilities->Terminal
  2. In the terminal window, change directory to the folder containing your CSV files by typing the following and hitting the enter key (change to the full path of the folder containing your CSV files, e.g. /Users/martin/Documents/CSVsToMerge)
    e.g.:

    Mac CD to CSVFilesToMerge

  3. This next command will merge the CSV files into one CSV file called Master.csv in the folder containing your CSV files:

    e.g.:

    Mac concatenate CSV files

Once the CSV files have been merged, you can open Master.csv in Excel and choose File->Save as to save it as an Excel workbook by selecting the ‘Excel Workbook (.xlsx)’ option in the Format drop-down.

If you want to avoid all this messing around, you could always let us do it for you.

A simple merge where the columns are the same is free!

Click here to see for yourself