How to Use SQL Statements in MS Excel
Using Structured Query Language (SQL) statements, you can retrieve data from data collections categorized into tables, records and fields. Use Microsoft Query in Excel to get data from external sources so you don't have to retype information you need to analyze using Excel functions. You can retrieve data from Access and SQL Server as well as Excel workbooks and text files. Using SQL statements in Excel typically includes connecting to an external database, creating the query by selecting columns of data, importing the data and using the data to conduct analysis.
Open a new Microsoft Excel file.
Set up a data source by clicking the "Data" tab and then clicking the "From Other Sources" option. Click the "From Microsoft Query" option and then specify the data source type or click the "New Data Source" and click the "OK" button. Click the "Connect" option and the supply the authentication information required to connect to the data source. If your database has tables, click the table you want to use in the Query Wizard. You can select the "Save my user ID and password in the data source definition" check box to save your logon information. Select the name of your data source, once it shows in the "Choose Data Source" dialog box. Open the data source.
Click the table and columns you want in your SQL query in the "Query Wizard - Choose Columns" and click the ">" button so that the columns appear in the "Columns in your query" area. Click the "Next" button. Then, filter the data, if necessary, and click the "Next" button. Specify how you want the data sorted and then click the "Next" button.
Click the toggle button to edit query in Microsoft Query and click the "Finish" button. From the "Microsoft Query" window, click "View" menu and then select the "SQL..." option. Once the SQL statement displays, you can edit it. Click the "OK" button when you are done.
Click the "File" menu and select the "Return data to Microsoft Excel" option to get the data into the blank worksheet.
Select how you want to view the data, for example as a table. Click the "OK" button.
Analyze your data. For example, sort your data from smallest to largest.