How to Extract Data from Multiple Excel Files

Tired of opening a dozen monthly sales reports from your team just to copy and paste the numbers into one master spreadsheet? We have all spent countless Mondays bogged down in this cycle. This article will show you how to automatically extract and consolidate data from multiple Excel files, saving you hours of mindless work and eliminating the risk of costly errors.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Consolidate Data from Multiple Excel Files?

In many businesses, data arrives in pieces. You might get a separate sales report from each regional office, a weekly performance file from each marketing channel, or a monthly inventory sheet from each warehouse. Working with this scattered information presents a few major problems:

• It’s inefficient: Manually copying and pasting data is a time-consuming, repetitive task. Hours spent on data entry are hours not spent on analyzing the information and making decisions.
• It’s prone to human error: One wrong copy-paste, one missed row, or one formatting mistake can compromise your entire analysis. These small errors are incredibly easy to make and difficult to spot.
• It provides no single source of truth: When data is fragmented, it's nearly impossible to get a clear, big-picture view of your business performance. You're always looking at pieces of the puzzle instead of the full image.

Consolidating your Excel files into a single, cohesive dataset solves these issues. It creates a reliable foundation for building summary reports, dynamic dashboards, and accurate forecasts.

Method 1: Manual Copy and Paste (And Why You Should Leave It Behind)

Let's start by acknowledging the most common method: manually copying and pasting. If you only have two or three files to combine once, this approach might be tempting for its simplicity. The process is exactly what it sounds like:

1. Create a new "master" Excel workbook.
2. Open your first source file.
3. Select the data you need (e.g., all rows and columns).
4. Copy the data (Ctrl+C).
5. Switch to your master workbook and paste it (Ctrl+V).
6. Open a source file, and repeat the process, pasting the new data below the previous batch.

While quick for a one-off task, this method quickly becomes a massive liability. It’s not scalable, not repeatable, and not reliable. The minute a new weekly report arrives, you have to start the whole process over. It’s the definition of busy work and the primary reason to learn a more automated approach.

Method 2: Use Power Query to Automatically Combine Files from a Folder

Power Query is the most powerful and scalable solution for this task, and it's already built into modern versions of Excel (Excel 2016 and later, and a free add-in for 2010/2013). Think of it as a background engine that can record your data transformation steps and repeat them for you with a single click. It's the perfect tool for consolidating files from a single folder.

Here’s the step-by-step guide.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Organize Your Source Files

Before you even open Excel, organization is key. Create a dedicated folder on your computer (e.g., "Monthly Sales Reports"). Place all of the Excel files you want to combine into this folder.

Crucial Tip: For Power Query to work its magic seamlessly, your Excel files should have a consistent structure. This means the data you want to combine should be in the same sheet name (e.g., always "Sheet1" or "Sales_Data") and have identical column headers in the same order.

Step 2: Start a New Excel Workbook and Open Power Query

Open a new, blank Excel workbook. This will be your master file where the consolidated data will live. From the Excel ribbon, navigate to the Data tab. In the "Get & Transform Data" section, click on Get Data.

Step 3: Point Power Query to Your Folder

From the dropdown menu, select From File > From Folder. A dialog box will appear. Click the Browse button and navigate to the dedicated folder you created in Step 1. Select it and click OK.

Step 4: Combine & Transform the Files

Excel will show you a preview window listing all the files in your selected folder. Instead of loading this list of files, you want to combine them.

At the bottom of this window, click a button called Combine & Transform Data. This button tells Excel, "Don't just show me the file names. I want you to open each file and stack the data inside on top of each other."

Step 5: Pick the Sheet to Combine

Next, Power Query needs to know where to find the data within each file. A "Combine Files" dialog box will pop up, showing a preview based on an example file (usually the first file in your folder).

On the left-hand side, you'll see a list of the sheets in that example file. Click on the sheet that contains the data you need (e.g., "Sheet1"). Power Query will show you a preview of the data on the right. Once it looks correct, click OK.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 6: Review in the Power Query Editor and Load

The Power Query Editor will now open. This is where you can further clean or transform the data, but for now, you should see that Power Query has already done the heavy lifting. It has opened every file, taken the data from the selected sheet, and stacked them into a single table. Notice the Source.Name column has been added, showing which file each row of data came from.

After a quick review, go to the Home tab in the editor and click the top half of the Close & Load button. Instantly, all the combined, consolidated data from every file in your folder will be loaded into a new sheet in your master Excel workbook, perfectly formatted as an Excel Table.

Step 7: The 'Refresh' Button is Your New Superpower

You’ve now consolidated your files. But here's where Power Query really shines. Next week, when a new report file arrives, you don't repeat any of those steps. All you do is:

1. Drop the new Excel file into your dedicated source folder.
2. Go back to your master Excel workbook.
3. Go to the Data tab and click Refresh All.

That’s it. Power Query will automatically repeat your saved query steps, grab the new data from the file, and append it to your master table. This turns a 20-minute manual task into a 5-second, one-click update.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 3: Use a VBA Macro for Old-School Automation

If you're using an older version of Excel without Power Query, or if you simply prefer using code, a Visual Basic for Applications (VBA) macro is another option. This approach is more rigid than Power Query but is powerful for straightforward consolidation. It requires a bit of upfront setup.

Here’s a basic script to get you started:

Sub CombineFiles()
    'Declare variables
    Dim Path As String
    Dim FileName As String
    Dim Wkb As Workbook
    Dim Ws As Worksheet
    Dim MasterWksht As Worksheet
    Dim NextRow As Long
    
    'Set up Master worksheet (the sheet where this code exists)
    Set MasterWksht = Application.ThisWorkbook.Worksheets("Sheet1") 'Change "Sheet1" to your destination sheet name
    
    'Clear existing data (optional, deletes old data before adding new)
    MasterWksht.Cells.Clear
    
    'IMPORTANT: Specify the path to your folder of Excel files. Remember the backslash at the end.
    Path = "C:\Users\YourName\Documents\Monthly Sales Reports\"
    FileName = Dir(Path & "*.xlsx")
    
    'Loop through all .xlsx files in the folder
    Do While FileName <> ""
        'Open the workbook
        Set Wkb = Workbooks.Open(Path & FileName)
        
        'Loop through each worksheet in the opened workbook
        For Each Ws In Wkb.Worksheets
            'Copy the used range from the source sheet
            Ws.UsedRange.Copy
            
            'Find the next empty row in the Master worksheet
            NextRow = MasterWksht.Cells(MasterWksht.Rows.Count, "A").End(xlUp).Row + 1
            
            'Paste the data into the master sheet
            MasterWksht.Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues
            'Copy header on first paste
            If NextRow = 1 Then
                Ws.UsedRange.Copy
                MasterWksht.Cells(1, 1).PasteSpecial Paste:=xlPasteAll
            End If

        Next Ws
        
        'Close the source workbook without saving changes
        Wkb.Close False
        
        'Get the next file in the folder
        FileName = Dir()
    Loop
    
    Application.CutCopyMode = False
    
    MsgBox "Data from all files has been successfully combined."

End Sub

How to Use This VBA Script:

1. Open the Excel workbook you want to use as your master file.
2. Press Alt + F11 to open the VBA editor.
3. In the menu, go to Insert > Module. A blank code window will appear.
4. Copy and paste the entire script into this module.
5. Critically important: In the line Path = "C:\Users\YourName\Documents\Monthly Sales Reports\", change the path to match the location of your source file folder. Ensure there is a backslash (\) at the end.
6. Press Alt + F11 again to close the editor.
7. To run the macro, press Alt + F8, select "CombineFiles" from the list, and click Run. The macro will then execute, combining the data for you.

Final Thoughts

Consolidating your Excel files is the first critical step in moving from painful data wrangling to effective data analysis. By using a robust tool like Power Query, you can say goodbye to endless copy-pasting and trust that your summary report is always accurate and up-to-date with just one click.

We built our platform to solve this exact problem, but on a much broader scale. The same frustrations of scattered data don't just happen with spreadsheets, they happen across all your business apps like Google Analytics, Shopify, and Stripe. Getting answers usually means downloading tons of CSVs and spending hours unifying data. At Graphed, we connect directly with all those data sources with a single click. This lets you ask questions about your business in normal English and instantly build real-time dashboards, completely skipping the spreadsheet gymnastics.