Mastering Microsoft Excel Formulas

[BaDshaH]

Published 1/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 2.00 GB | Duration: 4h 21m

Advance your Excel knowledge with Excel formulas

[b]What you'll learn[/b]
How to use 50+ everyday excel functions
How to combine multiple functions in Excel easily
Using Aggregate functions in excel
Using Text functions such as LEFT, CONCATENATE, TEXTJOIN etc
Using Date functions such as EOMONTH, EDATE, DATEDIF etc
Using Lookup functions such as VLOOKUP, XLOOKUP, INDEX&MATCH
Using Maths and Stats functions such as SUMIF, COUNTIF, AVERAGEIF etc
Using Logical functions such as IF, AND, OR etc

[b]Requirements[/b]
Familiarity with basic excel
Microsoft Excel for Windows

[b]Description[/b]
This excel course is taught by Ahmed Oyelowo, who is a 4 time Microsoft MVP, Microsoft Certified Trainer since 2018 and also a Microsoft Office Specialist (Excel Expert).In this course, you will learn, with practical examples, how to use over 50 everyday Excel functions.You will also be learning an easy way of combining multiple Excel functions inside a single Excel formula.The course comes with 2 sets of Excel Workbook files.One used by the instructor which you should use to follow along in the lessons andThe other one for your own practice. The practice file will automatically mark you once you have completed the exercises in there.You will also learn the logic of Cell Referencing, which is the back bone of writing accurate formulas when you need to replicate formulas across multiple cells in Excel.What functions will you learn in this course?Aggregate functions in ExcelSUMAVERAGECOUNTCOUNTALARGESMALLMINMAXText functions in ExcelLEFTRIGHTMIDFINDSEARCHLENUPPERLOWERCONCATENATECONCATTEXTJOINDate functions in ExcelDATEDAYMONTHYEAREDATEEOMONTHNETWORKDAYSNETWORKDAYS.INTLWEEKNUMWEEKDAYDATEDIFData Analysis Function in ExcelCOUNTIFSUMIFCOUNTIFSAVERAGEIFSUMIFSLOGICAL Functions in ExcelIFANDORLookup functions in ExcelVLOOKUPXLOOKUPMATCHINDEX

Overview
Section 1: Introduction to writing formulas in Excel
Lecture 1 Before you begin
Lecture 2 Download Class Demo files
Lecture 3 Introduction
Lecture 4 Understanding Function Syntax
Lecture 5 Understanding Expressions (1)
Lecture 6 Understanding Expressions (2)
Lecture 7 Introduction to Excel aggregate functions
Lecture 8 Using Aggregate Functions in Excel (2)
Lecture 9 Replicating formulas and using the AUTOSUM feature smartly
Lecture 10 Using the SUBTOTAL function for filtered data
Lecture 11 Counting data ranges
Section 2: Working with Excel Text functions
Lecture 12 Converting characters between cases in Excel
Lecture 13 Extracting characters from beginning and ending of texts with LEFT and RIGHT
Lecture 14 Using MID to extract characters and TRIM to remove extra spaces
Lecture 15 Combining texts with CONCATENATE, CONCAT and TEXTJOIN functions
Lecture 16 Coding and decoding between characters and numbers with UNICHAR and UNICODE
Lecture 17 Counting characters with LEN and finding position with FIND and SEARCH
Section 3: Working with Excel Date Functions
Lecture 18 Introduction to DATE functions
Lecture 19 Extracting DAY, MONTH and YEAR from a Date
Lecture 20 Extracting Day of Week and Week of year with WEEKDAY and WEEKNUM
Lecture 21 Using TEXT function to display dates in text formats
Lecture 22 Using DATEVALUE to convert date in text format to proper date format in excel
Lecture 23 Getting the endo of month date with EOMONTH function in Excel
Lecture 24 Moving periods between dates with EDATE function in Excel
Lecture 25 Using NETWORKDAYS & NETWORKDAYS.INTL to get number of working days in excel
Lecture 26 Introduction to DATEDIF function in Excel
Lecture 27 Calculating different units of age with DATEDIF
Section 4: Combining multiple functions in a single formula
Lecture 28 Introduction to combining multiple functions
Lecture 29 Combining Excel Functions (part 1)
Lecture 30 Combining Excel Functions (part 2)
Lecture 31 Combining Excel Functions (part 3)
Lecture 32 Combining Excel Functions (part 3b)
Section 5: Understanding Cell Referencing in Excel
Lecture 33 Introduction to Cell Referencing in Excel
Lecture 34 Using the Relative (default) cell referencing stye
Lecture 35 Using the Absolute (full lock) cell referencing style
Lecture 36 Using Mixed (row lock and column lock) referencing style
Section 6: Working with Maths, Statatistics and Data Analysis Functions in Excel
Lecture 37 Introduction to Logical Operators in Excel
Lecture 38 Using COUNTIF function to count number of value occurrences within a range
Lecture 39 Using COUNTIF function to count number of value occurrences within a range (2)
Lecture 40 Using COUNTIFS to count values in ranges with multiple criteria
Lecture 41 Summarizing data values based on a criteria with SUMIF and AVERAGEIF
Lecture 42 Summarizing data values based on multiple criteria with SUMIFS
Section 7: Working with Logical Functions in Excel
Lecture 43 Introduction to logical functions in Excel
Lecture 44 Using the IF function to write conditional expressions (part 1)
Lecture 45 Using the IF function to write conditional expressions (part 2)
Lecture 46 Understanding the AND & OR functions in Excel
Lecture 47 Combining IF with AND to write an expression based on multiple conditions
Lecture 48 Combining IF with OR to write an expression based on multiple conditions
Lecture 49 Using nested IF
Section 8: Using Lookup functions in Excel
Lecture 50 Introduction to VLOOKUP function
Lecture 51 Using the VLOOKUP function to return values matched from a data range (1)
Lecture 52 Using the VLOOKUP function to return values matched from a data range (2)
Lecture 53 Introduction to MATCH function in Excel
Lecture 54 Automating VLOOKUP column index with MATCH function
Lecture 55 Handling unfound values by combing VLOOKUP with IFERROR function
Lecture 56 Using the VLOOKUP function to return values matched from a data range (3)
Lecture 57 Introduction to XLOOKUP function
Lecture 58 Introduction to INDEX function
Lecture 59 Automating Row or Column number in INDEX with the MATCH function
This course is for all category of excel learners.,Looking to advance your excel knowledge beyond the basics,Looking to start or upskill yourself in data analytics

Homepage

[To see links please register or login]

[To see links please register or login]