Stacking data from multiple worksheets manually can be painfully time-consuming and could easily lead to copying errors. What’s more, this process becomes even more burdensome if you need to regularly update the dataset. Luckily, Excel’s Power Query tool can do all this for you.
Although this guide explains how to pull information from various XLSX files, you can follow a similar process with TXT, CSV, and XML files.
Step 1: Organize the Workbooks You Are Going to Combine
Before you begin combining Microsoft Excel files into one worksheet, there are some important steps to take.
First, make sure all the datasets you’re going to combine are structured in the same way. Specifically, they must contain the same number of columns, have the same column headers, and the worksheet tabs must have the same name. Also, the process will be much more straightforward if the file names follow a similar syntax.
That said, the columns don’t need to be in the same order, and the number of rows on each sheet can differ.
Second, ensure the files are saved in the same folder.
If possible, the folder should contain only the files you’re going to combine, as this makes the process cleaner and easier in later steps. However, if you must store other files in this folder as well, I’ll show you shortly how to ensure only the correct files are selected for the data merge.
Step 2: Open Excel and Link to the Relevant Folder
Once the files are correctly organized into the folder according to the above prerequisites, you’re ready to start pulling the data together.
First, open a new Microsoft Excel workbook, start a blank worksheet, and press F12 to save the file in a different folder from where the individual files you’re going to combine are located.
Related
The Best Excel Keyboard Shortcuts I Use as a Power User
The key to excelling in your spreadsheet work.
Next, in the Data tab on the ribbon, click “Get Data.” Then, hover over “From File,” and select “From Folder.”
Now, in the Browse dialog box, locate and select the folder where the files you want to combine are stored, and click “Open.”
Step 3: Choose the Files You Want to Combine
What you do in this step depends on what’s stored in the folder you selected. If it contains only the files you want to merge, see Section 3A below. On the other hand, if the folder also contains some files you don’t want to merge, skip to Section 3B.
3A: The Folder Contains Only the Files I Want to Merge
Earlier, I said that the workbook-merging process is cleaner and easier if the folder you select is dedicated to the files you want to combine. This is because you don’t need to filter out any unwanted files. So, since you’re already ready to combine the files, click “Combine,” and in the drop-down menu that appears, click “Combine And Transform Data.”
Now, after Excel briefly evaluates your data, you’ll see the Combine Files dialog box, and you’re ready to skip to Step 4.
3B: The Folder Contains Some Files I Don’t Want to Merge
If the selected folder also contains files other than the ones you want to merge, after you locate the folder in Step 2, you’ll see them listed in the details. In this case, you need to click “Transform Data,” as you want to make changes to the file selection before you combine the data.
Clicking this option launches the Power Query Editor, a separate window within Microsoft Excel where you can filter out the files you don’t want to include in the combining process.
In this case, the file named Contact Details needs to be excluded, so click the filter drop-down arrow in the Name column header, uncheck that file, and click “OK.”
Now, only the files you want to combine are listed, so go ahead and click the double down arrow in the Content column to launch the Combine Files dialog box.
Step 4: Combine the Files
There are a few things to look out for in the Combine Files dialog box.
First, by default, the workbook nominated as the sample file is the first one you selected in the list. The sample file will dictate how the data is structured when the files are combined, so, as long as all your files are structured consistently, it’s fine to leave this option as is.
Second, if your workbooks have more than one worksheet tab, you’ll see them listed in the Display Options pane on the left. This is why it’s important that all the tabs you want to combine across the nominated workbooks have the same name. Also, if you have formatted the data as Excel tables and named them, you’ll see the list of names here.
In this example, however, each workbook only has one worksheet (named Scores), so go ahead and select this option. After reviewing the preview of the selected sample file in the right-hand pane, click “OK” to confirm and launch the Power Query Editor.
Step 5: Transform the Data
If this is the first time you’ve opened the Power Query Editor in Excel, it can initially be overwhelming. However, once you take some time to understand what the different areas of the editor do, you’ll realize that it’s actually an intuitive tool.
Related
How to Clean Up and Import Data Using Power Query in Excel
Don’t overlook this amazing Excel tool!
The left-hand pane is where all the queries are located. When combining data from various Excel workbooks, you only need to focus on two of these—the Transform Sample File query, which is used as a template for any changes you want to make to the datasets, and the appended query at the bottom of the list, which consolidates the files. However, depending on what you want to do, they’re both useful for transforming your data.
First, in the Transform Sample File query, click the number format icons in the header of each column to tell Excel what types of data they contain. In this example, the first, third, and fourth columns contain whole numbers, and the second column contains a date.
Look in the Query Settings pane on the right-hand side of the Power Query Editor to see (and remove if necessary) each step you take in transforming your data.
Next, in the appended query, we don’t need the Source Name column, but it would be handy to have the year for each team score.
You can transform this column to keep only the years while removing the rest of the file names. To do this, click the column header so that all values in the column are selected, and in the Transform tab on the ribbon, click “Extract,” then “Text Before Delimiter.”
In this case, we want to keep the year, and remove everything after the first space. So, in the Delimiter text field, type a single space. Then, click “Advanced Options,” and select “From The Start Of The Input.” Now, click “OK.”
Finally, double-click the column header to rename it Year, and change the data type to “Whole Number.”
Take a moment to scroll down the combined data list to check that there aren’t any errors or any further transformations you wish to make.
Related
4 Excel Power Query Commands You Need to Know
Become an expert data manipulator.
Step 6: Load the Combined Data Onto a New Worksheet
Now that the data from the workbooks has been combined and transformed, it’s time to see how it looks in a regular Excel worksheet. In the Home tab on the Power Query Editor window, click the top half of the “Close And Load” split button.
Click the bottom half of the “Close And Load” split button for more loading options. For example, you can load the data onto an existing worksheet or as a PivotTable.
Now, the data loads on a new worksheet as a formatted Excel table, meaning you can format its appearance and properties in the Table Design tab on the ribbon.
Related
Everything You Need to Know About Excel Tables (And Why You Should Always Use Them)
This could totally change how you work in Excel.
To make further changes to the query in the Power Query Editor, after activating the Queries And Connections pane in the Data tab, right-click the query, and select “Edit.”
Once you’ve made the necessary edits in the Power Query Editor, click “Close And Load” again to update the resultant dataset.
Step 7: Append More Data From Additional Workbooks
Finally, let’s imagine the data for 2024 has now come in, and you want to append this new information to the existing query. Amazingly, because you’ve already taken the necessary steps in the Power Query Editor, all you need to do is ensure the workbook meets all the criteria I outlined in Step 1, and click a single button in Excel to refresh the table.
First, move the workbook containing the data to the folder where the previous years’ data is located, ensuring that the workbook name, worksheet structure, and tab name are consistent with the other files.
Then, open the workbook containing the previously appended data, and in the Queries And Connections pane (which you can activate by clicking “Queries And Connections” in the Data tab on the ribbon), click the “Refresh” icon next to the primary query.
After giving Excel a few seconds to complete this update, scroll down to see the additional data appended to the bottom of the table.
To keep any formatting you applied to the table before refreshing the Power Query, click “Properties” in the Table Design tab on the ribbon, and ensure “Preserve Cell Formatting” is checked. If you change the column widths, and don’t want these to automatically fit to the data they contain each time you refresh, uncheck “Adjust Column Width.”
As well as appending data from separate workbooks saved into a folder, you can stack data from multiple Excel sheets within a single workbook by creating data connections between them and combining the queries. What’s more, you can use Power Query Editor to import tables from the web.