How to Use SQL Statements in MS Excel? (original) (raw)

Last Updated : 01 Nov, 2023

Most Excel spreadsheets need you to manually insert data into cells before analyzing it or performing calculations using formulae or other functions. You may use Excel to get data from a big data source, such as an Access database, an SQL Server database, or even a huge text file. SQL statements in Excel allow you to connect to an external data source, parse fields or table contents, and import data without having to manually enter the data.

After importing external data using SQL commands, you may sort, analyze, and conduct any necessary computations. Here, we will be discussing how to execute SQL statements in MS Excel. For this, an open-source package called 'xlwings' is required. So, before we begin with the process of running SQL queries in MS Excel, we will have to install xlwings. For running SQL queries in MS Excel using xlwings, having Windows OS and Python is a must.

**Install Xlwings

Make sure you have installed pip for Python beforehand. If not, refer to this GeeksforGeeks link. Once you have installed pip, open your **Command Prompt type **pip install xlwings, and hit **Enter. Once this command is executed completely, type **xlwings add-in install and **hit Enter. Now, open **Excel, and you'll find xlwings section added.

**SQL Queries in Excel

**Step 1: Creation of Tables in Excel.

For the execution of **SQL queries in Excel, in this article, two tables have been created in Excel (same workbook) and will be used for demonstration of the same. The two tables are - **Employee Table and **Department Table, as depicted below:

**Table 1: Employee Table.

Employee-Table.

Employee Table

**Table 2: Department Table.

Department-Table

Department Table

**Step 2: Write the SQL query in Excel.

**Type in the SQL query to be executed in Excel. (You may first Merge and center the cells and then type in the SQL query).
**Note: When only one table is being referred to, use 'a'/'A' for referring to it. If there are two tables, for example, when Joins are used, use ****'a'/'A'** for the **first table and use ****'b'/'B'** for referring to the **second table.

SQL-Query-sample.

SQL Query

**Step 3: Running the SQL query in Excel.

For executing the SQL query, type in **=sql( in a **new cell, where you need the retrieved data to be displayed. Then, click on the Insert Function option, displayed to the **left of the Formula Bar.

insert-function

Executing Query

On clicking the Insert Function option, a dialog box appears, which requires 2 inputs - **Query and Tables. For the Query input, select the SQL query cell (above step) or simply manually type in the query to be executed.

For the Tables input, **hold and drag the entire table to be used for the SQL query. If there is more than one table, add the table(s) in a similar fashion in the Tables input. After this, click on the Ok button, and presto, the data is retrieved!

function-argument-shown

**Output: Now you can see the output of the SQL Query.

sql-query-output

output

**More Sample SQL Queries in Excel

**SELECT STATEMENT SQL: The **SELECT statement is used to get information from a database. The information returned is saved in a result table known as the result set.

**Select statement syntax: SELECT Age FROM a

select-query

Output

SELECT Name, Gender FROM a

selecting-name

Output

**WHERE CLAUSE SQL: To filter records, use the WHERE clause. It is used to extract only records that meet a predefined requirement.

**Where clause syntax: SELECT * FROM a WHERE Gender = 'Female'

where-condition-applied

Output

**OR OPERATOR: The **OR operators are used to filter records based on several criteria. If any of the conditions **separated by OR is TRUE, the OR operator shows a record.

**Or operator syntax: SELECT * FROM a WHERE Gender = 'MALE' OR Age < 40

or-operator

Output

**NOT OPERATOR: If the **condition(s) is NOT TRUE, the NOT operator shows a record.

**Not operator syntax: SELECT * FROM a WHERE NOT Gender = 'Female'

not-operator

Output

**MIN() FUNCTION: The **MIN() method returns the column with the lowest value.

Min function syntax: SELECT MIN(Age) FROM a

It shows the data of minimum age only

Output

**AVERAGE FUNCTION SQL: AVG() returns the average value of a numerical column.

Avg function syntax: SELECT AVG(Age) FROM a

It shows the data of average age only.

Output

**GROUP BY STATEMENT: The **SQL GROUP BY clause is used in conjunction with the SELECT statement to group identical data. In a SELECT statement, the GROUP BY clause comes after the WHERE clause and before the ORDER BY clause.

**Group By statement syntax: SELECT AVG(Salary) AS Avg_Sal, Gender FROM a GROUP BY Gender

group-by-query

Output

**SQL INNER JOIN: The INNER JOIN keyword selects records from both tables that have matching values.

**Inner join syntax: SELECT a.Name,a.Dept,b.D_Name,b.D_City FROM an INNER JOIN b ON a.Dept=b.D_Name

applying-inner-join

Output