Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Microsoft Excel Essentials: Level 2 - Intermediate/Advanced
First Section
First Lecture
Welcome To The Course! (2:05)
Project 1 - Creating A Data Entry Screen To Populate Multiple Templates
1000 - Proof Of Concept (4:49)
Planning Ahead (2:13)
Creating Our Data Entry Screen (4:08)
(Custom) Formatting Dates And Time (6:02)
Simple Calculations With Time (2:35)
More (Useful) Calculations With Time (8:06)
It's About Time (And Dates) (8:28)
Adding With Time (3:39)
Creating A Template From An Image (11:34)
Importing A Template From An Existing Excel File (2:21)
Converting Time To A Decimal (5:41)
A Little Bit Of Simple Data Entry (3:11)
Simple Conditional Formatting For A Cleaner View (4:42)
Calculating Time Out Of House Using Travel Time (4:46)
Simple Logical Testing And Nested Logical Testing (8:28)
Building Text Strings With A Formula (12:10)
Before We Move On...Accessing The Developer Ribbon (1:05)
A Tick Box Exercise (Of Sorts) (7:11)
Auto-populating Check Boxes (11:48)
PRACTICE EXERCISE 1 - Time To Add A New Entry (1:40)
Defining A Working Area, And Protecting Your Work (4:40)
PRACTICE EXERCISE 2 - Set Up A Working Area, And Limit User Entry (1:06)
Project 2 - Building A Database With Excel
Simple VLOOKUPs (4:16)
Step 1 - Get Some Data In, And Split It (3:54)
Using Data Validation To Get The Right Input (3:47)
Let's Build Our Database! (6:12)
Importing Data From A Text File (2:01)
Importing Data From A Word File (2:37)
Pulling Data From Multiple Sources (3:33)
Using OTHER Look-Ups To Look Up! (5:25)
LOOKUP From A LOOKUP With No Intermediary Step (2:19)
Data Arrays Don't Have To Start At A1 (2:58)
Some Common Reasons VLook-Ups Fail (6:02)
One Inherent Flaw In VLOOKUP (1:10)
POWER USER - A Breakdown Of Looking Up Backwards (6:44)
The Other Way Of Looking Up Backwards (6:49)
Backwards Look-Ups In Action (3:47)
POWER USER - Dealing With Inconsistencies In User Entry (8:19)
POWER USER - Fuzzy VLOOKUPs (3:33)
Power User - VLOOKUPs With Multiple Inputs (9:39)
Power User - Looking Up Multiple Inputs Using An Array Formula (4:45)
VLOOKUP's Brother...HLOOKUP (5:04)
POWER USER - The Holy Grail - How To Return Multiple Values From A Single Look Up (13:33)
What To Look For When THAT Formula Didn't Work (4:05)
The Fastest Way To Modify Your Column Numbers (7:24)
POWER USER - Vlook-Ups With Moving Columns (3:05)
Putting It All Together (9:52)
The Finishing Touch - How Many Records Did I Find (4:28)
Project 3 - Named Ranges
A Simple Static Named Range Using A Single Cell (3:44)
Creating A Named Range Using A Range Of Cells (2:53)
Using Row Labels To Name Multiple Ranges (2:34)
POWER USER - A Magic Trick Using Row And Column Labels (4:55)
POWER USER - Dynamic Named Ranges (8:06)
POWER USER - What To Do With Dynamic Names Ranges With Titles (5:22)
POWER USER - Dynamic Charts (10:27)
Horizontal Dynamic Named Ranges (11:09)
Project 4 - What Can I Have For Dinner
Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for? (1:25)
Hyperlinking To A Different Sheet In The Same Workbook (3:32)
Creating Our First Macro (5:37)
Assigning A Macro To A Button (4:13)
Creating A List For Our Dropdown Using A Dynamic Named Range (1:39)
Using A Conditional Format To Know When A Value Is Missing (4:34)
Copying Conditional Formats And Creating Our Drop-Downs (2:50)
Building Our Formula...INDIRECT Function (2:55)
Building Strings For Indirect Sheet And Cell References (7:16)
It's A One Or A Zero (2:35)
Working The Percentages And Adding Traffic Lights (4:06)
POWER USER - The HYPERLINK Function (And Problem) (3:16)
PRACTICE EXERCISE 1 - Fill In The Blanks (0:59)
PRACTICE EXERCISE 2 - Pretty It Up (With A Macro) (1:45)
PRACTICE EXERCISE 3 - Create A VLOOKUP Using A Built String With INDIRECT (2:13)
Project 5 - Using Excel For Gantt Charts...Timelines And Project Plans!
Creating A Gantt Chart Using A Worksheet (7:39)
Building The First Part Of Our Logical Test (4:20)
Multiple Logical Tests At Once Using AND (8:28)
Conditional Formatting...Where The Magic Happens (5:32)
Gantt Charts Using The Built In Charting Tools (4:51)
Gantt Charts With Different Colors For Different Criteria (7:47)
Project 6 - Bonus Section - Just For Fun
Bonus - How I Created Randomly Generated License Plate Numbers! (7:14)
Student Questions Answered!
Level 2 SQA 01 - Calls Text Data - Or How To Return a Column Title If Value is 1 (6:42)
Level 2 SQA 02 - Calls Text Data 2 - This Time Using Text! (9:02)
Level 2 SQA 03 - Extracting Phone Numbers From A Cell (2:39)
Level 2 SQA 04 - What Is The CHOOSE Function Really Used For (14:47)
Level 2 SQA 05 - Casing And Text Functions (6:35)
Level 2 SQA 06 - Dynamic Charting From A Drop Down (10:02)
Level 2 SQA 07 - Extracting a Unique List, And Summing The Money! (2:25)
Level 2 SQA 08 - SUMIF With Dynamic Sum Range (5:55)
Level 2 SQA 09 - VLOOKUPs With Pictures! (4:30)
Level 2 SQA 10 - Data Validation With Dependent Dropdowns (3:42)
Level 2 SQA 11- Data Validation With Dependent Dropdowns (Dynamic Named Range Workaround) (8:30)
Level 2 SQA 12 - Using 2 Labels As A Lookup From Drop-downs (6:47)
Level 2 SQA 13 - Tiered Pricing (9:00)
Level 2 SQA 14 - Worksheet Protection (4:39)
Level 2 SQA 15 - The Middle Name Problem...and Solution! (9:03)
Level 2 SQA 16 - Finding Matches, And Counting Entries (8:06)
Level 2 SQA 17 - Fee Calculator, or LOOKUPs That Are True, Not False (5:42)
POWER USER - A Magic Trick Using Row And Column Labels
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock