7. Excel Crash Course

COURSE
Wall Street Prep
8 hrs

7. Excel Crash Course

COURSE
Wall Street Prep
8 hrs
$33per person
OR
Included in GO1 PremiumStarting from $12 per user for teamsLearn moreTry it free
$33per person
OR
Included in GO1 PremiumStarting from $12 per user for teamsLearn moreTry it free

Course Overview 

For finance professionals, proficiency in Excel is a fundamental part of the job description. Whether you currently work in Excel at a beginner or intermediate level, this course will take you to the next level and turn you into an advanced "power" user. 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 alongside the instructor at every step of the way.

Instructor - Matan Feldman 

Matan Feldman is Wall Street Prep’s Founder and Managing Partner. His responsibilities include business development, development of courses, and overseeing training programs. Matan has overseen training programs for clients including Morgan Stanley, Credit Suisse, Raymond James, Stifel Nicolaus, FBR Capital Markets, Sagent Advisors, Giuliani Capital Advisors, JP Morgan, Cerberus, Wharton Business School, London Business School, Kellogg, Booth, Stern (NYU), and Cornell. Prior to founding Wall Street Prep, Matan served in several capacities on Wall Street — first as an Analyst in Chase Manhattan Bank's Mergers & Acquisitions Group in New York, and subsequently as an Associate within JP Morgan's Equity Research Group, covering Food & Drug Retail Equities.

Learning
CHAPTER 1: WELCOME TO EXCEL
7.1.1 Getting Startedvideo
7.1.2 Using the Keyboard To Navigate the Excel Ribbonvideo • 5 mins
7.1.3 Ribbon Layoutvideo • 5 mins
7.1.4 Excel Settingsvideo • 8 mins
7.1.5 Excel Basics Review - Quizquiz
CHAPTER 2: EXCEL BASICS
7.2.1 Basic Excel Shortcuts, Navigation & Editingvideo • 7 mins
7.2.2 Basic Formattingvideo • 5 mins
7.2.3 Referencing Cells From Other Worksheetsvideo • 5 mins
7.2.4 Navigation to Referencing Cells from Other Workbooksvideo • 2 mins
7.2.5 Navigation Within Excel Review - Quizquiz
CHAPTER 3: FORMATTING & NAVIGATION
7.3.1 Splitting & Freezing Panesvideo • 4 mins
7.3.2 Entering & Editing Datavideo • 8 mins
7.3.3 Columns & Rowsvideo • 7 mins
7.3.4 Paste Specialvideo • 10 mins
7.3.5 Ctrl Shortcutsvideo • 1 min
7.3.6 Naming Cellsvideo • 3 mins
7.3.7 Anchoring Cellsvideo • 4 mins
7.3.8 Naming Worksheetsvideo • 1 min
7.3.9 Grouping & Hiding Columns & Rowsvideo • 3 mins
7.3.10 Grouping Workbooksvideo • 3 mins
7.3.11 Formula Auditingvideo • 7 mins
7.3.12 Cell Alignment & Center Across Selectionvideo • 3 mins
7.3.13 Go to Specialvideo • 4 mins
7.3.14 Conditional Formattingvideo • 10 mins
7.3.15 Creating Dynamic Headers, Custom Formatting & the TEXT Functionvideo • 8 mins
7.3.16 More on Custom Formattingvideo • 16 mins
7.3.18 Find and Replacevideo • 3 mins
7.3.17 #Ref, #Div/0! & Other Excel Errorsvideo • 3 mins
7.3.19 Page layoutvideo • 7 mins
7.3.20 Entering/Editing Data Review - Quizquiz
CHAPTER 4: DATE & CONCATENATION FUNCTIONS
7.4.1 Introduction to Functions: SUM & Averagevideo • 3 mins
7.4.2 Simple & Nested IF Statementsvideo • 10 mins
7.4.3 IFS statement (2016+ only)video • 7 mins
7.4.4 IFERROR & Concatenationvideo • 8 mins
7.4.5 Date Functions: EOMONTH & EDATEvideo • 5 mins
7.4.6 ISNUMBER, ISTEXT & Combining with IF to Create Overridesvideo • 7 mins
7.4.7 More Date Functions: YEARFRAC, DAY, MONTH, YEAR & DATEvideo • 6 mins
7.4.8 AND & OR Functionsvideo • 6 mins
7.4.9 Logical Functions Review - Quizquiz
CHAPTER 5: LOOKUP FUNCTIONS & DATA TABLES
7.5.1 HLOOKUP, VLOOKUP, CHOOSE, OFFSET & INDEX Functionsvideo • 11 mins
7.5.2 Common Errors with HLOOKUP, VLOOKUP, CHOOSE, OFFSET & INDEXvideo • 3 mins
7.5.3 The MATCH Functionvideo • 3 mins
7.5.4 Combining H/VLOOKUP, OFFSET, INDEX, CHOOSE with MATCHvideo • 14 mins
7.5.5 Combining INDIRECT with &video • 8 mins
7.5.6 Creating Drop Down Menus with Data Validationvideo • 3 mins
7.5.7 Combining INDIRECT with MATCHvideo • 1 hr
7.5.8 The Address Functionvideo • 8 mins
7.5.9 Using COLUMN and ROW Functions and Counters in Complex Formulasvideo • 7 mins
7.5.10 Data Tablesvideo • 14 mins
7.5.11 When Data Tables Fail - Self Referencing IF Statementvideo • 8 mins
7.5.12 Lookup Functions and Data Tables Review - Quizquiz
CHAPTER 6: MATHEMATICAL FUNCTIONS
7.6.1 The SUMPRODUCT Functionvideo • 4 mins
7.6.2 Advanced SUMPRODUCT: Adding Criteria & Booleans in Excelvideo • 13 mins
7.6.3 SUMIF/S & AVERAGEIF/Svideo • 8 mins
7.6.4 CEILING, FLOOR & ABS Functionsvideo • 2 mins
7.6.5 ROUND, ROUNDUP, ROUNDDOWN & COMBIN Functionvideo • 4 mins
7.6.7 COUNT/A & COUNTIF/S Functionsvideo • 5 mins
7.6.6 The MIN & MAX Functionsvideo • 3 mins
7.6.8 Present Value & Future Value Functionsvideo • 7 mins
7.6.9 NPV & XNPV Functionsvideo • 7 mins
7.6.10 IRR & XIRR Functionsvideo • 3 mins
7.6.11 Mathematical Functions Review - Quizquiz
CHAPTER 7: TEXT FUNCTIONS
7.7.1 Text Functions (LEN, LEFT, RIGHT, MID, REPLACE, etc.)video • 8 mins
7.7.2 Using Text Functions to Solve Data Extraction Challengesvideo • 8 mins
7.7.3 Flash Fill (Excel 2013 Only)video • 3 mins
7.7.4 Text to Columnsvideo • 6 mins
7.7.5 Remove Duplicatesvideo • 2 mins
7.7.6 VALUE and DATEVALUEvideo • 4 mins
CHAPTER 8: SORTING, FILTERS AND PIVOT TABLES
7.8.1 Sort & Subtotalvideo • 4 mins
7.8.2 Autofiltervideo • 4 mins
7.8.3 Pivot Tables, Part 1video • 9 mins
7.8.4 Pivot Tables, Part 2video • 8 mins
CHAPTER 9: USING EXCEL TO SOLVE PROBLEMS
7.9.1 The fiscal half date problemvideo • 9 mins
7.9.2 The Olympic event problemvideo • 16 mins
7.9.3 Conclusionvideo • 1 min
APPENDIX: RECORDING MACROS & CUSTOM FORMATTING
7. Appendix VBA & Macros in Excelvideo • 7 mins
Supplementary Materials
Step by Step Course Manualdocument
Excel Practice Sheetdocument
Excel 2010 shortcutsdocument
Excel 2013 shortcutsdocument
Excel mac settingsdocument
Excel shortcuts 2016 Windows Macdocument
CommunityBlogPartners
© Copyright 2019 GO1 - All Rights Reserved