Formulas are *the key* to getting work done in Excel. In this accelerated video course, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn powerful skills to troubleshoot, trace errors, and fix problems. This is the formula training *you should have had* to begin with. End your frustration with Excel formulas *today*.

- Lifetime access to all videos 24 x 7 - learn online from anywhere
- An accelerated, step-by-step program for Excel formulas
- Guided practice - over 100 worksheets included
- Both Windows
__and__Mac shortcuts in all videos - A free Excel Shortcut PDF with 200+ shortcuts
- A 30-day money-back guarantee (we want you to be 100% satisfied)

You have remained a relevant source of value for me throughout my Excel career. Now that I am fairly advanced with Excel I tend to forget many things I have crammed in my head while trying to solve many problems with spreadsheets. From reviewing my original purchase of your package and your quick videos help remind me of ways that I could insert formulas more efficiently.

Ramon

Video | Time |
---|---|

1. What is a function? | 2:16 |

2. What is a formula? | 2:28 |

3. What is a cell reference? | 2:15 |

4. How to edit a formula | 2:26 |

5. How to use operators in formulas | 3:21 |

6. The order of operations | 2:54 |

7. How to use function arguments | 3:08 |

8. How to enter functions | 3:01 |

9. How to select arguments with the formula tip window | 1:56 |

10. How to do in-place changes with paste special | 2:36 |

Video | Time |
---|---|

11. What's a relative reference? | 2:38 |

12. How to use relative references - example 1 | 2:28 |

13. How to use relative references - example 2 | 2:37 |

14. What's an absolute reference? | 3:00 |

15. How to use absolute references - example 1 | 2:37 |

16. How to use absolute references - example 2 | 2:49 |

17. How to create a mixed reference | 2:33 |

18. How to use a mixed reference to create a running total | 2:09 |

19. How to create a reference to another worksheet | 2:55 |

20. How to create 3D references | 3:08 |

21. Create a dynamic reference to a worksheet | 3:28 |

Video | Time |
---|---|

22. Named ranges as absolute references | 2:16 |

23. How to create a named range | 3:04 |

24. How to apply a named range to an existing formula | 3:00 |

25. How to manage named ranges | 2:17 |

26. How to create a named constant | 2:32 |

27. Create a dynamic reference to a named range | 2:32 |

Video | Time |
---|---|

28. What is a dynamic named range | 2:35 |

29. How to create a dynamic named range with a Table | 3:00 |

30. How to create a dynamic named range with OFFSET | 3:39 |

31. How to create a dynamic named range with INDEX | 4:07 |

Video | Time |
---|---|

32. The IF function | 3:09 |

33. If this OR that | 3:02 |

34. How to create a formula with nested IFs | 3:15 |

35. How to make a nested IF formula easier to read | 3:06 |

Video | Time |
---|---|

36. What is text in Excel | 3:07 |

37. How to join values with the ampersand | 2:54 |

38. How to join cell values with CONCATENATE | 3:03 |

39. How to use CHAR and CODE functions | 3:04 |

40. How to join text with numbers | 3:09 |

41. How to concatenate with line breaks | 3:15 |

42. How to use concatenation to clarify assumptions | 3:20 |

43. How to use the REPT function to repeat things | 2:59 |

44. How to clean text with CLEAN and TRIM | 2:22 |

45. How to count characters with the LEN function | 2:52 |

46. How to change case with UPPER LOWER and PROPER | 2:59 |

47. How to extract text with LEFT and RIGHT | 2:35 |

48. How to find text with a formula | 3:16 |

49. How to extract a first name with a helper column | 3:06 |

Video | Time |
---|---|

50. How Excel stores dates and times | 2:44 |

51. How to display current date and time | 1:51 |

52. How to work with dates | 2:54 |

53. How to work with times | 2:55 |

54. How to create date and time series with formulas | 3:24 |

55. How to calculate the number of days between dates | 3:05 |

56. How to calculate years and months between dates | 3:51 |

57. How to calculate and highlight expiration dates | 3:54 |

58. How to calculate due dates with WORKDAY | 3:30 |

Video | Time |
---|---|

59. How to use the COUNT function | 1:31 |

60. How to use the COUNTIF function | 3:03 |

61. How to use the COUNTIFS function | 3:07 |

62. How to use the SUM function | 1:32 |

63. How to use the SUMIF function | 3:43 |

64. How to use the SUMIFs function | 3:46 |

65. How to calculate an average value | 2:31 |

66. How to use the AVERAGEIF function | 3:14 |

67. How to use the AVERAGEIFs function | 3:20 |

68. How to calculate maximum and minimum values | 2:53 |

69. How to rank values with the RANK function | 2:59 |

70. How to count items in a filtered list | 3:15 |

71. How to get nth values with SMALL and LARGE | 4:01 |

Video | Time |
---|---|

72. How to use VLOOKUP | 3:10 |

73. How to use VLOOKUP for approximate matches | 2:53 |

74. How to use VLOOKUP for wildcard matches | 3:24 |

75. What to do when VLOOKUP returns NA | 2:40 |

76. How to troubleshoot VLOOKUP approximate match | 2:43 |

77. How to group values with VLOOKUP | 2:41 |

78. Why VLOOKUP is better than nested IFs | 2:56 |

79. How to replace nested IFs with VLOOKUP | 3:55 |

80. How to use VLOOKUP to merge tables | 3:06 |

81. How to look things up with INDEX | 2:48 |

82. How to use the MATCH Function for exact matches | 3:23 |

83. How to use MATCH to find approximate matches | 2:53 |

84. How to look things up with INDEX and MATCH | 2:48 |

85. How to do a two-way lookup with INDEX and MATCH | 3:18 |

86. Two-way lookup with INDEX and MATCH approximate | 3:08 |

87. How to use HLOOKUP | 2:20 |

88. How to use the CHOOSE function | 3:41 |

Video | Time |
---|---|

89. How to use the status bar for quick calculations | 2:36 |

90. How to combine functions in a formula | 2:45 |

91. How to create a complex formula step by step | 3:07 |

92. How to convert formulas to values | 2:14 |

93. How to make an exact copy of a formula | 2:59 |

94. How to save a formula that's not finished | 1:19 |

95. Don't hard code values that may change | 3:09 |

96. How to fill in missing data with a simple formula | 3:19 |

97. How to fill in missing data part 2 | 2:55 |

98. How to pick names out of a hat with Excel | 3:07 |

99. How to build all-in-one formulas | 3:44 |

100. How to find missing values with COUNTIF | 2:35 |

101. How to build a simple summary table | 3:01 |

102. Intro to Boolean Logic | 3:33 |

103. Complex formula example 401k Match | 3:17 |

104. Simplified formula example 401k Match | 3:43 |

105. How to generate random values | 3:39 |

106. How to build a simple dynamic chart | 3:56 |

107. How to randomly assign people to teams | 3:49 |

Video | Time |
---|---|

108. How to trace a formula error | 2:29 |

109. How to find and highlight formulas | 3:54 |

110. How to check and debug a formula with F9 | 3:12 |

111. How to step through complex formulas using Evaluate | 3:16 |

112. Excel formula error codes | 4:22 |

113. How to set formula error checking options | 2:38 |

114. Examples of flagged errors in formulas | 3:00 |

115. How to trace formula relationships | 3:13 |

116. How to trap errors in formulas | 3:17 |

Video | Time |
---|---|

117. 23 Excel formula tips | 9:19 |

- The difference between a function and formula. (And, why you need to know.)
- The
*operators*that Excel uses for formulas (=, >, ^, *, etc.) and how to control what order they run in. - How to easily enter and edit formulas (with or
*without*a mouse). - What "arguments" are, and why most functions need them (and the easiest way to select arguments,
*by far*). - A cool trick you can use to update prices, dates, and other numbers
*without*using formulas (surprisingly useful).

- The ins and outs of relative, absolute, and mixed references, and why you need all three types.
- Why relative references make Excel so powerful.
- A clever way to use a mixed
- How to easily create formulas that link to
*other*worksheets. - How to create a "3d reference", a powerful (and non-obvious) way to link to data on other worksheets.
- How to create a "dynamic reference" to a worksheet, so you can select a different the worksheet and get new results on-the-fly.

- How named ranges can be used just like absolute references, and how they make your formulas much easier to read and copy.
- How you can create a named range in about 3 seconds (and how to apply it to an existing formula).
- A simple way to use named ranges to create a "dynamic reference".
- How to create a named constant (great for variables you don't want to see on the worksheet).

- How dynamic named ranges are incredibly useful when you plan to add data to a report over time.
- What is a dynamic named range, and how to create one in about 2 seconds using Control + T (by far the simplest way to go in most cases).
- How to roll you own dynamic range with both OFFSET (easier, but volatile) and INDEX (more difficult, but pro-level).

- How to quickly set up an IF function for "If this, then that".
- How you can use simple functions like AND and OR to make your IF formula far more powerful, without making it more complex.
- How to create a formula with nested IFs (and how you can make a nested IF formula easier to read using a simple trick).
- How you can use VLOOKUP instead of a nested IF when you need to manage more possibilities (it's simple, and makes your formula super easy to read and maintain).

- How to join text to other text and with numbers (and how to keep those numbers formatted the way you want).
- How to join values with the ampersand (&) or the CONCATENATE function.
- How to convert character codes to ASCII codes and back again, and how you can use this to solve tricky problems (like build a mailing address that contains line breaks).
- A slick way to use concatenation to show assumptions anywhere that makes sense (and in a way that's fully dynamic).
- How to use special Excel functions to easily strip extra spaces and line breaks from messy text.
- How to change case with formulas (instantly make text upper, lower, and capitalized, in any combination).
- How to extract text from the right,left, or middle.
- How to find (and replace) text with a formula.
- How to find certain characters and use their position to extract just the text you need (like first name from a name).
- How to build up a more complex function in simple, easy steps (this is the key to building more complicated formulas).

- How Excel stores dates and times (and why you need to understand this to avoid hours of frustration).
- How to display current date and time with a formula so that its always up to date.
- How to take apart a date with a formula, modify values, and put it back together again. This is the key to generating the dates you need with a formula.
- Ditto above with times.
- How to use a formula to create a custom date series (like the last day of the next 18 months).
- How to calculate the number of days between dates, including or excluding weekends or holidays.
- How to calculate years and months between dates (great for calculating age, legal status, time before expiration, etc.).

- How to count both numbers and text.
- How to count with one or more conditions (count just sales in the West region, or just electric cars, or both).
- SUM with and without conditions. Easily sum total sales of all houses over $350k with 3+ bedrooms.
- Calculate an average instantly, including or excluding blank or zero values. Even average just data that meets certain conditions.
- Display maximum and minimum values, and even rank items in a list dynamically — without sorting!

- How to use the mighty VLOOKUP function (in less than a minute).
- How to VLOOKUP for both approximate matches (think tax rates, or postage) or wildcard matches (just type a few characters to get a match).
- What to do when VLOOKUP can't find a match (you can display a much more friendly message than #N/A).
- How to group values with VLOOKUP (a fantastic way to group data into your own custom groups with a simple formula).
- Why VLOOKUP is better than nested IFs (it's not even close).
- How to replace nested IFs with VLOOKUP (step by step instructions).
- How to look things up with INDEX, perhaps the most powerful function in all of Excel.
- How to use the MATCH Function for exact and approximate matches (boring, but not when you add INDEX!)
- How to look things up with INDEX and MATCH (the pro-level replacement for VLOOKUP, that offers more flexibility).
- How to do a two-way lookup with INDEX and MATCH (a classic problem, when you need to find both the right column AND he right row).
- How to use HLOOKUP, VLOOKUPs less-famous sibling.

- How to use the status bar for quick calculations (instant, no formulas required).
- How to create complex formula step by step (the key is to work backwards).
- How to change formulas to values (a process you'll use again and again).
- How to make an exact copy of a formula, so that relative references don't change.
- How to save a formula that's not finished. Excel will not let you save a formula that has the wrong syntax, but there's a simple workaround.
- Don't hard code values that may change. This is the single biggest mistake that even intermediate users make in Excel. Don't do it! Let Excel do the hard work for you.
- How to fill in missing data with a simple formula. An incredibly easy technique that can save you hours of mind-numbing work. Also a beautiful example of the power that relative references provide.
- How to pick names out of a hat with Excel (a fast technique you can use anytime you need to draw prizes with Excel).

- The only thing worse than no formulas, is formulas that don't work right.
- How to find and highlight all formulas (a great starting point in any new worksheet).
- How to trace formula relationships, and get Excel to show you (with arrows) the flow of data from one formula to the next.
- How to trace a formula error back to it's source.
- How to check and debug a formula with F9 (probably the single most import skill you need to learn to become really proficient with Excel formulas).
- How to step through complex formulas using a built-in tool called the formula evaluator.
- Excel formula error codes..what they mean and how to get rid of them when needed.
- Examples of flagged errors in formulas and the options that Excel provides for each kind of error.
- How to trap errors in formulas and display something a little more friendly than #DIV/0 or #VALUE when things go wrong.

The short answer is that Excel remains a critical tool at the center of a huge number of business decisions. And, just like important decisions don't get made without a spreadsheet, spreadsheets don't do much without formulas. They are the truly the bread and butter of Excel.

And yet, a huge amount of time is wasted every day by intelligent people trying to solve typical business problems with Excel formulas. It's not their fault. Excel is complicated, and almost no one gets good training on formulas.

Core Formula is designed to give you the formula training you should have had to begin with.

Core Formula covers a lot of ground. Using bite-sized videos in razor-sharp HD format, Core Formula teaches you how to use formulas for text, dates and times, VLOOKUP, INDEX & MATCH, summing and counting, nested IFs, dynamic ranges, troubleshooting, and a wide variety of techniques for referencing cells.

Each video is short – typically 3 minutes or less – and all videos come with a practice worksheet that you can download directly below the video. The course is is self-paced, so can move through it as fast or slow as you like. You can get more info and a quick video walk-through of the course here. (This video also covers some good free resources we've created for learning Excel formulas and functions).

Yes. There are some small differences in the Mac and Win interface with respect to formulas, but in general this is one area in Excel where there is very good overlap between the Mac and Windows versions. The videos were recorded in Excel 2010 on Windows 7. All videos highlight shortcuts for both Windows and Mac.

Of course! If you aren't happy with the course, just let us know within 30 days and we'll give you a full refund. We're always interested in your feedback (so we can make the course better), but this unconditional guarantee. We want you to be completely happy.

When you purchase the course, an account will be created for you automatically on the Exceljet website. You'll receive an email immediately with a link to login, and when you do log in, you'll have full access to all videos and practice worksheets for the course(s) you purchased.

We think people learn quickly and effectively with a no-nonsense approach that is quick, clean, and to the point.

In other words: *less is more*. Excel brings plenty of complexity to the table already :)

If you're not completely satisfied, let us know within 30 days for a full refund. We want you to be 100% satisfied.