How to Import Data into Excel from Another Excel File (2 Ways)

You may believe that just copying and pasting data from another file into Excel would suffice to extract data. However, changing the source worksheet has no effect on the data. You’ll need to import data to make dynamic value changes. In this tutorial, we will show you how to import data into Excel from another Excel file.

How to Import Data into Excel from Another Excel File: 2 Handy Approaches

We’ve included a sample data set with a workbook entitled Subtotal Pivot Table in the image below. This worksheet’s data will be imported into another workbook. To achieve this, we’ll use the Power Query first. Later on, in another method, we will run VBA Codes to import data.

1. Apply Power Query to Import Data into Excel from Another Excel File

Firstly, we will import a table from another workbook in our current workbook Import Data from Another File. To add any data without editing, read the below section.

1.1 Import a Table from Another Excel File

Step 1: Use the Data Tab

Firstly, click on the Data.
Select the Get Data.
Choose the From File.
Then, choose the From Excel Workbook option from the list.

Step 2: Select the Source File

Select the source file.
Then, click on Import.

As a result, the existing Tables and Sheets will appear as Navigator.
Select Table1 from the list and check the preview on the right side.
Finally, click on Load.

As a result, as seen in the figure below, your Table will be imported into your current worksheet.

1.2 Import Data from Another Sheet and Edit Data

If you need to edit your imported data, follow the outlined steps below to edit or change it in The Power Query Editor.

Step 1: Use the Data Tab Feature

From the Data tab, click on the Get Data.
Then, choose the From File
Finally, select the From Excel Workbook option from the list.

Step 2: Selection of Source File

Select your source file to import.
Then, click on Import.

Step 3: Get the Preview in Navigator

As the Navigator window pops up, select Sheet1 as we want to import data from Sheet1.
Then, click on Transform Data.

Consequently, you will see the following image in the Power Query Editor.

Step 4: Remove Additional Rows

To remove the top row, click on the Remove Top Rows.

Type your preferred number(1) in the Number of rows box.
Press Enter.

Therefore, you will see the following result with the removed top row.

To remove the bottom row, click on the Remove Rows.
Select the Remove Bottom Rows.

Type your preferred number(1) in the Number of rows box.
Then, press Enter.

As a consequence, the result will appear with no additional bottom rows.

Additionally, click on the Use First Row as Headers to make the first row of the Table the Header.

As shown in the image below, the first row will transform into a Header.

Finally, click on the Close & Load option to see the data in the current workbook.

As a result, imported data will appear in the current worksheet.

Notes: If you make any changes to your source file data after importing it, the data will be updated in your current worksheet after refreshing. Importing data and connecting various workbooks has this benefit. Follow the outlined steps to understand the following example.

Steps: Update Imported Data from Source File

Firstly, change the source file data to 28 and save it.

Go back to the current workbook, and right-click on the cell.
Click on the Refresh.

Therefore, the changed data will be updated to 28 according to the source file.

2. Run a VBA Code to Import Data into Excel from Another Excel File

Additionally, you can apply VBA codes to import data from another file. Follow the simple steps below to do so.

Step 1: Insert a Module

Firstly, press Alt + F11 to start the VBA Macro.
From the Insert tab select the Module.

Step 2: Paste VBA Codes

In the Module box, paste the following VBA codes.

Sub ImportData()
'Define variables for workbooks
    Dim CurrentWkb As Workbook
    Dim SourceWkb As Workbook
'Define variables for source
    Dim SourceRng As Range
    Dim DestinationRng As Range
'Set value for your current workbook
    Set CurrentWkb = ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
'Define file types for selection
        .Filters.Add "Excel 2008-19", "*.xlsx; *.xlsm; *.xlsa"
'Give statement to 'False' for not allowing selection of multiple files
        .AllowMultiSelect = False
        .Show
'Apply If condition
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set SourceWkb = ActiveWorkbook
'Insert Input Box for selection source range
            Set SourceRng = Application.InputBox(prompt:="Import source range", Title:="External Source Range", Default:="A1", Type:=8)
            CurrentWkb.Activate
'Enter Input box for selection for destination range
            Set DestinationRng = Application.InputBox(prompt:="Select your destination", Title:=" Click any cell selecting Destination", Default:="A1", Type:=8)
'Import the source range to the current workbook
            SourceRng.Copy DestinationRng
            DestinationRng.CurrentRegion.EntireColumn.AutoFit
            SourceWkb.Close False
        End If
    End With
End Sub

Step 3: Run the VBA Codes

Save the program and press F5 to run it.
Select the source file to import.

Step 4: Select Range from the Source File

Then, select the range to import data.
Click on OK.

Step 5: Place the Imported Data in the Current Worksheet

Click any cell as the Destination Cell.
Finally, click on OK.

As a consequence, your data from the other file will be imported into the current file, as seen in the figure below.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Import Data from Another Excel.xlsm

Conclusion

To conclude, I hope this article has given you a tutorial about how to import data into Excel from another Excel file. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

Stay with us & keep learning.

<< Go Back to Importing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!