Unlocking Potential of Google Sheets QUERY Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

The QUERY function of Google Sheets is renowned for its flexibility and powerful data querying abilities. But what about those who prefer using Excel? Well, today we will learn how to use the potential of Google Sheets QUERY function in Excel.

In today’s fast-paced digital world, data analysis, and management have become essential skills for professionals across various industries. The QUERY function has revolutionized the way users interact with data in Google Sheets.

In this article, we will explore how you can leverage the Google Sheets QUERY function in Excel, enabling you to unlock new possibilities for data analysis, extraction, and reporting.

Google Sheets QUERY Function in Excel


Introduction to QUERY Function in Google Sheets

  • Objective

To retrieve and manipulate data from a specified range or table based on a specified query or condition.

  • Syntax
=QUERY(data, query, [headers])
  • Argument
Argument Required/Optional Value
data Required This refers to the range of cells or the table from which you want to extract data. It can be specified using the A1 notation (e.g., Sheet1!A1:B10) or as a named range.
query Required This is the query or condition that determines which data to retrieve from the specified range. It follows a SQL-like syntax with various clauses and operators. The query can include filtering conditions, sorting instructions, grouping, aggregation functions, and more.
headers optional This parameter specifies the number of header rows in the data range. By default, the value is 1, indicating that the first row contains column headers. If your data does not have headers, you can set this parameter to 0.
  • Output

A new table or range of cells that displays the results of the query applied to the specified data range.


Comparing Google Sheets and Excel

As we are going to use the potential of Google Sheets QUERY function in Excel, we need to know the similarities and key differences between these two applications. Both of these applications have some pros and cons in various aspects.

 ➡ Similarities between Google Sheets and Excel

  • Spreadsheet Structure: Both Google Sheets and Excel are spreadsheet applications that organize data into rows and columns.
  • Basic Formulas and Functions: They both offer a wide range of built-in formulas and functions for performing calculations and data manipulation.
  • Formatting and Styling: Similar formatting options are available in both applications, allowing users to customize cell styles, colors, borders, and more.
  • Collaboration and Sharing: Both Google Sheets and Excel provide collaboration features, enabling multiple users to work on the same spreadsheet simultaneously and share it with others.

 ➡ Key differences in functionality and formulas

  • Cloud-Based vs. Desktop Application: Google Sheets is a cloud-based application that requires an internet connection, while Excel is a desktop application that runs on a local machine.
  • QUERY Function: The Query function is a powerful data querying tool native to Google Sheets, allowing users to extract and filter data based on specified criteria. Excel does not have a built-in equivalent function, which makes it challenging to replicate the exact functionality.
  • Add-Ons and Extensions: Google Sheets offers a wide range of add-ons and extensions through its marketplace, providing additional functionalities and integrations. Excel also has add-ins, but the options are more limited.
  • Macros and VBA: Excel supports Visual Basic for Applications (VBA), which enables users to create macros and automate tasks. Google Sheets does not have native support for VBA, although it does provide some automation capabilities through Google Apps Script.

 💡 Challenges of using Google Sheets functions in Excel

  • Syntax Differences: The syntax and structure of formulas can vary between Google Sheets and Excel. This means that formulas or functions written in Google Sheets may not work directly in Excel without modifications.
  • Formula Compatibility: Google Sheets may have certain formulas or functions that are not available in Excel, or they might behave differently. This can impact the compatibility and portability of spreadsheets between the two platforms.
  • Data Source Integration: Google Sheets has seamless integration with other Google services, such as Google Drive and Google Forms. Excel, on the other hand, integrates well with other Microsoft Office applications and external data sources.
  • Learning Curve: Transitioning from Google Sheets to Excel or vice versa might require some adjustment and learning, especially when it comes to utilizing different functions, features, and shortcuts.

Unlocking Potential of Google Sheets QUERY Function in Excel: 6 Examples

So far we have learned that the QUERY function returns a new table or range of cells as output that displays the results of the query applied to the specified data range. The QUERY function of Google Sheets is not available in Microsoft Excel, so we can’t directly use it here. But we can use some Excel features and functions to get the same output obtained from the QUERY function. Let’s explain with some examples how we can unlock the potential of the Google Sheets QUERY function in Excel.


1. Excel Equivalent of QUERY Function: Copy Whole Range

Let’s say, we have some sales information in the range B4:E12 and we want to copy the whole range to somewhere else in the sheet. Besides using copy-paste, you can use the Google Sheets QUERY function to select all columns. The following QUERY function copies the whole data range.

=QUERY(B4:E12, "SELECT*")

The query statement is enclosed in double quotation marks. The asterisk (*) is a wildcard that represents all columns in the selected range. So, this statement is requesting all columns from the specified range.

Select all columns and copy whole range with Google Sheets QUERY function

If you want to select all the columns in Excel, you can use a combination of INDEX, ROW, and COLUMN functions. The following formula will act the same as the above QUERY function in Google Sheets. Apply it in cell G4 and then drag the Fill Handle through the rows and columns to get your desired output.

=INDEX($B$4:$E$12, ROW()-3, COLUMN(A1))

 💡 Formula Breakdown

  • ROW()-3: returns the current row number of the cell where the formula is entered, and subtracting 3 from it adjusts the row number to be relative to the range B4:E12. For example, if the formula is in row 4, the result of ROW()-3 would be 1, which corresponds to the fourth row (B4:E4) of the specified range.
  • COLUMN(A1): returns the column number of the reference provided. In this case, the reference is A1, which is the first column of the range B4:E12. So, the result of COLUMN(A1) is 1.
Note: We have used absolute reference for the range $B$4:$E$12 so that the reference doesn’t change while dragging the formula.

Select all columns and copy whole range with Excel equivalent of QUERY function

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


2. Excel Equivalent of QUERY Function: Select Specific Column

If you want to select specific columns in the QUERY function, you have to define the column names in the SELECT statement. For the dataset below, if we want to select columns B, D, and F, we have to use the following QUERY formula.

=QUERY(B4:F14, "SELECT B, D, F")

Select specific columns with Google Sheets QUERY function

If you want to perform the same operation in Excel for specific columns, apply the following array formula.

=INDEX($B$4:$F$14,ROW($B$4:$F$14)-3,{1,3,5})

This formula will select column 1,3,5 from the specific range $B$4:$F$14.

Select specific columns with Excel equivalent of QUERY function


3. Excel Equivalent of QUERY Function: Extracting Values Based on Condition

In the dataset, both “Male” and “Female” persons are included. If we apply a condition, for example, we only want data for “Male” sellers only, the Google Sheets QUERY function can easily extract data based on this condition. Here, the function uses the “WHERE” statement.

=QUERY(B4:E14, "SELECT * WHERE C = 'Male'")

This formula finds the text string “Male” in column C and returns all the corresponding row data.

Extract values with Google Sheets QUERY function based on condition

Excel has a special feature that can perform the same task as Google Sheets QUERY function with the “Where” statement: Filter feature.

  • Click on the header row and press CTRL+SHIFT+L. That will insert Filter in the header.
  • Click on the dropdown arrow of the desired column where you want to apply the condition (i.e., Gender).

Filter shortcut in Excel

  • Select the desired filter (i.e., Male).
  • Click OK.

Filtering values

This will make only the data of “Male” persons visible.

Filtering and extracting values based on condition in Excel

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


4. Excel Equivalent of QUERY Function: Performing Mathematical Calculations

We can calculate Average, Minimum, and Maximum Values in Google Sheets with QUERY. If we want to calculate those values from the “Sales” column (D), then you can use the following formula to get the maximum, minimum and average of sales.

=QUERY(B4:F14,"SELECT MAX(D), MIN(D), AVG(D)")

Mathematical calculation with QUERY function in Google Sheets

On the other hand, we can’t find these values in Excel with a single formula. Excel has MAX, MIN and AVERAGE functions for getting these values.

For the maximum value of sales:

=MAX(D5:D14)

For the minimum value of sales:

=MIN(D5:D14)

For the average value of sales:

=AVERAGE(D5:D14)

Mathematical calculation in Excel

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


5. Excel Equivalent of QUERY Function: Sorting Data

By applying the QUERY function in Google Sheets, you can sort your data in ascending or in descending order. For the dataset, if we want to sort the data according to “Sales” column in descending order, apply the following formula with “desc” statement.

=QUERY(B5:E14, "select * order by D desc")

Sorting data with Google Sheets QUERY function

But Excel has a built-in SORT function which is used to sort data both in ascending and descending order. The following formula will work out to serve your purpose.

=SORT(B5:E14,3,-1,FALSE)

Here, the second argument 3 means we want to sort column 3 and 3rd argument “-1” indicates we want to sort by descending order.

Excel SORT function for sorting data


6. Excel Equivalent of QUERY Function: Counting Numbers of Rows Based on Specific Criteria

Suppose, we want to count the number of rows on a specific criteria. We only want to count the data of “Male”. In that case, the following formula will count cell from column B that meets the criteria “Male” in column C.

=QUERY(B5:F14, "SELECT COUNT(B) WHERE C = 'Male'")

Count numbers of rows based on criteria with Google Sheets QUERY function

In Excel, the COUNTIF function counts numbers on specific criteria. The following formula will perform a similar operation to Google Sheets.

=COUNTIF(B5:E14,"Male")

Frequently Asked Questions

1. Are there any limitations or differences when using the QUERY function in Excel compared to Google Sheets?

Since the QUERY function is specific to Google Sheets, it is not directly available in Excel. The differences and limitations will depend on the alternative functions and methods you use in Excel to achieve similar results.

2. Can I use the same syntax and parameters in the QUERY function in Excel as in Google Sheets? 

The syntax and parameters used in the QUERY function are specific to Google Sheets and are not directly applicable to Excel. When using alternative functions in Excel, you will need to refer to the documentation and syntax guidelines for those functions.

3. Are there any specific examples or use cases where the QUERY function in Excel is particularly useful?  

Although the QUERY function is not available in Excel, there are various scenarios where alternative functions can be useful. For example, you can use the FILTER feature to extract specific rows based on conditions, or the SORT function to sort your data. The choice of function will depend on the specific requirements of your data analysis task.


Takeaways from this Article

  • The QUERY function is a powerful tool in Google Sheets that allows you to retrieve, filter, and manipulate data using SQL-like syntax.
  • Unfortunately, the QUERY function is not available in Excel. However, there are alternative functions such as FILTER, SORT, SUMIFS, and COUNTIFS that can help you achieve similar results.
  • When using Excel, it’s important to familiarize yourself with the available functions and their syntax to effectively filter, sort, and perform calculations on your data.
  • While you can’t directly use the QUERY function in Excel, you can combine multiple functions and techniques to accomplish complex data analysis tasks.
  • It’s essential to understand the limitations and differences between the QUERY function in Google Sheets and the alternative functions in Excel to choose the right approach for your data analysis needs.

Download Practice Workbook

You can download the practice book from the link below.


Conclusion

The QUERY function in Google Sheets offers a powerful way to retrieve, filter, and manipulate data from a range using SQL-like queries. By using the QUERY function in Google Sheets, users can extract specific data subsets, apply complex filters, sort and aggregate data, and even perform calculations within the query itself.

The QUERY function is not directly available in Excel, but it has alternatives that can replicate the task of the Google Sheets QUERY function. Excel has its own strengths for data analysis and reporting. So, by unlocking the potential of Google Sheets QUERY function in Excel, users can achieve similar results.


Related Articles

<< Go Back to Import Google Sheets to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo