Google Sheets Cheat Sheet

Compare Columns

# Compares Columns A and B by outputting values that are found in column A but NOT B. 
Useful for checking a full site crawl against a sitemap.

=FILTER(A2:A,ISNA(MATCH(A2:A,B2:B,0)))

Concatenate Array Formula in Google Sheets

=ArrayFormula(if(len(B2:B),B2:B&" "&C2:C&" "&E2:E,iferror(1/0)))

Conditional Formatting - Highlight Row

# Highlight an entire row based on the first instance of a set of data

=and(countif($A:$A,$A2)>0,match($A2,$A:$A,0)=row())

i.e. if row a's data is:

1
1
1
1
4
4
4
5
6
6
6

only the first 1, 4, 5, and 6 would be highlighted. The repeats of the data would not be.

Count How Many Times Data Occurs Within A Range

=ArrayFormula(if(row(B:B)=1,"Title of Column",if(len(B:B),countif(unique(match(B:B&C:C,B:B&C:C,0)+match(B:B&C:C&H:H,B:B&C:C&H:H,0)/50000),"<="&match(B:B&C:C,B:B&C:C,0)+match(B:B&C:C&H:H,B:B&C:C&H:H,0)/50000)-countif(unique(match(B:B&C:C,B:B&C:C,0)+match(B:B&C:C&H:H,B:B&C:C&H:H,0)/50000),"<"&match(B:B&C:C,B:B&C:C,0)),)))

Empty Cell? No problem

# use this to display something in the even that there is no data (or data) in a cell. In this example, if cell e532 has something in it, then put a "Y" in whichever cell the formula is placed in.

=IF(ISBLANK(E532),,"Y")

Or as an array formula

=arrayformula(iferror(if(isblank(B1:B37)*isblank(B1:B37),"No","Yes")))

filter by regular expression

# choose "custom formula is"

=not(regexmatch(N:N, "(?i)word1|word2|\.html"))

Get data from one column based on multiple criteria

=QUERY( 'Pardot Export'!A2:E , "Select C where D starts with 'CEO' and E='Credit Union'")

Get Last Directory in a URL string

=REGEXEXTRACT(A1,"/([^/]+)/$")

Grab The Last Part of The URL After The TLD

=arrayformula(iferror(REGEXEXTRACT(A2:A,"(?:\.com|\.net|\.edu)(.+)")))

Highlight Row if a particular cell is NOT empty

# Choose your range and start your formula with the cell at the beginning of your range

=$D88<>""

Put the data from all your columns into one list

## In this case columns B, C, D, E, and F into column A.

=ArrayFormula( TRANSPOSE(SPLIT(SUBSTITUTE(CONCATENATE(SUBSTITUTE( TRIM( SUBSTITUTE(B2:B," ",CHAR(9)) & " " & SUBSTITUTE(C2:C," ",CHAR(9)) & " " & SUBSTITUTE(D2:D," ",CHAR(9)) & " " & SUBSTITUTE(E2:E," ",CHAR(9)) &" " & SUBSTITUTE(F2:F," ",CHAR(9)) &" " & SUBSTITUTE(G2:G," ",CHAR(9)) &" " ) ," " ,CHAR(10))&CHAR(10)),CHAR(9)," "), CHAR(10))))

Remove the label row from a query formula

#Normally, adding the query with a modifier such as "sum" or "avg" will cause there to be a heading row. The "label sum(g)''" makes
the label blank.

=QUERY('Freshbooks Export'!A$2:G, "select Sum(G) where A='"&$A2&"' label Sum(G)''")

Replace Spaces with Dashes

# Replace Spaces with Dashes
=arrayformula(SUBSTITUTE(B3:B," ","-"))

# Replace spaces with Dashes and lowercase (good for urls)
=arrayformula(LOWER(SUBSTITUTE(B3:B35," ","-")))

Sort

# This will sort all data on sheet "name of tab" on cells a:l in descensing order

=sort('Name-of-tab'!A2:L, 1, false)

Total Only Visible Cells

=SUBTOTAL(9;B2:B11) 

function_codes - replace the 9 as the first argument to change how the subtotal works

1 is AVERAGE
2 is COUNT
3 is COUNTA
4 is MAX
5 is MIN
6 is PRODUCT
7 is STDEV
8 is STDEVP
9 is SUM
10 is VAR
11 is VARP

vlookup

#vlookup formula with "iferror" to remove "N/A" from empty cells

=iferror(vlookup(A1, 'Sheet1'!A2:A200, 2, false))

or better - with array formula so you don't have to drag

=arrayformula(iferror(vlookup(A1:A, 'Sheet1'!A2:A, 2, false)))

VLOOKUP to the left

=arrayformula(iferror(vlookup(A2:A,{Sheet8!E2:E,Sheet8!D2:D},2,0)))

=arrayformula(iferror(vlookup(Range,{Column To Match,Column To Left},2,0)))

Vlookup to the left

=index('Search Data'!A:A,match(B2,'Search Data'!B:B))

Thanks for sharing these! Your formula to Count How Many Times Data Occurs Within A Range solved a problem I've been scratching my head over for a week!!

nice one