Get External Data from another Excel File

We can get external data from another Excel file to create interesting reports.
How to implement the transfer of data from one Excel file into another:

Click on the Data tab
From the ‘Get External Data’ command group click on ‘From Other sources’
From the drop down options select ‘From Microsoft Query’
A new window ‘Choose Data Source’ pops up
Select ‘Excel Files*’ from the Databases tab
The check-box ‘Use the Query Wizard to create/edit queries’ at the bottom of the window is checked by default
Click on OK
A new window called ‘Select Workbook’ opens
Under the Database Name text box you can see ‘*.xls*’ which represents all Excel files or workbooks
Under Directories you can view the path to My Documents folder c:\… \my documents
Below this you can see that My Documents folder is open
Left of this is a multi-line text box showing Excel files from the folder My Documents
We select the file ‘sort-data-microsoft-excel.xlsx’
Click on OK
A new window opens with the title Query Wizard – Choose Columns
Below that you can read ‘What columns of data do you want to include in your query?’
Below that on the left is written: Available tables and columns:’
Below you can see +Sheet1$, +Sheet2$ and +Sheet3$
Click on the +sign next to Sheet2$
Many field names or headers come into view
Click on the item Name of student and select ‘>’ to place the header under ‘Columns in your query:’
Click on the iten ‘Average Marks and click on ‘>’ to place the header under ‘Columns in your query:’
Click on the button Options… and ensure that the check-boxes ‘Tables’, ‘Views’, ‘System Tables’ and ‘Synonyms’ under ‘show’ in the Table Options windoware selected. The most important check box is ‘System Tables’.
Click on Name of student in the right under ‘Columns in your query:’
Click on ‘Preview Now’ button at the botton on the left and you’ll see a preview of the data in the text box below ‘Preview of data in selected column:’ If you can’t see the preview you’ve done something incorrectly. You’ll notice that if the item ‘System Tables’ in the Table Options is not selected then no preview is available. In fact, your Query Wizard – Choose Columns window is blank!
Click on Next
The Query Wizard – Filter Data window opens. You see the remark below the title: Filter the data to specify which rows to include in your query. If you don’t want to filter the data click next.
We’ll click Next
In the new window Query Wizard – Sort Order we’ll select ‘Average Marks’ under Sort by and then select the option ‘Descending’
Click Next
In the window Query Wizard – Finish we are asked the question: What would you like to do next? We have two options: Return Data to Microsoft Excel or View data or edit query in Microsoft Query
We can also ‘Save the Query…’ for future use
We click on the finish button
A new window called Import Data opens
Under ‘Select how you want to view this data in your workbook’ the Table option is automatically selected and you have two more options
We first click on ‘Properties…’ button at the bottom left
The Connection Properties window opens where you can set some interesting properties like: ‘Refresh every minutes’ and ‘refresh data when opening the file’. The option Enable background refresh is selected by default
Click on OK and you are back to Import data window where you are offered to put data starting in Cell $A$1. But we have data in this cell and we therefore select cell A3 by clicking in the worksheet
Finally we click OK
Our data is imported as a table starting in cell A3
We select the Average Marks data and format it to 2 decimal places

In this manner we have imported the data that we need from one Excel file into another Excel file.

View the Excel training video:

Further Reading:
Pull External Data into Excel

3 thoughts on “Get External Data from another Excel File”

    I thank you very much for your interest to educate us EXCEl.
    I am doing cloth trading business
    In EXCEL workbook I have the account detail of my customers in different sheet in the same workbook.
    I want to link the whole sheet from master work book to individual customer workbook.
    I need this because I have to send the link of the customer workbook for sharing with my customer from my DROPBOX A/c.

  2. Hello,

    I have used the following expression for copying data from one excel to another, but when i closing the source file then i am getting the following error message #REF!.
    Where C1 – is the source of the file.
    D,E,I – is the cell that consists value
    Can you please provide the solution for the above problem.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.