Naimul Hasan Arif
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.
Download Practice Workbook
You can download the practice book from the link below.
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
- 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.
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 column 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.
Read More: How to Import Data from Google Sheets to Excel (3 Easy Ways)
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, F, we have to use the following QUERY formula.
=QUERY(B4:F14, "SELECT B, D, F")
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.
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.
Excel has a special feature that can perform the same task like 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).
- Select the desired filter (i.e., Male).
- Click OK.
This will make only the data of “Male” persons visible.
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 maximum, minimum and average of sales.
=QUERY(B4:F14,"SELECT MAX(D), MIN(D), AVG(D)")
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)
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")
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.
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'")
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.
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.
