Published 12/2022

What you'll learn

EFFICIENCY: Learn to work SMARTER in Ms Excel not HARDER

DONT MEMORIZE: Microsoft Excel Formulas are like Cooking. Learn the structure to write dynamic and powerful functions from 0

PRACTICAL: Learn Formula that you will use in real life (text manipulation, counting, summing, conditions, lookups)

PRO: Get Access to tips and tricks that will stay with you forever

EXPERIENCE: FROM EX HARVARD, MCKINSEY CONSULTANT & HEAD OF ADVANCED ANALYTICS IN BIG INSTITUTIONS

ALL LEVELS: Get access to a well structured course that goes from NOOB to Expert / Advanced level with Dynamic Array Formulas that you find in Office 365

FUN: Straight to the point yet entertaining lectures

LIFETIME: Access to HOURS of material such as lectures, practice sheets, quizzes and others

 

Requirements

Very basic knowledge of Excel

Preferred to have Microsoft Excel 2013+ as some features/formulas might not be available

For the advanced lessons, Excel 2021 / Office 365 to get the benefit of dynamic array formulas

 

Description

Why learn Microsoft Excel Formulas: Today, Ms Excel is becoming the tool of choice in companies (in the USA alone, more than 500,000 companies actively use Excel) to be able to perform analysis and present data in a Quick way. 90%+ of the work won't require complicated models. In addition, Excel is becoming more and more similar to tools such as Power BI, albeit with lower computing power. Learning (advanced level) Excel will help you learn the other tools quickly!Work Smarter not Harder: With the advance of technology and information, jobs are becoming more and more challenging. In addition, data and analysis are becoming more and more accessible with time. The difference in productivity between a NOOB in Excel and an advanced User is more than 10X. Imagine completing a task in 1 h vs 10h!Why this course: Formulas are the bread and butter of everything you do in Excel. You need them to perform your analysisUnlike many courses, this course teaches you how to use formulas in real life - You will see example you can relate to in addition to a selection of the most important formulas you will requireThis course teaches you how to understand formulas and not memorize them - This will enable you to use any formula in the futureCourse is designed for all levels: Whether you want to learn from scratch or deepen your understand in Excel with new Office 365 formulas, you are at the right place! 4. Curriculum - Practical Hands on Excel formulas & functions training:Formula structure, shortcuts, cell referencing and formula auditing toolsConditional operators such as IF, IFs, multiple ifs, and, OrStatistical formulas such as Sumif, averageif, random numbersLookup such as Vlookup, Hlookup, Xlookup, Offset, Choose and named rangesText manipulation functionsDate and Time functions such as Today, Now and NetworkdaysFormatting in Conditional formatting with a functionNew Dynamic Array formulas for Excel 2021 & Office 365! That will revolutionize how Excel worksMany tricks such as transposing data, and building dynamic graphs &reports 5. What you will learn:How to use the most common formulas in Excel in a practical wayHow to save time while performing your data analysisHow to build dynamic reportsNew Dynamic Array formulasHow to use formulas such as Sumproduct in unconventional waysHow to become pro at lookups with tricks such a reversed lookup, advanced lookups combining the basic index & match conceptsHow to Manipulate text like a pro which is key in data cleansingHow to play with dates to extract metrics such as Turnaround TimeHow to automate repetitive tasks with no VBA (and multiple other tricks)How to troubleshoot and fix your formulas 6. About me:Harvard MBAExtensive consulting experience with McKinsey & Co, having learned the ability to dissect problems and perform complex analysis in no time11+ years as a Vice President in a bank heading analytical and strategy teamsBuilt the whole analytics department of a 3500+ employee share services subsidiaryDegree in Computational Sciences among othersWell versed in Data and Artificial intelligence, having built chatbotsPassion for teaching Excel as a backbone to increasing productivity

 

Overview

Section 1: 01. The Beginning! Setting up the Right Way

 

Lecture 1 L01 Housekeeping, expectations & outline

 

Lecture 2 Sneak peak into the Ms Excel Formula course

 

Lecture 3 L02 Understanding the degree of difficulty of each lesson - the star concept

 

Lecture 4 All Material for Course

 

Section 2: 02. Formula 101 (*)

 

Lecture 5 Material to Download for Section

 

Lecture 6 L01 Intro

 

Lecture 7 L02 Excel Formula Syntax (**)

 

Lecture 8 L03 Referencing other Workbooks or Worksheets (*)

 

Lecture 9 L04 Order of Preference in Calculations (**)

 

Lecture 10 L05 Cell Referencing (**)

 

Lecture 11 L06 Hack - How to Copy Formulas and reference original range (*****)

 

Lecture 12 L07 Common Error types (**)

 

Lecture 13 L08 Formula Auditing Part 1 (***)

 

Lecture 14 L09 Evaluate Formula & Error Checking (*)

 

Lecture 15 L10 Go to Special Feature (*****)

 

Lecture 16 L11 Useful Shortcuts (**)

 

Lecture 17 L12 Formula Shortcuts (*)

 

Lecture 18 L13 Menu Shortcuts (***)

 

Lecture 19 L14 PC & Mac Extensive Shortcut List (*****)

 

Lecture 20 L15 Increase the size of the Text in the Formula Bar (*****)

 

Lecture 21 L16 Basic Excel Formulas you need to know (*)

 

Lecture 22 L17 Calculate Percentages correctly (***)

 

Lecture 23 L18 Circular Reference (*)

 

Lecture 24 L19 Data Validation Basics (*)

 

Lecture 25 L20 Unstack your data with this trick (*****)

 

Lecture 26 L21 Transpose to Horizontal (****)

 

Lecture 27 L22 Transpose to Vertical (*****)

 

Section 3: 03. IF statements & Logical Operators (**)

 

Lecture 28 Material to download for Section

 

Lecture 29 L01 Intro

 

Lecture 30 L02 If Statement (*)

 

Lecture 31 L03 Nested IF Statement (**)

 

Lecture 32 L04 And / Or Conditions (***)

 

Lecture 33 L05 Not <> with IF (*)

 

Lecture 34 L06 IFS statement & Emojis trick (***)

 

Lecture 35 L07 IFerror (**)

 

Lecture 36 L08 Isxxx statements + Row coloring trick (***)

 

Lecture 37 L09 IF with partial match - Pro trick (*****)

 

Section 4: All About Stats (***)

 

Lecture 38 Material to download for Section

 

Lecture 39 L01 Intro

 

Lecture 40 L02 Round (Normal, Up or Down) (*)

 

Lecture 41 L03 Popular Stat functions (*)

 

Lecture 42 L04 Ranking Basics (**)

 

Lecture 43 L05 Percentile (***)

 

Lecture 44 L06 Dealing with Randomness (***)

 

Lecture 45 L07 Ratios in Excel (***)

 

Lecture 46 L08 Sumproduct - Basic Use (**)

 

Lecture 47 L09 Master Sumproduct (*****)

 

Lecture 48 L10 Countifs, Sumifs, Averageifs (***)

 

Lecture 49 L11 Sumifs with Dates + EOMonth formula (***)

 

Lecture 50 L12 Build a Dynamic Mini Dashboard with Sumifs, Countifs, AverageIf (***)

 

Lecture 51 L13 Identify and Count Duplicates in 1 step + Range Name (***)

 

Lecture 52 L14 Sumif with Partial Match (****)

 

Lecture 53 L15 Control Shift Enter

 

Lecture 54 L16 MinIFs, MaxIfs & Aggregate - How to get min/max with conditions (*****)

 

Lecture 55 L17 Advanced Ranking - No skip (*****)

 

Section 5: Lookups (****)

 

Lecture 56 Material to download for Section

 

Lecture 57 L01 Intro

 

Lecture 58 L02 Named Ranges & Excel Tables (**)

 

Lecture 59 L03 Vlookup / Hlookup (*)

 

Lecture 60 L04 Row, Rows, Column, Columns practical application (****)

 

Lecture 61 L05 Joining Data with Vlookup (***)

 

Lecture 62 L06 Improve your Vlookup with an Excel Table (*)

 

Lecture 63 L07 Troubleshoot your Vlookup (**)

 

Lecture 64 L08 Approximate Match for Vlookup for lookup within boundaries (****)

 

Lecture 65 L09 Index Match Basics (***)

 

Lecture 66 L10 Index Match Advanced (*****)

 

Lecture 67 L11 Lookup in different sheets with Indirect (*****)

 

Lecture 68 L12 Reverse Lookup problem (*****)

 

Lecture 69 L13 Complex Lookup with Index & Sumproduct (*****)

 

Lecture 70 L14 Xlookup (***)

 

Lecture 71 L15 Cool HR Dashboard with XLOOKUP (***)

 

Lecture 72 L16 Choose Formula and its applications (****)

 

Lecture 73 L17 Offset with Rolling average example (****)

 

Lecture 74 L18 YTD Calculations with Offset & Sumproduct (*****)

 

Lecture 75 L19 Offset and Charts (****)

 

Lecture 76 L20 Multiple Matches (*****)

 

Section 6: Dealing with Text (**)

 

Lecture 77 Material to download for Section

 

Lecture 78 L01 Intro

 

Lecture 79 L02 Flash Fill (*)

 

Lecture 80 L03 Changing the Case of Text (**)

 

Lecture 81 L04 Concatenate Text (**)

 

Lecture 82 L05 Text Extraction (**)

 

Lecture 83 L06 Text & Value (***)

 

Lecture 84 L07 Search & Find practical application (**)

 

Lecture 85 L08 Combining Search & Find with other functions for powerful results (***)

 

Lecture 86 L09 More Complex example with Search/Find (****)

 

Lecture 87 L10 Substitute (***)

 

Lecture 88 L11 System Data (*****)

 

Section 7: Date & Time (***)

 

Lecture 89 Material to Download for Section

 

Lecture 90 L01 Intro

 

Lecture 91 L02 Dates Basics (*)

 

Lecture 92 L03 Flash fill & Format (*)

 

Lecture 93 L04 Today & Now (*)

 

Lecture 94 L05 Date fields extraction (*)

 

Lecture 95 L06 Get First/Last day of the month and First day of the year (***)

 

Lecture 96 L07 YearFrac (**)

 

Lecture 97 L08 Workday & Workday.Intl (***)

 

Lecture 98 L09 Networkdays, Networkdays.INTL + Changing holiday list (****)

 

Lecture 99 L10 Weekday & DateDif (**)

 

Lecture 100 L11 Count Fridays between 2 days (****)

 

Lecture 101 L12 Define a custom weekend in Networkdays (*****)

 

Section 8: Formatting with Formulas (****)

 

Lecture 102 Material to download for Section

 

Lecture 103 L01 Intro

 

Lecture 104 L02 Conditional formatting rules with formula (****)

 

Lecture 105 L03 Boring Mod used in a Powerful way (*****)

 

Lecture 106 L04 Conditional formatting with logical operators (***)

 

Lecture 107 L05 Adding icons to your reports (****)

 

Lecture 108 L06 Matrix Coloring (****)

 

Section 9: Dynamic Array formulas - Office 365 (*****)

 

Lecture 109 Material to download for Section

 

Lecture 110 L01 Intro

 

Lecture 111 L02 The Situation

 

Lecture 112 L03 The Buzz about dynamic arrays (*****)

 

Lecture 113 L04 Spill Range & # (*****)

 

Lecture 114 L05 Excel Table to automatically calculate formulas for new data (****)

 

Lecture 115 L06 Important new Excel Functions (*****)

 

Lecture 116 L07 Sort (*****)

 

Lecture 117 L08 Sortby (*****)

 

Lecture 118 L09 Filter (*****)

 

Lecture 119 L10 Unique (*****)

 

Lecture 120 L11 Filtering non adjacent columns (*****)

 

Lecture 121 L12 Creating a dropdown menu (*****)

 

Lecture 122 L13 Filtering based on DropDown Menu (*****)

 

Lecture 123 L14 Improve your calculations with Dynamic Arrays (*****)

 

Lecture 124 L15 Formatting with Dynamic Arrays (*****)

 

Lecture 125 L16 Charts with Dynamic Arrays (*****)

 

Lecture 126 L17 Sequence (*****)

 

Lecture 127 L18 Real life Dashboard with Sequence, Filter & Sort (*****)

 

Lecture 128 L19 Recap of Randarray (***)

 

Lecture 129 L20 Frequency (*****)

 

Lecture 130 L21 Create a Matrix with Transpose (*****)

 

Lecture 131 L22 Merging Spilled ranges (*****)

 

Lecture 132 L23 Simplify your formula with Let (*****)

 

Course is designed to go from no knowledge about formulas to advanced levels - No matter your level, you will benefit from the different sections,Beginners will learns to write a formula for the first time,Advanced Excel users will learn the new Dynamic Array formulas in Excel 2021 & OFFICE 365