Google Sheets formulas that work the first time
Step-by-step tutorials, fixes for formula errors and free tools. Every formula is tested in Google Sheets before we publish it.
Latest guides
-
COUNTIF with Multiple Criteria in Google Sheets (AND and OR, Tested)
COUNTIF takes one condition. To count rows that meet two or more, use COUNTIFS for “this and that”, and a different approach for “this or that”. The OR case is…
-
COUNTIF Contains Text in Google Sheets: Partial Matches and Wildcards
To count cells that contain a word, rather than cells that equal it, put an asterisk on each side of the word: =COUNTIF(D2:D13, “*apple*”). The asterisk stands for “any characters,…
-
XLOOKUP Returning Multiple Columns in Google Sheets
One XLOOKUP can bring back a whole row of results instead of a single cell. You don’t need one formula per column: give XLOOKUP a result range that is several…
-
XLOOKUP with Multiple Criteria in Google Sheets (and Why It Needs ARRAYFORMULA)
XLOOKUP searches one column. To find a row that matches two or three conditions at once, such as a region and a product, you turn the conditions into a single…
-
VLOOKUP vs XLOOKUP vs INDEX MATCH in Google Sheets: 10 Tests
Google Sheets has three common ways to look up a value: VLOOKUP, XLOOKUP and INDEX with MATCH. All three find “Cherry” and return its units. They differ in what happens…
-
UNIQUE Without Blanks in Google Sheets (and Other Hidden Duplicates)
UNIQUE returns each distinct value of a range once, and an empty cell counts as a value. So a list with gaps, or a range that runs to the bottom…
-
Google Sheets QUERY: ORDER BY Multiple Columns, Tested
To sort a QUERY by more than one column, list the columns after order by, separated by commas, each with its own direction: =QUERY(A1:F8, “select A, B, D order by…
-
QUERY from Another Sheet or Spreadsheet in Google Sheets
QUERY doesn’t care where its data lives. Point the first argument at another tab and it works the same as on the current one; wrap that argument in IMPORTRANGE and…
-
Split Text into Columns or Rows in Google Sheets (and the SPLIT Traps)
SPLIT breaks a cell into pieces at a delimiter and spills them into the cells to the right. It looks simple, and with =SPLIT(A2, “,”) on clean data it is.…
-
Remove Extra Spaces in Google Sheets (When TRIM Doesn’t Work)
TRIM removes spaces at the start and end of a cell and turns runs of spaces in the middle into one. For text you typed yourself, that is enough. For…