Mastering Excel formulas, automation, and data modeling

Unlock advanced Excel functions, automate workflows, and build powerful data models for better decision-making

Created byLong Nguyen

Last updated 11/2024

English

What you'll learn

Master data validation techniques to ensure accuracy, create dynamic dropdowns, and apply customized formulas for better data entry management.
Enhance your logical thinking by using IF, AND, OR, and nested IFs to solve complex decision-making problems within your spreadsheets.
Automate lookups with VLOOKUP, MATCH, INDEX, and CHOOSE, making data retrieval faster, more flexible, and error-free.
Streamline error checking and formula auditing using Excel’s built-in tools to identify, troubleshoot, and correct spreadsheet issues efficiently.
Unlock the power of data modeling with SUMPRODUCT, Goal Seek, and Scenario Manager to analyze different outcomes and improve data-driven decision-making.
Gain proficiency in handling complex Excel formulas, tracing precedents and dependents, and ensuring the integrity of your data with advanced auditing tools.

Course content

5 sections • 23 lectures • 1h 54m total length

Introducing data Validation6:11
This lesson is just an introduction of Data validation and how useful this tool is.
Creating dropdown lists5:45
In this video, we will talk about how to create a dropdown list to prevent people from entering incorrect data into our worksheets.
Using formulas in data validation5:00
Can we use a formula to create data validation? Let's see how.
Working with data validation5:40
Let's take a look at how to work with and manage data validation
Advanced conditional formatting4:48
apply conditional formatting with formulas and data validation to highlight low stock and entire rows based on a country dropdown, using absolute vs relative references.

Logical function 1 - IF5:37
You heard about this function, right? Let's learn how to use it today.
Logical function 2 - AND, OR4:55
Now let's talk about 2 simple yet very imprtant logical functions: AND and OR
Combining Logical Functions 1 - IF, AND, OR3:36
Learn to combine if with and and or to build powerful conditional logic in Excel, calculating a 10 percent penalty and a $250 discount for eligible cases.
Combining Logical Functions 2- Nested IFs4:32
Learn to use nested if functions to produce three or more outcomes, classifying balances as A, B, or C and applying multi-step discount logic.
Handling Errors- IFERROR, IFNA4:53
Master Excel error handling by applying iferror and ifna to replace div zero and na errors with blanks or zeros, and show how these functions affect your calculations.

Introduction to Lookups- CHOOSE3:25
The very first lookup function we are going to talk about is the "Choose" function
Approximate Matches- Range VLOOKUP3:29
Today's lesson is all about Range Vlookup
Exact Matches- Exact Match VLOOKUP5:10
Today's lesson is all about Exact Match Vlookup
Finding a Position - MATCH function5:08
Use the match function to dynamically locate column positions for vlookup, ensuring robust lookups when price list columns change. Learn exact-match lookups, reference locking, and safe formula copying.
Dynamic Lookups - INDEX, MATCH6:40
Learn how to use index and match to perform dynamic lookups across a discount matrix, combining discount category and customer category to automate discount calculations.

Error Checking7:11
Error checking and calculation Options5:48
Learn to identify and fix circular references and inconsistent formulas in Excel using error checking, green triangles, and calculation options—automatic vs manual calculation and calculate now.
Trace Precedents and Dependents4:12
Learn to use trace precedents and trace dependents to locate and fix workbook errors, ensure totals match, and understand formula references across worksheets.
Evaluate Formula, Watch Window5:06

Modelling Functions- SUMPRODUCT4:00
Use sumproduct to calculate weighted average of pessimistic, likely, and optimistic estimates, with arrays and absolute references, then count tasks above a threshold by converting booleans to 1s and 0s.
Data Tables5:06
Master how Excel data tables enable what-if analysis by varying expenses and revenue inputs in a single formula across rows or columns, revealing multiple outcomes.
Goal Seek3:22
discover how to use excel's goal seek to change one input (b5) so that the calculation in e20 hits break-even or a targeted profit, via what-if analysis.
Scenario Manager4:49
Master Excel's scenario manager to compare price and quantity scenarios, generate a scenario summary of projected profits, and store all estimations in one worksheet.

Requirements

Intermediate Excel skills and knowledge
Willing to learn and practise a lot
Excel 2016 or newer version installed
Internet connection

Description

Take your Excel expertise to the next level with "Mastering Excel formulas, automation, and data modeling".

This advanced course dives deep into the critical functions and tools that power complex decision-making and data analysis. From building dynamic lookup formulas to automating error checks and modeling scenarios, this course is designed for professionals who want to maximize their data capabilities and create robust, error-proof spreadsheets.

What You’ll Learn:

Master Data Validation: Ensure data integrity with advanced data validation techniques, including creating dropdown lists and applying formulas for more customized rules.
Enhance Conditional Logic Skills: Learn to work with powerful logical functions like IF, AND, OR, and Nested IFs to make complex decisions based on multiple criteria.
Automate Lookups for Efficient Analysis: Master lookup functions like VLOOKUP, MATCH, INDEX, and CHOOSE to automate data retrieval, saving you time and improving your efficiency.
Optimize Error Checking & Formula Auditing: Discover tools to trace errors, evaluate formulas, and ensure your spreadsheets are error-free and optimized for performance.
Unlock the Power of Data Modeling: Dive into data modeling techniques like SUMPRODUCT, Scenario Manager, and Goal Seek to build complex models and make data-driven decisions with ease.

This course is designed to empower you with the most powerful Excel tools to handle complex tasks, automate processes, and create models that help drive key business insights. By the end, you’ll have the skills needed to tackle real-world challenges and optimize your workflow for peak productivity.

Who this course is for:

Office workers, especially accountants, auditors, HR officers, managers
Intermediate Excel learners

Mastering Excel formulas, automation, and data modeling

What you'll learn

Explore related topics

Course content

All about Data validation5 lectures • 27min

Conditional Logic5 lectures • 24min

Automating Lookups5 lectures • 24min

Formula Auditing and Protection4 lectures • 22min

Data Modelling4 lectures • 17min

Requirements

Description

Who this course is for: