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.


Download Practice Workbook

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


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

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.

Sample 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.

Handy Approaches to Import Data into Excel from Another Excel File

Step 2: Select the Source File

  • Select the source file.
  • Then, click on Import.

Handy Approaches to Import Data into Excel from Another Excel File

  • 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.

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

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.

Handy Approaches to Import Data into Excel from Another Excel File

Step 2: Selection of Source File

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

Handy Approaches to Import Data into Excel from Another Excel File

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.

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

Step 4: Remove Additional Rows

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

Handy Approaches to Import Data into Excel from Another Excel File

  • Type the number on the row.
  • Press Enter.

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

  • Type the number on the row.
  • Then, press Enter.

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

  • Additionally, click on the Use First Row as Headers for making the first row of the Table the Header.

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

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.

Handy Approaches to Import Data into Excel from Another Excel File

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

Handy Approaches to Import Data into Excel from Another Excel File

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

Sample Data

Read More: How to Import Data from Text File into Excel (3 Methods)


Similar Readings


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.

Sample Data

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

Sample Data

Step 3: Run the VBA Codes

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

Sample Data

Step 4: Select Range from the Source File

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

Sample Data

Step 5: Place the Imported Data in the Current Worksheet

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

Sample Data

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

Sample Data

Read More: Import Data from Excel into Word Automatically Using VBA (2 Ways)


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.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo