Skip to content

Latest commit

 

History

History
84 lines (83 loc) · 4.14 KB

Tips-and-Tricks.md

File metadata and controls

84 lines (83 loc) · 4.14 KB

Tips and Tricks

Save Time; Magic; Fun

  1. Absolute & relative cell references ........... (F4)
  2. Copy from Left ........... (CTRL + R)
  3. Copy from Top ........... (CTRL + D)
  4. Format Cell ........... (CTRL + 1)
  5. Select Non-Adjacent cells .......... (CTRL + Click) ⭐
  6. Highlight Precedents ........... (CTRL + [)
  7. Highlight Dependents ........... (CTRL + ])
  8. Select the active column ........... (CTRL + Space)
  9. Select the active row ........... (CTRL + Shift)
  10. CTRL + SHIFT + + (Plus) ........... (Insert a new row/col) ⭐
  11. CTRL + - (Minus) ........... (Remove a row/col) ⭐
  12. CTRL + SHIFT + # ........... (0-6, e.g., 4 = $ Format)
  13. Bypass a hyperlink (CTRL+SHIFT)
  14. Paste List ........... (F3)
  15. Flash Fill ........... (CTRL + E)
  16. CTRL + Shift + Mouse Scroll (Navigate Horizontally)
  17. Copy Entire Sheet
  18. Insert Blank Column or Row ........... (Shift + Fill Handle)
  19. Define names for cells
  20. Import data from web e.g., https://www.imdb.com/chart/top or https://en.wikipedia.org/wiki/Sovereign_wealth_fund
  21. Copy Visible Cells Only ........... (Go to > Special)
  22. Transpose Column / Row
  23. Fill Options ........... (Fill Handle + Righ-click)
  24. Named ranges ........... (Zoom <= 39%)
  25. Arabic Numbers ........... (Custom format prefix [$-,200]) e.g., ١٩٧٥
  26. ;;; ........... Hidden Numbers ⭐
  27. • @ ........... (Alt 7 + Cell value)
  28. F9 (Edit mode) ........... Formula's value
  29. Watch Window
  30. Multiple-clicks Trace Precedents/Dependents
  31. Double-click Trace Precedents/Dependents Arrow
  32. Double-click Format Painter
  33. Customize Quick Access Toolbar (Add, Remove, Order)
  34. Line Breaks (Alt+Enter)
  35. Flash Fill
  36. Fill > Justify (Break down long sentences)
  37. Select the whole contiguous data set of a cell (CTRL+A, CTRL+SHIFT+*)
  38. Text to Columns
  39. Multiple Cells, Same Data (CTRL+Enter)
  40. Multiple Cells, Navigation (Tab)
  41. Paste Special (Operation: Add, Subtract, Multiply, Divide)
  42. Charts as Templates
  43. Display Units in $000s
  44. Convert numbers stored as text to numbers (Paste special with Multiply by 1; VALUE(); Text to Columns) + Formatting
  45. 3D formula
  46. Highlight Rows Based on a Cell Value (Conditional Formatting) e.g., = $B2 > 10
  47. Screenshot
  48. People Graph
  49. Number format (POSITIVE; NEGATIVE; ZERO; TEXT e.g., #,##0.00;(#,##0.00);"-";[Red]@
  50. Clear format
  51. Camera 📷
  52. Inserts today's date (CTRL + ;)
  53. Inserts the current time (CTRL + Shift + ;)
  54. Instant Calc (Min, Max, Sum, Count, etc)
  55. Instant Calc (Without header, scroll-down 1 row)
  56. Center Across Selection
  57. User name (Personalize your copy of Microsoft Office)
  58. Closing Parenthesis (Formulas)
  59. Spaces (Before =, Before (, Between logical operators e.g., >=)
  60. Clipboard task pane
  61. Hide zero values 0;-0;;@
  62. Dashboard Zone (Hide all the non-dashboard columns Shift+CTRL+➡)
  63. Errors #xx!, #xx?, #x/x, #x/x! e.g., #DIV/0!, #N/A!, etc. learn more
  64. Hide/Unhide slicing (filter) using the group rows features
  65. Total number of rows and columns on a worksheet 1,048,576 rows by 16,384 columns (17,179,869,184 cells or ~17.2B cells) learn more
  66. The 1900 date system
  67. '# "Years" (Format)
  68. '#,, "Million" | #.#0,, "Million" | #, "Thousand"
  69. ROW() //Index
  70. Select Objects
  71. Quick Access Toolbar Hotkeys (ALT + #)
  72. =MATCH("col_name",Table[#Headers],0)
  73. Insert data from picture
  74. Search the menu (Context-sensitive)
  75. PivotTable Fields pane (Position, Layout)
  76. ? ActiveWorkbook.PivotCaches.Count (PivotCache Count)
  77. =REPT("|",10) and Use "Britannic Bold" font ➡ In-cell bar chart
  78. Gregorian Format - "B1dd/mm/yy"; Hijri Format - "B2dd/mm/yy"
  79. =STOCKHISTORY("MSFT", "2024-1-1","2024-9-30",,,,0,1,2,3,4,5)