How do I automatically import data into Excel?

You can easily copy or import Excel worksheet data into Word documents. When you import from an Excel file, you can choose to import linked or unlinked data. If it's linked, you will be able to update, change and remove the links.

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.

Table of Contents hide

Download Practice Workbook

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

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

1.1 Import a Table from Another Excel File

1.2 Import Data from Another Sheet and Edit Data

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

Conclusion

Related Articles

Download Practice Workbook

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

Import Data from Another Excel.xlsm

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.

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 the number on the row.
• 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 the number on the row.
• Then, press Enter.

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

• Additionally, click on the Use First Row as Headers for making 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.

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

Similar Readings

• Excel Formula to Get First 3 Characters from a Cell(6 Ways)
• Transfer Data from One Excel Worksheet to Another Automatically
• VBA Code to Convert Text File to Excel (7 Methods)
• How to Import Text File with Multiple Delimiters into Excel (3 Methods)
• Excel VBA: Pull Data Automatically from a Website (2 Methods)

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.

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.