Google Sheet

Basics

Range:

• Absolute/Relative ranges: A1, $A1, A$1, $A$1 (F4 switch)
• Named Ranges
• Dynamic range (use INDEX to return a reference as boundary of a range)

Copy down / drag down: right-bottom cell corner, drag or double click (ctrl-shift-enter)

Formula Bar: activate to show helps on formula, drag to enlarge, ctrl-enter to break lines

Condition

If() - test condition, choose between value for true & value for false

ifs() - save from nested if, multiple condition multiple values

and(cond1, cond2) or(cond1, cond2)

sumif(): sum only when condition is met

sumifs(): sum only when multiple condition is met

Criterion

Used in formulas such as "countif":

• ">10"
• "*/something" - wildcard

Counting

count() - just count the numbers

counta() - count everything not blank

countblank()

countif() - count, if (criterion like ">10")

countifs() - multiple conditions

countunique() - count unique values

Data Validation

Use data validation to make a drop-down menu

To make a "second level drop-down" dependent on a first drop-down, use something like 'filter' to populate the data for second drop-down into some area first

Custom formula: start with a "="; for example require a column unique

Formatting

Custom format:

• Number, can use ";" to specify formatting for positive/negative/zero
• Can use special characters (arrows etc.) to make it look cool
• Can put [green] etc., to specify color

Conditional format:

• Rules has sequence

Referencing Functions

• VLOOKUP / HLOOKUP : look down/right, find exact or drop back, then right / down
• LOOKUP: look up key in one row/column, then find the value from another
• INDEX: return a cell reference from range, row, column
• INDIRECT: convert text reference to range
• OFFSET: start from a cell, travel offset columns/rows, return a cell or a range of given size
• UPLUS ("+"): first cell

VLOOKUP / HLOOKUP / LOOKUP (alternatively use INDEX & MATCH combination)

Vlookup (look down a RANGE, find exact/drop back, then right):

• look up a search key
• in first column of a range
• if (is_sorted) true, find a position not necessarily exact match, otherwise needs exact match
• if not found error N/A
• if found, return the value of another column by given index

HLOOKUP (look right a RANGE, find exact / drop back, then down)

LOOKUP (look up key in a ROW OR COLUMN, find, then retrieve the value from another ROW OR COLUMN)

INDEX

index(range, row, column), returns a cell REFERENCE (not just the value):

• row / column offset (1 being the left/top most)
• omit row / column, return whole column / row

Usage:

• DYNAMIC RANGE - use index() to return a reference, and use that reference in a range expression =A1:index(A:A,counta(A:A))

INDIRECT & OFFSET

Take a text reference ("Sheet!A1:B5") and return the actual cell/range

Usage:

• Combine with Row() function, to create a vector of 1...n

Offset: take a cell as starting point, travels offset rows / columns, then return the cell or a range of given rows / columns

UPLUS ("+")

Just return the first cell. Can also use "+". e..g. "+filter(...)"

RANGE / CELL TO NUMBER FUNCTIONS

• ROW COLUMN ROWS COLUMNS
• MATCH: search in a range for value and return position

ROW/COLUMN/ROWS/COLUMNS

Return the coordination & width/height of range

MATCH

Basic:

• Search in a range (row or column) for a value (exact match, ascending, descending), and
• return the first matching POSITION (1 being the starting of range)

Wildcard: "*" for wildcard, "?" for single character

QUERY & FILTER

FILTER

Filter rows of a given range and only leave those satisfies a condition (example "B4:B>10", specify a column), return a FILTERED AREA

Condition:

• Use second and more conditions for "AND"
• Use "+" for "OR"

QUERY (The most powerful)

Basic: query(range, query, header) - header is just number of header rows

Range:

• range
• another query (to reference column, use col2, col3)

Query language (Google Visualization API Query Language https://developers.google.com/chart/interactive/docs/querylanguage):

• select
  • *
  • A, B, C
  • Col2, Col3... embedded query ("Col" is case sensitive)
• where
  • = (equals)
  • and or...
  • is (is not) null
• group by
• order by
• limit
  • 5 (only top 5)
• label
  • count(B) 'No. whatever'

CONVERSION FUNCTIONS

N ("--")

Converts TRUE/FALSE to 1 & 0

SUMPRODUCT

Two or more ranges (second one can be locked), first each cell * corresponding cell, then SUM

Easier than a "product" column then SUM

Also since TRUE/FALSE as 1/0 (tip: --(TRUE) converts to 1,0), the product would be AND, so SUMPRODUCT can be used as SUMIFS (but not readable, so not encouraged)

Array Formulas

{}:

• {range1, range2} - puts two ranges side by side
• {range1; range2} - two ranges up and down
• If values are used instead of range, this actually creates a row/column

ArrayFormula (ctrl/command+enter):

• without arrayformula, range1*range2 only gives the first cell * first cell
• use ctrl-enter (arrayformula(range1 * range2) : repeats the multiply with each cell
• allow non-array formula use a range
• NOTE: some function like "AND", "OR" actually aggregates the range (AND the whole range) so cannot work with arrayformula in desired manner, use product

Import Formulas

IMPORTRANGE - Able to bring data from another Google Sheet into current one:

• Needs authorization
• Automatic
• Can be used in {} to combine data

IMPORTHTML - grab data from web page table/list etc., needs some experiment and luck...but can be powerful (like copy paste)

IMPORTXML - grab xml from url, then use xquery! Able to do some 'mass query' if url is from a column... powerful

REGEX functions

(Syntax reference) https://github.com/google/re2/blob/master/doc/syntax.txt

REGEXMCH - match and return true/false

REGEXEXTRACT - extract pattern from string:

• (?: ) - non-capturing group, group to match but not return

REGEXREPLACE - replace pattern in string

Date formulas

Date is just a formatting. Fundamentally stored as a serial number (1 = 1 day, time: decimals)

Literal: date '2001-12-31'

int() - remove time, leave only date

date(year, month, day) - convert to date number

now() - now

today() - today without time

eomonth() - last day of month of a target date's previous/current/after month (specified), to calculate start of month, calculate the last day of previous month and +1

month(), year() - the month/year number of...

String Formulas

"Q"& - concatenate

SPARKLINE

Fun little charts in single cell to show trends

SMALL/LARGE

Return the nth smallest/largest

TRANSPOSE / MMULT

Transpose: row/column transpose (rows to columns and vice versa)

MMULT: matrix multiply

FREQUENCY / RANK

Frequency: Take a range of values, and another range of steps, calculate the frequency of values fall into each step

Rank: the rank position of a value in a range of values

SORT / SORTN

Sort: a range by values in a column

SORTN: get max n rows.

SUBTOTAL

Like SUM() but:

• never double counting (so SUMTOTAL in another SUMTOTAL won't count)
• respond to filter
• more than just SUM, choose between average, sum, ....

GETPIVOTDATA

Correct way to get data from pivot table (which has variable size)

HYPERLINK

Not just to URL can also to cell, range of sheet