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

Alan Jarvis, Everything Is Easy, Once You Know How

Get unlimited access to every class

Taught by industry leaders & working professionals

Topics include illustration, design, photography, and more

Get unlimited access to every class

Taught by industry leaders & working professionals

Topics include illustration, design, photography, and more

Lessons in This Class

99 Lessons (9h 8m)

- 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 - Horizontal Dynamic Named Ranges
  
  11:09
- 58. 4 - Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for?
  
  1:25
- 59. 4 - Hyperlinking To A Different Sheet In The Same Workbook
  
  3:32
- 60. 4 - Creating Our First Macro
  
  5:37
- 61. 4 - Assigning A Macro To A Button
  
  4:13
- 62. 4 - Creating A List For Our Dropdown Using A Dynamic Named Range
  
  1:39
- 63. 4 - Using A Conditional Format To Know When A Value Is Missing
  
  4:34
- 64. 4 - Copying Conditional Formats And Creating Our Drop Downs
  
  2:50
- 65. 4 - Building Our Formula...INDIRECT Function
  
  2:55
- 66. 4 - Building Strings For Indirect Sheet And Cell References
  
  7:16
- 67. 4 - It's A One Or A Zero
  
  2:35
- 68. 4 - Working The Percentages And Adding Traffic Lights
  
  4:06
- 69. 4 - POWER USER - The HYPERLINK Function (And Problem)
  
  3:16
- 70. 4 - Exercise 1 - Fill In The Blanks
  
  0:59
- 71. 4 - Exercise 2 - Pretty It Up With A Macro
  
  1:45
- 72. 4 - Exercise 3 - Create A VLOOKUP Using A Built String With INDIRECT
  
  2:13
- 73. 5 - Creating A Gantt Chart Using A Worksheet
  
  7:39
- 74. 5 - Building The First Part Of Our Logical Test
  
  4:20
- 75. 5 - Multiple Logical Tests At Once Using AND
  
  8:28
- 76. 5 - Conditional Formatting
  
  5:32
- 77. 5 - Gantt Charts Using The Built In Charting Tools
  
  4:51
- 78. 5 - Gantt Charts With Different Colors For Different Criteria
  
  7:47
- 79. Bonus - How I Created Randomly Generated License Plate Numbers!
  
  7:14
- 80. Level 2 SQA 01 Calls Text Data Or How To Return a Column Title If Value is 1
  
  6:42
- 81. Level 2 SQA 02 Calls Text Data 2 This Time Using Text!
  
  9:02
- 82. Level 2 SQA 03 Extracting Phone Numbers From A Cell
  
  2:39
- 83. Level 2 SQA 04 What Is The CHOOSE Function Really Used For
  
  14:47
- 84. Level 2 SQA 05 Casing And Text Functions
  
  6:35
- 85. Level 2 SQA 06 Dynamic Charting From A Drop Down
  
  10:02
- 86. Level 2 SQA 07 Extracting a Unique List, And Summing The Money!
  
  2:25
- 87. Level 2 SQA 08 SUMIF With Dynamic Sum Range
  
  5:55
- 88. Level 2 SQA 09 VLOOKUPs With Pictures!
  
  4:30
- 89. Level 2 SQA 10 Data Validation With Dependent Dropdowns
  
  3:42
- 90. Level 2 SQA 11 Data Validation With Dependent Dropdowns (Dynamic Named Range Workaround)
  
  8:30
- 91. Level 2 SQA 12 Kinda A Vlookup From 2 Drop down Lists
  
  6:47
- 92. Level 2 SQA 13 Tiered Pricing
  
  9:00

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

Alan Jarvis, Everything Is Easy, Once You Know How

Watch this class and thousands more

Watch this class and thousands more

Lessons in This Class

1. 0 - Introduction, And Welcome To The Course

2. 1 - Proof Of Concept

3. 1 - Planning Ahead

4. 1 - Creating Our Data Entry Screen

5. 1 - (Custom) Formatting Dates & Time

6. 1 - Simple Calculations With Time

7. 1 - More (Useful) Calculations With Time

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

9. 1 - Adding Time

10. 1 - Creating A Template From An Image

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

12. 1 - Converting Time To A Decimal

13. 1 - A Little Bit Of Simple Data Entry

14. 1 - Simple Conditional Formatting For A Cleaner View

15. 1 - Calculating Time Out Of House Using Travel Time

16. 1 - Simple Logical Testing And Nested Logical Testing

17. 1 - Building Text Strings With A Formula

18. 1 - Before We Move On

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

20. 1 - Auto-populating Check Boxes

21. 1 - Radio Buttons as an Alternative to Check Boxes

22. 1 - PRACTICE EXERCISE 1 - Time To Add A New Entry

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

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

25. 2 - Simple VLOOKUPs

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

27. 2 - Using Data Validation To Get The Right Input

28. 2 - Let's Build Our Database!

29. 2 - Importing Data From A Text File

30. 2 - Importing Data From A Word File

31. 2 - Pulling Data From Multiple Sources

32. 2 - Using OTHER Look-Ups To Look Up!

33. 2 - LOOKUP From A LOOKUP With No Intermediary Step

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

35. 2 - Some Common Reasons VLOOKUPs Fail

36. 2 - One Inherent Flaw In VLOOKUP

37. 2 - POWER USER - A Breakdown Of Looking Up Backwards

38. 2 - POWER USER - The Other Way Of Looking Up Backwards

39. 2 - Backwards Look-Ups In Action

40. 2 - POWER USER - Dealing With Inconsistencies In User Entry

41. 2 - POWER USER - Fuzzy VLOOKUPs

42. 2 - POWER USER - VLOOKUPs With Multiple Inputs

43. 2 - POWER USER - Looking Up From Multiple Inputs Using An Array Formula

44. 2 - VLOOKUPs Brother...HLOOKUP

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

46. 2 - What To Look For When THAT Formula Didn't Work

47. 2 - The Fastest Way To Modify Your Column Numbers

48. 2 - POWER USER - VLOOKUPS With Moving Columns

49. 2 - Putting It All Together

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

51. 3 - A Simple Static Named Range Using A Single Cell

52. 3 - Creating A Named Range Using A Range Of Cells

53. 3 - Using Row Labels To Name Multiple Ranges

54. 3 - POWER USER - A Magic Trick Using Row And Column Labels

55. 3 - POWER USER - Dynamic Named Ranges

56. 3 - POWER USER - What To Do With Dynamic Names Ranges With Titles

57. 3 - Horizontal Dynamic Named Ranges

58. 4 - Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for?

59. 4 - Hyperlinking To A Different Sheet In The Same Workbook

60. 4 - Creating Our First Macro

61. 4 - Assigning A Macro To A Button

62. 4 - Creating A List For Our Dropdown Using A Dynamic Named Range

63. 4 - Using A Conditional Format To Know When A Value Is Missing

64. 4 - Copying Conditional Formats And Creating Our Drop Downs

65. 4 - Building Our Formula...INDIRECT Function

66. 4 - Building Strings For Indirect Sheet And Cell References

67. 4 - It's A One Or A Zero

68. 4 - Working The Percentages And Adding Traffic Lights

69. 4 - POWER USER - The HYPERLINK Function (And Problem)

70. 4 - Exercise 1 - Fill In The Blanks

71. 4 - Exercise 2 - Pretty It Up With A Macro

72. 4 - Exercise 3 - Create A VLOOKUP Using A Built String With INDIRECT

73. 5 - Creating A Gantt Chart Using A Worksheet

74. 5 - Building The First Part Of Our Logical Test

75. 5 - Multiple Logical Tests At Once Using AND