Search code examples
excelxlsx

What would be the easiest way to combine many xlsx files?

I have around 500 xlsx files all containing a table with the exact same columns.

How would I combine them all into one so that all the data is on one sheet? I'm not too fussed about the headers, I can delete them manually if necessary.

Solution

  • You should be able to accomplish this with Get Data "from Folder".

    1. First put all of your files alone in their own folder.

    2. Now open a new workbook and under "Data" click "Get Data" > "From File" > "From Folder".

    3. Navigate to your folder and select it.

    4. You should see your files listed at this point. Click the "Combine" down arrow and choose "Combine & Load to..."

    5. Click "Sheet1" (or your preferred sheet) and then you can see the sample data and click okay.

    6. Lastly you can choose a "Table" and then a table will load with all of the combined data from all of your files.

    I have successfully used this for many hundreds of XML files in the past and did advanced data calculation before generating the output and it was extremely useful and can easily update if your source files update as well!

    There are some videos on YouTube which help walk through this process as well but most likely with CSV files etc. but it's pretty much the same process.