📎 This article includes 1 downloadable practice file ↓
In this article
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.
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.
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).
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.
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.
Stuck on a step? Ask a question and the AI answers using this article.