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.
- Find data that meets certain criteria
- To make calculations on data
- 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.
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:
We can do similar things in Excel with the following procedures.
- Select the entire table and Copy the entire table.
- Then, pick a suitable location and give the Paste command with CTRL + V. You can use other ways to paste it too.
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.
- First of all, select the columns by clicking on the columns numbers. Keep in mind that you have to keep pressing on CTRL button.
- Then, click on the column number on which column you want to place it.
- Finally, press CTRL + V to paste the selected columns.
Thus, we can create a table with certain columns from a table
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.
- Select a cell and apply the following formula to calculate the average in the range D5:D14.
- Similarly, input the following formula to calculate a minimum value in the range D5:D14.
- Along with that, input the following formula to calculate the maximum value in the range D5:D14.
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.
- Select the entire table.
- Go to Data.
- From the Sort & Filter, choose Sort Largest to Smallest.
Thus, we can sort data with just a simple click in Excel.
For more expertise, you can practice here.
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.