The Complete Guide to Excel Multiple Workbook Consolidation: Power Query, Python, and VBA Compared

1. Introduction: Breaking Free from Month-End Excel Chaos

Common Pain Point: The Manual Copy-Paste Hell

“Here we go again with month-end…”

Sales data sent from branches nationwide. Survey results from each department. Daily inventory management sheets. Opening each Excel file one by one, copying, pasting… before you know it, half a day has passed. Sound familiar?

Are you particularly struggling with these scenarios?

• 📁 Manually consolidating 20+ Excel files every month
• 📊 All files have the same structure, yet somehow automation seems impossible
• ⏰ Getting told “add this data” the day before deadline, guaranteeing overtime
• 😰 Copy-paste errors causing number discrepancies and major headaches later

What This Article Will Solve

As of now, Excel file consolidation has become surprisingly simple. This article provides a comprehensive explanation of 4 methods optimized for your specific situation.

• Complete in 5 minutes: Power Query requires zero programming knowledge
• Handle massive data: Python can process 100 files instantly
• Easy team sharing: VBA allows anyone to execute with one button click
• Leverage latest features: Further efficiency gains with new Microsoft 365 capabilities

2. Comparison Table: Choose the Best Consolidation Method for You

Pros and Cons by Method

Scenario-Based Recommendations

• Monthly routine tasks → Power Query (easiest)
• 100+ files bulk processing → Python (fastest)
• Team-wide usage → VBA (easy to distribute)
• OneDrive/SharePoint integration → Power Automate (automation)

3. Method 1: Instant Consolidation with Power Query [No Macros Required – Easiest]

What is Power Query?

Power Query is a data acquisition and transformation tool that has been standard in Excel since 2016. It enables complex data processing with just mouse operations, requiring no programming knowledge.

Preparation: Organizing Folders and Files

First, gather all Excel files you want to consolidate into one folder.

📁 2025 Sales Data
  ├── 📄 January_Tokyo_Branch.xlsx
  ├── 📄 January_Osaka_Branch.xlsx
  ├── 📄 January_Nagoya_Branch.xlsx
  └── 📄 January_Fukuoka_Branch.xlsx

Important: Ensure all files have unified sheet names and column structure (headers).

Step-by-Step Guide

Step 1: Open a New Excel File

First, open a new Excel file that will serve as the consolidation destination. File downloads are available at the bottom of the page.

Step 2: Launch Power Query

1. Click the “Data” tab in the ribbon menu
2. Click “Get Data“
3. Select “From File” → “From Folder“

Step 3: Select Folder

1. Click the “Browse” button
2. Select the folder you prepared earlier
3. Click “OK“

Step 4: Confirm File List

The list of files in the folder will be displayed. Check these points:

• Are all necessary files displayed?
• Are there any unwanted files included?

Step 5: Combine Data

1. Click the “Combine” button at the bottom of the screen
2. Select “Transform Data & Combine“

Step 6: Combine Settings

1. A sample file is automatically selected
2. Select the sheet you want to consolidate (usually “Sheet1”)
3. Confirm content in preview
4. Click “OK“

Step 7: Confirm in Power Query Editor

The Power Query Editor opens. Confirm the following:

• Is data loaded correctly?
• Are column types appropriate (numeric, text, date, etc.)?

The following operations are also possible as needed:

• Delete unnecessary columns
• Change data types
• Apply filters

Step 8: Load Data

1. Click “Close & Load” in the top left
2. Data is loaded into a new sheet

Common Issues and Solutions

Q1: “Data types don’t match” error appears

Cause: Numbers and text are mixed in the same column

Solution: Unify the data type of the relevant column to “Text” in Power Query Editor

Q2: Only some files are loaded

Cause: Different file formats (mixed .xls and .xlsx, etc.)

Solution: Unify all to the same format (recommended: .xlsx)

Q3: Headers appear duplicated

Cause: The first row of each file is not recognized as headers

Solution: Set “Use first row as headers” in Power Query Editor

4. Method 2: High-Speed Processing of Large Data with Python Scripts

Benefits of Using Python

• Overwhelmingly fast processing speed: Can process 1000 files in seconds
• Infinite customization: Conditional branching and complex processing possible
• Error handling: Skip problematic files and continue
• Log output: Can record processing status

Environment Setup (Beginner-Friendly)

Step 1: Python Installation

1. Download the latest version from Python Official Site
2. Check “Add Python to PATH” during installation

Step 2: Install Required Libraries

Execute the following in Command Prompt (Windows) or Terminal (Mac):

pip install pandas openpyxl xlsxwriter

Sample Code and Execution Method

Save the following code as “excel_merge.py”:

import pandas as pd
import glob
import os
from datetime import datetime

def merge_excel_files(folder_path, output_file):
    """
    Function to consolidate Excel files in specified folder

    Parameters:
    folder_path: Path to folder containing Excel files to consolidate
    output_file: Output file name
    """

    # Record processing start time
    start_time = datetime.now()
    print(f"Processing started: {start_time.strftime('%Y-%m-%d %H:%M:%S')}")

    # Get Excel files in folder
    excel_files = glob.glob(os.path.join(folder_path, "*.xlsx"))
    print(f"Files found: {len(excel_files)}")

    # Create list of dataframes
    df_list = []

    for file in excel_files:
        try:
            # Display filename
            print(f"Loading: {os.path.basename(file)}")

            # Read Excel file
            df = pd.read_excel(file)

            # Add filename as column (optional)
            df['Source File'] = os.path.basename(file)

            # Add to list
            df_list.append(df)

        except Exception as e:
            print(f"Error: {file} - {str(e)}")
            continue

    # Combine all dataframes
    if df_list:
        merged_df = pd.concat(df_list, ignore_index=True)

        # Save as Excel file
        merged_df.to_excel(output_file, index=False)

        # Processing complete
        end_time = datetime.now()
        processing_time = end_time - start_time

        print(f"\nProcessing complete!")
        print(f"Output file: {output_file}")
        print(f"Total rows: {len(merged_df)}")
        print(f"Processing time: {processing_time.total_seconds():.2f} seconds")
    else:
        print("No files found to consolidate.")

# Execution example
if __name__ == "__main__":
    # Specify folder path and output filename
    folder_path = r"C:\Users\YourName\Documents\2025_Sales_Data"
    output_file = "Consolidated_Data_2025.xlsx"

    # Execute function
    merge_excel_files(folder_path, output_file)

Customization Points

To extract only specific columns

# Select only necessary columns
df = pd.read_excel(file, usecols=['Date', 'Product Name', 'Sales'])

To filter by conditions

# Extract only data with sales >= 10000
df = df[df['Sales'] >= 10000]

To consolidate multiple sheets

# Read all sheets
xlsx = pd.ExcelFile(file)
for sheet_name in xlsx.sheet_names:
    df = pd.read_excel(file, sheet_name=sheet_name)
    df_list.append(df)

5. Method 3: Easy-to-Share Automation with VBA Macros

When to Choose VBA

• Want to complete everything within Excel
• Want to distribute to team members for their use
• Want to execute with one button click
• Cannot install Python on company PCs

Code Explanation and Setup Method

Step 1: Open VBA Editor

1. Open Excel
2. Press Alt + F11 to launch VBA Editor
3. Select “Insert” → “Module”

Step 2: Paste the Following Code

Sub MergeExcelFiles()
    '=====================================
    ' Excel Multiple Workbook Consolidation Macro
    ' Created: 2025
    ' Function: Consolidate Excel files in specified folder
    '=====================================

    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim MasterWb As Workbook
    Dim MasterWs As Worksheet
    Dim LastRow As Long
    Dim FileCount As Integer

    ' Error handling
    On Error GoTo ErrorHandler

    ' Temporarily pause screen updating (speed optimization)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' Select folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing Excel files to consolidate"
        If .Show = -1 Then
            FolderPath = .SelectedItems(1) & "\"
        Else
            MsgBox "No folder selected.", vbExclamation
            Exit Sub
        End If
    End With

    ' Create consolidation destination workbook
    Set MasterWb = Workbooks.Add
    Set MasterWs = MasterWb.Sheets(1)
    MasterWs.Name = "Consolidated Data"

    ' Initialize file counter
    FileCount = 0

    ' Get first file
    FileName = Dir(FolderPath & "*.xlsx")

    ' Process all files
    Do While FileName <> ""
        ' Exclude self
        If FileName <> ThisWorkbook.Name Then

            ' Open file
            Set wb = Workbooks.Open(FolderPath & FileName)
            Set ws = wb.Sheets(1)

            ' Copy data
            If FileCount = 0 Then
                ' Copy first file with headers
                ws.UsedRange.Copy
                MasterWs.Range("A1").PasteSpecial xlPasteValues
            Else
                ' Copy only data portion for subsequent files
                LastRow = MasterWs.Cells(Rows.Count, 1).End(xlUp).Row
                ws.UsedRange.Offset(1, 0).Copy
                MasterWs.Cells(LastRow + 1, 1).PasteSpecial xlPasteValues
            End If

            ' Close file
            wb.Close SaveChanges:=False

            ' Increment counter
            FileCount = FileCount + 1

            ' Display progress in status bar
            Application.StatusBar = "Processing... " & FileCount & " files completed"
        End If

        ' Next file
        FileName = Dir()
    Loop

    ' Processing complete
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.StatusBar = False

    ' Display results
    MsgBox "Consolidation complete!" & vbCrLf & _
           "Files processed: " & FileCount & " files" & vbCrLf & _
           "Total data rows: " & MasterWs.Cells(Rows.Count, 1).End(xlUp).Row, _
           vbInformation, "Processing Complete"

    Exit Sub

ErrorHandler:
    ' Error handling
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.StatusBar = False
    MsgBox "An error occurred." & vbCrLf & _
           "Error details: " & Err.Description, vbCritical
End Sub

Setting Up One-Button Execution

Step 1: Create Button

1. Return to Excel sheet
2. “Developer” tab → “Insert” → “Button”
3. Drag on sheet to place button

If Developer tab is not displayed:

• File → Options → Customize Ribbon → Check “Developer”

Step 2: Assign Macro

1. Right-click button → “Assign Macro”
2. Select “MergeExcelFiles”
3. Click “OK”

Step 3: Change Button Design (Optional)

1. Right-click button → “Edit Text”
2. Change to an intuitive name like “Execute File Consolidation”

6. Method 4: Leveraging New Microsoft 365 Features

Automation with Power Automate Desktop

Power Automate Desktop is a free RPA (Robotic Process Automation) tool included with Microsoft 365.

Benefits

• Scheduled execution possible: Automatic execution at set times daily
• Error notifications: Email alerts when processing fails
• Integration with other apps: Works with Teams, Outlook, SharePoint
• Log recording: Automatically saves all processing history

Basic Setup Steps

1. Install Power Automate Desktop
   • Free download from Microsoft Store
2. Create new flow
   • Click “New flow”
   • Give it an intuitive name (e.g., Monthly Sales Data Consolidation)
3. Add actions

1. Launch Excel
2. Get files in folder
3. Loop through files
4. Copy data
5. Paste to master file
6. Send completion notification to Teams

Leveraging Excel Online Co-editing Features

Combining Excel Online with OneDrive enables real-time co-editing and automatic consolidation.

Setup Method

1. Create folder in OneDrive
   • Create dedicated folder for each branch to upload to
2. Cloud integration with Power Query
   • Data → Get Data → Select From OneDrive
   • Set automatic refresh to always get latest data
3. Distribute sharing links
   • Send edit-permission links to each person in charge
   • Check update status in real-time

7. Practical Tips & Troubleshooting

Pre-Consolidation Checklist

Before starting consolidation work, always check the following:

• [ ] Are all file sheet names unified?
• [ ] Do header rows (column names) match completely?
• [ ] Are date formats unified (yyyy/mm/dd etc.)?
• [ ] Are there no full-width numbers mixed with numbers?
• [ ] Are there no unnecessary blank rows/columns?
• [ ] Do filenames contain no special characters (/, *, ? etc.)?

Common Errors and Solutions

Error 1: Garbled characters occur

Cause: Character encoding mismatch

Solution:

• For CSV files, re-save in UTF-8 format
• Specify character encoding when loading with Power Query

Error 2: Dates become numbers

Cause: Date format recognition error

Solution:

# For Python
df['Date'] = pd.to_datetime(df['Date'], format='%Y/%m/%d')

Error 3: Out of memory error

Cause: File size too large

Solution:

• Use 64-bit version of Excel
• Split processing with Python
• Delete unnecessary columns beforehand

Performance Optimization Tips

Power Query Acceleration

• Enable background refresh
• Utilize query folding
• Remove unnecessary steps

Python Acceleration

# Speed up with parallel processing
from concurrent.futures import ThreadPoolExecutor

def process_file(file):
    return pd.read_excel(file)

with ThreadPoolExecutor(max_workers=4) as executor:
    df_list = list(executor.map(process_file, excel_files))

VBA Acceleration

' Change calculation to manual
Application.Calculation = xlCalculationManual
' Execute processing
' ...
' Return calculation to automatic
Application.Calculation = xlCalculationAutomatic

8. Security and Compliance

Data Consolidation Precautions

When handling corporate data, always implement the following security measures:

Personal Information Handling

• Masking: Delete or anonymize unnecessary personal information beforehand
• Access permissions: Make consolidated files accessible only to minimum necessary members
• Encryption: Store important data encrypted

Audit Log Recording

# Example of recording logs with Python
import logging

logging.basicConfig(
    filename='data_merge.log',
    level=logging.INFO,
    format='%(asctime)s - %(message)s'
)

logging.info(f'Consolidation processing started - User: {os.getlogin()}')
logging.info(f'Files processed: {len(excel_files)}')

Importance of Backups

Always backup original data before consolidation:

1. Store original files in separate folder
2. Regularly backup consolidated data
3. Utilize cloud storage

9. Summary: First Steps You Can Take Right Now

Which Method is Best for You?

Having explained four methods in detail, let me summarize recommendations by situation once more:

🎯 Beginners Who Want to Start Right Away

→ Start with Power Query. No macros required, usable in 5 minutes.

🚀 Power Users Handling Large Data

→ Try Python. Initially challenging, but becomes the ultimate weapon once mastered.

👥 Managers Wanting Team-Wide Usage

→ VBA for simple one-button operation.

🔄 Those Aiming for Regular Automation

→ Power Automate Desktop for complete automation.

Next Actions

1. Practice with sample data first
   • Try with 2-3 small files
   • Build successful experience
2. Gradually expand scope
   • Increase from 10 files → 50 files → 100 files
   • Learn solutions when errors occur
3. Share with team
   • Share success stories with colleagues
   • Get feedback for improvements

Finally: Your Time is Precious

Why not use the time spent on manual copy-pasting for more creative and valuable work?

Excel multiple workbook consolidation is no longer “nice to have” but an expected skill in our era. Using the methods introduced in this article, you can achieve automation.

Start today, and transform your next month-end.

When you succeed, please share it with others around you.

Appendix: Ready-to-Use Templates & Resources

Downloadable Templates

We’ve prepared the following templates:

1. Power Query Sample Dataset
   • Practice sales data (5 files)
   • Includes step-by-step PDF guide
2. Python Script Collection
   • Basic consolidation script
   • Advanced version with error handling
   • GUI version (using tkinter)
3. Excel File with VBA Macros
   • Ready-to-use consolidation macro
   • Includes customization guide