Exceljet

Quick, clean, and to the point

Excel tips: formulas

These videos come from our accelerated video training.
Tip: Calculate due dates with the WORKDAY function
This video show how to calculate due dates with the WORKDAY and WORKDAY.INTL functions. You can use WORKDAY to calculate intelligent ship dates, delivery dates, and completion dates that...
Run time: 3:40
Tip: How to plot survey data in a bar chart
In this video, we demo how to plot results from a survey question in an Excel bar chart. Along the way, we use the COUNTIF function to build a summary table, and show a simple technique for...
Run time: 3:42
Tip: Shade groups of rows with conditional formatting
In this video, we'll look at how to use conditional formatting to shade alternating groups of rows. For example, you can use this approach to shade groups of 3 rows, groups of 4 rows,...
Run time: 3:01
Tip: How to highlight approximate match lookups
In this video we show how to highlight approximate match lookups with conditional formatting and the LOOKUP function. Highlighting approximate matches is tricky, because you must replicate...
Run time: 3:55
Tip: Two-way lookup with INDEX and MATCH approximate
In this video, we'll look at how to build a two-way lookup with INDEX and MATCH, using approximate matching. This is a classic solution for non-exact lookups like income tax, shipping...
Run time: 3:10
Tip: How to highlight exact match lookups
In this video, we show you how to use conditional formatting to highlight rows and columns associated with exact match lookups. This is a sleek, interactive way to show users matching...
Run time: 4:20
Tip: Simplified formula example - 401k Match
In this video, we simplify two formulas, by replacing several IF function with MIN function and boolean logic. This is a great trick to make annoyingly complex formulas easier to read and...
Run time: 3:43
Tip: Complex formula example - 401k Match
In this video, we build two formulas to calculate the company match for a 401k contribution. This is a great example of building a complex formula with several nested IFs in small steps.
Run time: 3:17
Tip: How to use boolean logic in Excel formulas
In this video, we introduce the concepts of boolean values and boolean logic. Boolean logic can be a great way to simplify and speed up complex formulas, since it requires no branching.
Run time: 3:30
Tip: How to use VLOOKUP to merge tables
VLOOKUP is a great way to merge data in two tables based on a common ID. This video shows you how in 3 minutes. You can even use this technique to reorder the columns you retrieve.
Run time: 3:10
Tip: How to build a simple summary table
Although Pivot Tables are fantastic tools for summarizing data, sometimes you just want a simple, lightweight solution based on formulas. The good news is you can build your own summaries...
Run time: 3:01
Tip: How to combine functions in a formula
Combining functions is a core skill you *must* have in order to move beyond the basics in Excel. In this video, we show how to split, manipulate, and reassemble values using multiple...
Run time: 2:53
Tip: Perfect conditional formatting with dummy formulas
Conditional formatting with formulas can be tricky because you can't see what happens to the formula when the rule is applied. Dummy formulas let you visualize how formulas will behave...
Run time: 3:28
Tip: How to find missing values with COUNTIF
In this video, we use COUNTIF to compare lists and count values that belong to both lists. We also "flip" the formula to count values in one list that are missing on the other.
Run time: 2:35
Tip: How to make a nested IF formula
Nesting is a powerful tool, but once you combine more than a couple IFs, the logic can start to look like spaghetti quickly :) The trick is to keep things organized. In this video, Dave...
Run time: 3:15

Pages