background
Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for May 2024 for May 2024
:
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for May 2024 is Open
Wall Street Prep

Excel Crash Course

You don't have access to this course, but you can purchase it below.

Excel Crash Course

  •   91 Lessons
  •   8h 39m
  •   107,411 Students

For finance professionals, proficiency in Excel is a fundamental part of the job description. Whether you describe yourself as a beginner or proficient in Excel, this course will take you to the next level and turn you into an advanced "power" user.

course image

Course Objective

We’ll start with the basics before we quickly introduce you to lesser known time-saving keyboard shortcuts and powerful Excel functions and features that you can immediately put to use on the job. The only way to learn Excel is by doing, so you’ll be completing Excel exercises every step of the way.

This course is used to train new hires at:

What You Will Learn

  • Accelerate the internalization of the key time-saving shortcuts via exercise-based internalization drills and video-driven guidance
  • Learn how to incorporate and take advantage of the most popular and helpful Excel functions and table features for financial analysis
  • Learn to quickly create macro-based shortcuts
  • Build data tables and perform sensitivity analysis
  • Enhance the presentation of financial and valuation models by adding forms and toggles to your spreadsheets

Course Samples

Combining INDIRECT with MATCH
Go To Special
Dynamic Headers, Custom Formatting & the TEXT Function

Course TOC

Chapter 1: Welcome to Excel
1. Getting Started 9:12
2. Course Downloads Files
3. Excel for Mac Quick Setup 8:08
4. Using the Keyboard to Navigate the Excel Ribbon 5:13
5. Ribbon Layout 4:51
6. Excel Settings 7:57
7. Excel Basics Review Quiz
Chapter 2: Excel Basics
8. Basic Excel Shortcuts, Navigation & Editing 7:22
9. Basic Formatting 4:54
10. Referencing Cells from Other Worksheets 4:34
11. Navigation to Referencing Cells from Other Workbooks 1:27
12. Navigation Within Excel Review Quiz
Chapter 3: Formatting & Navigation
13. Splitting & Freezing Panes 4:12
14. Entering & Editing Data 7:57
15. Columns & Rows 6:31
16. Paste Special 9:52
17. Ctrl Shortcuts 1:28
18. Naming Cells 3:11
19. Anchoring Cells 3:32
20. Naming Worksheets 1:10
21. Grouping & Hiding Columns & Rows 3:12
22. Grouping Workbooks 2:44
23. Formula Auditing 6:50
24. Cell Alignment & Center Across Selection 2:56
25. Go To Special 4:12
26. Conditional Formatting 10:16
27. Creating Dynamic Headers, Custom Formatting & the TEXT Function 7:41
28. More on Custom Formatting 15:46
29. #Ref, #Div/0! & Other Excel Errors 2:54
30. Find and Replace 2:33
31. Page layout 6:31
32. Entering/Editing Data Review Quiz
Chapter 4: Date & Concatenation Functions
33. Introduction to Functions: SUM & Average 3:03
34. Simple & Nested IF Statements 9:58
35. IFS statement (2016+ only) 7:14
36. IFERROR & Concatenation 7:58
37. Date Functions: EOMONTH & EDATE 5:26
38. ISNUMBER, ISTEXT & Combining with IF to Create Overrides 7:10
39. More Date Functions: YEARFRAC, DAY, MONTH, YEAR & DATE 6:26
40. AND & OR Functions 6:17
41. Logical Functions Review Quiz
Chapter 5: Lookup Functions & Data Tables
42. HLOOKUP, VLOOKUP, CHOOSE, OFFSET & INDEX Functions 10:45
43. Common Errors with HLOOKUP, VLOOKUP, CHOOSE, OFFSET & INDEX 2:47
44. The MATCH Function 3:29
45. Combining H/VLOOKUP, OFFSET, INDEX, CHOOSE with MATCH 13:51
46. Combining INDIRECT with & 8:12
47. Creating Drop Down Menus with Data Validation 2:59
48. Combining INDIRECT with MATCH 6:54
49. The Address Function 7:36
50. Using COLUMN and ROW Functions as Counters in Complex Formulas 7:06
51. Data Tables 13:52
52. When Data Tables Fail: Self Referencing IF Statement 8:01
53. XLOOKUP: Why it Rocks 8:46
54. Nested (Two-Way) XLOOKUP vs INDEX MATCH 5:26
55. Using XLOOKUP to Generate Multiple Values 2:21
56. Scenario Analysis Using XLOOKUP 4:46
57. Where XLOOKUP Loses To INDEX MATCH 11:39
58. Lookup Functions & Data Tables Review Quiz
Chapter 6: Mathematical Functions
59. The SUMPRODUCT Function 4:28
60. Advanced SUMPRODUCT: Adding Criteria & Booleans in Excel 13:06
61. SUMIF/S & AVERAGEIF/S 8:22
62. CEILING, FLOOR & ABS Functions 1:36
63. ROUND, ROUNDUP, ROUNDDOWN & COMBIN Function 3:52
64. The MIN & MAX Functions 2:50
65. COUNT/A & COUNTIF/S Functions 5:01
66. Present Value & Future Value Functions 6:44
67. NPV & XNPV Functions 7:29
68. IRR & XIRR Functions 3:18
69. Mathematical Functions Review Quiz
Chapter 7: Text Functions
70. Text Functions (LEN, LEFT, RIGHT, MID, REPLACE, etc.) 7:59
71. Using Text Functions to Solve Data Extraction Challenges 7:48
72. Flash Fill (Excel 2013 Only) 2:47
73. Text to Columns 6:23
74. Remove Duplicates 1:57
75. VALUE and DATEVALUE 4:07
Chapter 8: Sorting, Filters and Pivot Tables
76. Sort & Subtotal 4:16
77. Autofilter 4:09
78. Pivot Tables, Part 1 9:11
79. Pivot Tables, Part 2 8:00
Chapter 9: Using Excel to Solve Problems
80. The fiscal half date problem 9:22
81. The Olympic event problem 16:13
82. Conclusion 0:23
Chapter 10: Using LAMBDA to Create Custom Functions
83. Create your first LAMBDA function 7:35
84. LAMBDA: Create a CAGR function 4:52
85. LAMBDA: Create a DSO function 3:32
86. LAMBDA: The =IMPLIEDG() function 4:18
87. LAMBDA: The =EOQUARTER() function 8:12
88. LAMBDA: The =TSM() function 8:16
89. LAMBDA: The =SHEETNAME() function 2:51
90. Using LAMBDA functions across multiple workbooks 2:48
Appendix: Recording Macros & Custom Formatting
91. VBA & Macros in Excel 7:07

Learner Feedback

Earn CPE Credits with WSP Online Courses

Wall Street Prep is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.