Gain Mastery Of Over 135+ Excel Functions (Office 2021)

[Panter]

Gain Mastery Of Over 135+ Excel Functions (Office 2021)
Published 10/2022
MP4 | Video: h264, 1280x720 | Audio: AAC, 44.1 KHz
Language: English | Size: 10.68 GB | Duration: 24h 48m

Be a professional in MS Excel Functions - Dynamic Array functions, Formula Auditing, Evaluating data, etc



What you'll learn
Excel 365/Office 2021 Dynamic Array Functions - XMATCH, XLOOKUP, SORT, FILTER, RANDARRAY, SEQUENCE, UNIQUE, SORTBY
Understand how to manipulate INDEX/MATCH functions and VLOOKUP/MATCH functions and HLOOKUP/MATCH function
Get a good grip on how to work with Maths & Trig Functions - TRUNC, POWER, SUM, SUMIF, SUMIFS, QUOTIENT, SUMX2PY2, SUMX2MY2, ,LCM, SIN, COS, TAN, etc
Understand how to work with Lookup & Reference Functions - VLOOKUP, HLOOKUP, ADDRESS, ROW, TRANSPOSE, MATCH, UNIQUE, etc
Master how to work with Logical Functions - IF, IFERROR, IFS, NOT, OR, AND, etc
Enjoy how to work with Text Functions - LET, CONCAT, MID, REPT, TEXT, ARRAYTOTEXT, TRIM, LEN, LEFT, RIGHT, etc
Understand how to work with Statistical Functions - FREQUENCY, MAXIFS, MINIFS, RANKEQ, COUNTA, COUNTIF, COUNTIFS, MIN, MAX, MEDIAN, etc
Understand how to work with Date & Time Functions - DAYS360, NOW, TODAY, DATE, TIME, etc
Master the skill of tracing dependents and precedents of data
Master how to combine VLOOKUP and HLOOKUP functions to perform one function

Requirements
This course assumes you have knowledge of basic Excel workbook practices.
This course was designed using Excel 2021 software
You can use Excel 2013, Excel 2016, Excel 2019, Office Excel 365 software
Some Functions treated (Dynamic Array Functions) is not be available in Excel 2013/2016

Description
You are welcome to this Excel Functions course. You will be guided step by step on how to be an excellent formula analyst with Microsoft Excel. In this course you will:Learn how to work with Excel functions from scratch (absolute beginner) to a comfortable professionalThe New Excel 365/Excel 2021 Dynamic Array Functions - SORT function, FILTER function, RANDARRAY function, SEQUENCE function, UNIQUE function, XLOOKUP function, SORTBY functionMaster the skill of tracing dependents and precedents of dataFlourish in the skill of nesting functionsLearn how to map data with VLOOKUP and INDEX functionMaster how to combine VLOOKUP and HLOOKUP functions to perform one functionUnderstand how to manipulate and combine INDEX/MATCH functions and VLOOKUP/MATCH functions and VLOOKUP/INDIRECT functions and HLOOKUP/MATCH functions and more combinations of functions to maximize efficiency.Understand how to create lists of items with Data ValidationAutomate data entry with Auto-Fill and Flash-fill.Master the tool of evaluating dataCreating and Deleting and Editing Defined NamesUnderstand how to work with Statistical Functions - AVERAGEIFS, COUNTA, COUNTIF, COUNTIFS, MEDIAN, MODE, MAX, MIN, AVERAGE, LARGE, SMALL, etcUnderstand how to work with Maths & Trig Functions - TRUNC, POWER, SUM, SUMIF, SUMIFS, QUOTIENT, SUMX2PY2, SUMX2MY2, LCM, GCD, SIN, COS, TAN, etcUnderstand how to work with Lookup & Reference Functions - XLOOKUP, XMATCH, VLOOKUP, HLOOKUP, ROW, ROWS, TRANSPOSE, COLUMN, COLUMNS, SORTBY, FILTER, MATCH, UNIQUE, etcUnderstand how to work with Date & Time Functions - DAYS360, NOW, TODAY, DATE, TIME, WEEKDAYS, etcUnderstand how to work with Logical Functions - IF, IFS, IFERROR, NOT, OR, AND, etcUnderstand how to work with Text Functions - CONCAT, MID, REPT, TEXT, ARRAYTOTEXT, TRIM, LEN, LEFT, RIGHT, etc

Overview

Section 1: Introduction

Lecture 1 Welcome Introduction - part 1

Lecture 2 Welcome Introduction - part 2

Lecture 3 Welcome Introduction - part 3

Lecture 4 Getting the best from the course materials

Lecture 5 Introduction to Excel - part 1

Lecture 6 Introduction to Excel - part 2

Lecture 7 Find, Replace, Wildcard

Lecture 8 Introduction to Excel - part 3

Lecture 9 Dynamics of Excel Workbook

Section 2: Basic Formula in Excel

Lecture 10 Introduction to Formula and Basic Arithmetic Operators

Lecture 11 Selecting a range of values in a formula

Lecture 12 Using a range of values with arithmetic operators

Lecture 13 Spill range and spill range reference - part 1

Lecture 14 Logical Operators in Excel

Lecture 15 Coercing True or False into numbers (1,0)

Lecture 16 Using a range of values with logical operators

Lecture 17 Spill range and spill range reference - part 2

Lecture 18 Concatenating in Excel

Section 3: Absolute Referencing vs Relative Referencing

Lecture 19 Relative vs Absolute Referencing - Part 1

Lecture 20 Relative vs Absolute Referencing - Part 2

Section 4: Syntax of Functions in Excel

Lecture 21 Understand the anatomy of a Excel Function

Section 5: Maths & Trig Functions

Lecture 22 SUM, PRODUCT, QUOTIENT, MOD Functions

Lecture 23 POWER, SQRT, SUMPRODUCT, SUMSQ Functions

Lecture 24 Nesting Functions - Part 1

Lecture 25 LCM, GCD, ROMAN Functions

Lecture 26 Nesting Functions - Part 2

Lecture 27 TRUNC, INT, ROUND, ROUNDUP, ROUNDDOWN Functions

Lecture 28 LOG, EVEN, ODD functions

Lecture 29 ABS, BASE, FACT Functions

Lecture 30 SUMX2MY2, SUMX2PY2 functions

Lecture 31 RAND, RANDBETWEEN Functions

Lecture 32 RADIANS, SIN, COS, TAN, PI, DEGREES, TANH, COSH, SINH functions

Lecture 33 SUMIF function - Introduction

Lecture 34 SUMIF function - logical operators

Lecture 35 SUMIF function - using wild card - Part 1

Lecture 36 SUMIF function - using wild card - Part 2

Lecture 37 SUMIF function - summing with SUMIF

Lecture 38 SUMIFS function

Section 6: Excel Logical Functions

Lecture 39 OR function

Lecture 40 XOR function

Lecture 41 AND function

Lecture 42 NOT, FALSE, TRUE functions

Lecture 43 SWITCH function

Lecture 44 SWITCH function with Logical operators - part 1

Lecture 45 SWITCH function with Logical operators - part 2

Lecture 46 IF function

Lecture 47 IF function - Nesting IF functions

Lecture 48 IF function nesting OR, AND, NOT functions

Lecture 49 IFS function

Lecture 50 IFERROR function

Lecture 51 Using emojis in logical function

Section 7: Excel Text Functions

Lecture 52 CHAR, UNICHAR, BAHTEXT functions

Lecture 53 CONCAT, TEXTJOIN functions

Lecture 54 LEFT, RIGHT, LEN functions

Lecture 55 LOWER, UPPER, PROPER Functions

Lecture 56 DOLLAR, FIXED functions

Lecture 57 SUBSTITUE, REPLACE functions

Lecture 58 REPT, TRIM, EXACT functions

Lecture 59 REPT function to create a data bar

Lecture 60 Using emojis in REPT function

Lecture 61 FIND, SEARCH, MID functions

Lecture 62 TEXT Function

Section 8: Array Constants/Formula in Excel

Lecture 63 Array Formula Constants - part 1

Lecture 64 Array Formula Constants - part 2

Lecture 65 Importing an array of data from one worksheet to another

Section 9: More on Text Functions

Lecture 66 ARRAYTOTEXT function

Lecture 67 LET function

Lecture 68 VALUE, CLEAN, VALUETOTEXT functions

Lecture 69 Reverse order of characters with TEXJOIN and MID functions

Section 10: Defined Names

Lecture 70 Defined Names

Lecture 71 Deleting and Editing Defined Names

Lecture 72 Creating Defined Names with headings

Section 11: Formula Auditing

Lecture 73 Trace Dependents and Precedents - Part 1

Lecture 74 Trace Dependents and Precedents - Part 2

Lecture 75 Shortcut keys to find the direct and indirect precedents and dependents

Lecture 76 Using 'Go To Special' to find the precedents and dependents

Lecture 77 Trace Dependents and Precedents - Part 3

Lecture 78 Show Formula and Evaluate Formula

Lecture 79 Error Checking

Section 12: Date and Time Functions

Lecture 80 DATE, DATEVALUE, DAY, DAYS functions

Lecture 81 DAYS360, EDATE, EOMONTH, HOUR functions

Lecture 82 ISOWEEKNUM, MINUTE, MONTH, NETWORKDAYS, NETWORKDAYS.INT

Lecture 83 NOW, TODAY, TIME, TIMEVALUE, SECOND functions

Lecture 84 WEEKDAY, WEEKNUM, YEAR, YEARFRAC, WORKDAY Functions

Lecture 85 Calculating age of someone in Excel

Lecture 86 Using RANDBETWEEN with WEEKDAY or MONTH or DATE

Section 13: Lookup & Reference Functions

Lecture 87 INDEX, MATCH functions

Lecture 88 MATCH function - Match type

Lecture 89 Nesting MATCH inside INDEX function - part 1

Lecture 90 Nesting MATCH inside INDEX function - part 2

Lecture 91 CHOOSE, TRANSPOSE functions

Lecture 92 COLUMN, COLUMNS, ROW, ROWS functions

Lecture 93 LOOKUP, VLOOKUP functions

Lecture 94 VLOOKUP Function again

Lecture 95 Nesting MATCH function inside VLOOKUP

Lecture 96 HLOOKUP Function

Lecture 97 Absolute Referencing in table array HLOOKUP

Lecture 98 MATCH and HLOOKUP combined

Lecture 99 Using VLOOKUP to find duplicate values in 2 array of data

Lecture 100 OFFSET function

Lecture 101 HYPERLINK, FORMULATEXT, INDIRECT functions

Lecture 102 Mapping Data with VLOOKUP and INDIRECT function - scenario 1

Lecture 103 Mapping Data with VLOOKUP and INDIRECT function - scenario 2

Lecture 104 Evaluating the VLOOKUP and INDIRECT mapping

Lecture 105 ADDRESS Function

Lecture 106 VLOOKUP and HLOOKUP combined - part 1

Lecture 107 VLOOKUP and HLOOKUP combined - part 2

Lecture 108 VLOOKUP and RANDBETWEEN - generate random data - Part 1

Lecture 109 VLOOKUP and RANDBETWEEN - generate random data - Part 2

Lecture 110 INDEX and RANDBETWEEN - to generate random data

Section 14: Dynamic Array Formulas (Office 2021/Microsoft 365 )

Lecture 111 XMATCH function

Lecture 112 XMATCH - search mode

Lecture 113 XLOOKUP - part 1

Lecture 114 XLOOKUP - part 2

Lecture 115 XLOOKUP - 2 Way Lookup

Lecture 116 XLOOKUP -[if not found] and[match mode]

Lecture 117 XLOOKUP with multiple criteria

Lecture 118 XLOOKUP with logical criteria

Lecture 119 UNIQUE function

Lecture 120 FILTER function

Lecture 121 FILTER function on column data

Lecture 122 SORT function

Lecture 123 SORT function - sort by columns -[by_cols] being TRUE

Lecture 124 SORTBY function

Lecture 125 Custom sort - SORTBY with LEN function

Lecture 126 Custom sort - SORTBY with MATCH function

Lecture 127 SORTBY with TEXT function - Custom sort dates

Lecture 128 SORTBY with MONTH or DAY functions - Custom sort dates

Lecture 129 SORTBY function on column

Lecture 130 SORTBY and COLUMN to reverse the order of a column list

Lecture 131 SORTBY and ROW to reverse the order of a row list

Lecture 132 RANDARRAY function

Lecture 133 RANDARRAY with CHAR to generate random alphabets

Lecture 134 RANDARRAY with DATE and EDATE functions

Lecture 135 SEQUENCE function - part 1

Lecture 136 SEQUENCE function - part 2

Lecture 137 ROUNDUP and SEQUENCE - repeated numbers

Lecture 138 MOD and SEQUENCE - repeated sequence

Lecture 139 SEQUENCE with TRANSPOSE, ROMAN, TEXT functions

Lecture 140 SEQUENCE with DATE and EDATE functions

Lecture 141 Reverse a list with SEQUENCE, INDEX, ROWS functions

Lecture 142 Reverse a list with SEQUENCE, SORTBY, ROWS functions

Lecture 143 Alphabetic sequence with SEQUENCE, CHAR

Lecture 144 Generate sequence of time - SEQUENCE, TIME and HOUR

Lecture 145 SEQUENCE, RANDARRAY, SORTBY, INDEX to generate random array of data - Part 1

Lecture 146 SEQUENCE, RANDARRAY, SORTBY, INDEX to generate random array of data - Part 2

Lecture 147 Filtering every nth value in a data - FILTER and MOD and SEQUENCE - part 1

Lecture 148 Filtering every nth value in a data - FILTER and MOD and SEQUENCE - part 2

Lecture 149 Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 1

Lecture 150 Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 2

Lecture 151 Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 3

Section 15: Statistical Functions

Lecture 152 COUNT, COUNTA, COUNTBLANK functions

Lecture 153 COUNTIF - Part 1

Lecture 154 COUNTIF - Part 2

Lecture 155 COUNTIF - Part 3

Lecture 156 COUNTIFS function

Lecture 157 AVERAGE, AVERAGEA functions - part 1

Lecture 158 AVERAGE, AVERAGEA functions - part 2

Lecture 159 AVERAGEIF function

Lecture 160 AVERAGEIFS function

Lecture 161 SMALL, LARGE functions

Lecture 162 LARGE or SMALL combined with IF function

Lecture 163 MATCH combined with LARGE or SMALL function

Lecture 164 LARGE or SMALL with the k-th as an array

Lecture 165 Combine INDEX and MATCH and LARGE or SMALL and IF functions

Lecture 166 Combine XLOOKUP and LARGE or SMALL and IF functions

Lecture 167 GEOMEAN function

Lecture 168 HARMEAN function

Lecture 169 MIN, MAX function

Lecture 170 MAX, MIN with the IF function

Lecture 171 MIN MAX with date and time and with LEN function

Lecture 172 MAXIFS, MINIFS function - part 1

Lecture 173 MAXIFS, MINIFS function - part 2

Lecture 174 Combine INDEX and MATCH and MAX or MIN Function

Lecture 175 Combine VLOOKUP and MAX or MIN function

Lecture 176 Combine XLOOKUP and MAX or MIN Function

Lecture 177 Combine INDEX and MATCH and MAXIFS or MINIFS function

Lecture 178 Combine VLOOKUP and MAXIFS or MINIFS function

Lecture 179 Combine XLOOKUP and MAXIFS or MINIFS Function

Lecture 180 MODE, MODE.SNGL, MODE.MULT Functions

Lecture 181 Finding the MODE of texts using INDEX and MODE and MATCH function

Lecture 182 Combining INDEX and MODE and IF and MATCH Functions

Lecture 183 MEDIAN Function

Lecture 184 RANK, RANK.EQ, RANK.AVG Functions

Lecture 185 FREQUENCY Function

Section 16: Wrapping up

Lecture 186 Wrap up

This course is for beginners in Excel Functions. If you want to have an excellent grasp of Excel Functions, Arrays and Data Manipulation, then this Excel course is for you.

Show Content

Spoiler
https://anonymz.com/?https://www.udemy.c...fice-2021/

Show Content

Spoiler
https://rapidgator.net/file/8f46a1c6c3d0....part1.rar
https://rapidgator.net/file/69160e7eaca0....part2.rar
https://rapidgator.net/file/276f478fa7f8....part3.rar

Show Content

Spoiler
https://nitroflare.com/view/D2780B5B137E....part1.rar
https://nitroflare.com/view/AF060CF15A3B....part2.rar
https://nitroflare.com/view/7090726E60A5....part3.rar