How to combine multiple Excel files into one sheet

Often the data you need is spread across multiple Excel XLSX files in multiple sheets. For example, there might be an Excel file for each year, with a separate sheet for each month. Generally you will need to combine (stack) these separate Excel files and sheets together into a single consolidated sheet, before you can do anything useful.

We can do this by dragging files onto Easy Data transform and merging them one on top of the other (vertically), using a Stack transform.

Just:

• Drag the Excel files onto Easy Data Transform to create an input item for each sheet.
• With the input items selected, click Stack in the Left pane.

However this is tedious if there are many files and sheets. Also you would need to update the inputs to the Stack if there is a change to the Excel files. For example new, renamed or deleted files or sheets. It more convenient to drag the folder containing the Excel files onto Easy Data Transform and use wildcards to select the files and sheets you want to import.

This allows us to input hundreds or thousands of Excel files and sheets into a single consolidated dataset in seconds.

Check the and sub folders box if you also want to input files from sub-folders.

Click List matches to see which files and sheets will be input, and in what order.

You can optionally add meta information to show which file and sheet each row of the new dataset was input from.

If column names or column ordering varies between the sheets, you can handle this using a schema.

Once you are happy with your new dataset, you can output it to a new Excel file, or other format.

See the video above for more details.

Combining CSV, XML, JSON and other types of files is very similar, but you don’t have to worry about sheets.

You can also merge sheets side-by-side using a common ‘key’ column.

Easy Data Transform can also help with cleaning, filtering, merging, re-shaping and visualizing your data. All without coding. It can easily handle datasets with millions of rows and thousands of columns and your data never has to leave your computer. Download the free trial for Windows or Mac and try it yourself.