Video Lesson: Information Functions in Excel

In the following lesson I go through the Experience Level 1 Information Functions.  These functions are used to obtain information about formatting & referencing about specific cell or range references.  In this lesson I review the following functions:

ISBLANK – ISERROR – ISTEXT – ISNONTEXT – ISNUMBER

as well as the following info_types of the CELL() function:

“address” – “row” – “col” – “contents” – “width” – “filename”

“color” – “prefix” – “protect” – “type” – “format”

The CELL(“format”) info_type has many code results, so I’ve summarized them here:

Make sure to download the latest Master Workbook to follow along with the video lesson.

Video Lesson: Advanced Lookup – Index & Match

The versatility and usefulness of the Index and Match functions are cherished by Excel veterans, but many less experienced users have either not encountered them, or are a bit confused by the usage of the two.

This Excel Video Lesson shows you how you can use the Index & Match functions to do advanced lookups which can be more reliable and versatile than just VLOOKUP or HLOOKUP alone.  In the video I go over the following uses of Index & Match:

– Two-Way Lookup (using both a row header and column header to find the intersecting value).  This can be very helpful when columns and rows may be added or deleted to your ranges.

– Averaging entire row/column information based on a lookup value.

– Summing entire row/column information based on a lookup value.

Make sure to download the most recent Master Workbook so that you can follow along at home!

Video Lesson: Logical Functions and Formulas

This Excel video lesson is about Logical Functions and Formulas in Excel.  Here are the formulas that I go through in the video lesson:

Logical formulas are extremely useful in more advanced Excel spreadsheets/methods and they are a great tool to have at your disposal.

Make sure to download the most recent Master Excel Workbook so that you can follow along. The spreadsheet I’m reviewing is in the Function Examples sheet in that Excel file.

The formulas covered in this video lesson are:

IF – IFERROR – AND – OR – TRUE – FALSE – NOT

Thanks for watching and feel free to leave any questions or comments in the comment box below!  I will respond via email if you include it with your comment.

Video Lesson: Lookup / Reference Functions

As listed in the Master Workbook (see link at the top of the page), there are functions grouped into categories. This video is a demonstration of the Experience Level 1 – Lookup & Reference functions.

The functions covered in this video lesson are:

ADDRESS  — COLUMN — COLUMNS — HLOOKUP  — INDEX — INDIRECT

LOOKUP — MATCH — OFFSET  —  ROW  —  ROWS  —  VLOOKUP

Extract of Level 1 – Lookup and Reference Functions from the Master Workbook:

A good follow-up lesson to check out would be either the Advanced Vlookup Examples or Advanced Lookup – Index & Match lessons.

Excel Video Lesson: Text Functions

This video tutorial is a demonstration of the Experience Level 1 Text functions.

Make sure to download the newest version of the Master Workbook that I updated earlier today to follow along.

The functions covered in this lesson are:

CONCATENATE — EXACT — FIND — LEFT — LEN — LOWER — MID — PROPER
REPLACE — RIGHT — SEARCH — TEXT — TRIM — UPPER

Extract of Level 1 – Text Functions from the Master Workbook:

Text Functions marked as Level 1

Next video lesson is Logical Functions and Formulas.

Any feedback is appreciated.

Excel Video Lesson: Date & Time Functions

As listed in the Master Workbook, there are functions grouped into categories. This video is a demonstration of the Experience Level 1 Date & Time functions.

The functions covered in this video lesson are:

DATE — DATEVALUE — TODAY  — DAY  — MONTH — YEAR
TIME — TIMEVALUE — NOW — HOUR — MINUTE — SECOND

CORRECTION: I mention in the video that Excel views dates as the number of days since January 1, 1900 but it is actually January 0, 1900 (a bit confusing since January 0 doesn’t make intuitive sense).

Now that you’re an Excel ninja when it comes to Date & Time functions, try mastering Text Functions (e.g. LEN, CONCATENATE)