Self-Paced Course
Excel Formulas & Functions
Master 75+ formulas & functions, and learn how to transform basic Excel worksheets into dynamic and powerful analytics tools.
Course Description
This course is all about using formulas & functions to transform basic Excel worksheets into dynamic and powerful analytics tools.
We'll cover 75+ core functions, using hands-on demos designed to showcase why they are important and how they can be applied in a variety of ways. This isn't about memorizing syntax; it's about learning how to THINK like Excel.
By the end of the course, you'll have the skills to build interactive models and dashboards, automate tedious and time-consuming tasks, and explore raw data using text functions, logical operators, time intelligence, dynamic arrays, and much, much more.
Whether you're looking to brush up on the basics or develop expert-level formula skills, you've come to the right place.
Â
COURSE CONTENTS:
9.5 hours on-demand video (16.0 CPE credits)
9 quizzes
9 homework assignments
2 skills assessments (1 benchmark, 1 final)
COURSE CURRICULUM:
- Welcome to the Course!
- Benchmark Assessment
- Course Structure & Outline
- DOWNLOAD: Course Resources
- Setting Expectations
- Introduction
- Formula Syntax
- Fixed & Relative References
- Common Error Types
- Formula Auditing: Trace Precedents & Dependents
- Formula Auditing: Evaluate Formula & Error Checking
- Ctrl Shortcuts
- Function Shortcuts
- Alt Key Tips
- PRO TIP: Data Validation
- Congrats, You're a Developer!
- QUIZ: Formulas 101
- HOMEWORK: Formulas 101
- Introduction
- Anatomy of the IF Statement
- Nested IF Statements
- AND/OR Operators
- NOT vs. <>
- Fixing Errors with IFERROR
- Common IS Statements
- QUIZ: Logical Operators
- HOMEWORK: Logical Operators
- Introduction
- Basic Statistical Functions
- SMALL/LARGE & RANK/PERCENTRANK
- RAND() & RANDBETWEEN
- The SUMPRODUCT Function
- SUMPRODUCT Demo
- COUNTIFS/SUMIFS/AVERAGEIFS
- DEMO: Building a Basic Dashboard
- Combining COUNTIF & SUMPRODUCT
- PRO TIP: Data Profiling with COUNT & SUMPRODUCT
- QUIZ: Statistical Functions
- HOMEWORK: Statistical Functions
- Introduction
- Working with Named Arrays
- ROW/ROWS & COLUMN/COLUMNS
- VLOOKUP/HLOOKUP
- Joining Data with VLOOKUP
- Fixing Errors with IFERROR & VLOOKUP
- VLOOKUP Reference Array Options
- Approximate Match Lookups
- The INDEX Function
- The MATCH Function
- Using INDEX & MATCH Together
- Combining MATCH with VLOOKUP
- UPDATE: VLOOKUP Correction
- SNEAK PEEK: XLOOKUP (Excel 365 Only)
- DEMO: Building Dashboards with XLOOKUP
- Selecting List Items With CHOOSE
- The OFFSET Function
- Combining OFFSET with COUNTA
- DEMO: Using OFFSET to Create a Scrolling Chart
- QUIZ: Lookup/Reference Functions
- HOMEWORK: Lookup/Reference Functions
- Introduction
- UPPER/LOWER/PROPER & TRIM
- The CONCATENATE Function (&)
- LEFT/MID/RIGHT & LEN
- TEXT/VALUE
- SEARCH/FIND
- Categorizing Data with IF(ISNUMBER(SEARCH))
- Combining RIGHT, LEN, and SEARCH
- SUBSTITUTE
- QUIZ: Text Functions
- HOMEWORK: Text Functions
- Introduction
- DATEVALUE
- Date Formatting & Fill Series
- TODAY()/NOW()
- YEAR/MONTH/DAY & HOUR/MINUTE/SECOND
- The EOMONTH Function
- The YEARFRAC Function
- WEEKDAY, WORKDAY & NETWORKDAYS
- The DATEDIF Function
- DEMO: Designing a Budget Pacing Tool
- QUIZ: Date & Time Functions
- HOMEWORK: Date & Time Functions
- Introduction
- Creating, Editing & Managing Rules
- Highlighting Every Other Row Using MOD
- Formatting Cells Based on the Value of Another Cell
- Formatting Cells Using Statistical Functions
- Formatting Cells Using Text & Logical Operators
- QUIZ: Formula-Based Formatting
- HOMEWORK: Formula-Based Formatting
- 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-Downs 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 Arrays with CHOOSE
- PRO TIP: Declaring Variables with LET
- QUIZ: Dynamic Array Formulas
- HOMEWORK: Dynamic Array Formulas
- Introduction
- The INDIRECT Function
- The HYPERLINK Function
- WEBSERVICE & FILTERXML: Weather Underground
- WEBSERVICE & FILTERXML: Open Weather Map
- QUIZ: Bonus Functions
- Final Assessment
- Course Feedback Survey
- Share the love!
- Next Steps
WHO SHOULD TAKE THIS COURSE?
Analysts looking to extract insight from raw data, and develop expert-level formula skills
Excel users who want to build interactive models or dashboards (forecasts, financial models, scenario tests, etc)
Anyone seeking to improve productivity and work more efficiently with data
WHAT ARE THE COURSE REQUIREMENTS?
Microsoft Excel 2013+ (ideally Office 365)
No advance preparation is required (basic familiarity with Excel formulas recommended, but not required)
WHAT ARE THE COURSE OBJECTIVES?
Identify basic Excel formula syntax and functionality, including reference types, errors, shortcuts, and auditing tools
Identify and interpret logical operators and conditional statements, including the IF, AND, OR, NOT, and IFERROR functions
Identify and interpret examples of statistical calculations, including the MAX/MIN, RANK, RAND, SUMIFS/COUNTIFS/AVERAGEIFS, and SUMPRODUCT functions
Identify and interpret examples of joining and extracting data using lookup functions, including VLOOKUP, INDEX, MATCH, XLOOKUP, CHOOSE, and OFFSET
Identify and interpret examples of wrangling text data using formulas, including the TEXT, LEFT/MID/RIGHT, SEARCH, TRIM, and LEN functions
Identify and interpret examples of Excel's date handling, including the TODAY/NOW, DATEDIF, YEARFRAC, EOMONTH, WEEKDAY/WORKDAY/NETWORKDAYS functions
Identify Excel's dynamic array functionality, including compatibility, spilled range properties, growing source data, and differences with legacy Excel
Identify and interpret examples of dynamic array formulas, including SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, RANDARRAY, FREQUENCY, TRANSPOSE, and LET
CPE ACCREDITATION DETAILS:
CPE Credits: 16.0
Field of Study: Information Technology
Delivery Method: QAS Self Study
Maven Analytics LLC is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have the final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
For more information regarding administrative policies such as complaints or refunds, please contact us at admin@mavenanalytics.io or (857) 256-1765.
*Last Updated: December 1, 2021
Start learning for FREE, no credit card required!
Every subscription includes access to the following course materials
- Interactive Project files
- Downloadable e-books
- Graded quizzes and assessments
- 1-on-1 Expert support
- 100% satisfaction guarantee
- Verified credentials & accredited badges
Ready to become a
data rockstar?
Start learning for free, no credit card required!