IIRF Online > Office Productivity > Microsoft > Excel Formulas and Functions > Microsoft Excel: Advanced Excel Formulas & Functions
Microsoft Excel: Advanced Excel Formulas & Functions by Udemy
Learn 75+ Excel spreadsheet formulas for data analysis & business intelligence, with a top Microsoft Excel instructor!
Course Highlights
- Learn how to THINK like Excel, and write powerful and dynamic Excel formulas from scratch
- Automate, streamline, and completely revolutionize your workflow with Excel
- Master unique tips & techniques that you won't find in ANY other course, guaranteed
- Explore fun, interactive, and highly effective demos from a best-selling Excel instructor
- Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
- Write advanced conditional, text, date and lookup functions, including XLOOKUP & Dynamic Arrays
Skills you will learn!
Curriculum
4 Topics
Course Structure & Outline
READ ME: Important Notes for New Students
DOWNLOAD: Course Resources
Setting Expectations
14 Topics
Introduction: Excel Formulas 101
Excel Formula Syntax
Writing Efficient Formulas with Fixed & Relative References
Common Excel Error Types
Formula Auditing: Trace Precedents & Dependents
Formula Auditing: Evaluate Formula & Error Checking (PC Only)
Navigating Excel Worksheets with Ctrl Shortcuts
Saving Time with Function Shortcuts
Accessing Tools with Alt Key Tips
BONUS: Mac Shortcuts
PRO TIP: Creating Drop-Down Menus with Data Validation
Congrats You're a Developer!
Excel Formulas 101
HOMEWORK: Excel Formulas 101
9 Topics
Introduction
Anatomy of the IF Statement
Nesting Multiple IF Statements
Adding Conditional AND/OR Operators
Using NOT & "<>" Conditionals
Fixing Errors with IFERROR
Common IS Statements
Excel Logical Operators
HOMEWORK: Logical Operators
12 Topics
Introduction
Basic Excel Statistical Functions
Extracting Values with SMALL/LARGE & RANK/PERCENTRANK
Randomization with RAND() & RANDBETWEEN
Row-Level Calculation with SUMPRODUCT
DEMO: Calculating Shipping Costs with SUMPRODUCT
Conditional Aggregation with COUNTIFS SUMIFS & AVERAGEIFS
DEMO: Building a Basic Dashboard with COUNTIFS & SUMIFS
DEMO: Counting Duplicates with COUNTIF & SUMPRODUCT
PRO TIP: Data Profiling with COUNT & SUMPRODUCT
Excel Stats Functions
HOMEWORK: Common Stats Functions
21 Topics
Introduction
Working with Named Ranges
Counting Rows & Columns with ROW/ROWS & COLUMN/COLUMNS
Introduction to VLOOKUP/HLOOKUP
Joining Data with VLOOKUP
Fixing Errors with IFERROR & VLOOKUP
VLOOKUP Reference Array Options
Approximate Match Lookups
Navigating Cell Ranges with INDEX
Matching Text & Values with MATCH
Combining INDEX & MATCH to Dynamically Search Ranges
Combining MATCH & VLOOKUP for More Flexible Lookups
TROUBLESHOOTING: VLOOKUP with Duplicate Keys
SNEAK PEEK: XLOOKUP (Office 365 Only)
DEMO: Building Dashboards with XLOOKUP (Office 365 Only)
Selecting List Items with CHOOSE
Defining Ranges with OFFSET
Combining OFFSET with COUNTA to Create a Flexible Range
PRO TIP: Using OFFSET to Create Interactive Charts
Excel Lookup & Reference Functions
HOMEWORK: Lookup & Reference Functions
11 Topics
Introduction
Capitalization with UPPER LOWER PROPER & TRIM
Combining Text with CONCATENATE (&)
Extracting Strings with LEFT MID RIGHT & LEN
Converting Text to Values with TEXT & VALUE
Searching Text String with SEARCH & FIND
Categorizing Data with IF(ISNUMBER(SEARCH))
Combining RIGHT LEN and SEARCH
Replacing Text with SUBSTITUTE
Excel Text Functions
HOMEWORK: Text Functions
12 Topics
Introduction
Understanding Excel Date Syntax with DATEVALUE
Formatting Dates & Filling Date Series
Creating Real-Time Functions with TODAY & NOW
Extracting Time Periods with YEAR MONTH DAY HOUR MINUTE & SECOND
Calculating the Month Start or End with EOMONTH
Calculating % of Year with YEARFRAC
Defining Time Periods with WEEKDAY WORKDAY & NETWORKDAYS
Calculating Differences Between Dates with DATEDIF (Legacy Function)
DEMO: Building a Simple Budget Pacing Tool
Excel Date & Time Functions
HOMEWORK: Date & Time Functions
8 Topics
Introduction
Creating Editing & Managing Formula-Based Rules
Highlighting Rows Using the MOD Function
Formatting Based on the Value of Another Cell
Formatting Cells Using Stats Functions
Formatting Cells Using Text Functions & Logical Operators
Excel Formula-Based Formatting
HOMEWORK: Formula-Based Formatting
25 Topics
Intro & Compatibility
Project Intro: Maven Recruiters
Legacy vs. "Dynamic" Excel
Spill Range Properties
PRO TIP: Growing Source Data
Common Dynamic Array Functions
The SORT Function
The SORTBY Function
The FILTER Function
The UNIQUE Function
PRO TIP: Drop-Down Lists with SORT & UNIQUE
Combining SORT FILTER & UNIQUE
Referencing Spill Ranges in Calculations
Conditional Formatting with Dynamic Arrays
PRO TIP: Creating Charts from Dynamic Arrays
The SEQUENCE Function
PRO TIP: Top N Calculations with SEQUENCE & LARGE
The RANDARRAY Function
PRO TIP: Creating Random Top N Lists
The FREQUENCY Function
The TRANSPOSE Function
PRO TIP: Joining Dynamic Arrays with CHOOSE
PRO TIP: Declaring Variables with LET
QUIZ: Dynamic Array Formulas
HOMEWORK: Dynamic Array Formulas
6 Topics
Introduction
Creating Custom References with INDIRECT
Linking to Worksheet Locations with HYPERLINK
Real-Time Weather Conditions with WEBSERVICE & FILTERXML (Part 1)
Real-Time Weather Conditions with WEBSERVICE & FILTERXML (Part 2)
Bonus Excel Functions
1 Topic
BONUS LESSON
Microsoft Excel: Advanced Excel Formulas & Functions
Related Excel Formulas and Functions Courses
Thank You!
Your review has been submitted successfully.