Mastering Microsoft Excel Formulas

Advance your Excel knowledge with Excel formulas

What you'll learn

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

Requirements

Familiarity with basic excel

Microsoft Excel for Windows

Description

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