How to use an SQL query in Microsoft Excel

Microsoft Excel is one of the most commonly used programs for data management and analysis. However, sometimes it can be difficult to achieve desired results using only the program's standard functions.

A solution to this problem is using an SQL query in Excel. An SQL query allows you to extract data from a database and manipulate it to obtain specific information.

In this guide, we will explain how to use an SQL query in Microsoft Excel to extract data from an external database and analyze it within the program.

What is an SQL query?

SQL (Structured Query Language) is a programming language used for managing databases. With an SQL query, you can select, insert, update or delete data within a database.

SQL queries are composed of commands that are sent to the database to perform specific operations on the data. These commands include SELECT (to select data), INSERT (to insert new data), UPDATE (to update existing data) and DELETE (to delete data).

How to use an SQL query in Microsoft Excel

To use an SQL query in Microsoft Excel, you need to have access to the external database's data. This means that the database must be accessible through an ODBC (Open Database Connectivity) server.

• Step 1: Open Microsoft Excel and select the "Data" tab.
• Step 2: Select "From Other Sources" and then "From ODBC".
• Step 3: Select the external database from the dropdown menu and click "Connect".
• Step 4: Enter the database access credentials (if required) and select the desired table(s).
• Step 5: Click "OK" to import the data into the Excel worksheet.
• Step 6: Select the cell where you want to insert the SQL query results.
• Step 7: Select the "Data" tab and click "From Other Sources".
• Step 8: Select "From ODBC" and choose the external database from the list.
• Step 9: Enter the SQL query in the dialog box and click "OK".

At this point, the results of the SQL query will be displayed in the selected cell. You can use Excel's standard functions to manipulate the data as needed.

Example of using an SQL query in Microsoft Excel

Suppose we have a database containing information about employees of a company, including their name, surname, hire date and salary. We want to create an SQL query to select all employees hired after a certain date and display only their names and surnames.

The SQL query would be as follows:

After running this query in Excel, only the names and surnames of employees hired after January 1st, 2020 will be displayed.

Creating a new SQL query

To create a new SQL query in Microsoft Excel, follow these steps:

• Step 1: Click on the "Data" tab in the ribbon.
• Step 2: Select "From Other Sources" in the "Get External Data" group.
• Step 3: Choose "From ODBC" from the drop-down menu.
• Step 4: In the "Select Data Source" dialog box, select the ODBC data source you want to use and click "OK".
• Step 5: In the "Import Data" dialog box, select the option "Use SQL Query" and click "OK".

This will bring up the "Enter SQL Statement" dialog box. Here you can type or copy/paste your SQL query from another source.

Here are some examples of SQL queries that can be used in Microsoft Excel:

• Example 1: SELECT * FROM table_name WHERE column = value;
• Example 2: SELECT column1, column2 FROM table_name WHERE column3 = value;
• Example 3: SELECT AVG(column) FROM table_name WHERE column2 = value;

After typing or pasting your SQL query, click "OK" to return to the "Import Data" dialog box.

In the "Import Data" dialog box, select the option "Table" and specify where you want to insert the data in your workbook. Click "OK" to import the data from the SQL query.

Using SQL syntax to filter data

Structured Query Language (SQL) is a language used to manage and manipulate data in a database. In Microsoft Excel, SQL syntax can be used to filter data within a table.

Basic Syntax

The SQL syntax for filtering data in Excel requires the use of the "QUERY" function. The basic structure of the function is as follows:

Where "table_reference" is the range of cells containing the data to be filtered and "sql_query" is the SQL query that defines the filter conditions.

Practical Example

Suppose we have a table containing information about company employees, including name, surname, age, and salary. We want to filter only employees with a salary greater than 2000 euros per month.

• Select an empty cell and type "=QUERY(A1:E10,"SELECT * WHERE E > 2000")"
• Press ENTER and we will see only rows corresponding to employees with a salary greater than 2000 euros per month.

In this example, we used the SQL query "SELECT * WHERE E > 2000". The keyword "SELECT" indicates that we want to select all columns in the table, while "WHERE" indicates the filter condition. In this case, we are selecting only rows where the value in column E (the salary) is greater than 2000 euros.

SQL syntax can be used to create more complex and customized filters, depending on the user's needs. It is important to have a good understanding of SQL syntax to fully utilize this feature in Excel.