Skip to content

Excel / Sheets

Basic Info

This returns the Column Letter but removes the Row Number 

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

The column number, but also doing math so I can do VLOOKUP without having to physically count everything 

="ColNUM "&COLUMN()&CHAR(10)&"ColSUM "&COLUMN()-21

Conditional Formatting for Google Sheets 

=regexmatch(C3,"Keyword")
=COUNTIF (A:A, A1)>1

CONCAT or NOT - both do the same thing

...use the &, though 

=CONCATENATE(A1," ",B1)
=A1&" "&B1
image

Concatenate Fields with Carriage Returns

=A2&IF(B2<>"",CHAR(10)&B2,"")&IF(C2<>"",CHAR(10)&C2,"")

Convert All Zips Variants to Leading Zeroes

=SWITCH(LEN(A2),4,TEXT(A2,"00000"),5,A2,8,TEXT(A2,"00000-0000"),9,TEXT(A2,"00000-0000"))

Day in Previous Quarter (First / Last)

=DATE(YEAR(DateCell),FLOOR(MONTH(DateCell)-1,3)-1,0)
=DATE(YEAR(DateCell),FLOOR(MONTH(DateCell)-1,3)+1,0)

A2 being the Google Sheets Tab ID (gid=1896709346) 

=HYPERLINK("#gid="&A2,REGEXEXTRACT(CELL("address",SheetName!$A$1),"'?([^']+)'?!"))

Dynamic Array with CountIF

This will show all the Unique values and how many times they show up. Two columns. 

=LET(data, B1:B20, u, UNIQUE(data), HSTACK(u, COUNTIF(data, u)))

This will show all the Unique values and how many times they show up, but with one column! 

=LET(data, B1:B20, u, UNIQUE(data), u & "  (" & COUNTIF(data, u) & ")")