Microsoft Excel: Advanced Excel Formulas & Functions

3 Reviews

286 Enrolled

6 Hours

$11.99$195.00

Save 93% -Sale ended

93 Lessons (6h)

Getting Started
Course Structure & Outline4:12
Downloading the Homework Exercises1:42
Excel Formulas 101
Introduction1:06
The Formula Library & Auditing Tools4:37
DEMO: Evaluate Formula1:25
Basic Formula Syntax3:31
Fixed & Relative Cell References4:57
Common Errors & IFERROR7:25
Function, CTRL & ALT Shortcuts6:36
BONUS: Mac Shortcuts2:08
Data Validation Rules3:28
Fixed vs. Volatile Functions3:21
QUIZ: Formulas 101
HOMEWORK: Formulas 1010:46
Conditional Statements & Logical Operators
Introduction1:23
Anatomy of the IF Statement4:01
Nested IF Statements4:53
AND/OR Operators8:47
NOT () Operators3:17
Addressing Errors with IFERROR4:03
Common IS Statements4:00
QUIZ: Conditional Statements & Logical Operators
HOMEWORK: Conditional Statements & Logical Operators0:44
Statistical Functions
Introduction1:12
Basic Stats Functions5:17
SMALL/LARGE & RANK/PERCENTRANK6:19
RAND() & RANDBETWEEN2:12
The SUMPRODUCT Function3:55
COUNTIFS/SUMIFS/AVERAGEIFS4:49
DEMO: Basic Dashboards with Stats Functions8:09
QUIZ: Statistical Functions
HOMEWORK: Statistical Functions0:35
Lookup & Reference Functions
Introduction1:23
Named Arrays3:30
ROW/ROWS & COLUMN/COLUMNS3:18
VLOOKUP & HLOOKUP5:54
DEMO: Joining Data with VLOOKUP6:23
Combining IFERROR & VLOOKUP4:30
VLOOKUP Range Options6:18
The INDEX Function1:59
The MATCH Function2:32
Combining INDEX & MATCH6:08
Combining MATCH & VLOOKUP4:47
UPDATE: VLOOKUP Correction5:05
The OFFSET Function2:05
Combining OFFSET & COUNTA2:58
DEMO: Building a Scrolling Chart with OFFSET9:51
QUIZ: Lookup & Reference Functions
HOMEWORK: Lookup & Reference Functions0:54
Text Functions
Introduction0:57
UPPER, LOWER, PROPER & TRIM3:32
CONCATENATE (&)3:26
LEFT, MID, RIGHT & LEN3:31
TEXT & VALUE4:20
SEARCH & FIND5:04
Categorizing Data with IF(ISNUMBER(SEARCH))5:39
Combining RIGHT, LEN & SEARCH5:43
The SUBSTITUTE Function2:21
QUIZ: Text Functions
HOMEWORK: Text Functions0:59
Date & Time Functions
Introduction2:13
Understanding DATEVALUE5:05
Date Formatting & Fill Series3:39
TODAY() & NOW()2:16
YEAR/MONTH/DAY & HOUR/MINUTE/SECOND3:04
The EOMONTH Function6:17
The YEARFRAC Function2:27
WEEKDAY, WORKDAY & NETWORKDAYS7:24
The DATEDIF Function4:23
DEMO: Budget Pacing Tool11:07
QUIZ: Date & Time Functions
HOMEWORK: Date & Time Functions0:35
Formula-Based Formatting
Introduction1:30
Creating and Managing Formula-Based Rules4:39
DEMO: Highlighting Rows with MOD3:50
DEMO: Formatting Cells Based on Values3:29
DEMO: Formatting Cells with Stats Functions5:28
DEMO: Formatting Cells with Text & Conditional Functions5:08
QUIZ: Formula-Based Formatting
HOMEWORK: Formula-Based Formatting0:34
Basic Array Formulas
Introduction1:26
Rules of Array Formulas3:24
Pros & Cons of Array Formulas2:15
Vertical, Horizontal & 2-D Array Constants6:26
Using Array Constants in Formulas3:38
Named Array Constants4:31
The TRANSPOSE Function4:21
Linking Data: Array vs. Non-Array Comparison2:36
DEMO: Returning the "X" Largest Values3:09
DEMO: Counting Characters Across Cells1:52
DEMO: Creating a "MAX IF" Function (Part 1)3:09
DEMO: Creating a "MAX IF" Function (Part 2)6:54
The Double Unary Operator ("--")3:49
QUIZ: Array Formulas
HOMEWORK: Array Formulas2:09
Badass Bonus Functions
Introduction1:41
The INDIRECT Function10:55
The HYPERLINK Function6:19
Real-Time Data with WEBSERVICE & FILTERXML7:16
QUIZ: Badass Bonus Functions
Wrapping Up
Resources & Next Steps0:41

Master 75+ Excel Formula & Functions with Hands-On Demos

Excel Maven

Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.

He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.

Description

It's time to show Excel who's boss! Whether you're starting from square one or aspiring to become an absolute Excel wizard, this is the right place for you. This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Through hands-on, contextual examples, you'll learn why these formulas are awesome and how they can be applied in a number of ways.

Access 93 lectures & 6 hours of content 24/7
Easily build dynamic tools & Excel dashboards to filter, display, & analyze your data
Go rogue & design your own formula-based formatting rules
Join datasets from multiple sources in seconds w/ lookup, index, & match functions
Pull real-time data from APIs directly into Excel
Manipulate dates, times, text, & arrays
Automate tedious & time-consuming analytics tasks using cell formulas and functions in Excel

Specs

Details & Requirements

Length of time users can access this course: lifetime
Access options: web streaming, mobile streaming
Certification of completion not included
Redemption deadline: redeem your code within 30 days of purchase
Experience level required: all levels
Microsoft Excel required

Terms

Unredeemed licenses can be returned for store credit within 30 days of purchase. Once your license is redeemed, all sales are final.

3 Reviews

4.7/ 5

All reviews are from verified purchasers collected after purchase.

paul monson

Verified Buyer

I have not used this product yet but am looking forward to using it soon. I have used Microsoft Excel for many years but have never taken advantage of its' many Advanced Formulas and Functions

Sep 3, 2022

Andrew Mauricio

Verified Buyer

Its easy to use and a lifetime course I can go back and refresh anytime. It's made easy to follow and the steps are easy to understand.

Jul 4, 2020

John Castleton

Verified Buyer

Half-way through. Despite being an Excel macro and function user for many years, there's always something new to learn or old to improve on. Happy with the purchase.

Feb 19, 2020

Your Cart

Your cart is empty. Continue Shopping!

Processing order...