Microsoft Excel Essentials: Level 2 - For Intermediate/Advanced Students - From User To Superuser | Alan Jarvis | Skillshare

# Microsoft Excel Essentials: Level 2 - For Intermediate/Advanced Students - From User To Superuser

#### Alan Jarvis, Everything Is Easy, Once You Know How

Play Speed
• 0.5x
• 1x (Normal)
• 1.25x
• 1.5x
• 2x
99 Lessons (9h 5m)
• 1. 0 - Introduction, And Welcome To The Course

1:58
• 2. 1 - Proof Of Concept

4:49
• 3. 1 - Planning Ahead

2:13
• 4. 1 - Creating Our Data Entry Screen

4:08
• 5. 1 - (Custom) Formatting Dates & Time

6:02
• 6. 1 - Simple Calculations With Time

2:35
• 7. 1 - More (Useful) Calculations With Time

8:06
• 8. 1 - It's About Time (And Dates)

8:28
• 9. 1 - Adding Time

3:39
• 10. 1 - Creating A Template From An Image

11:34
• 11. 1 - Importing A Template From An Existing Excel File

2:21
• 12. 1 - Converting Time To A Decimal

5:41
• 13. 1 - A Little Bit Of Simple Data Entry

3:11
• 14. 1 - Simple Conditional Formatting For A Cleaner View

4:42
• 15. 1 - Calculating Time Out Of House Using Travel Time

4:46
• 16. 1 - Simple Logical Testing And Nested Logical Testing

8:28
• 17. 1 - Building Text Strings With A Formula

12:10
• 18. 1 - Before We Move On

1:05
• 19. 1 - A Tick Box Exercise (Of Sorts)

7:11
• 20. 1 - Auto-populating Check Boxes

11:48
• 21. 1 - Radio Buttons as an Alternative to Check Boxes

13:25
• 22. 1 - PRACTICE EXERCISE 1 - Time To Add A New Entry

1:40
• 23. 1 - Defining A Working Area And Protecting Your Work

4:40
• 24. 1 - PRACTICE EXERCISE 2 - Set Up A Working Area And Limit User Entry

1:06
• 25. 2 - Simple VLOOKUPs

4:16
• 26. 2 - Step1: Get Some Data In And Split It!

3:54
• 27. 2 - Using Data Validation To Get The Right Input

3:47
• 28. 2 - Let's Build Our Database!

6:12
• 29. 2 - Importing Data From A Text File

2:01
• 30. 2 - Importing Data From A Word File

2:37
• 31. 2 - Pulling Data From Multiple Sources

3:33
• 32. 2 - Using OTHER Look-Ups To Look Up!

5:25
• 33. 2 - LOOKUP From A LOOKUP With No Intermediary Step

2:19
• 34. 2 - Data Arrays Don't Have To Start At A1!

2:58
• 35. 2 - Some Common Reasons VLOOKUPs Fail

6:02
• 36. 2 - One Inherent Flaw In VLOOKUP

1:10
• 37. 2 - POWER USER - A Breakdown Of Looking Up Backwards

6:44
• 38. 2 - POWER USER - The Other Way Of Looking Up Backwards

6:49
• 39. 2 - Backwards Look-Ups In Action

3:47
• 40. 2 - POWER USER - Dealing With Inconsistencies In User Entry

8:19
• 41. 2 - POWER USER - Fuzzy VLOOKUPs

3:33
• 42. 2 - POWER USER - VLOOKUPs With Multiple Inputs

9:39
• 43. 2 - POWER USER - Looking Up From Multiple Inputs Using An Array Formula

4:45
• 44. 2 - VLOOKUPs Brother...HLOOKUP

5:04
• 45. 2 - POWER USER - The Holy Grail - How To Return Multiple Values From A Single LookUp

13:33
• 46. 2 - What To Look For When THAT Formula Didn't Work

4:05
• 47. 2 - The Fastest Way To Modify Your Column Numbers

7:24
• 48. 2 - POWER USER - VLOOKUPS With Moving Columns

3:05
• 49. 2 - Putting It All Together

9:52
• 50. 2 - The Finishing Touch: How Many Records Did I Find?

4:28
• 51. 3 - A Simple Static Named Range Using A Single Cell

3:44
• 52. 3 - Creating A Named Range Using A Range Of Cells

2:53
• 53. 3 - Using Row Labels To Name Multiple Ranges

2:34
• 54. 3 - POWER USER - A Magic Trick Using Row And Column Labels

4:55
• 55. 3 - POWER USER - Dynamic Named Ranges

8:06
• 56. 3 - POWER USER - What To Do With Dynamic Names Ranges With Titles

5:22
• 57. 3 - POWER USER - Dynamic Charts

10:27
• 58. 3 - Horizontal Dynamic Named Ranges

11:09
• 59. 4 - Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for?

1:25
• 60. 4 - Hyperlinking To A Different Sheet In The Same Workbook

3:32
• 61. 4 - Creating Our First Macro

5:37
• 62. 4 - Assigning A Macro To A Button

4:13
• 63. 4 - Creating A List For Our Dropdown Using A Dynamic Named Range

1:39
• 64. 4 - Using A Conditional Format To Know When A Value Is Missing

4:34
• 65. 4 - Copying Conditional Formats And Creating Our Drop Downs

2:50
• 66. 4 - Building Our Formula...INDIRECT Function

2:55
• 67. 4 - Building Strings For Indirect Sheet And Cell References

7:16
• 68. 4 - It's A One Or A Zero

2:35
• 69. 4 - Working The Percentages And Adding Traffic Lights

4:06
• 70. 4 - POWER USER - The HYPERLINK Function (And Problem)

3:16
• 71. 4 - Exercise 1 - Fill In The Blanks

0:59
• 72. 4 - Exercise 2 - Pretty It Up With A Macro

1:45
• 73. 4 - Exercise 3 - Create A VLOOKUP Using A Built String With INDIRECT

2:13
• 74. 5 - Creating A Gantt Chart Using A Worksheet

7:39
• 75. 5 - Building The First Part Of Our Logical Test

4:20
• 76. 5 - Multiple Logical Tests At Once Using AND

8:28
• 77. 5 - Conditional Formatting

5:32
• 78. 5 - Gantt Charts Using The Built In Charting Tools

4:51
• 79. 5 - Gantt Charts With Different Colors For Different Criteria

7:47
• 80. Bonus - How I Created Randomly Generated License Plate Numbers!

7:14
• 81. Level 2 SQA 01 Calls Text Data Or How To Return a Column Title If Value is 1

6:42
• 82. Level 2 SQA 02 Calls Text Data 2 This Time Using Text!

9:02
• 83. Level 2 SQA 03 Extracting Phone Numbers From A Cell

2:39
• 84. Level 2 SQA 04 What Is The CHOOSE Function Really Used For

14:47
• 85. Level 2 SQA 05 Casing And Text Functions

6:35
• 86. Level 2 SQA 06 Dynamic Charting From A Drop Down

10:02
• 87. Level 2 SQA 07 Extracting a Unique List, And Summing The Money!

2:25
• 88. Level 2 SQA 08 SUMIF With Dynamic Sum Range

5:55
• 89. Level 2 SQA 09 VLOOKUPs With Pictures!

4:30
• 90. Level 2 SQA 10 Data Validation With Dependent Dropdowns

3:42
• 91. Level 2 SQA 11 Data Validation With Dependent Dropdowns (Dynamic Named Range Workaround)

8:30
• 92. Level 2 SQA 12 Kinda A Vlookup From 2 Drop down Lists

6:47
• 93. Level 2 SQA 13 Tiered Pricing

9:00
• 94. Level 2 SQA 14 Worksheet Protection

4:39
• 95. Level 2 SQA 15 The Middle Name Problem

9:03
• 96. Level 2 SQA 16 Findng Matches, And Counting Entries

8:06
• 97. Level 2 SQA 17 Fee Calculator, or LOOKUPs That Are True, Not False

5:42
• 98. Level 2 SQA 18 The IF(s) Functions

6:01
• 99. Level 2 SQA 18 Drop Down List, Shifting Ranges

6:07
85 students are watching this class