Top 5 Advanced Functions for Data Analysis in Google Sheets
August 7th, 2026
Once you are past SUM, IF, and basic lookups, data analysis in Google Sheets is mostly about reshaping tables: filter without destroying the source, aggregate with readable criteria, spill results down a column, and enrich rows from other sheets. These five functions are the ones that show up again and again in real analysis work — each one with a short example and a link to go deeper.
1. QUERY — SQL-style analysis in a cell
QUERY runs a small SQL-like language over a range. Filter, sort, group, and aggregate in one formula instead of stacking FILTER + SORT + SUMIF.
=QUERY(A1:E500, "SELECT A, SUM(E) WHERE C = 'EU' GROUP BY A ORDER BY SUM(E) DESC", 1)
That returns each product (column A) sold in region EU, total revenue (column E), sorted high to low — treating row 1 as headers. Columns inside the query string are lettered relative to the range you pass in, not absolute sheet letters.
Use QUERY when you need multi-step analysis (filter + group + sort) in one place. For a full walkthrough of SELECT, WHERE, GROUP BY, LABEL, and PIVOT, see Mastering the QUERY Function in Google Sheets.
2. FILTER — keep only the rows that matter
FILTER returns a live subset of a range. The source stays untouched; the result spills as an array you can chart, look up against, or feed into another formula.
=FILTER(A2:D1000, C2:C1000 = "active", D2:D1000 >= 1000)
This keeps rows where status is active and amount is at least 1000. Multiple conditions are ANDed when you pass them as separate arguments. Combine with SORT for ranked views without helper columns:
=SORT(FILTER(A2:D1000, C2:C1000 = "active"), 4, FALSE)
That filters active rows, then sorts by column 4 descending. FILTER is the right default when you want a dynamic slice of a table; reach for QUERY when you also need grouping or SQL-style aggregation. More patterns, including dashboards with lookups, are in How to Use FILTER + XLOOKUP for Dynamic Dashboards.
3. ARRAYFORMULA — one formula for an entire column
ARRAYFORMULA makes single-cell formulas operate on whole ranges, so you stop dragging fill handles and breaking them when rows are inserted.
=ARRAYFORMULA(IF(A2:A1000="", "", B2:B1000 * C2:C1000))
Put that in D2 and it fills line totals for every non-blank row in A. The same idea works for cleanup and flags:
=ARRAYFORMULA(IF(A2:A="", "", TRIM(CLEAN(A2:A))))
Functions that already spill (FILTER, SORT, SEQUENCE, UNIQUE) do not need a wrapper. Use ARRAYFORMULA for arithmetic, IF, and text functions applied row-by-row across a column. Deeper patterns and related tools (SEQUENCE, SORTN, and more) are covered in Mastering Array Formulas in Google Sheets.
4. XLOOKUP — enrich and join analysis tables
XLOOKUP is the modern replacement for VLOOKUP: search any column, return any column, and handle missing matches cleanly.
=XLOOKUP(A2, Products!A:A, Products!C:C, "Unknown")
That pulls a product category from a lookup sheet into your analysis table, with "Unknown" when the ID is missing — no #N/A noise in charts or pivots. For whole columns at once:
=ARRAYFORMULA(IF(A2:A="", "", XLOOKUP(A2:A, Products!A:A, Products!C:C, "Unknown")))
Compared with VLOOKUP, you are not locked to searching the leftmost column, and you do not count columns with a brittle index number. For a side-by-side with INDEX/MATCH, see XLOOKUP vs INDEX/MATCH in Google Sheets (2026) and the dedicated XLOOKUP guide.
5. UNIQUE — distinct values for dimensions and checks
UNIQUE returns the distinct rows (or columns) from a range. That is how you build dimension lists, validate categories, and prep inputs for charts or further FILTER/QUERY steps.
=UNIQUE(C2:C1000)
Lists every distinct status (or region, or SKU) once. Sort the result when you need a stable order:
=SORT(UNIQUE(C2:C1000))
The optional third argument keeps only values that appear exactly once — useful for finding one-off entries or orphaned IDs:
=UNIQUE(A2:A1000, FALSE, TRUE)
Pair UNIQUE with COUNTIF or QUERY to count frequency per category, or with FILTER to build pick-lists that update as the source grows. Cleanup and reshape habits that usually come before UNIQUE are in Data Cleaning, Sorting, and Analysis Tips in Google Sheets.
How these five fit together
A typical analysis flow uses more than one of them:
- Clean and normalize the raw export (
TRIM/CLEAN, validation). - Build distinct categories with
UNIQUE. - Slice the working set with
FILTER(or fullQUERYwhen grouping). - Enrich rows with
XLOOKUPagainst reference tables. - Spill calculated columns with
ARRAYFORMULAinstead of drag-filled formulas.
You do not need all five in every sheet. Start with FILTER + XLOOKUP for interactive views, add QUERY when summaries get heavy, and use ARRAYFORMULA / UNIQUE to keep the model maintainable as data grows.