Microsoft Excel - Advanced Excel Formulas & Functions
4.6 /5- Offered byUDEMY
Microsoft Excel - Advanced Excel Formulas & Functions at UDEMY Overview
Duration | 6 hours |
Total fee | ₹11,200 |
Mode of learning | Online |
Difficulty level | Intermediate |
Credential | Certificate |
Microsoft Excel - Advanced Excel Formulas & Functions at UDEMY Highlights
- Compatible on Mobile and TV
- Earn a Cerificate on successful completion
- Get Full Lifetime Access
- Course Instructor: Maven Analytics
Microsoft Excel - Advanced Excel Formulas & Functions at UDEMY Course details
- Excel users who have basic skills but want to get really, REALLY good
- Anyone hoping to expand their analytics skill set, work more efficiently with data, and take their career to a new level
- Students looking for an engaging, hands-on, and highly interactive approach to Excel analytics training
- Excel users looking to build strong analytical thinking and business intelligence skills
- Anyone looking to pursue a career in data analysis or business intelligence
- 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, tools and case studies that you won't find in ANY other course, guaranteed
- Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
- Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
- Build Excel formulas to analyze dates, text fields, values and arrays
- This Microsoft Excel Advanced Formulas and Functions training course from Infinite Skills takes you beyond the basics of Excel, and teaches you how to use the advanced formulas and functions in this spreadsheet program by Microsoft
- You will begin by reviewing the basic operations, such as sum and count syntax, function processing order, and knowing the troubleshooting tools
- You will explore extended formula usage with 3D referencing, naming cells and absolute referencing. Guy proceeds to instruct you on how to look up and reference type functions using VLookup and HLookup
Microsoft Excel - Advanced Excel Formulas & Functions at UDEMY Curriculum
Getting Started
Course Structure & Outline
READ ME: Important Notes for New Students
DOWNLOAD: Course Resources
Setting Expectations
Excel Formulas 101
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
PRO TIP: Creating Drop-Down Menus with Data Validation
Congrats, You're a Developer!
HOMEWORK: Excel Formulas 101
Conditional Statements & Logical Operators
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
HOMEWORK: Excel Logical Operators
Common Excel Statistical Functions
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: Counting Non-Blank Cells
HOMEWORK: Excel Stats Functions
Lookup & Reference Functions
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
CHOOSE
Navigating & Referencing Cell Ranges with OFFSET
Combining OFFSET with COUNTA to Create a Flexible Range
PRO TIP: Using OFFSET to Create an Interactive Scrolling Chart
HOMEWORK: Excel Lookup & Reference Functions
Text Functions
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
HOMEWORK: Excel Text Functions
Date & Time Functions
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
HOMEWORK: Excel Date & Time Functions
Formula-Based Formatting
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
HOMEWORK: Excel Formula-Based Formatting
Array Formulas
Introduction
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
Extra Bonus Functions
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)
Wrapping Up
Conclusion & Next Steps
More from Maven Analytics
Other courses offered by UDEMY
Student Forum
Anything you would want to ask experts?