Core Formula

Purchase this course to access all lessons and start learning.

Formula basics
Video Time
Save Video 1. What is a function? Watched 2:16
Save Video 2. What is a formula? Watched 2:28
Save Video 3. What is a cell reference? Watched 2:15
Save Video 4. How to edit a formula Watched 2:26
Save Video 5. How to use operators in formulas Watched 3:21
Save Video 6. The order of operations Watched 2:54
Save Video 7. How to use function arguments Watched 3:08
Save Video 8. How to enter functions Watched 3:01
Save Video 9. How to select arguments with the formula tip window Watched 1:56
Save Video 10. How to do in-place changes with paste special Watched 2:36
References
Video Time
Save Video 11. What's a relative reference? Watched 2:38
Save Video 12. How to use relative references - example 1 Watched 2:28
Save Video 13. How to use relative references - example 2 Watched 2:37
Save Video 14. What's an absolute reference? Watched 3:00
Save Video 15. How to use absolute references - example 1 Watched 2:37
Save Video 16. How to use absolute references - example 2 Watched 2:49
Save Video 17. How to create a mixed reference Watched 2:33
Save Video 18. How to use a mixed reference to create a running total Watched 2:09
Save Video 19. How to create a reference to another worksheet Watched 2:55
Save Video 20. How to create 3D references Watched 3:08
Save Video 21. Create a dynamic reference to a worksheet Watched 3:28
Named ranges
Video Time
Save Video 22. Named ranges as absolute references Watched 2:16
Save Video 23. How to create a named range Watched 3:04
Save Video 24. How to apply a named range to an existing formula Watched 3:00
Save Video 25. How to manage named ranges Watched 2:17
Save Video 26. How to create a named constant Watched 2:32
Save Video 27. Create a dynamic reference to a named range Watched 2:32
Dynamic named ranges
Video Time
Save Video 28. What is a dynamic named range Watched 2:35
Save Video 29. How to create a dynamic named range with a Table Watched 3:00
Save Video 30. How to create a dynamic named range with OFFSET Watched 3:39
Save Video 31. How to create a dynamic named range with INDEX Watched 4:07
IF Function
Video Time
Save Video 32. The IF function Watched 3:09
Save Video 33. If this OR that Watched 3:02
Save Video 34. How to create a formula with nested IFs Watched 3:15
Save Video 35. How to make a nested IF formula easier to read Watched 3:06
Working with text
Video Time
Save Video 36. What is text in Excel Watched 3:07
Save Video 37. How to join values with the ampersand Watched 2:54
Save Video 38. How to join cell values with CONCATENATE Watched 3:03
Save Video 39. How to use CHAR and CODE functions Watched 3:04
Save Video 40. How to join text with numbers Watched 3:09
Save Video 41. How to concatenate with line breaks Watched 3:15
Save Video 42. How to use concatenation to clarify assumptions Watched 3:20
Save Video 43. How to use the REPT function to repeat things Watched 2:59
Save Video 44. How to clean text with CLEAN and TRIM Watched 2:22
Save Video 45. How to count characters with the LEN function Watched 2:52
Save Video 46. How to change case with UPPER LOWER and PROPER Watched 2:59
Save Video 47. How to extract text with LEFT and RIGHT Watched 2:35
Save Video 48. How to find text with a formula Watched 3:16
Save Video 49. How to extract a first name with a helper column Watched 3:06
Dates and times
Video Time
Save Video 50. How Excel stores dates and times Watched 2:44
Save Video 51. How to display current date and time Watched 1:51
Save Video 52. How to work with dates Watched 2:54
Save Video 53. How to work with times Watched 2:55
Save Video 54. How to create date and time series with formulas Watched 3:24
Save Video 55. How to calculate the number of days between dates Watched 3:05
Save Video 56. How to calculate years and months between dates Watched 3:51
Save Video 57. How to calculate and highlight expiration dates Watched 3:54
Save Video 58. How to calculate due dates with WORKDAY Watched 3:30
Statistics
Video Time
Save Video 59. How to use the COUNT function Watched 1:31
Save Video 60. How to use the COUNTIF function Watched 3:03
Save Video 61. How to use the COUNTIFS function Watched 3:07
Save Video 62. How to use the SUM function Watched 1:32
Save Video 63. How to use the SUMIF function Watched 3:43
Save Video 64. How to use the SUMIFS function Watched 3:46
Save Video 65. How to calculate an average value Watched 2:31
Save Video 66. How to use the AVERAGEIF function Watched 3:14
Save Video 67. How to use the AVERAGEIFS function Watched 3:20
Save Video 68. How to calculate maximum and minimum values Watched 2:53
Save Video 69. How to rank values with the RANK function Watched 2:59
Save Video 70. How to count items in a filtered list Watched 3:15
Save Video 71. How to get nth values with SMALL and LARGE Watched 4:01
Lookups
Video Time
Save Video 72. How to use VLOOKUP Watched 3:10
Save Video 73. How to use VLOOKUP for approximate matches Watched 2:53
Save Video 74. How to use VLOOKUP for wildcard matches Watched 3:24
Save Video 75. What to do when VLOOKUP returns NA Watched 2:40
Save Video 76. How to troubleshoot VLOOKUP approximate match Watched 2:43
Save Video 77. How to group values with VLOOKUP Watched 2:41
Save Video 78. Why VLOOKUP is better than nested IFs Watched 2:56
Save Video 79. How to replace nested IFs with VLOOKUP Watched 3:55
Save Video 80. How to use VLOOKUP to merge tables Watched 3:06
Save Video 81. How to look things up with INDEX Watched 2:48
Save Video 82. How to use the MATCH Function for exact matches Watched 3:23
Save Video 83. How to use MATCH to find approximate matches Watched 2:53
Save Video 84. How to look things up with INDEX and MATCH Watched 2:48
Save Video 85. How to do a two-way lookup with INDEX and MATCH Watched 3:18
Save Video 86. Two-way lookup with INDEX and MATCH approximate Watched 3:08
Save Video 87. How to use HLOOKUP Watched 2:20
Save Video 88. How to use the CHOOSE function Watched 3:41
Practical Examples
Video Time
Save Video 89. How to use the status bar for quick calculations Watched 2:36
Save Video 90. How to combine functions in a formula Watched 2:45
Save Video 91. How to create a complex formula step by step Watched 3:07
Save Video 92. How to convert formulas to values Watched 2:14
Save Video 93. How to make an exact copy of a formula Watched 2:59
Save Video 94. How to save a formula that's not finished Watched 1:19
Save Video 95. Don't hard code values that may change Watched 3:09
Save Video 96. How to fill in missing data with a simple formula Watched 3:19
Save Video 97. How to fill in missing data part 2 Watched 2:55
Save Video 98. How to pick names out of a hat with Excel Watched 3:07
Save Video 99. How to build all-in-one formulas Watched 3:44
Save Video 100. How to find missing values with COUNTIF Watched 2:35
Save Video 101. How to build a simple summary table Watched 3:01
Save Video 102. Intro to Boolean Logic Watched 3:33
Save Video 103. Complex formula example 401k Match Watched 3:17
Save Video 104. Simplified formula example 401k Match Watched 3:43
Save Video 105. How to generate random values Watched 3:39
Save Video 106. How to build a simple dynamic chart Watched 3:56
Save Video 107. How to randomly assign people to teams Watched 3:49
Audit and troubleshoot
Video Time
Save Video 108. How to trace a formula error Watched 2:29
Save Video 109. How to find and highlight formulas Watched 3:54
Save Video 110. How to check and debug a formula with F9 Watched 3:12
Save Video 111. How to step through complex formulas using Evaluate Watched 3:16
Save Video 112. Excel formula error codes Watched 4:22
Save Video 113. How to set formula error checking options Watched 2:38
Save Video 114. Examples of flagged errors in formulas Watched 3:00
Save Video 115. How to trace formula relationships Watched 3:13
Save Video 116. How to trap errors in formulas Watched 3:17
Bonus Material
Video Time
Save Video 117. 23 Excel formula tips Watched 9:19