About Microsoft Excel

Get External Data from another Excel File

Home Back Next



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.

Further Reading

Home Back Next