-
-
Save ayo-codes/8cd26ab3199bdff7feaffd2d6c4ec0ed to your computer and use it in GitHub Desktop.
Google Sheets Cheat Sheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=ArrayFormula(if(len(B2:B),B2:B&" "&C2:C&" "&E2:E,iferror(1/0))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=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)),))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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"))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=QUERY( 'Pardot Export'!A2:E , "Select C where D starts with 'CEO' and E='Credit Union'") |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=REGEXEXTRACT(A1,"/([^/]+)/$") |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=arrayformula(iferror(REGEXEXTRACT(A2:A,"(?:\.com|\.net|\.edu)(.+)"))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Choose your range and start your formula with the cell at the beginning of your range | |
=$D88<>"" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
## 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)))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Replace Spaces with Dashes | |
=arrayformula(SUBSTITUTE(B3:B," ","-")) | |
# Replace spaces with Dashes and lowercase (good for urls) | |
=arrayformula(LOWER(SUBSTITUTE(B3:B35," ","-"))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=index('Search Data'!A:A,match(B2,'Search Data'!B:B)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment