Excel: Advanced Formulas and Functions
About Course
Join Excel guru Dennis Taylor as he debunks the mystique around Excel’s many formulae and functions. Dennis begins with a few crucial formula shortcuts that will expedite your work before covering a wide range of functions, including VLOOKUP, MATCH, and INDEX, statistical functions, text functions, and date-and-time, math, text, and information functions. He also covers functions like VLOOKUP, MATCH, and INDEX. Dennis gives concrete examples to make it simple for viewers to use Excel’s most potent formulas and functions in realistic situations. You should be aware that this course is recorded in Excel for Office 365, but you can still follow along with any current version, including 2019, 2016, and 2013.
Learning objectives
- Recognize the results of the MOD function.
- Identify the Excel random number generating functions.
- Apply the ROMAN function to convert a Roman numeral to Arabic.
- Give an example where the LEFT and RIGHT functions could be used.
- Describe the TRIM function.
- Define the Excel Text functions.
- Differentiate between Information functions.
Course Content
Introduction
Chapter 1 : Formula & Function Tips & Shortcuts
-
Display and highlight formulas
05:00 -
Use the auditing tools
06:18 -
Use entire row/column references
03:01 -
Change formulas to values and update values without formulas
05:55 -
Simplify debugging formulas with the F9 key
04:07 -
Enhance readability with range names
05:10 -
Create 3D formulas to tabulate data from multiple sheets
05:11 -
Chapter 1 Quiz : Formula & Function Tips & Shortcuts
Chapter 2: If & Related Functions
-
Explore IF logical tests and use relational operators
04:36 -
Create and expand the use of nested IF statements
04:44 -
Create compound logical tests with AND, OR, NOT, and IF
05:28 -
Use IFS for multiple conditions
02:50 -
Chapter 2 Quiz: If & Related Functions
Chapter 3: Lookup & Reference Functions
-
Explore the VLOOKUP and HLOOKUP functions
03:41 -
Find approximate matches with VLOOKUP and HLOOKUP
05:02 -
Use VLOOKUP to find exact matches and search large tables
05:05 -
Find table-like data within a function using CHOOSE
04:03 -
Use the SWITCH function for formula-embedded selection
04:45 -
Locate data with the MATCH function
03:28 -
Retrieve information by location with the INDEX function
02:47 -
Use the MATCH and INDEX functions together
04:03 -
Document formulas with the FORMULATEXT function
02:37 -
Extract and count unique entries from a list with UNIQUE
04:28 -
Use the XLOOKUP function
05:07 -
Chapter 3 Quiz: Lookup & Reference Functions
Chapter 4: Power Functions
-
Tabulate data using a single criterion with COUNTIF, SUMIF, and AVERAGEIF
04:10 -
Tabulate data using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIF
04:05 -
Use MAXIFS and MINIFS
02:34 -
Use the SUBTOTAL function to prevent double counting
04:58 -
Chapter 4 Quiz: Power Functions
Chapter 5: Statistical Functions
-
Find middle and most common values with MEDIAN and MODE
02:55 -
Rank data without sorting using RANK and RANK.EQ
04:07 -
Find the largest and smallest values with LARGE and SMALL
02:05 -
Tabulate blank cells with the COUNTBLANK function
04:57 -
Use COUNT, COUNTA, and the status bar
03:41 -
Chapter 5 Quiz: Statistical Functions
Chapter 6: Math Functions
-
Work with the ROUND, ROUNDUP, and ROUNDDOWN functions
05:43 -
Use MROUND, CEILING, and FLOOR for specialized rounding
03:10 -
Use INT, TRUNC, ODD, and EVEN for specialized rounding
03:19 -
Use MOD to find remainders and apply conditional formatting
05:08 -
Explore practical uses for RAND, RANDARRAY, and RANDBETWEEN
05:36 -
Convert a value between measurement systems with CONVERT
03:14 -
Use the AGGREGATE function to bypass errors and hidden data
04:53 -
Use ROMAN and ARABIC to display different number systems
03:17 -
Chapter 6 Quiz: Math Functions
Chapter 7: Date & Time Functions
-
Understand Excel date and time capabilities in formulas
04:49 -
Use various date and time functions
04:43 -
Use the TODAY and NOW functions for date and time entry
05:35 -
Identify weekdays with the WEEKDAY function
03:57 -
Count working days and completion dates (NETWORKDAYS and WORKDAY)
04:06 -
Tabulate date differences with the DATEDIF function
05:53 -
Calculate dates with EDATE and EOMONTH
05:17 -
Chapter 7 Quiz: Date & Time Functions
Chapter 8: Reference Functions
-
Get data from remote cells with the OFFSET function
04:08 -
Return references with the INDIRECT function
03:53 -
Use INDIRECT with Data Validation for multitiered pick lists
05:23 -
Chapter 8 Quiz: Reference Functions
Chapter 9: Text Functions
-
Locate and extract data with FIND, SEARCH, and MID
05:11 -
Extract data with the LEFT and RIGHT functions
03:42 -
Use the TRIM function to remove unwanted spaces in a cell
04:48 -
Combine data with symbols (&) and CONCATENATE
03:28 -
Use CONCAT and TEXTJOIN to combine data from different cells
05:04 -
Adjust alphabetic case with UPPER, LOWER, and PROPER
04:26 -
Adjust character content with REPLACE and SUBSTITUTE
05:17 -
Use utility text functions: TEXT, REPT, VALUE, and LEN
05:26 -
Use the new LET function
05:23 -
Create custom functions with LAMBDA
05:50 -
New functions: TEXTBEFORE, TEXTAFTER, and TEXTSPLIT
06:18 -
Chapter 9 Quiz: Text Functions
Chapter 10: Information Functions
-
Extract information with the CELL and INFO functions
04:52 -
Explore various information functions
04:36 -
Use several error-checking functions
05:18 -
Track and highlight formula cells with ISFORMULA
03:32
Next steps
-
Next steps
00:34
Student Ratings & Reviews
No Review Yet