How to write a SQL query in excel with an example

Last Updated: 2023-01-08 23:24:19

Excel is a popular spreadsheet application that is commonly used to store and analyze data. While Excel is not a database, it does have some limited capabilities for accessing and manipulating data in a structured way. writing a SQL query in Excel can be a powerful tool for working with and analyzing data stored in an Excel worksheet. It can help you to quickly and efficiently extract and transform data, allowing you to focus on analyzing and interpreting the results.

 

To write a SQL query in Excel, you can use the QUERY function. This function allows you to execute a SQL-like language to manipulate data in an Excel worksheet. Here is the syntax for the QUERY function:

 

QUERY(data, query, [headers])

data is a range of cells that contains the data you want to query.

the query is the query you want to execute, written in a SQL-like language.

headers is an optional argument that specifies whether the first row of data contains headers. If headers are set to 1, the first row will be treated as headers; if it is set to 0, the first row will not be treated as headers.
 

Here is an example of how you can use the QUERY function to write a simple SQL query in Excel:

=QUERY(A2:D10, "SELECT * WHERE A = 'Apple'", 1)

This query will select all columns (*) from the range A2:D10 where the value in column A is 'Apple'. The 1 at the end of the query indicates that the first row contains headers.

You can also use the QUERY function to perform more complex operations, such as sorting, grouping, and joining data from multiple tables. For more information on the QUERY function and the syntax of the SQL-like language it uses, you can refer to the Microsoft Office documentation or search online for tutorials and examples.

 

An example of how to write a SQL query in Excel using the QUERY function:

Imagine that you have an Excel worksheet with the following data:

     A       B       C
1 | Name  | Age | Gender
2 | John  | 25  | M
3 | Maria | 30  | F
4 | Bill  | 35  | M
5 | Sarah | 40  | F

This worksheet has a table with three columns: Name, Age, and Gender. The first row contains headers. If you want you can learn how to create a table in SQL

Suppose you want to select only the names of the people who are male. You can use the following QUERY function to do this:

=QUERY(A2:C5, "SELECT A WHERE C = 'M'", 1)

This query will select column A (the Name column) from the range A2:C5 where the value in column C (the Gender column) is 'M'. The 1 at the end of the query indicates that the first row contains headers.

 

The result of this query will be a new table with only the names of the male individuals:

     A
1 | Name
2 | John
3 | Bill

You can use the QUERY function to perform other operations as well, such as sorting and grouping data, or combining data from multiple tables. For more information on the QUERY function and the syntax of the SQL-like language it uses, you can refer to the Microsoft Office documentation or search online for tutorials and examples.

 

In summary, the QUERY function in Excel allows you to execute a SQL-like language to manipulate data in an Excel worksheet. To use the QUERY function, you need to specify the data to be queried and write a query using the SQL-like language. The QUERY function is useful for tasks such as selecting specific columns or rows of data, sorting and filtering data and performing calculations on data.

 

By learning how to write a SQL query in Excel, you can gain a powerful tool for working with and analyzing data stored in an Excel worksheet. Whether you are a business analyst, data scientist, or simply someone who works with data on a regular basis, learning how to write a SQL query in Excel can help you to more efficiently and effectively extract and transform data, allowing you to focus on analyzing and interpreting the results.