Import data into Excel from another Excel file

You can import data into an Excel worksheet using text files, databases and another Excel file. The process can help data entry as well as data analysis. Although importing data from another Excel file appears easy there can be some complications if you don’t set the right options. You can use a microsoft query to import data from another Excel worksheet and save the query. This helps in updating the imported data in case the original file data is changed. Also the imported data can be saved as a template which gets populated with the relevant data once the query is run.

The import of data is implemented as follows:

  • Click on the data menu in the ribbon
  • Then click on ‘Get External Data’ and from the sub menu select ‘from other sources
  • From the drop down options select ‘From Microsoft Query’
  • In the ‘Choose Data Source’ window click on ‘Excel files*’ and ‘OK’.
  • Then select the file from which you wish to import the data. Click ‘OK’. In the ‘Query Wizard’ window you will see the sheets of the file listed only if you have selected ‘Tables’ and ‘System Tables’ via the ‘Options…’ feature.
  • Next you can select the fields that you wish to import, filter the data based on criteria and also sort the data
  • Now you can save the query with an appropriate name and then select ‘Return Data to Microsoft Excel’ option button
  • Finally click finish to import the data starting from a cell address of your choice

You can also save the file with the imported data as an Excel template file which will be filled with data when you open it again and run the saved query!

Further reading:
Import data from one excel spreadsheet to another?

One thought on “Import data into Excel from another Excel file”

  1. would you be so kind to help me with something a little bit more complicated 🙂 i need to make a button that has asigned macro that imports into a main excel file the data in the second columns from multiple excel files, basicaly i need a button that i click and a small window to apears in wich i select from a list of existing items(in this case products) defined in numbers (ex. 1 to 10)(each number is asigned to a diferent directory adress) and when i click any number the file from that directory opens( that contains multiple excel files) and i click on one excel file in that directory just the the second column is copied and pasted into my main(let’s say from the 4th column onwards, first 3 are ocupied with other data, and when i click the buton again it will copy the data from another file also onto the next column unocupied column of the main and so on… kind regards to you and anticipated thanks!

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.