With most Excel spreadsheets, you enter data manually into cells and then use formulas or other functions to analyze it or perform calculations. If you have a large data source, such as an Access database, a SQL Server database or even a large text file, you can also retrieve data from it using Excel. Using SQL statements in Excel enables you to connect to an external data source, parse field or table contents and import data – all without having to input the data manually. Once you import external data with SQL statements, you can then sort it, analyze it or perform any calculations that you might need.
1. Open Microsoft Excel and then create a new workbook file or open an existing file to which you want to connect an external data source.
2. Click “Data” on the Excel Ribbon. Click the “From Other Sources” icon in the Get External Data section. Click “From Microsoft Query” on the drop-down menu.
3. Click the type of your data source in the Choose Data Source window. Click and enable the “Use the Query Wizard to create/edit queries” option, and then click “OK.” The Connecting to Database window will appear first, and then a few seconds later the Select Database file browser window will appear.
4. Browse to the folder and file for your database or data source file. Highlight the file name of the data source and click “OK.” The Query Wizard box will appear on the screen.
5. Click and select the table in the data source that contains the fields you want to query with SQL and import into your Excel spreadsheet. Click the “>” button in the middle of the Query Wizard window to populate the Columns in Your Query pane with field names from the selected table in your data source. Click the “Next” button to continue.
6. Select filter options for the data to retrieve and display in the spreadsheet if you desire. By creating a filter for data in one or more fields, you instruct Excel to retrieve only data from the data source that meets certain conditions or criteria. For instance, if your data source contains a list of customers and their contact information, you might have a field in the table for telephone numbers. If you only wanted to retrieve customers from the data source that have a (919) area code, you can do so by applying a filter. Click the “Phone_Number” or other similarly named field in the Column to Filter pane and select “contains” in the filter type list. Enter "919" in the next field and press “Next.”
7. Select an ascending or descending sort order for the records to retrieve from the data source. Click the “Next” button. Enable the “Return Data to Microsoft Excel” option and click the “Finish” button.
8. Click and enable the “Table” option in the Import Data window. Enable the “Existing Worksheet” option and click the “red arrow” icon to the right of the cell field under the Existing Worksheet label. Click and select the cell where you want to position the top right corner of the data table containing records from the external data source.
9. Click “OK.” Excel displays a table with retrieved records that return as a result of the underlying SQL query of the data source by the Query Wizard.
10. View the SQL query to see how the code retrieves data from the external data source. Click “Existing Connections” on the Data tab. Click the “Query from Type of Data Source’” icon in the Connections in this Workbook section of the Existing Connections window. The Import Data window will appear on the screen.
11. Click the “Properties” button. In the Connection Properties window, click the “Definition” tab. Locate the text in the Command Text box. The native SQL query code appears there. For a SQL query that retrieved records from an external table labeled “Personal_Contacts,” the code would appear similar to the following: SELECT tbl_Personal_Contscts.ID, tbl_Personal_Contacts.Contact_Name, tbl_Personal_Contscts.Phone_Number, tbl_Personal_Contscts.Email_Address FROM `C:\Users\NameOfUser\Documents\Database1.accdb`.tbl_Personal_Contacts tbl_Personal_Contacts
12. Click the “OK” button to close the Connection Properties window. Edit other data in the spreadsheet and save the workbook as needed.
- Instead of connecting to an existing data source, you can also create a new data source in the Choose Data Source window. If you select the “New Data Source” option, you can select the type of data source you want to connect and open a compatible database program, such as Microsoft Access or Microsoft FoxPro, directly from within Excel.
- Thinkstock/Comstock/Getty Images