Excel Analytics - Advanced Excel Formulas & Functions
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
*** Excel Analytics - Advanced Formulas & Functions Includes 60+ FREE Downloadable PDF cheat sheets and 9 Excel project files ***
It's time to show Excel who wears the pants in this relationship. Whether you're starting from square one or aspiring to become an absolute Excel badass, you've come to the right place.
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. While most Excel courses focus on simply what each formula does, I teach through hands-on, contextual examples designed to showcase why these formulas are awesome and how they can be applied in a number of ways. I will not train you to regurgitate functions and formula syntax; I will teach you how to THINK like Excel.
By the end of the course you'll be writing robust, elegant formulas and functions from scratch, allowing you to:
We'll dive into a broad range of Excel formulas & functions, including:
What gives you the right to teach this class? Can't I just Google this stuff?
Fantastic question. First and foremost, I have a genuine passion for Excel that most people reserve for things like kittens, ice cream, and/or significant others. The only thing I love more than learning Excel is teaching it, and as the founder of Excel Maven I've been lucky enough to teach thousands of people just like you over the past 7+ years. My teaching style is conversational, authentic and to the point, and I will always communicate complex concepts in a framework that is clear and easy to comprehend.
As a full-time analytics consultant and Excel instructor, I cut my teeth using Excel to solve real-world business problems and develop award-winning analytics & data visualization tools for Fortune 500 companies. If you care about creds, I'm a card-carrying MOS Certified Excel Expert and my work has been featured by Microsoft and the New York Times. Ok so I don't actually carry the card, but you get the idea.
If you're looking for the ONE course with all of the advanced formulas and functions that you need to know to become an absolute Excel ninja, you've come to the right place.
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Before We Dive In|
Course Structure & OutlinePreview
|Section 2: Excel Formulas 101|
The Formula Library & Auditing Tools
Basic Formula Syntax
Fixed, Relative, & Mixed References
Common Errors & the IFERROR statement
Function, CTRL & ALT Shortcuts
BONUS: Shortcuts for Mac Users
Creating Custom Data Validation RulesPreview
Fixed vs. Volatile Functions
|Section 3: Logical Operators|
Anatomy of the IF Statement
Nested IF Statements
NOT vs. "<>"
Fixing Errors with IFERRORPreview
Common IS Statements
|Section 4: Statistical Functions|
Basic Statistical Functions
SMALL/LARGE & RANK/PERCENTRANK
RAND() & RANDBETWEEN
The SUMPRODUCT Function
PROJECT SHOWCASE: Building a Basic Dashboard with COUNTIFS & SUMIFSPreview
|Section 5: Lookup/Reference Functions|
Working with Named Arrays
ROW/ROWS & COLUMN/COLUMNS
Joining Data with VLOOKUPPreview
Fixing Errors with IFERROR & VLOOKUP
VLOOKUP Reference Array Options
The INDEX Function
The MATCH Function
Using INDEX & MATCH together
Combining MATCH with VLOOKUP
The OFFSET Function
PROJECT SHOWCASE: Using OFFSET to create a dynamic scrolling chart
|Section 6: Text Functions|
UPPER/LOWER/PROPER & TRIM
The CONCATENATE Function (&)
LEFT/MID/RIGHT & LENPreview
Categorizing Data with IF(ISNUMBER(SEARCH))
Combining RIGHT, LEN, and SEARCH
|Section 7: Date & Time Functions|
DATEVALUE: Your New BFF
Date Formatting & Fill Series
YEAR/MONTH/DAY & HOUR/MINUTE/SECOND
The EOMONTH FunctionPreview
The YEARFRAC Function
WEEKDAY, WORKDAY & NETWORKDAYS
The DATEDIF Function
PROJECT SHOWCASE: Designing a Sample Budget Pacing Tool
|Section 8: Formula-Based Formatting|
Creating, Editing & Managing Formula-Based Rules
Highlighting Every Other Row Using the MOD function
Formatting Cells Based on the Value of Another CellPreview
Formatting Cells Using Statistical Functions
Formatting Cells Using Text Functions & Logical Operators
|Section 9: Array Formulas|
Rules of Array Functions
Pros & Cons of Array Functions
Vertical, Horizontal, and 2-Dimensional Array Constants
Using Array Constants in Formulas
Named Array Constants
The Transpose FunctionPreview
Linking Data Between Sheets: Array vs. Non-Array Comparison
Returning the "X" Largest Values in a Range
Counting Characters Across Cells
Creating a "MAX IF" Array Formula
|Section 10: Badass Bonus Functions|
The INDIRECT Function
HYPERLINK: as Awesome as it SoundsPreview
Tapping into Real-Time Data with WEBSERVICE & FILTERXML
|Section 11: Wrapping Up|
Conclusion & Next Steps
Chris Dutton is a Certified Microsoft Excel Expert and Analytics Consultant with nearly a decade of experience working with Fortune 500 companies across automotive, retail, insurance, and travel verticals. Specializing in B.I. and data visualization, Chris founded Excel Maven in 2014 to deliver high-quality, hands-on training to students across the country.