Excel: Advanced Formulas and Functions

Last Update January 25, 2024

1 already enrolled

About This 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.

Curriculum

66 Lessons4h 50m

Introduction

Use the most powerful formulas and functions in Excel00:00:37Preview

Chapter 1 : Formula & Function Tips & Shortcuts

Chapter 2: If & Related Functions

Chapter 3: Lookup & Reference Functions

Chapter 4: Power Functions

Chapter 5: Statistical Functions

Chapter 6: Math Functions

Chapter 7: Date & Time Functions

Chapter 8: Reference Functions

Chapter 9: Text Functions

Chapter 10: Information Functions

Next steps

Your Instructors

Daniel

5.0/5

71 Courses

1 Review

166 Students

Job Skills Training

4.95/5

235 Courses

41 Reviews

735 Students

Excel: Advanced Formulas and Functions

About This Course

Learning objectives

Curriculum

Introduction

Use the most powerful formulas and functions in Excel00:00:37Preview

Chapter 1 : Formula & Function Tips & Shortcuts

Display and highlight formulas00:5:00

Use the auditing tools00:6:18

Use entire row/column references00:3:1

Change formulas to values and update values without formulas00:5:55

Simplify debugging formulas with the F9 key00:4:7

Enhance readability with range names00:5:10

Create 3D formulas to tabulate data from multiple sheets00:5:11

Chapter 1 Quiz : Formula & Function Tips & Shortcuts

Chapter 2: If & Related Functions

Explore IF logical tests and use relational operators00:4:36

Create and expand the use of nested IF statements00:4:44

Create compound logical tests with AND, OR, NOT, and IF00:5:28

Use IFS for multiple conditions00:2:50

Chapter 2 Quiz: If & Related Functions

Chapter 3: Lookup & Reference Functions

Explore the VLOOKUP and HLOOKUP functions00:3:41

Find approximate matches with VLOOKUP and HLOOKUP00:5:2

Use VLOOKUP to find exact matches and search large tables00:5:5

Find table-like data within a function using CHOOSE00:4:3

Use the SWITCH function for formula-embedded selection00:4:45

Locate data with the MATCH function00:3:28

Retrieve information by location with the INDEX function00:2:47

Use the MATCH and INDEX functions together00:4:3

Document formulas with the FORMULATEXT function00:2:37

Extract and count unique entries from a list with UNIQUE00:4:28

Use the XLOOKUP function00:5:7

Chapter 3 Quiz: Lookup & Reference Functions

Chapter 4: Power Functions

Tabulate data using a single criterion with COUNTIF, SUMIF, and AVERAGEIF00:4:10

Tabulate data using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIF00:4:5

Use MAXIFS and MINIFS00:2:34

Use the SUBTOTAL function to prevent double counting00:4:58

Chapter 4 Quiz: Power Functions

Chapter 5: Statistical Functions

Find middle and most common values with MEDIAN and MODE00:2:55

Rank data without sorting using RANK and RANK.EQ00:4:7

Find the largest and smallest values with LARGE and SMALL00:2:5

Tabulate blank cells with the COUNTBLANK function00:4:57

Use COUNT, COUNTA, and the status bar00:3:41

Chapter 5 Quiz: Statistical Functions

Chapter 6: Math Functions

Work with the ROUND, ROUNDUP, and ROUNDDOWN functions00:5:43

Use MROUND, CEILING, and FLOOR for specialized rounding00:3:10

Use INT, TRUNC, ODD, and EVEN for specialized rounding00:3:19

Use MOD to find remainders and apply conditional formatting00:5:8

Explore practical uses for RAND, RANDARRAY, and RANDBETWEEN00:5:36

Convert a value between measurement systems with CONVERT00:3:14

Use the AGGREGATE function to bypass errors and hidden data00:4:53

Use ROMAN and ARABIC to display different number systems00:3:17

Chapter 6 Quiz: Math Functions

Chapter 7: Date & Time Functions

Understand Excel date and time capabilities in formulas00:4:49

Use various date and time functions00:4:43

Use the TODAY and NOW functions for date and time entry00:5:35

Identify weekdays with the WEEKDAY function00:3:57

Count working days and completion dates (NETWORKDAYS and WORKDAY)00:4:6

Tabulate date differences with the DATEDIF function00:5:53

Calculate dates with EDATE and EOMONTH00:5:17

Chapter 7 Quiz: Date & Time Functions

Chapter 8: Reference Functions

Get data from remote cells with the OFFSET function00:4:8

Return references with the INDIRECT function00:3:53

Use INDIRECT with Data Validation for multitiered pick lists00:5:23

Chapter 8 Quiz: Reference Functions

Chapter 9: Text Functions

Locate and extract data with FIND, SEARCH, and MID00:5:11

Extract data with the LEFT and RIGHT functions00:3:42

Use the TRIM function to remove unwanted spaces in a cell00:4:48

Combine data with symbols (&) and CONCATENATE00:3:28

Use CONCAT and TEXTJOIN to combine data from different cells00:5:4

Adjust alphabetic case with UPPER, LOWER, and PROPER00:4:26

Adjust character content with REPLACE and SUBSTITUTE00:5:17

Use utility text functions: TEXT, REPT, VALUE, and LEN00:5:26