How to Use MS Query in Excel

By Shreya Mehta

MS Query can use Excel to retrieve data from external sources such as databases and files. Importing the data into Excel saves the user time, as she can simply analyze the data without having to retype all of the data in Excel. Another great feature is that when the data in the original source gets updated, the updated data is reflected in Excel as well.

Step 1

Open a new document in Excel. Go to the "Data" tab and click on "From Other Sources." Select the option "From Microsoft Query."

Step 2

Double-click on the option "New Data Source" in the Databases tab. Type a name you would like to give the data source. Select a driver for the type of database that you are using for the data source. Click on the "Connect" button.

Step 3

Provide the necessary information when prompted, and click "OK." This will depend on the type of driver that was selected. For example, if your data source is an Excel file, you will be asked to select a workbook.

Step 4

Click on the empty field next to the "Create New Data Source" dialog box if you have a table in your database that you would like to display automatically in the Query Wizard. Click on the tables you would like to display in the Query Wizard.

Step 5

Check off the box labeled "Save my user ID and password in the data source definition" if you didn't type in your user ID and password when using the data source. The new data source will now appear in the list under "Choose Data Source."

Step 6

Go to the Data tab and click on "Refresh All" under "Connections" whenever you need to refresh the data in Excel from the original source.