Master Microsoft Excel: Advanced Excel Formulas & Functions
Last updated 10/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 9.22 GB | Duration: 17h 24m
Unlock the Power of Excel for Data Analysis, Automate Tasks and Save Time with Advanced Formulas and Functions(100+ Fns)
What you'll learn

Master over 100 functions across various categories, including statistical, logical, text, lookup, and dynamic array, Date and Times functions.
Apply advanced Excel formulas and functions to solve complex problems
Work with logical functions (IF, AND, OR, IFS, SWITCH, etc.) to create conditional statements
Master lookup and reference functions (VLOOKUP, XLOOKUP, INDEX-MATCH, etc.) to retrieve data from different parts of your spreadsheet.
Master Dynamic Arrays functions (SEQUENCE, UINQUE, SORT, FILTER, RANDARRAY, etc.) to perform complex data operations with ease.
Utilize date and time functions (DATE, TODAY, YEAR, WORKDAY, NETWORKDAYS, YEARFRAC, DATEDIF, etc.) to manipulate dates and times efficiently.
Leverage text functions (CONCAT, LEFT, RIGHT, MID, FIND, SEARCH, SUBTITUTE, etc.) to clean and manipulate text data
Employ statistical functions (AVERAGE, COUNT, COUNTA, SUM, MIN, MAX, etc.) to analyze your data.
Apply formulas to real-world business scenarios.
Gain confidence in working with large datasets.
Learn to identify, troubleshoot, and handle common Excel errors.
Requirements
Students should have access to a computer with Microsoft Excel installed (From MS Excel 2010)
Fundamental understanding of Excel's interface, including how to navigate worksheets and enter data into cells.
Downloadable practice files provided (Optional)
Description
Unlock the power of excel for data analysis, automate tasks, make intelligent decisions, and save time with over 100 formulas and functions.Are you ready to take your Microsoft Excel skills to the next level? Look no further! This comprehensive course, Master Microsoft Excel: Advanced Excel Formulas & Functions, is designed to transform you from an Excel novice into a data analysis powerhouse. Whether you're a business professional, student, or data enthusiast, this course will equip you with the tools to easily tackle complex data challenges.What You'll LearnYou'll dive deep into the world of Excel formulas and functions. Starting with the fundamentals, you'll quickly progress to advanced techniques that will revolutionize the way you work with data. Here's a glimpse of what you'll master:Excel Formula Fundamentals: Understand the building blocks of Excel formulas, including cell references, ranges, and the critical difference between relative and absolute references. Advanced Functions: Explore over 100 functions across various categories, including statistical, logical, text, lookup, and dynamic array functions.Conditional Logic: Learn to make your spreadsheets smarter with IF statements, IFS functions, and SWITCH statements.Text Manipulation: Discover powerful text functions that will help you clean, combine, and extract information from text data effortlessly.Lookup and Reference Functions: Master VLOOKUP, XLOOKUP, and the powerful combination of INDEX and MATCH to retrieve data like a pro.Date and Time Functions: Gain control over the date and time calculations, from basic date extraction to complex business day computations.Dynamic Arrays: Harness the power of Excel's newest feature - dynamic arrays - to easily perform complex data operations.Error Handling: Learn to identify, troubleshoot, and elegantly handle common Excel errors.Why Choose This Course?Comprehensive Coverage: With over 100 functions covered, you'll have a toolkit for any data challenge.Practical Projects: Apply your learning to real-world scenarios, including a student grading system and data analysis projects.Step-by-Step Approach: Complex concepts are broken down into easy-to-follow lessons, ensuring you grasp every detail.Hands-On Learning: Each section includes exercises and projects to reinforce your understanding.Future-Proof Skills: Learn the latest Excel features, including dynamic arrays and advanced lookup functions.What You'll AchieveBy the end of this course, you'll be able to:Automate repetitive tasks with advanced formulasPerform complex data analysis with confidenceCreate dynamic, self-updating spreadsheetsSolve real-world business problems using ExcelImpress employers with your advanced Excel skillsDon't let complex data hold you back. Enroll now in Master Microsoft Excel: Advanced Excel Formulas & Functions and transform the way you work with Excel. Whether you're looking to advance your career, improve your productivity, or simply master one of the most powerful tools in the business world, this course is your key to success.Unlock the full potential of Excel and become the go-to Excel expert in your organization. Your journey to Excel mastery starts here!By the end, you'll have a complete skillset in advanced Excel formulas and functions. This will allow you to handle even the most complicated data tasks with confidence and efficiency. Upgrade your career, streamline your work, and gain an advantage in today's data-driven world.Get Started Today!
Overview
Section 1: Setting the Stage for Excel Functions Mastery
Lecture 1 Overview of Course Structure and Outline
Lecture 2 Important Notes for New Students
Lecture 3 Setting Expectation
Lecture 4 Resources and Assets
Section 2: Excel Formula Fundamentals
Lecture 5 Introduction
Lecture 6 Excel Cell References vs. Cell Ranges: What You Need to Know
Lecture 7 Understanding and Using Named Ranges in Excel
Lecture 8 Understanding and Using Relative Cell References in Excel
Lecture 9 Understanding and Using Absolute Cell References in Excel
Lecture 10 Understanding the Difference Between Formulas and Functions in Excel
Lecture 11 Mastering the Syntax of Excel Formulas Part 1
Lecture 12 Mastering the Syntax of Excel Formulas Part 2
Section 3: Understanding Operators in Excel
Lecture 13 Arithmetic Operators
Lecture 14 Comparison Operators
Lecture 15 Logical Operators
Section 4: Statistical Functions in Excel
Lecture 16 Practice Workbook
Lecture 17 How to Use Excel's Function Wizard to Insert Functions and It's Arguments
Lecture 18 Excel FORMULATEXT Function: Display Formulas as Text
Lecture 19 Working with SUM Function
Lecture 20 Working with MIN & MAX Function
Lecture 21 Working with COUNT, COUNTA & COUNTBLANK Functions
Lecture 22 Working With AVERAGE
Lecture 23 Working with PRODUCT Function
Lecture 24 Working with ROUND, ROUNDDOWN & ROUNDUP
Lecture 25 Mastering the CEILING and TRUNC Functions in Excel
Lecture 26 Working with POWER Function
Lecture 27 Extracting Values with SMALL & LARGE
Lecture 28 How to generate Random Numbers with RAND and RANDBETWEEN in Excel
Lecture 29 Mastering Absolute Values in Excel with the ABS Function
Lecture 30 How to convert Between Number Bases Using the BASE Function
Lecture 31 How to convert Between Roman and Arabic Numbers with ROMAN and ARABIC Functions
Lecture 32 How calculate Square Roots Easily with SQRT Function
Lecture 33 How to create Dynamic Summaries in Excel with the SUBTOTAL Function
Lecture 34 How to Use the SUMPRODUCT Function to Perform Calculations in Excel
Section 5: Conditional Statement and Logical Operators in Excel
Lecture 35 Practice Workbooks
Lecture 36 Introduction
Lecture 37 Working with IF Function in Excel
Lecture 38 Working IFS Function and Multiple Conditions
Lecture 39 Excel SWITCH Function
Lecture 40 Introduction Logical Operators
Lecture 41 Logical AND Function
Lecture 42 Logical OR Function
Lecture 43 Logical NOT Function
Lecture 44 Combining LOGICAL OPERATORS
Section 6: Conditional Statistical Functions in Excel
Lecture 45 Practice Workbook
Lecture 46 Introduction
Lecture 47 Working with COUNTIF Function in Excel
Lecture 48 Working with COUNTIFS Function in Excel
Lecture 49 Working with SUMIF Function in Excel
Lecture 50 Working with SUMIFS Function in Excel
Lecture 51 Working with AVERAGEIF Function in Excel
Lecture 52 Working with AVERAGEIFS Function in Excel
Section 7: Text Functions in Excel
Lecture 53 Practice Workbooks
Lecture 54 Change Case Functions UPPER, LOWER & PROPER
Lecture 55 Combining Text Data with CONCAT(ETNATE)/ & TEXTJOIN Functions
Lecture 56 Converting Text to Values with TEXT & VALUE
Lecture 57 Extracting Strings with LEFT, MID & RIGHT
Lecture 58 Getting Length of String with LEN Function
Lecture 59 Cleaning Text Data with TRIM & CLEAN Functions
Lecture 60 Replacing Text Data with REPLACE & SUBSTITUTE Functions
Lecture 61 Master Exact Comparisons in Excel with the EXACT Function
Lecture 62 Learn to Use REPT Function in Excel
Lecture 63 Quickly Locate Text in Excel with the FIND Function
Lecture 64 Locate Data Quickly with Excel's SEARCH Function
Lecture 65 Get System Details with Excel's INFO Function
Lecture 66 Create Clickable Links in Excel with HYPERLINK Function
Lecture 67 (PROJECT) Text Analysis: Excel Formula for Automatic Word Count in Sentences
Lecture 68 (PROJECT) Email Parser: How to Extract Username and Domain from Email Address
Section 8: Get Information with Common IS Functions In Excel
Lecture 69 Practice Workbook
Lecture 70 Introduction
Lecture 71 Check if numbers are even or odd using the ISEVEN and ISODD Functions in Excel
Lecture 72 Easily Identify Empty Cells with ISBLANK Function in Excel
Lecture 73 Easily Identify Logical Values with ISLOGICAL Function in Excel
Lecture 74 Easily Identify Text and Numbers with ISTEXT and ISNUMBER Functions in Excel
Lecture 75 Learn How to Use ISNA, ISERR, and ISERROR to Identify Different Types of Errors
Section 9: PROJECT (Student Grading System and Data Analysis)
Lecture 76 Practice Workbooks
Lecture 77 Introduction
Lecture 78 Working With Student Database Part 1
Lecture 79 Working With Student Database Part 2
Lecture 80 Working With Student Database Part 3
Lecture 81 Working With Student Database Part 4
Lecture 82 Working With Student Database Part 5
Section 10: Look up and References Functions
Lecture 83 Practice Workbooks
Lecture 84 Learn to Find Row Numbers Easily with Excel's ROW Function
Lecture 85 Count the number of rows in cell range with the ROWS Function
Lecture 86 Find Column Numbers Easily with Excel's COLUMN Function
Lecture 87 Count the number of columns in cell range with the COLUMNS Function
Lecture 88 Use VLOOKUP to Match and Extract Data from Excel Tables
Lecture 89 VLOOKUP PROJECT (Student Database)
Lecture 90 Mastering XLOOKUP Part 1: Introduction and Basic Lookups
Lecture 91 Mastering XLOOKUP Part 2: Directional Searches and Multiple Output Columns
Lecture 92 Mastering XLOOKUP Part 3: Wildcard Query and Merging Two Tables with XLOOKUP
Lecture 93 Master Advanced Data Retrieval Using INDEX and MATCH in Excel
Lecture 94 INDEX & MATCH PROJECT(Student Data Analysis)
Section 11: DYNAMIC ARRAYS Functions In Excel
Lecture 95 Practice Workbook
Lecture 96 Introduction
Lecture 97 Easily Rotate Data from Horizontal to Vertical Using TRANSPOSE Function
Lecture 98 Select Specific Values from a List Using Excel's CHOOSE Function
Lecture 99 Extract Distinct Values from a Range with Excel's UNIQUE Function
Lecture 100 Create Custom Number Patterns Easily Using SEQUENCE in Excel
Lecture 101 How to Use the RANDARRAY Function to Generate an Array of Random Numbers
Lecture 102 Mastering Excel's Dynamic SORT Function: Part 1
Lecture 103 Mastering Excel's Dynamic SORT Function: Part 2
Lecture 104 SORTBY in Excel: Sorting Data Based on Multiple Columns
Lecture 105 Mastering FILTER Function Part 1: Basic Filters
Lecture 106 Mastering FILTER Function Part 2: Advanced Filtering with Multiple Criteria
Lecture 107 Mastering FILTER Function Part 3: Logical Filtering with AND and OR
Lecture 108 PROJECT: Practical Applications of the FILTER Function in Data Analysis (Part 1)
Lecture 109 PROJECT: Practical Applications of the FILTER Function in Data Analysis (Part 2)
Lecture 110 PROJECT: Practical Applications of the FILTER Function in Data Analysis (Part 3)
Section 12: Mastering Date and Time Functions in Excel: From Basics to Advanced Techniques
Lecture 111 Practice Workbook
Lecture 112 Introduction
Lecture 113 Entering Dates in Different Formats in Excel
Lecture 114 How to Identify if Dates are Entered as Text
Lecture 115 Entering Times in Different Formats in Excel
Lecture 116 Entering Dates and Times Together in Excel
Lecture 117 Formatting Dates & Time in Excel - Part 1: Introduction
Lecture 118 Formatting Dates & Time in Excel - Part 2: Custom Date Formatting
Lecture 119 Formatting Dates & Time in Excel - Part 3: Custom Time Formatting
Lecture 120 Simple Date & Time Math in Excel
Lecture 121 Extracting Date/Time Elements in Excel Using INT and MOD Functions
Lecture 122 Mastering the DATEVALUE Function in Excel
Lecture 123 How to Construct a Date and perform calculation Using the DATE Function in Excel
Lecture 124 Mastering the NOW Function in Excel
Lecture 125 Mastering the TODAY Function in Excel
Lecture 126 Extracting the Day of the Month from a Date with DAY Function in Excel
Lecture 127 Extracting the Month from a Date Using the MONTH Function in Excel
Lecture 128 Extracting the Year from a Date Using the YEAR Function in Excel
Lecture 129 How to Use TIMEVALUE to Convert Text to Time in Excel
Lecture 130 How to Use the TIME Function to Construct Time Values in Excel
Lecture 131 How to Use the HOUR Function to Get the Hour from a Time in Excel
Lecture 132 How to Use the MINUTE Function to Get the Minute from a Time in Excel
Lecture 133 How to Use the SECOND Function to Get the Second from a Time in Excel
Lecture 134 Date & Time Shortcuts
Lecture 135 How to Use the WEEKDAY Function to Identify Weekdays in Excel
Lecture 136 How to Use the DAYS Function to Find Date Differences in Excel
Lecture 137 How to Use the WORKDAY Function for Business Day Calculations in Excel
Lecture 138 How to Use WORKDAY.INTL to calculate Workdays with Custom Weekends and Holidays
Lecture 139 NETWORKDAYS: Calculate Workdays Between Dates Excluding Weekends and Holidays
Lecture 140 How to Use NETWORKDAYS.INTL for Workdays with Custom Weekends and Holidays
Lecture 141 How to Use EOMONTH to Find the Last and First Day of a Month in Excel
Lecture 142 How to Use EDATE to Find Dates by Adding or Subtracting Months
Lecture 143 How to calculate Date Differences with YEARFRAC Function in Excel
Lecture 144 How to Use DATEDIF to Find Date Differences in Years, Months, and Days
Lecture 145 Project: Calculate Age and Upcoming Birthday with Excel Date Functions
Lecture 146 How to convert Month Text to Month Number in Excel
Section 13: Data Validation and Error Handling in Excel
Lecture 147 Practice Workbook
Lecture 148 How to Recognize and Resolve Common Excel Errors (#VALUE!, #REF!, etc.)
Lecture 149 Mastering Data Validation in Excel - Part 1
Lecture 150 Mastering Data Validation in Excel - Part 2
Lecture 151 Mastering Data Validation in Excel - Part 3
Lecture 152 How to Fix Errors Using IF, ISERROR, IFERROR, and Related Functions
Section 14: Course Review and Conclusion
Lecture 153 Recap of Key Concepts and Formulas and What's Next
This course is designed for intermediate Excel users who want to learn advanced formulas and functions to unlock the full potential of Excel for data analysis, automation, and reporting.,Anyone hoping to expand their analytics skill set, work efficiently with data, and take their career to a new level,Excel users looking to build powerful analytical thinking and business intelligence skills,Self-learners passionate about mastering Excel
Screenshot