Excel Tips & Tricks - lukes8/wiki-notes GitHub Wiki

Shortcuts

ctrl + alt + V = paste with option window
win + d + p = open dialog for Chart Pivot creation
Data - Remove duplicates - removes duplicates in selected column
Data - Sort = sort selected column
PivotTable ANalyze - Field List = show field list with options what to hide or display for table
Home - Clipboard - arrow next = open panel with clopboard history that can be used simply (sometimes it fails, just open new sheet or restart excel)

How to get nick from an email

IN [email protected]

FORMULA =CONCAT(MID(A1, FIND(".", A1)+1, 4), LEFT(A1, 3))

OUT peacluk

How to get unique list from multiple columns

IN a a b f c d c a

FORMULA =UNIQUE(INDEX(A1:B4,MOD(SEQUENCE(COUNTA(A1:B4))-1, 4)+1,ROUNDUP(SEQUENCE(COUNTA(A1:B4))/4, 0)))

OUT a b c d f

How to compare two columns and get only matches values from first column

IN a a b c c h d g n d

FORMULA =IF(ISERROR(MATCH(A1,$B$1:$B$5,0)), "", K4)

it will match A1 cell with second B column, you can simply copy paste the formula to whole A column

OUT a c d