Core Formula

Formula basics

• The difference between a function and a 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 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 your 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 building 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 it is 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 the right row).
• How to use HLOOKUP, VLOOKUP's less-famous sibling.

Formula productivity tips

• How to use the status bar for quick calculations (instant, no formulas required).
• How to create complex formulas step by step (the key is to work backward).
• 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 its source.
• How to check and debug a formula with F9 (probably the single most important 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.