Append Data From Many Files in a Folder into One Excel File?
How to append data from many files in a folder into one Excel file? It's a question that many have wondered about and rightly so. It can be so easy to have files that you should have put on a single sheet. However, over time the data has been spread over multiple files.
Now, before we continue, you must understand that the data you wish to append needs to have a similar structure. It is possible to append data that has difference column headers using this method, but it's not what we'll be doing here.
If you want to follow along with this tutorial, be sure to download the exercise files. You will remember a lot more if you follow along.
Append Data Exercise File - Download Here
Examining the files to append
Before we continue it might be a good idea to have a look at the files that we are wanting to append. If you've downloaded and unzipped the above exericses then you'll see a folder that contains 3 files:
Open a file and you'll see the structure of one of the files. The column headings are the same for all the files.
To keep this simple we will append Excel data that has the same structure.
Appending the Excel files in a folder into one file
If you want to append all the Excel files in the folder you can do the following:
First, in a blank Excel spreadsheet click on Data from the Ribbon (Found at the top of the screen). Then click the Get Data drop down list on the left and goto From File then From Folder.
After you've done that you will need to find the folder that contains the files you wish to append.
Please note that the files themselves are not displayed when you're selecting the folder. Don't be distressed by this it's quite normal.
When you've found the folder you want then click Open.
Once you have selected the folder containing the files to be combined or appended to each other you, Excel will display the files currently in the folder.
If you look closely, you can see information such as:
- File Name
- Extension
- Modified and Created and Last accessed dates
- Folder information
This information can be quite handy.
Now click on the Combine button and from the options choose Combine & Transform Data.
Combining the Files in Power Query
You are now in the Combine files dialog box. Now, combining and appending are pretty much the same thing you are basically bringing data together into one data source. This way you can work with the data as one Table.
However, in this Combine files dialog box you will need to choose which file determines the structure for all the files in your folder. By default, Excel chooses the first file in your folder as the sample file. If you click on the Sample File drop down menu you can see all of the files in the folder.
You can see I have 3 files. Excel has selected the 2019 Accounts spreadsheet as the sample file. You can choose any file you want. However, if any of the other files have columns or data that is not included in the sample file, then that data will not be combined.
Click on Sheet 1.
You can see the data to be appended.
Click OK.
Now the data has been imported into Power Query. Power Query is an Excel tool that will give you the ability to clean and transform data. Its purpose is to transform data into a structure that can be easily analysed. Such as in a Pivot Table.
Let us now use Power Query to clean this appended, or combined, data.
Starting off, we only want to use the Date to the Total columns.
Click on Data then hold the shift key (on the keyboard) then click Total.
Next, right click any of the selected column headers and choose Remove Other Columns.
You can see that all of the other columns, the none selected ones, have been removed.
Next, click on the Data type button to the left of the Price column header and choose currency.
A Small note on using Power Query
Now that you're in the world of Power Query, you need to be a little more specific about the type of data that a column contains. Before we changed this column header the data type was decimal number. But making it more specific helps when it comes to analysing the data.
Change the data type for Total to Currency.
Now it's time to load the appending Excel sheets back into our blank Excel spreadsheet.
Click Close & Load. (Top Left).
You can now see that all 3 files are appended, or combined, into one.
You can see from the above filter that the Table includes all 3 years. Also, if you've had a chance to see the video, I demonstrate how any additional files are automatically appended to the table.
So as long as the structure of the data is the same. I mean the names of the column headers. You can keep creating files in the same folder and the data will automatically be added to the table.
I know that technically, from an Excel point of view, this is called Combining data. However, when I was looking for this solution I referred to this technique as appending data.
This is because, basically, data is being appended or added to the end of other data.
Benefits of using Power Query to Append or Combine data
One of the main benefits of appending or combining multiple Excel files from a single folder is that it allows for more efficient data management. Instead of having to manually open and copy data from each individual file, appending or combining them into a single file streamlines the process and saves time.
Extra Benefits
Another benefit is that it makes it easier to analyze and manipulate the data. By having all the data in a single file, it is much simpler to run calculations and create charts and graphs. This can be especially useful for large data sets or when working with data from different sources.
Additionally, appending or combining multiple Excel files can also improve data accuracy. By combining data from different sources, it is possible to cross-reference and verify the information. This can help to identify and correct any errors or inconsistencies that may be present in the data.
Appending or combining multiple Excel files also allows for better collaboration among team members. By having a single file that contains all the data, it is easier for multiple people to work on the same project at the same time. This can greatly improve productivity and speed up the completion of tasks.
Lastly, Appending or combining multiple Excel files also allows for better data backup and archiving. By having all the data in a single file, it is much easier to make backups and store them in a safe place. This can help to prevent the loss of valuable data due to computer failures or other issues.
Other Excel Videos Tutorials
