How To Use Microsoft Query In Excel

To use Microsoft Query in Excel, open the Data tab, launch From Microsoft Query, connect to a data source, then choose the columns, filters and sort order you need. Return the results to a worksheet and refresh the connection when the source changes. If the.

Check your data source before starting

Microsoft Query retrieves external data for analysis in Excel. It is useful for repeatable reports: you define which records to retrieve once, then run the same query again when you need updated results.

This walkthrough uses desktop Excel on Windows. Have the database or file location, the appropriate connection driver and permission to read the data ready. For a company database, ask its administrator which server, database and sign-in method to use.

Microsoft Query and Power Query have different interfaces. The Get Data commands for importing from a database, file or website may open Power Query instead. For this walkthrough, select the command explicitly named From Microsoft Query. Microsoft's data import options guide explains how to expose the legacy commands.

If you are also setting up Office, the guides to activating Microsoft Office using Command Prompt and finding an Office product key using Command Prompt cover those separate setup tasks. Database access still requires the correct connection details and permissions.

Open Microsoft Query and connect to your data

1. Open the workbook that will hold your results.
2. If Microsoft Query is hidden, select File > Options > Data. Under Show legacy data import wizards, enable Microsoft Query (Legacy), then select OK. Close and reopen the workbook if the command does not appear.
3. Select Data > Get Data > Legacy Wizards > From Microsoft Query (Legacy). In older ribbon layouts, look under Data > From Other Sources > From Microsoft Query.
4. In Choose Data Source, select an existing source or choose New Data Source. For a new source, enter a recognisable name, select the appropriate driver and click Connect.
5. Supply the server or file location and the authentication details requested by the driver. Complete the connection dialogue to return to the source list.
6. Select Use the Query Wizard to create/edit queries, choose your source and select OK.

A data source definition stores connection information; the query specifies what to retrieve. Reusing a source saves you from entering the same connection details for every report. Microsoft's Microsoft Query guide covers source setup and the wizard.

Select, filter and import the records you need

1. In the Query Wizard, expand the relevant table or view and add the columns required for your report. Leave out fields you will not use.
2. Apply filters to restrict the results. For a regional sales report, for example, select the required region rather than importing every region and hiding unwanted rows afterwards.
3. Choose the sort columns and whether each should be ascending or descending. Use a date field when you need records in chronological order.
4. At the final wizard screen, choose Return data to Microsoft Excel and click Finish.
5. In Excel's import dialogue, choose the destination worksheet and starting cell, then confirm the import. Use a clear area with room for the results to grow.

Before building a report, compare a few returned records with the source. Check that dates and amounts have the expected values and that your filters include the intended records. An empty result can mean that no rows match the criteria, even when the connection works.

Check joins before combining tables

When you add related tables, check how their fields connect. A customer identifier might link a customers table to an orders table. Joining on a name instead can produce incorrect matches if names repeat.

Also decide what each result row should represent. One customer with several orders can legitimately appear several times. If you need one row per customer, summarise the orders before treating the result as a customer list.

Refine a query with parameters or SQL

At the wizard's final screen, choose View data or edit query in Microsoft Query when you need more control. Use the graphical editor to adjust criteria and joins, or open View > SQL to inspect the statement. SQL functions and syntax depend on the database and driver, so start with a working query before adding calculations or grouping.

Prompt for a different filter value

A parameter lets you reuse a query for different regions, categories or dates. Rather than editing a fixed filter each time, the query asks for a value when it runs.

1. Open the query in Microsoft Query and select View > SQL.
2. In the WHERE clause, replace the fixed comparison value with a question mark or a descriptive prompt enclosed in square brackets, such as [Enter region]. Keep the actual field name and comparison operator appropriate to your source.
3. Select OK, enter a test value when prompted and inspect the results before returning the data to Excel.

Microsoft's parameter query instructions explain both prompt formats. Choose wording that tells the person running the report what input is expected.

Refresh the data and build your report

To retrieve updated records, right-click inside the imported results and select Refresh. Use Data > Refresh All when you intend to update the workbook's connections together. Refresh reruns the query; it does not make the worksheet a continuously live view of the database.

For automatic refresh options, open Data > Queries & Connections, select the Connections tab, right-click the relevant connection and choose Properties. On the Usage tab, look for refresh on opening the file or refresh at a specified interval. Available options depend on the connection. Microsoft's connection properties reference describes these controls.

Build formulas, charts or a PivotTable from the imported results once you have checked them. After refreshing the source data, refresh any dependent PivotTables as needed. Avoid manually correcting values inside the imported range: a later refresh can replace those edits.

Resolve connection and result problems

• If the source is unavailable, check the server or file location and any required network connection.
• If sign-in fails, confirm the authentication method and read permissions with the database administrator.
• If the driver is missing, obtain the appropriate driver for your database and Excel installation.
• If rows are missing or duplicated, inspect the filter criteria and table joins before changing the report.
• If retrieval is slow, reduce unnecessary columns and records before adding more calculations or joins.

For related spreadsheet tasks, browse the Microsoft Office guides. Start your first query with one table and a small selection of columns, check the returned records, then run a manual refresh before expanding the report.