Training By Himanshu

Tag: Google Sheets Tips

  • Mastering QUERY Function in Google Sheets: Complete Guide with Examples & Interview Questions

    The QUERY function in Google Sheets is one of the most powerful and versatile tools available. It allows you to perform SQL-like data manipulations—filtering, sorting, aggregating, and grouping—on your spreadsheet data.

    📌 QUERY Function Syntax

    QUERY(data, query, [headers])

    🔹 Arguments:

    1. data: The range of cells that you want to query.
    2. query: A string written in a pseudo-SQL format.
    3. headers (optional): The number of header rows at the top of your data (default is 1).

    🧠 Why Use QUERY?

    It combines the power of multiple functions like FILTER, SORT, VLOOKUP, SUMIF, UNIQUE, and even PIVOT TABLES—all in one.

    ✅ Basic Examples

    Assume we have the following data in A1:D6:

    NameAgeDepartmentSalary
    John25Sales30000
    Alice30HR35000
    Bob24Sales28000
    Carol29Marketing40000
    Dave35HR38000

    🔹 1. Select All Rows

    =QUERY(A1:D6, "SELECT *", 1)

    🔸 Returns the full table.

    🔹 2. Select Specific Columns

    =QUERY(A1:D6, "SELECT A, C", 1)

    🔸 Returns only Name and Department columns.

    🔹 3. Filtering Rows (WHERE Clause)

    =QUERY(A1:D6, "SELECT A, D WHERE C = 'Sales'", 1)

    🔸 Shows Name and Salary of employees in Sales department.

    🔹 4. Using Comparison Operators

    =QUERY(A1:D6, "SELECT A, B WHERE D > 30000", 1)

    🔸 Returns Name and Age of employees earning more than 30,000.

    🔹 5. Sorting (ORDER BY)

    =QUERY(A1:D6, "SELECT A, D ORDER BY D DESC", 1)

    🔸 Returns Name and Salary sorted by Salary in descending order.

    🔹 6. Grouping and Aggregating (GROUP BY)

    =QUERY(A1:D6, "SELECT C, AVG(D) GROUP BY C", 1)

    🔸 Calculates average salary per department.

    🔹 7. Labeling Columns

    =QUERY(A1:D6, "SELECT C, AVG(D) GROUP BY C LABEL AVG(D) 'Average Salary'", 1)

    🔸 Adds a custom label to the aggregated column.

    🔹 8. Limit Results

    =QUERY(A1:D6, "SELECT * LIMIT 3", 1)

    🔸 Returns only the first 3 rows.

    🔹 9. Combining WHERE and ORDER BY

    =QUERY(A1:D6, "SELECT A, D WHERE D > 30000 ORDER BY D DESC", 1)

    🔸 Filters employees with salary > 30,000 and sorts them in descending order.

    🔹 10. Dynamic Query with Cell Reference

    =QUERY(A1:D6, "SELECT A, D WHERE D > "&E1, 1)

    🔸 Assuming cell E1 has the value 30000, this will filter dynamically.

    ⚠️ Notes:

    • Text values in queries must be enclosed in single quotes (‘ ‘).
    • Numbers and cell references can be added directly.
    • QUERY is case-insensitive by default.

    🎯 Common Use Cases

    • Creating dashboards
    • Creating dynamic reports
    • Filtering datasets based on dropdown selections
    • Summarizing large data tables
    • Converting flat data into summarized views like pivot tables

    📘 Real-Life Example:

    Imagine a school with student records:

    StudentClassSubjectMarks
    Rahul10Math85
    Sneha10Science90
    Aman11Math78
    Priya10Math92

    To find average marks in each subject for class 10:

    =QUERY(A1:D5, "SELECT C, AVG(D) WHERE B = 10 GROUP BY C", 1)

    🔸 Returns Math and Science with their average marks for class 10 students.

    💼 Top 10 Interview Questions on Google Sheets QUERY Function

    1. Q: What is the QUERY function in Google Sheets?
      A: It allows you to use SQL-like queries to filter, sort, group, and summarize data.
    2. Q: How do you filter records using a text value in QUERY?
      A: Use WHERE column = 'Text', e.g., WHERE C = 'Sales'.
    3. Q: How can you sort data using QUERY?
      A: Use ORDER BY clause: ORDER BY column [ASC|DESC].
    4. Q: What does GROUP BY do in QUERY?
      A: It aggregates values (e.g., SUM, AVG) based on unique groups in a column.
    5. Q: How do you rename column headers in the QUERY result?
      A: Use the LABEL clause: LABEL AVG(D) 'Average Salary'.
    6. Q: What’s the difference between SELECT * and SELECT A, B?
      A: SELECT * selects all columns; A, B selects only specific columns.
    7. Q: How can you use a cell reference in a QUERY?
      A: Concatenate it: "SELECT A WHERE B > "&E1
    8. Q: Can you use OR and AND in QUERY filters?
      A: Yes. Example: WHERE B > 25 AND C = 'HR'
    9. Q: What happens if you omit the headers parameter?
      A: QUERY assumes the first row is the header by default (1).
    10. Q: How is QUERY different from FILTER function?
      A: FILTER is simpler and only filters data. QUERY is more powerful with sorting, aggregation, grouping, and SQL-like operations.

    Featured products

  • How to Delete All Rows with Specific Text in Google Sheets (Manual + Script)

    🗑️ How to Delete All Rows Containing Specific Text in a Column in Google Sheets

    When working with large datasets, you may need to delete all rows where a certain word or value appears in a specific column — like removing all rows where column B says "Cancelled".

    You can do this manually, with filters, or use Google Apps Script to automate it.

    ✅ Method 1: Use Filter to Delete Rows Containing Specific Text (Manual)

    Steps:

    1. Select your data range.
    2. Go to Data > Create a filter.
    3. Click the filter icon in the target column (e.g., Column B).
    4. Uncheck all and select only the value you want to delete (e.g., "Cancelled").
    5. Select the filtered rows by clicking the row numbers.
    6. Right-click > Delete selected rows.
    7. Turn off the filter.

    ✅ Best for: Small to medium datasets.

    ✅ Method 2: Use Google Apps Script (Automatic & Reusable)

    If you want a repeatable way to delete rows based on a value, use this simple script.

    🔧 Script to Delete Rows Containing Specific Text:

    1. Click Extensions > Apps Script.
    2. Paste this code:
    javascriptCopyEditfunction deleteRowsWithText() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const columnToCheck = 2; // Column B (use 1 for A, 2 for B, etc.)
  const textToDelete = "Cancelled";
  const data = sheet.getDataRange().getValues();
  
  for (let i = data.length - 1; i >= 0; i--) {
    if (data[i][columnToCheck - 1] === textToDelete) {
      sheet.deleteRow(i + 1);
    }
  }
}
    1. Save and click ▶️ Run.

    ✅ Customizable: Change columnToCheck and textToDelete as needed.

    ✅ Why loop backward? It prevents row shifting issues while deleting.

    🚀 Want to Learn All These Google Sheets Hacks (and More)?

    If you’re enjoying these powerful techniques, you’ll love our full Google Sheets training!

    🔗 💡 Unlock the Power of Google Sheets – Enroll Now

    🎯 Limited-Time Offer: ₹1,299 → ₹449 only!

    📘 Course Highlights:

    • 🎥 29 video lessons
    • 🕒 3 hours 46 minutes total duration
    • 🔍 Beginner to advanced: formulas, pivot tables, scripts, data analysis
    • 🧑‍🏫 Learn through real-world examples and practical demos

    Whether you’re managing business reports, automating tasks, or cleaning data — this course helps you master Google Sheets efficiently.

    Top rated products

  • How To Combine Multiple Columns Into One Single Column In Google Sheets?

    Whether you’re organizing survey data, merging name fields, or stacking column values into a single list — Google Sheets offers multiple ways to combine multiple columns into one.

    ✅ Scenario:

    Suppose you have this data:

    ABC
    TomAliceJohn
    SamRaviRiya

    You want to create a single vertical list like:

    nginxCopyEditTom  
Sam  
Alice  
Ravi  
John  
Riya

    🔹 Method 1: Use the FLATTEN Function (Quick & Easy)

    Formula:

    excelCopyEdit=FLATTEN(A1:C2)

    Result:

    It combines all the values from the range A1:C2 into a single column.

    📝 Note: FLATTEN() reads the data row-wise, moving left to right, top to bottom.

    🔹 Method 2: Using ARRAYFORMULA with SPLIT and JOIN

    If your data is dynamic or you want to control delimiters, use this formula:

    excelCopyEdit=TRANSPOSE(SPLIT(JOIN(",", A1:C2), ","))

    ✅ What It Does:

    • JOIN(",", A1:C2) → Converts your 2D data into one comma-separated string.
    • SPLIT(..., ",") → Splits it back into individual elements.
    • TRANSPOSE(...) → Converts the horizontal array into a vertical column.

    🔹 Method 3: Stack Columns Vertically Using FILTER

    If you want to stack entire columns (e.g., A, B, C) but remove blank cells, use:

    excelCopyEdit=FILTER(A:A, A:A <> "")

    Repeat for B and C, or stack together like:

    excelCopyEdit={FILTER(A:A, A:A <> ""); FILTER(B:B, B:B <> ""); FILTER(C:C, C:C <> "")}

    This ensures that blank cells are skipped and data appears vertically.

    🚀 Ready to Master Google Sheets from Basics to Brilliance?

    🎓 All of these tricks and tons more are explained with step-by-step videos in my bestselling Google Sheets course:

    🔗 🔓 Unlock the Power of Google Sheets – Enroll Now!

    💥 Limited-Time Offer: ₹1,299 → ₹449 only!

    📘 Course Highlights:

    • 29 expertly crafted videos
    • 3 hours and 46 minutes of quality content
    • Covers basic to advanced topics (formulas, pivot tables, automation, scripts)
    • Ideal for students, professionals, freelancers, business users

    💡 Learn how to organize, automate, and analyze your data like a pro.

    Top rated products

  • How to Combine Date and Time in Google Sheets (No Add-ons Needed)

    📅⏰ How To Combine Date And Time Columns Into One Column In Google Sheets?

    When working with separate date and time columns, you may need to merge them into a single datetime format. Here’s how to do it effortlessly:

    ✅ Example Setup

    A (Date)B (Time)
    26/06/202510:30 AM

    You want column C to show:
    26/06/2025 10:30 AM

    ✅ Method 1: Use a Simple Formula

    In cell C2, use this formula:

    excelCopyEdit=A2 + B2

    What it does:
    In Google Sheets, dates and times are stored as numbers. Adding a date and time simply combines them.

    ✅ Step-by-Step Instructions

    1. Ensure that column A has dates (26/06/2025) and column B has times (10:30 AM).
    2. In column C, enter: =A2+B2
    3. Format column C:
      • Click Format > Number > Custom date and time
      • Use the format: dd/mm/yyyy hh:mm AM/PM or any style you prefer.

    You now have a complete datetime column.

    ⚠️ Common Issues & Fixes

    • Wrong format? → Apply a custom datetime format from the Format menu.
    • #VALUE! error? → Ensure both columns contain valid date and time values.

    🚀 Want to Master Google Sheets from Start to Finish?

    📣 Learn this and hundreds of other practical skills in my complete Google Sheets course:

    🔗 Enroll Now – Unlock the Power of Google Sheets

    💰 Special Offer: ₹1,299 ₹449 (Limited Time Only!)

    🎓 What’s Inside:

    • ✅ 29 value-packed videos
    • ⏱ 3 hours 46 minutes of step-by-step tutorials
    • 📊 Covers formulas, pivot tables, data analysis, automation, and more!
    • 🧠 Easy-to-follow explanations + real-world examples

    👨‍🏫 Whether you’re a student, working professional, or entrepreneur — this course will empower your productivity and data skills.

    Related Products

  • How to Get a List of Sheet Names in Google Sheets (Step-by-Step)

    Google Sheets doesn’t offer a built-in formula to list sheet names directly like Excel VBA might. However, you can achieve it easily using Google Apps Script. Here’s how:

    ✅ Step 1: Open Google Apps Script

    1. Open your Google Sheets file.
    2. Click on Extensions > Apps Script.

    ✅ Step 2: Paste This Script

    In the script editor, paste the following code:

    javascriptCopyEditfunction listSheetNames() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const sheetNames = sheets.map(sheet => [sheet.getName()]);
  
  const outputSheetName = "Sheet List";
  let outputSheet = ss.getSheetByName(outputSheetName);
  
  if (!outputSheet) {
    outputSheet = ss.insertSheet(outputSheetName);
  } else {
    outputSheet.clear();  // Clear old data
  }
  
  outputSheet.getRange(1, 1, sheetNames.length, 1).setValues(sheetNames);
}

    ✅ Step 3: Save and Run

    1. Click the 💾 Save icon and name your project.
    2. Click the ▶️ Run button.
    3. If prompted, authorize the script to access your spreadsheet.

    ✅ What Happens Next?

    • A new sheet called “Sheet List” will be created (or updated).
    • It will display the names of all sheets in your file—automatically.

    🚀 Want to Master Google Sheets from A to Z?

    🎯 Whether you’re just starting out or looking to boost your spreadsheet superpowers, our premium Google Sheets course is your gateway to mastery.

    🔗 Enroll Now – Unlock the Power of Google Sheets

    🔥 Offer: ₹1,299 ₹449 (Limited-Time Deal)

    What You’ll Get:

    • 📹 29 videos totaling 3 hours 46 minutes
    • ✅ From beginner basics to advanced data analysis
    • 📈 Learn formulas, pivot tables, data validation, automation & more
    • 💼 Ideal for students, professionals, entrepreneurs

    💡 With real-world examples and practical exercises, you’ll quickly become confident in handling data, automating tasks, and making smarter decisions.

    Related Products

  • How to Generate QR Codes in Excel and Google Sheets (Step-by-Step Guide)

    You can generate QR codes in Excel (Microsoft 365) and Google Sheets easily using built-in features or free add-ons. Here’s a detailed guide for both platforms:

    ✅ In Microsoft Excel (Microsoft 365)

    🔸 Method 1: Using Excel Add-in – “QR4Office”

    📌 Steps:

    1. Open Excel and go to the Insert tab.
    2. Click on “Get Add-ins” (or Office Add-ins).
    3. Search for “QR4Office” and click Add.
    4. Once added, go to Insert → My Add-ins → QR4Office.
    5. A QR code generator pane will appear on the right.

    🎯 To Generate a QR Code:

    • Enter the text or URL you want to convert.
    • Adjust size, color, and error correction level.
    • Click Insert — the QR code will appear in your sheet as an image.

    🔸 Method 2: Using a Web API (Google Chart API)

    You can generate QR codes dynamically using a formula with an image from an online API.

    📌 Steps:

    1. Use this formula in a cell:
    =IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & A2)

    ✅ Replace A2 with the cell that has the text or link you want to turn into a QR code.

    📝 chs=150x150: Size of the QR code
    📝 chl=: The data encoded in the QR code

    Note: Excel’s IMAGE function is available in Microsoft 365 versions only.

    ✅ In Google Sheets

    📌 Steps:

    1. In a cell, enter this formula:
    =IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & A2)

    ✅ Replace A2 with the reference cell containing the text or URL you want in the QR code.

    The QR code will appear in the cell as an image.

    🧠 Extra Tips:

    • You can drag the formula down to generate QR codes for an entire list.
    • You can use ENCODEURL(A2) inside the formula to safely encode special characters:
    =IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & ENCODEURL(A2))

    Top rated products