,
Learn these Excel Tricks to start saving time today!
We all agree that there isn’t enough time in a day to accomplish everything. Improving your Excel skills is a great place to begin to save a few minutes everyday. Excel has a lot of magic capabilities that are not immediately apparent. Just learning one trick can save you minutes every day.
1. Concatenate : Join up to 255 text strings into one text string
2. Remove duplicate (available in tool bar) : Data – Remove duplicate
3. Vlookup : find reference cell vertically, check out this tutorial.
4. Match/Index : 1-way left look up, lookup entire row/column, 2-way lookup with two maches, check out this tutorial
5. Index/Count : create dynamic range
6. Index/Choose : select range of multiple tables
7. Match ( true, array <>””,0) : find the first not null value
8. Pivot Table : create summary reports, check this tutorial
9. Sum if : calculate sum under certain conditions, check this tutorial
10. F4 : lock down the value/range, check this tutorial
11. F2 : edit the cell
12. Ctrl + shift + down : select all cells in column
13. Tab : auto fill function
14. Alt + = : Automatically SUM() the entire column
15. Alt + F1 : insert chart
16. Offset : return dynamic cell/range
17. tool bar – Conditional format- data bar
18. Ctrl + t : insert table
19. Ctrl + F3 : name manager
20. F3 : select name
21. Pivot table – design – report layout – select show in tabular
22. Alt + o + d : conditional formatting rule
23. MOD ( row(),2) : highlight every other row
24. Large/Small function: top/least X numbers
25. Round function : rounds a number to a specified number of digits
26. Calculate Running total : lock part of the range B$2:B2
27. Alt + d + l : data validation
28. Ctrl + shift + 4 : currency format
29. Ctrl + 1 : format cells
30. Show number 2/3, use = 2/3, otherwise will show date
31. Highlight the range and hit enter to type in numbers
32. Use General Number Format to see what is REALLY in the cell
33. Pivot table – slicer
34. Paste special – linked pic : picture in Excel will change if you update the original picture
35. Mode function : get most frequent value
36. Use STDEVP if you have all the values
37. Highlight the formula and hit F9 : see formula value
38. HLOOKUP : find reference cell horizontally
39. Ctrl + down arrow : move to the last cell in the column
40. Search function
41. & : connect cell and text – A1&” text”
42. Indirect function : Returns the reference specified by a text string
43. Ctrl + shift + 8 : select whole table
44. Ctrl + shift + F3 : create name for selected range
45. Consolidate function : summarize value without sorting
46. Select one cell in one sheet, hold sheet and click the last sheet : reference multiple sheets for the same cell
47. Text ([date], “dddd”) : show day as Monday, Tuesday…
48. Eomonth function : Returns the serial number for the last day of the month
49. EDATE function : calculate maturity dates or due dates
50. networkdays.intl function : Returns the number of whole workdays between two dates
51. Return Multiple Items From One Lookup Value for Table:
=IF(ROWS(A$7:A7)<=$B$3,INDEX(‘358’!A$16:A$39,SMALL(IF(‘358’!$C$16:$C$39=$B$1,IF(‘358’!$D$16:$D$39=$B$2,ROW(‘358’!$D$16:$D$39)-ROW(‘358’!$D$16)+1)),ROWS(A$7:A7))),””)
B3=COUNTIFS(‘358′!C16:C39,B1,’358’!D16:D39,B2)
52. Alt + F11 : macro
53. HYPERLINK function : create hyperlink
54. Stem & Leaf Chart with REPT & COUNTIF function
55. Alt + E + A + F : Clear format only
56. Alt + E + A + A : remove content and format
57. Ctrl + Z : undo
58. Ctrl Y : undo undo
59. F5 : Go to- special
60. Alt + ; : select visible cells
61. Data – outline – subtotal
62. Proper (Trim(Clean))) : clean up data
63. Alt + A + E : text to column
64. DATE, YEAR, MONTH, DAY and WEEKDAY functions : check this tutorial