Training By Himanshu

Tag: learn Google Sheets

  • 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 Create a Dependent Drop Down List in Google Sheets (Dynamic & Easy)

    🔄 How to Create a Dependent Drop Down List in Google Sheets

    A dependent drop-down list means that the options in the second dropdown depend on the selection made in the first. This is especially useful for things like selecting a category and sub-category, country and state, etc.

    ✅ Step-by-Step Example:

    Let’s say you want this setup:

    📋 Source Data:

    CategorySub-Items
    FruitsApple, Banana, Mango
    VegetablesCarrot, Spinach
    BeveragesTea, Coffee

    🔧 Step 1: Set Up Your Lists

    Use a new sheet (e.g., named "Lists"):

    makefileCopyEditA1: Fruits       B1: Apple    C1: Banana   D1: Mango  
A2: Vegetables   B2: Carrot   C2: Spinach
A3: Beverages    B3: Tea      C3: Coffee

    🔧 Step 2: Name Each Range

    1. Select B1:D1 (Apple, Banana, Mango).
    2. Go to Data > Named ranges, name it Fruits.
    3. Do the same for:
      • B2:C2 → Name it Vegetables
      • B3:C3 → Name it Beverages

    📝 Important: The named range must match exactly with the text in your first dropdown.

    🔧 Step 3: Create the First Dropdown (Main Category)

    1. In your main sheet, click on cell A1.
    2. Go to Data > Data validation.
    3. Under Criteria, choose List of items and type:
    CopyEditFruits,Vegetables,Beverages

    Click Done.

    🔧 Step 4: Create the Dependent Dropdown

    1. Click on cell B1 (where the dependent dropdown will go).
    2. Go to Data > Data validation.
    3. Under Criteria, choose Custom formula is.
    4. Enter:
    excelCopyEdit=INDIRECT(A1)

    ✅ This tells Google Sheets: “Get the named range based on the value in A1.”

    Click Done.

    🔍 How It Works:

    • When you select “Fruits” in A1 → B1 will show Apple, Banana, Mango
    • If you select “Vegetables”, you’ll see Carrot, Spinach in B1

    🚀 Want to Learn Google Sheets Like a Pro?

    This kind of powerful, dynamic logic is covered step-by-step in our Google Sheets course!

    🔗 ✅ Enroll Now – Unlock the Power of Google Sheets

    💥 Special Price: ₹1,299 → Just ₹449!

    🎯 What You’ll Learn:

    • ✅ 29 step-by-step video tutorials
    • 🕒 3 hours 46 minutes of practical content
    • 🔎 From formulas & charts to scripts, automation, and dashboards
    • 👨‍🎓 Perfect for beginners and professionals alike

    📈 Start automating, organizing, and analyzing data like an expert.

    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