Merging Multiple CSVs in Excel – A Step-by-Step Guide

Meet Priya Sharma, a data analyst at Sunrise Technologies Pvt. Ltd., based in Pune. It’s Monday morning. Her manager, Mr. Rajiv Mehta, walks in with a slightly worried expression.

Rajiv: “Priya, I just got CSV reports from all 10 regional sales teams. I need them merged into one master file. Can you do this ASAP for the review meeting?”

Priya smiles. “Of course, Sir. I know a few ways to merge CSVs depending on what you want. Let me show you.”

---

🎯 The Problem

There are 10 CSV files like:

• Sales_North.csv
• Sales_South.csv
• Sales_East.csv
• Sales_West.csv
• …and so on.

Each file has the same columns:
| Date | Region | Product | Sales |

Now Priya needs to combine them into one Excel file.

---

🛠️ Method 1: Copy-Paste (For Beginners or Very Small Data)

👩‍💻 Scenario:

Priya’s intern Rohan asks, “Can’t we just open each CSV and copy-paste?”

Priya: “Yes, Rohan. That works if it’s only 2–3 small files. But it’s not scalable. Still, here’s how.”

✅ Steps:

1. Open all CSV files in Excel.
2. Select the data (excluding the header after the first file).
3. Paste it into a master workbook (say, All_Sales.xlsx).
4. Save as Excel file.

⚠️ Drawbacks:

• Manual and slow.
• Easy to make mistakes.
• Not suitable for 100s of files.

---

🛠️ Method 2: Power Query (Smart and Scalable – Excel 2016+)

Now Priya opens Excel 365, clicks on Data > Get Data > From Folder.

👩‍🏫 Priya explains:

“Power Query is perfect for this. It can merge unlimited CSVs from a folder in just a few clicks.”

---

✅ Steps:

1. Put all CSV files in one folder (e.g., D:\CSV_Sales_Reports).
2. Open Excel → Go to Data tab.
3. Click Get Data > From File > From Folder.
4. Browse and select the folder.
5. A list of files appears → Click Combine & Transform Data.
6. Power Query Editor opens.
7. Preview and make sure columns match.
8. Click Close & Load → All data loads into a single table.

---

🎉 Benefits:

• Super fast.
• Dynamic: If new CSVs are added, just refresh the query.
• Can apply filters, remove duplicates, rename columns, etc.

---

🛠️ Method 3: Using VBA Macro (For Automation Lovers)

One of Priya’s teammates, Amit, loves automation. He suggests:

Amit: “Let’s use a macro. It’ll loop through all CSV files and merge them automatically.”

✅ VBA Script:

Priya opens a blank workbook and presses Alt + F11, pastes the following:

Sub MergeCSVFiles()
    Dim ws As Worksheet
    Dim folderPath As String
    Dim fileName As String
    Dim lastRow As Long
    Dim csvData As Workbook

    ' Set your folder path
    folderPath = "D:\CSV_Sales_Reports\"

    ' Add a new sheet for merged data
    Set ws = ThisWorkbook.Sheets(1)
    ws.Cells.Clear

    fileName = Dir(folderPath & "*.csv")
    
    Do While fileName <> ""
        Set csvData = Workbooks.Open(folderPath & fileName)
        
        ' Copy the data (excluding header if not first file)
        With csvData.Sheets(1)
            If ws.Cells(1, 1).Value = "" Then
                .UsedRange.Copy ws.Cells(1, 1)
            Else
                lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
                .UsedRange.Offset(1, 0).Copy ws.Cells(lastRow, 1)
            End If
        End With
        
        csvData.Close False
        fileName = Dir
    Loop

    MsgBox "All CSVs merged!"
End Sub

🔁 Output:

Automatically reads and merges all .csv files from the folder into a single worksheet.

---

🛠️ Method 4: Python (Advanced / Data Science Teams)

Later, Priya trains interns like Anjali, who’s from a data science background. She shows her how to use Python and Pandas.

import pandas as pd
import glob

# Path to folder
files = glob.glob("D:/CSV_Sales_Reports/*.csv")

# Merge all
df = pd.concat([pd.read_csv(file) for file in files], ignore_index=True)

# Save to Excel
df.to_excel("D:/All_Sales.xlsx", index=False)

“This method is powerful when dealing with large files or when merging needs logic like filtering rows, calculating totals, etc.”

---

🔍 Final Touch: Cleaning & Formatting

After merging, Priya:

• Applies Filters.
• Adds Conditional Formatting.
• Inserts Pivot Tables to analyze Sales by Region/Product.
• Shares a well-formatted All_Sales_Report.xlsx with Rajiv.

---

🏁 Conclusion

Rajiv (Manager): “Excellent work, Priya! Now I understand we don’t need to fear CSV chaos anymore.”

Priya (smiling): “Exactly Sir! We’ve got tools like Power Query, VBA, Python—and good teamwork.”

---

✅ Summary Table

Method	Best For	Skill Level	Dynamic?	Tools Needed
Copy-Paste	1–3 small files	Beginner	❌	Excel
Power Query	5–500+ files, repeatable tasks	Intermediate	✅	Excel 2016+ / 365
VBA	Custom automation	Advanced	✅	Excel + Macros
Python & Pandas	Data cleaning, large datasets	Expert	✅	Python environment

---