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
-
Extract Text Before or After a Character in Google Sheets
Pulling out the part of a cell before or after a character is one of the most common text jobs in a spreadsheet: the name before the @ in an…
-
Convert Text to Numbers in Google Sheets: What VALUE Can and Can’t Read
A number stored as text looks like a number, but most functions treat it as words. The dangerous part is that they rarely complain. In our test, three cells holding…
-
Cross Out a Row When a Checkbox Is Checked in Google Sheets
A to-do list in Google Sheets looks much better when finished tasks are crossed out. You don’t need a script: a conditional formatting rule can apply strikethrough to the whole…
-
Conditional Formatting Custom Formulas in Google Sheets: 13 Tested Examples
A custom formula rule colors a cell when a formula you write returns TRUE. It is the most flexible kind of conditional formatting in Google Sheets: it can look at…
-
How to Change Row Color When a Checkbox Is Checked in Google Sheets
To color a whole row when its checkbox is checked, use a conditional formatting rule with a custom formula that points at the checkbox column with a dollar sign before…
-
#VALUE! Error in Google Sheets: 8 Causes and How to Fix Them
#VALUE! means a function received a value of the wrong type, almost always text where it expected a number. The good news is that Google Sheets tells you exactly which…
-
INDEX MATCH Not Working in Google Sheets: 9 Causes and Fixes
INDEX MATCH looks up a value in one column and returns the matching value from another. When it fails, it fails in two ways: with an error such as #N/A…
-
IMPORTRANGE Not Working in Google Sheets: Every Error and Fix
IMPORTRANGE copies a range from one Google Sheets file into another. It has two arguments, and both must be text: the file’s URL or ID, and the range, including the…
-
How to Replace #N/A with a Blank in Google Sheets (IFNA vs IFERROR)
To show a blank cell instead of #N/A in Google Sheets, wrap the formula in IFNA and use an empty text as the replacement: =IFNA(VLOOKUP(A2, Products!A:C, 3, FALSE), “”) #N/A…
-
#REF! Error in Google Sheets: Every Cause and How to Fix It
#REF! means a formula points to something that doesn’t exist or can’t be used: cells that were deleted, a sheet that isn’t there, a column beyond the end of a…