Shortcut

Auto add $ to all

press F4

Show cell formula

press F2

Pin the cell

pin the cell, don’t change the cell in a formula when inserting or deleting a row
INDIRECT

Reference: https://www.youtube.com/watch?time_continue=86&v=mv_Oo1WCVHU

Show what row you are in

excel

=ROW()

例如，當你在 cell A7 輸入，結果是 7；在 cell C2 輸入，結果是 2。

Find the average of the top 3 largest numbers

用 AVERAGE 和 LARGE

excel

=AVERAGE(LARGE(range,{1,2,3}))

SUM

excel

=SUM(value1, value2...)

SUMIF (for single criterion)

SUMIF can only perform conditional sums with a single criterion. To use multiple criteria, use the database function DSUM.

excel

=SUMIF(range, criterion, [sum_range])

SUMIF(A1:A10,">20")

SUMIF(A1:A10,"Paid",B1:B10)

Reference: https://support.google.com/docs/answer/3093583

SUMD (for multiple criteria)

excel

=SUMIF(range, column name, criteria range)

Reference: https://www.youtube.com/watch?v=UhnVgvyO1DU

COUNT (count numbers only)

counts the number of cells that contain numbers

excel

=COUNT(A1:A20)

COUNTA (count all type of data)

counts the number of non-empty cells, only works with all data types.

excel

=COUNTA(A1:A20)

COUNTIF

counts the number of cells that meet the criteria

excel

COUNTIF(range, criterion)

COUNTIF(A1:A10,">20")

COUNTIF(A1:A10,"Paid")

COUNTIFS

excel

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

COUNTIFS(A1:A10, ">20", B1:B10, "<30")

LEN

counts the number of characters in a cell, including spaces.

TRIM

remove all 最前&最後 spaces

IF

excel

=IF(criteria, true result, false result)

=IF(A2>5000,"expensive","ok")

CONCATENATE

combining data in 2 (or more) different cells into one cell

excel

=concatenate(A1, “ “, B1, C1, D1)

INDEX MATCH

excel

=INDEX([ToBeDisplayed-result-coloumn],MATCH(B3,[searchColumn],0))

INDEX MATCH multiple criteria

press enter shift ctrl

excel

=INDEX([ToBeDisplayed-result-coloumn],MATCH(1,(E2=J:J)*(Q2=K:K),0),3)

Reference: https://spreadsheeto.com/index-match/

Find url inside a text cell

excel

=MID(C11,FIND("http",C11),IFERROR(FIND(" ",C11,FIND("http",C11))-1,LEN(C11))-FIND("http",C11)+1)

Reference: https://stackoverflow.com/questions/25429211/extract-urls-from-a-cell-of-text-in-excel

Extract text after the last slash /

excel

=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),100))

Reference: https://www.mrexcel.com/board/threads/extract-data-after-last-slash.960998/

Extract text after the last equal sign =

excel

=RIGHT(F2,LEN(F2)-FIND("@",SUBSTITUTE(F2,"=","@",(LEN(F2)-LEN(SUBSTITUTE(F2,"=","")))/LEN("="))))

Reference: https://www.mrexcel.com/forum/excel-questions/768565-extract-substring-after-last-space-text.html

Extract text after the last equal sign = (1 conditions)

excel

=IF(B3="keyword", RIGHT(P3,LEN(P3)-FIND("@",SUBSTITUTE(P3,"=","@",(LEN(P3)-LEN(SUBSTITUTE(P3,"=","")))/LEN("=")))),"NA")

Extract text after the last equal sign = (2 conditions OR)

excel

=IF(OR(B3="keyword1",B3="keyword2"), RIGHT(P3,LEN(P3)-FIND("@",SUBSTITUTE(P3,"=","@",(LEN(P3)-LEN(SUBSTITUTE(P3,"=","")))/LEN("=")))),"NA")

Extract the text after the second equal sign =

excel

=MID(B2, FIND("=", B2, FIND("=", B2)+1)+1,256)

Reference: https://www.extendoffice.com/documents/excel/3881-excel-extract-text-before-second-space.html

Extract Text Before The Second Space

excel

=IF(ISERROR(FIND(" ",A2,FIND(" ",A2,1)+1)),A2,LEFT(A2,FIND(" ",A2,FIND(" ",A2,1)+1)))

Reference: https://www.extendoffice.com/documents/excel/3881-excel-extract-text-before-second-space.html

Extract Text After The Second Space

excel

=MID(A2, FIND(" ", A2, FIND(" ", A2)+1)+1,256)

Reference: https://www.extendoffice.com/documents/excel/3881-excel-extract-text-before-second-space.html

Remove comma if the Last Character is comma

excel

=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

Reference: https://www.extendoffice.com/documents/excel/3371-excel-delete-last-first-character-if.html

Remove comma if the first Character is comma

excel

=IF(LEFT(A1,1)=",",RIGHT(A1,LEN(A1)-1),A1)

Formatting

shade every other row in Excel

conditional formatting custom

excel

=MOD(ROW(),2)=1

Reference: https://support.microsoft.com/en-us/help/919194/how-to-shade-every-other-row-in-excel

Turn the value to million

e.g. 120,000 —> 0.1M

Code

#,#0.00,,M

Reference:
https://help.gooddata.com/display/doc/Formatting+Numbers+in+Reports
https://www.clearpeaks.com/setting-custom-data-format-in-obiee-answers/

How to delete all blank rows in Excel in 3 seconds

YouTube: How to delete all blank rows in Excel in 3 seconds | The Tech Train