100+ Practical Excel Formulas & Tricks- Beginner To Advanced

[LeeAndro]

Published 12/2022MP4 | Video: h264, 1280x720 | Audio: AAC, 44.1 KHzLanguage: English | Size: 5.48 GB | Duration: 9h 35m

Complete Microsoft Excel Formula hand on training with Real Life Practical Examples.​

Learn & don't memorize!

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
LIFE: 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 challeg. In addition, data and analysis are becoming more and more accessible with . 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 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 while perfog 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 How 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 11+ 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 Bning! 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 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 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 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 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 for Section

Lecture 78 L01 Intro

Lecture 79 L02 Flash Fill (*)

Lecture 80 L03 Chag 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 &

Lecture 89 Material to 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 + Chag 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 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 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 Meg 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,Bners will learns to write a formula for the first ,Advanced Excel users will learn the new Dynamic Array formulas in Excel 2021 & OFFICE 365

HomePage:

https://www.udemy.com/course/100excelformula/

DOWNLOAD

https://1dl.net/1jdecfeu2703/BYjRXhI6__100_practi.part1.rar
https://1dl.net/rc6toh4ugydm/BYjRXhI6__100_practi.part2.rar
https://1dl.net/vpx10pgf53rs/BYjRXhI6__100_practi.part3.rar
https://1dl.net/8cm60qezan8d/BYjRXhI6__100_practi.part4.rar
https://1dl.net/le6j74tvf37v/BYjRXhI6__100_practi.part5.rar
https://1dl.net/z54qogir8suu/BYjRXhI6__100_practi.part6.rar