How to combine multiple Excel workbooks into one
By
SpreadCheaters
Let’s consider a scenario where we have two separate Excel workbooks: one containing sales data for Supercars and the other containing sales data for Common cars. To simplify the data analysis process, we aim to merge these workbooks into a single file. It is crucial to highlight that both files should adhere to the same format and be located within the same folder.
Combining multiple Excel workbooks into one is useful because it allows easier data management and analysis by consolidating related information in a single file. Instead of navigating through multiple files, users can access all the data they need in one centralized location, saving time and effort.
Step 1 – Navigate to the “Get Data” option
– Launch Microsoft Excel and open your desired workbook.
– Navigate to the “Data” tab located on the Excel ribbon.
– Look for the “Get Data” option in the “Get & Transform Data” section.
– Click on the “Get Data” option.
Step 2 – Browse the folder containing files
– A drop-down menu will appear with various data source options.
– Move your move to the “From File” option and further options will appear.
– From those options, select the “From Folder” option.
– Then, browse and select the folder that contains the files we wish to combine.
– After that, click on “Open”.
Step 3 – Open the “Power Query Editor”
– After clicking on “Open”, a dialogue box will appear on your screen.
– Locate an option named “Combine” at the bottom of the dialogue box.
– Click on the down arrow adjacent to it.
– Then choose the “Combine & Transform Data” option.
– Doing this will open another dialogue box.
– Just select the sheet you want to have as the first sheet and click on “OK”.
– This will open the “Power Query Editor”.
Step 4 – Modify the Data
– In the “Power Query Editor”, make necessary changes to your data.
– For instance, we are removing the “Source.Name” column for a better appearance, because this just shows the file names from which the data has been merged together. We can see that we have got the data from both files as shown in the image.
Step 5 – Combine the data from two workbooks
– Locate the “Close and Load” option under the “Close” group in the “Power Query Editor”.
– Click on this option and the data of two workbooks will be combined in one file.
