+- Forum Rockoldies (https://rockoldies.net/forum)
+-- Forum: Fotobearbeitung - Photoshop (https://rockoldies.net/forum/forumdisplay.php?fid=16)
+--- Forum: E-Learning, Tutorials (https://rockoldies.net/forum/forumdisplay.php?fid=18)
+--- Thema: Gain Mastery Of Over 135+ Excel Functions (Office 2021) (/showthread.php?tid=77442)
Gain Mastery Of Over 135+ Excel Functions (Office 2021) - Panter - 12.11.2023
![[Bild: 7s4oai51fymucaxmwgrpfjaio5.jpg]](https://abload.de/img/7s4oai51fymucaxmwgrpfjaio5.jpg)
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.
![[Bild: gainmasteryofover135e0oifc.jpg]](https://abload.de/img/gainmasteryofover135e0oifc.jpg)