Google Sheets formulas with QUERY and arrays.
QUERY, ARRAYFORMULA, FILTER, UNIQUE, and IMPORTRANGE. Written for Sheets, not a blended Excel dialect.
QUERY
Google Sheets QUERY: Select Filter and Group
QUERY runs a small SQL-like statement against a range. It is the fastest way to filter, sort, and aggregate a sheet without helper columns, if column types are consistent.
Read guide- ARRAYFORMULAARRAYFORMULA in Google Sheets: Fill a ColumnARRAYFORMULA lets one formula return a column or grid of results. Use it instead of dragging a formula down, and leave the cells below empty so the array can expand.
- FILTERGoogle Sheets FILTER: Keep Rows That MatchFILTER returns rows that meet a condition. In Sheets it is often simpler than QUERY for one or two tests, and it keeps original column order.
- IMPORTRANGEIMPORTRANGE in Google Sheets: Pull Other FilesIMPORTRANGE copies a range from another spreadsheet. The first use asks for access. After that, QUERY and FILTER can treat the import like a local table.
- UNIQUEGoogle Sheets UNIQUE: Distinct Values FastUNIQUE spills distinct rows from a range. It is the standard way to feed dropdowns and summary QUERY sources in Google Sheets.
- REGEXEXTRACTREGEXEXTRACT in Google Sheets, Pull TextREGEXEXTRACT returns the first match of a regular expression. Excel does not use this name. Wrap misses with IFNA.
- SPLITSPLIT in Google Sheets, Divide One CellSPLIT writes each piece into the next empty column. Protect those columns or wrap with INDEX if you only need one piece.
- GOOGLEFINANCEGOOGLEFINANCE in Sheets, Live Market DataGOOGLEFINANCE returns Google Finance data. It is Sheets-only. Treat it as delayed market data, not a trading feed.
- IMPORTHTMLIMPORTHTML in Google Sheets, Pull a TableIMPORTHTML fetches a public URL and reads the nth table or list. Prefer official CSV or API exports when they exist.
- BYROWBYROW in Google Sheets, One Result Per RowBYROW walks a range and returns one value per row. Pair it with LAMBDA. Excel 365 has the same idea.
- LAMBDALAMBDA in Google Sheets, Name a FormulaLAMBDA lists parameter names, then the formula that uses them. A bare LAMBDA is a function until you call it.
- XLOOKUPXLOOKUP in Google Sheets, Exact LookupSheets XLOOKUP can look left or right and can return a custom miss value. FILTER remains the habit when several rows can match.
- SPARKLINESPARKLINE in Google Sheets, Mini ChartsSPARKLINE paints a chart in one cell from a numeric range. Options control type, color, and axis. Excel has a different Sparkline UI.
- SEQUENCESEQUENCE in Google Sheets, Build a Number ListSEQUENCE(n) writes 1 through n down a column. Add rows, columns, start, and step to build a grid.
- TOCOLTOCOL in Google Sheets, Flatten a RangeTOCOL walks a range and writes every cell into one column. Skip blanks if you only want values.
- VLOOKUPVLOOKUP in Google Sheets, Classic LookupSheets VLOOKUP needs the key in the first column of the range. Sorted approximate match is easy to get wrong.
Need a Sheets formula for this tab?
Describe the tab names, ranges, and filters. Copy a Google Sheets formula.