Some Excel Tricks

I am learning Advanced Microsoft Excel and thinking of sharing some tricks that (I found/or I think I found) could ease one's struggle. The shortcuts at glance may seem strange (but similar) to some user. Just note that Command on Mac computer is quite the same as Control on Windows computer; and Option on Mac is similar to Alt on Windows one. 

• Simply-Sexy-Shortcuts

1. Control (Ctrl) + ; = current date | Command (Shift + Ctrl) + ; = current time
2. Command (Ctrl) + Z = undo | Command (Ctrl) + X = redo
3. Option (Alt) + Enter = enter within cell
4. Simple calculation formula rules: ^(1/2) = Square root| "Linked" & "in" = "Linkedin" (combine)| <> = different; 
5. A5:B7 (not fixed; i.e. the range would automatically adjust as you enter the formula for the next cell) <> $A$5:$B$7 (fixed!)
6. [!} Useful Tips! "Escape" is the best way to cancel all the data you are inputting. / Choose the range by using Command/Shift then Command+D to fulfil the formula to the selected range.

• Fantastically-Freaking-Formulae

1. [Statistics] AVERAGEIF (range, criteria1)| AVERAGEIFS (range, "criteria1", "criteria2",...); COUNT/COUNTBLANK/COUNTIF/S (range)|

2. [Time] NOW() = current date+time| TODAY() = date| NETWORKDAYS(start-date,end-date,[holiday]) | DAYS360(start-date,end-date,[method]) |
3. [Look-UP & Reference] HYPERLINK(link/location/sheet,"Name") = name anything/link in the way you want | 

   INDEX(reference, MATCH(value1 & value2, array1 & array2, match_type)) = match value1&value 2 with corresponding index | 

4. [Mathematics & Trigonometry] EVEN/ODD() | SUMIF(range,"criteria") | SQRT() square root | 
5. [Text] CONCATENATE(" "," ",..) = combine ~(" "&" ") {in4.||} | LEFT/RIGHT(text,number-characters); MID(text,start-num,number-characters) | VALUE(): turn numbered text into calculable number | 

   e.g. IF(AND(B2 > 0, C2 > 0), "1000", "No bonus") | e.g.2 IF(B2<0, REPT("<" , B2 * -100)) | IFERROR(value,value_if_error)

• Don't-Defer-Data

1. PivotTable (@ Insert Tab) is an expeditious tool to analyze data by rearrange and pivot columns and rows
2. Graph (@tbc)