How to Use QUERY Function of Google Sheets in Excel (3 Common Ways)

Google Sheets offers a versatile way to work with data in your spreadsheets. The QUERY function is one of the most powerful functions in Google Sheets. It takes a range of cells as input and then runs a Google Visualization API QUERY Language QUERY on that data. The result is a dynamic and interactive data table that can be sorted, filtered, and explored. However, we can also do similar sorts of operations in Microsoft Excel that can be done with the QUERY function in Google Sheets. In this article, I am going to explain how to use QUERY function of Google Sheets in Excel. I hope it will be very helpful for you to understand some of the common operations in different platforms.


Download Practice Workbook


Operation of QUERY Function in Google Sheets

There are a few different things you can do with the QUERY function in Google Sheets.

  1. Find data that meets certain criteria
  2. To make calculations on data
  3. To manipulate data in various ways

One common use for the QUERY function is to find data that meets certain criteria. For example, you could use it to find all the rows in a sheet that contain a certain word or phrase. To do this, you would specify the criteria in the QUERY function, and it would return all the rows that match that criteria.

Another common use for the QUERY function is to make calculations on data. For example, you could use it to find the average of a column of data or to sum up all the values in a column. To do this, you would specify the calculation you want to perform in the QUERY function, and it would return the result of that calculation.

You can also use the function to manipulate data in various ways. For example, you could use it to sort data in a sheet or to filter data so that only certain rows are visible. To do this, you would specify the manipulation you want to perform in the QUERY function, and it would perform that manipulation on the data.

QUERY Function Syntax in Google Sheets

The syntax for the QUERY function is as follows:

=QUERY(range, select statement)

The range is the range of cells that you want to select data. The select statement is used to specify what data you want to select.


3 Common Ways to Use QUERY Function of Google Sheets in Excel

We can do some similar sorts of work in both Google Sheets and Excel. QUERY function is a very effective tool in Google Sheets. Similar types of work we can do in Excel.

For more clarification, I am going to use a dataset where I have mentioned employees’ personal information in different columns.

Google Sheets Query Function in Excel


Operation 1: Find Data with Certain Criteria in Excel

We can find data from a table in Google Sheets with the QUERY function. We can do similar in Excel too. But in different ways. I am going to discuss them in the following section.


1.1 Create a Similar Whole New Table

In order to create  a duplicate of a table in Google Sheets, we can use the following formula:

=QUERY(B4:F14,"select *")

We can do similar things in Excel with the following procedures.

Steps:

  • Select the entire table and Copy the entire table.

Create a Similar Whole New Table

  • Then, pick a suitable location and give the Paste command with CTRL + V. You can use other ways to paste it too.

Google Sheets Query Function in Excel

Thus, we have a copied table that can be done with QUERY in Google Sheets.


1.2 Create a Table with Certain Columns

We can also create a table with certain columns from a table. For this purpose in Google Sheets, The following formula is used:

=QUERY(B4:F14, "select B,E,F")

Where, B4:F14 is the entire table and select B,E,F is that columns name that I want.

Steps:

  • First of all, select the columns by clicking on the columns numbers. Keep in mind that you have to keep pressing on CTRL button.

Create a Table with Certain Columns

  • Then, click on the column number on which column you want to place it.

  • Finally, press CTRL + V to paste the selected columns.

Google Sheets Query Function in Excel

Thus, we can create a table with certain columns from a table

Read More: How to Import Data from Google Sheets to Excel (3 Easy Ways)


Operation 2: Perform Mathematical Calculations in Excel

A mathematical calculation can also be done with the help of the QUERY function. All the steps are described below.


Calculate Average, Minimum, and Maximum Values

We can calculate Average, Minimum, and Maximum Values in Google Sheets with QUERY.

We just need to apply the following formula.

=QUERY(range,"SELECT MAX(column_name), MIN(column_name), AVG(column_name)")

 We can do similar things in Excel too.

Steps:

  • Select a cell and apply the following formula to calculate the average in the range D5:D14.
=AVERAGE(D5:D14)

Perform Mathematical Calculations in Excel

 

  • Similarly, input the following formula to calculate a minimum value in the range D5:D14.
=MIN(D5:D14)

  • Along with that, input the following formula to calculate the maximum value in the range D5:D14.
=MAX(D5:D14)

Google Sheets Query Function in Excel

Read More: How to Convert Google Sheets to Excel with Formulas


Operation 3: Manipulate Data in Excel

With the help of the QUERY function, data manipulation can be done in Google Sheets. They are described below.


Sort Data in Descending Order in a Table

To sort data in descending order in Google Sheets, we can use the following formula with the QUERY function.

=QUERY(B4:F14, "select * order by A desc",1)

We can do similar things in Excel. The steps are given below.

Steps:

  • Select the entire table.
  • Go to Data.
  • From the Sort & Filter, choose Sort Largest to Smallest.

Manipulate Data in Excel

Thus, we can sort data with just a simple click in Excel.

Google Sheets Query Function in Excel

Read More: How to Import Data from Google Sheets to Excel Using VBA


Practice Section

For more expertise, you can practice here.

Practice Section


Conclusion

At the end of this article, I like to add that I have tried to explain some common operations on how to use QUERY function of Google Sheets in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo