Microsoft Excel - Advanced Excel Formulas & Functions

COURSE | Started On : Monday, 10 October 2022 15:35 | 293
Instructor: Unknown User Unknown User

Master 75+ MS Excel formulas and learn data analysis.

COURSE OVERVIEW

Whether you're starting from square one or aspiring to become an absolute Excel power user, you've come to the right place.

This course will give you a deep understanding of the advanced Excel formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool.

 

Learning Objectives 

  • Easily build dynamic tools & Excel dashboards to filter, display and analyze your data
  • Create your own formula-based Excel formatting rules
  • Join datasets from multiple sources with XLOOKUP, INDEX & MATCH functions
  • Manipulate dates, times, text, and arrays
  • Automate tedious and time-consuming tasks using cell formulas and functions in Excel (no VBA required!)
  • Pull real-time data from APIs directly into Excel (weather, stock quotes, directions, etc.
Course Information
Creator
Unknown User
Published Date
2022-10-10 15:35:34
Certificate Term
Complete all lessons
INSTRUCTORS
Unknown User

Unknown User

Courses by Unknown User

Enrolled Users
Uzair Bhamji
Uzair Bhamji
Alex Kaggua
Alex Kaggua
Mohamed Shafeek
Mohamed Shafeek
Aamir Sohail
Aamir Sohail
Rohan
Rohan
P. Balasubramanian
P. Balasubramanian
Course Structure & Outline
Setting Expectations
DOWNLOAD - Course Resource Files
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
Saving Time with Function Shortcuts
Accessing Tools with Alt Key Tips
Creating Drop Down Menus with Data Validation
Congrats, You're a Developer!
Homework - Excel Formulas 101
Anatomy of the IF Statement
Nesting Multiple IF Statements
Adding Conditional AND OR Operators
Using NOT & _____ Conditionals
Fixing Errors with IFERROR
Common IS Statements
Homework - Excel Logical Operators
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
Homework - Excel Stats Functions
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
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 Peak - 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
Homework - Excel Lookup & Reference Functions
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
Replacing Text with SUBSTITUTE
Homework - Excel Text Functions
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
Homework - Excel Date & Time Functions
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
Homework - Excel Formula Based Formatting
READ ME - Array Formulas are Changing!
Rules of Excel Array Functions
Pros & Cons of Array Functions
Defining Vertical, Horizontal, and 2 Dimensional Arrays
Using Array Constants in Formulas
Defining & Referencing Named Array Constants
Restructuring Data with TRANSPOSE
Linking Data Between Sheets Array vs Non Array Comparison
Returning the Largest Values in a Range
Counting Characters Across Cells
Creating a MAX IF Array Formula
Creating a MAX IF Array Formula with Multiple Criteria
Converting Boolean Values Using the Double Unary
Homework - Excel Array Functions
Creating Custom References with INDIRECT