Excel Power User Beginner Tutorial

Welcome to the Excel Power User Beginner Tutorial. This is the seventh video in our Excel tutorial series. Be sure you have completed the first three videos as this will ensure you have the necessary tools to be effective during these more advanced tutorials. You may also want to ensure you have a notebook and pen handy for taking notes and writing down specific steps to aid you as you progress through this tutorial.

During the Excel Power User Beginner Tutorial, you will:

1. Become more familiar with VLOOKUP and its usage.
2. Learn the difference between INDEX and MATCH functions and their relationship to VLOOKUP.
3. Learn how Data Arrays work when analyzing data.
4. Learn how to use the OFFSET and COUNTA functions to create dynamic data arrays.
5. And Finally combine the various junctions when working with Conditional Formatting and Data Validation.

Please note you will need to download come Sample Files to complete this video tutorial. I have provided the links to the file in the Support Documents section below!

This first Microsoft Excel Power User Tutorial is roughly 1.75 hours in length and is broken into chapters:

CHAPTERS:

VLOOKUP 0:03
Range Lookup 7:45
Autofilling Lookup 10:41
Defining Names 14:06
Using Named Ranges to Retrieve Data 17:18
INDEX 22:11
MATCH 27:41
INDEX & MATCH (Part 1) 30:28
INDEX & MATCH (Part 2) 34:27
INDEX & MATCH (Part 3) 40:23
Introduction to Array Formula 44:00
Array Formula 46:20
Array Function 51:49
Dynamic Named Ranges (Offset) 1:01:03
OFFSET Function 1:05:30
Name Manager 1:12:52
Basic Conditional Formatting 1:18:37
Building New Rules and Troubleshooting 1:22:06
Expanding Conditional Formatting Fields 1:30:39
Text Search Conditional Formatting 1:33:14
Data Validating Conditional Formatting 1:40:16

Support Documentation:

Retrieving Data

Working with Arrays

Advanced Conditional Formatting