VLOOKUP() allows for easy matching of a lookup value in a table that then returns the corresponding value in one of the other columns of the table.

Since months are different lengths and years start on different days, there are functions in Excel to handle these details. EDATE() increments dates by one month (whether that is a 30 or 31 day month). EOMONTH() increments a date to the end of a month (either 30 or 31). WEEKDAY() returns the day of the week (1-7) for a date. And WEEKNUM() returns the week number for a date (useful for collecting weekly sales totals).

Dates and times are stored as “serial numbers” and Excel provides functions to find the components: day, month, year, hour, minute, and second.

The way dates and times are typically formatted are not how they are actually stored in a spreadsheet. This video describes the way that Excel uses “serial numbers” for dates and times.

This function allows for an alternative value if a formula results in an error (such as division by zero).

The IF() function allows you to check a (boolean) condition, then if the condition is TRUE use one formula, or if the condition is FALSE, use another formula.

Just like Excel will handle standard arithmetic, there is also a way to create formulas that return TRUE or FALSE. These can be used to then create calculations that depend on the boolean result — calculate one way if true, calculate a different way if false.

The regression tool allows us to see relationships between columns. In the video we look at one column related to another, but if you have more than one independent variable, the only change is to highlight more than one column. Unfortunately, those columns need to be in one contiguous range.

Excel added a new chart type for histograms. Older versions of Excel will not have this option.

Create scatterplots without highlighting new data by using the INDEX() function