Google Workspace Lesson 5: Sheets Formulas Excel Doesn’t Have

Google Workspace Lesson 5: Sheets Formulas Excel Doesn’t Have featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min readUpdated 12 October 2026

📘 Google Workspace Beginner Course · Lesson 5 of 6

In this article
  1. QUERY: SQL in a cell
  2. ARRAYFORMULA: one formula for a whole column
  3. IMPORTRANGE: pull data from another file
  4. Handy extras
  5. Named functions and LAMBDA
  6. Where beginners go wrong
  7. Practice
  8. Continue learning

Sheets has a few functions with no direct Excel equivalent, and they’re the main reason some teams prefer it. Learn these and you’ll do in one formula what takes several steps elsewhere.

Advertisement

QUERY: SQL in a cell

=QUERY(Orders!A1:H61, "select C, sum(G) group by C order by sum(G) desc", 1)
=QUERY(Orders!A1:H61, "select A, D, G where C = 'North' and G > 50000", 1)

Columns are letters, text values in single quotes, the last argument is the number of header rows. Totals, filters and sorting in one formula. Full guide: QUERY for Excel users.

ARRAYFORMULA: one formula for a whole column

=ARRAYFORMULA(IF(F2:F="", "", F2:F * 1.18))

Fills every row, including future ones, from a single cell. (Excel 365’s dynamic arrays do similar things.)

IMPORTRANGE: pull data from another file

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/…", "Orders!A1:H")

The first time, click Allow access. Combine with QUERY to bring in only what you need.

Advertisement

Handy extras

=SPLIT("Mumbai-MH-400001", "-")                     ' split into columns
=GOOGLETRANSLATE("Thank you for your order", "en", "hi")
=SPARKLINE(G2:G21)                                    ' tiny chart in a cell
=IMAGE("https://…/logo.png")                          ' picture in a cell
=GOOGLEFINANCE("NSE:INFY", "price")                   ' delayed market price
=DETECTLANGUAGE(A2)

Insert › Checkbox gives TRUE/FALSE cells for task lists; count them with =COUNTIF(H2:H, TRUE).

⚠️ GOOGLEFINANCE data can be delayed and is for information only, not trading. Sheets-only functions break when the file is downloaded to Excel.

Named functions and LAMBDA

Data › Named functions lets you save a formula as your own function (like Excel’s LAMBDA): e.g. GST(amount).

Where beginners go wrong

Problem Cause
QUERY returns blanks for some rows Mixed text and numbers in a column; QUERY uses the majority type
#REF! with IMPORTRANGE Access not yet allowed
ARRAYFORMULA “would overwrite data” Something is typed in the output range

More: IMPORTRANGE with QUERY.

Advertisement

Practice

Download the practice file below and upload it to Google Drive (File › Import in Sheets). Its Tasks sheet lists each formula to try on the Orders data.

📎 Practice files for this article

  • 📗
    Practice sheet (import into Google Sheets)60 orders plus a Tasks sheet: QUERY, FILTER, UNIQUE, SPLIT, ARRAYFORMULA, GOOGLETRANSLATE, SPARKLINE and more.
    ⬇ XLSX · 8 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong