Video lessons in this course

What you'll learn

Formula basics

  • 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).

References

  • 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 reference to create a running total (a pro-tip you can use in all kinds of situations).
  • 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.

Named ranges

  • 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).

Dynamic named ranges

  • 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).

IF Function

  • 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).

Text formulas

  • 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).

Dates and times

  • 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.).

Statistics

  • 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!

Lookups

  • 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.

Formula productivity tips

  • 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).

Audit and troubleshoot

  • 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.

Q + A

Why Excel Formulas?

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.

What's in the course?

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).

Will the course work for both Windows and Mac?

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.

Do you have a guarantee?

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.

How do I access the course content?

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.

Why short videos?

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 :)