Many businesses receive monthly sales reports in separate Excel files. Manually combining these files into a single master file each month can be time-consuming and error prone.
Contents:
1. Combining Multiple Excel Files into One
1.1. Folder Setup
1.2. Connecting to the Folder
1.3. Combining Individual Files
Combining Multiple Excel Files into One
We're going to use a built-in tool called Power Query to automate the process of combining multiple files into a single master file. If you've never used Power Query before, don't worry, it's easy!
1. Folder Setup
The first thing we need is one folder and central location to hold all of the individual files that we want to combine.
Create a folder named "Monthly Sales Files" and place all the monthly report Excel files in this folder. It can be anywhere on your computer; in the example it's placed on the desktop.
Here are all of the individual files inside of the "Monthly Sales Files" folder.
Ensure each excel file has a consistent structure inside of the workbook. The columns and overall layout of the workbook need to be the same across all reports. If they vary from file to file, you will run into issues combining them.
2. Connecting to the Folder
The next step is to create a connection to the monthly sales folder. Create a new file, I'm calling mine "Combined_Sales" but you can use whatever name you'd like.
On the new, blank excel sheet, go to the Data ribbon tab > Get Data > From File > and click From Folder.
Then select the "Monthly Sales Files" folder and click "Open". This will launch a window prompting you to combine all of the files found within the selected folder.
3. Combining Files
The next step is to use Power Query to automatically detect and combine the individual files, and pull it into our "master" file.
You'll see this window where Power Query displays a list of all files in the folder.
Click on Combine & Load
Power Query will prompt you to specify the data to combine. Select the sheet, range, or table, that contains the sales data (e.g., "Sheet1").
Power Query will use one file as a "Sample File" and use that file to transform and combine all of the other files. Any changes made to the sample file, will be reflected across all files.
Click OK to combine the data from all files.
If there are no errors, then you should see a new sheet was created with all of the data from the individual sheets combined into one table.
The "Queries & Connections" window on the right will also pop up. This gives you an overview of all the queries and different data connections in your workbook and lets you monitor and troubleshoot them.
A few different files were automatically created. Everything under "Helper Queries" are background functions that facilitate the file combination.
The "Monthly Sales Files" highlighted in green under "Other Queries", is the actual query that is run to combine the files. This is what is loaded and displayed in the workbook.
Understanding the Helper Queries
When you use Power Query to combine files from a folder, it creates several helper queries and functions to automate the process. Here's a breakdown of each component.
Parameter1 (Sample file)
Acts as a parameter to hold a reference to a sample file from the folder.
This parameter is used by the Transform Sample File query to define the structure and necessary transformations.
Sample File
A single file from the folder is used as a representative example.
This file is used to create and test the transformations that will be applied to all other files in the folder. The Sample File query reads the data from this file.
Transform File
A function generated by Power Query based on the Transform Sample File query.
This function takes a file as input and applies the transformations defined in the Transform Sample File query. It is used to process each file in the folder.
Transform Sample File
Defines the transformations that should be applied to the sample file.
This query includes steps like promoting headers, changing data types, and other transformations. The steps defined here are used to generate a function that applies the same transformations to all files in the folder.
Transforming Data
Once the data is loaded into Power Query, you may need to perform some transformations to clean up and standardize the data.
To edit the query and its output, right click the query under "Other Queries" and press edit. This will launch the Power Query editor.
Some of the most common changes needed are:
Promoting column headers and remove top x number of rows
Removing unnecessary columns
Filtering out empty rows or any unwanted data
Ensure data types are consistent and accurate across the columns (e.g., dates, numbers).
Once the data is cleaned and transformed, click Home > Close & Load to load the combined data into a new worksheet in Excel.
The data will automatically update each time new files are added to the folder and the query is refreshed.
To update the query with any added files or changes, click on the table output > Query ribbon tab > Refresh.
