How to combine multiple files in Excel
It's common to have data stored across multiple files that you want to combine to make analysis easier.
One way is to manually copy and paste the data from each into the same worksheet. However, there is a more efficient method.
Carry out the following steps:
1️⃣ On the Data tab, go to Get Data ＞ From File ＞ From Folder.
2️⃣ Navigate to the folder containing the files and select Open to bring up a window with details about each one.
3️⃣ Select Combine ＞ Combine & Transform Data, and specify the settings for each file (if necessary).
4️⃣ Select OK to launch the Power Query Editor.
5️⃣ Apply any necessary transformations to get the data up to scratch.
6️⃣ Select Close & Load ＞ Close & Load To.
7️⃣ Choose 'Existing worksheet' and select OK to load the data into the worksheet.
In the video example, several CSV files are housed in a folder containing sales data for different years.
For step 5, three changes are necessary.
Firstly, the autogenerated Source.Name column is removed, as the original file name each row belongs to is not needed.
Secondly, the repeated column headers are also removed from the data, so only one row remains of them.
These are then promoted as the column headers, replacing the generic Column1, Column2, and Column3.
On that note, it's worth mentioning that data is rarely perfect, so it's highly likely you'll need to pop into Power Query to make adjustments. If you don't, at step 3, you can always use Combine & Load instead to dump straight to the grid.
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.
Find us on:
🌐 Website: https://globalexcelsummit.com
👔 LinkedIn: / global-excel-summit
🆇 X: https://x.com/ExcelSummit
🤳 TikTok: / globalexcelsummit
📸 Instagram: / globalexcelsummit
🟦 Facebook: / globalexcelsummit
🧵 Threads: https://www.threads.net/@globalexcels...