Google Sheets Reference
Kip Landergren
(Updated: )
My cheat sheet for Google Sheets covering common examples, resources, and FAQs.
Contents
Frequently Used Functions
AND(logical_expression1, [logical_expression2, ...]) |
The AND function returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false. |
DATEDIF(start_date, end_date, unit) |
Calculates the number of days, months, or years between two dates. |
ISBLANK(value) |
Checks whether the referenced cell is empty. |
MOD(dividend, divisor) |
Returns the result of the modulo operator, the remainder after a division operation. |
QUOTIENT(dividend, divisor) |
Returns one number divided by another |
REGEXMATCH(text, regular_expression) |
Whether a piece of text matches a regular expression. |
Examples
# check if cell is not empty
=NOT(ISBLANK(A1))
# calculate decimal age, in years
=DATEDIF(A1, TODAY(), "M")/12
# cell contains text
=REGEXMATCH($A1, "text")
Resources
FAQ
Applying a Google Sheets equation clears conditional formatting on the column. How do I get it back?
Only the following has worked for me:
- delete a conditional format rule
- on delete, the remaining rules will be re-applied
- re-create the deleted rule
How do I apply multiple conditional formatting criteria to the same range?
Create a new conditional formatting rule for each permutation of the criteria you specify.
Example:
If you want rows to have:
- a green background where column A contains “project”
- bold text where column B contains “incomplete”
You would create the following two conditional format rules in this order:
# custom formula
=AND(REGEXMATCH($A1, "project"), REGEXMATCH($B1, "incomplete"))
# formatting
Green Background, Bold Text
# custom formula
=REGEXMATCH($A1, "project")
# formatting
Green Background