Zenler Player
Your course is loading. Hang tight.
The Ultimate Excel Formulas Course Excel 365
Back to curriculum
0% Complete
0% Complete
How to take this course
Conditional Calculations Introduction
Logical Tests
What are logical tests and comparative operators?
AND and OR
What is the use of Logical Operators and IF statement ?
How do I construct an IF statement?
Nested IF Statement
Conditional Calculations - Time to think
Activity
Are there any shortcuts when it comes to IF statement?
Other IF and IFS functions
How can I SUM with conditions attached?
How can I SUM with multiple conditions attached?
AVERAGEIF and COUNTIF
IFS functions to replace Nested IF Statements
MINIFS and MAXIFS
Conditional Calculations Final activity
Lookup Functions Introduction
The basics of VLOOKUP
What is HLookup and Vlookup and what limitations does it pose?
INDEX and MATCH
What is INDEX and MATCH
Can I combine LOOKUP and MATCH?
LOOKUP and MATCH
Can I combine INDEX and MATCH?
LOOKUP to the left
How can I lookup to the left?
Lookup Activity
The CHOOSE function
LOOKUP with TEXT
SWITCH
XLOOKUP - Exact Match
XLOOKUP - Match mode
XLOOKUP - search mode
Relative and Absolute cell referencing introduction
How can one become more efficient at writing formula?
Relevant and Absolute cell references
What is a relative cell reference?
What is an absolute cell reference?
How do I lock in cells going down the rows?
How do I lock in cells going across the columns?
Why is rounding so important?
Rounding in Excel
How do I round a a number to a specified number of digits?
How do I round up?
How do I round down?
How do I round to CEILING or FLOOR?
How do I round to an integer, odd or even number?
SMALL and LARGE
Date Time and Text in Excel Introduction
Working with Text 1
TEXTJOIN Function
Working with Text 2
Advanced TEXTJOIN with Array IF Statements
When would a user work with Text in Excel?
How do I extract text using LEFT, RIGHT and MID?
How do I clean up and format my text?
How can I search and amend a text string?
Custom Formatting
What is custom formatting?
How can I use custom formatting to add text to a cell?
How can I create a dynamic label?
Working with Date and Time 1
Working with Date and Time 2
How does Excel recognise Date and Time?
How do I calculate duration?
How do I use EOMONTH?
How can custom formatting change the presentation of date and time?
What other date and time functions should I be aware of?
How does working with date and time increase the efficiency of Excel Users?
Data Validation Introduction
Data Validation
What is Data Validation?
How do I apply data validation?
How do I add an error alert?
Error Resistance
What is an error resistance formula?
What functions can I use to create error resistant formula?
Formula Auditing Tips
How to document an entire workbook
Financial Functions Introduction
Future Value
How do I calculate Future value in Excel?
Simple and compound interest
Effective Interest Rate
How do I calculate effective interest?
Present Value
NPV
How do I calculate present value and NPV?
Payment
How do I use the PMT function from the borrowers view?
How do I use the PMT function from the lenders view and what is NPER?
Other financial functions
Introduction to Analysis
Working with tables theory
Filters and Tables
Extract data from table with 1 criteria
Extract data from table with 2 criteria
1 Variable What IF Analysis
How do I carry out a 1 Variable what if Analysis?
2 Variable What IF Analysis
How do I carry out a 2 variable what if analysis?
What is goal seek, scenario manager and solver?
Goal Seek
How do I use Goal Seek?
How do I use Scenario Manager?
Using Solver
How do I use Solver?
Advanced use of Solver
Correlation between two values
Rank and Reference Introduction
Rank
Unique rank
What are reference functions and how do they benefit users?
What is the OFFSET function?
OFFSET Examples
How do I use OFFSET
Indirect Example
What is the Indirect Function?
How do I use INDIRECT and overcome errors?
COLUMN
How do I use ROWs and COLUMNs
Rank and Reference Activity
Rank and Reference Activity Solution
Named Ranges and UDF Introduction
Dynamic Named Ranges
What are named ranges and what are the benefits of use?
How do I set up named ranges?
How to I find and edit and use named ranges?
How do I set up dynamic named ranges?
What you should know about User Defined Functions
UDF Activity
UDF Activity solutions
What is a Pivot Table
How to set up a basic PivotTable
Setting up a Pivot Table
Basic Calculations in Pivot tables
How do I change the value settings
How do I add calculations to my Pivot tables
More on Pivot tables
How do I work with dates in Pivot tables?
How do I add a report filter?
Adding Calculated Fields
Adding Pivot Charts
Awesome Pivot table tricks
PIvot table Activities
Pivot table activity solutions
Course Conclusion
Conditional Calculations
How to take this course
Conditional Calculations Introduction
Logical Tests
Preview
What are logical tests and comparative operators?
AND and OR
What is the use of Logical Operators and IF statement ?
How do I construct an IF statement?
Nested IF Statement
Conditional Calculations - Time to think
Activity
Are there any shortcuts when it comes to IF statement?
Other IF and IFS functions
How can I SUM with conditions attached?
How can I SUM with multiple conditions attached?
AVERAGEIF and COUNTIF
IFS functions to replace Nested IF Statements
Preview
MINIFS and MAXIFS
Conditional Calculations Final activity
Lookup Functions
Lookup Functions Introduction
The basics of VLOOKUP
Preview
What is HLookup and Vlookup and what limitations does it pose?
INDEX and MATCH
What is INDEX and MATCH
Can I combine LOOKUP and MATCH?
LOOKUP and MATCH
Can I combine INDEX and MATCH?
LOOKUP to the left
How can I lookup to the left?
Lookup Activity
The CHOOSE function
LOOKUP with TEXT
Preview
SWITCH
XLOOKUP - Exact Match
XLOOKUP - Match mode
XLOOKUP - search mode
Relative and Absolute cell References and Rounding
Relative and Absolute cell referencing introduction
How can one become more efficient at writing formula?
Relevant and Absolute cell references
What is a relative cell reference?
What is an absolute cell reference?
How do I lock in cells going down the rows?
How do I lock in cells going across the columns?
Why is rounding so important?
Rounding in Excel
How do I round a a number to a specified number of digits?
How do I round up?
How do I round down?
How do I round to CEILING or FLOOR?
How do I round to an integer, odd or even number?
SMALL and LARGE
Working with Date, Time and Text in Excel
Date Time and Text in Excel Introduction
Working with Text 1
TEXTJOIN Function
Working with Text 2
Advanced TEXTJOIN with Array IF Statements
When would a user work with Text in Excel?
How do I extract text using LEFT, RIGHT and MID?
How do I clean up and format my text?
How can I search and amend a text string?
Custom Formatting
What is custom formatting?
How can I use custom formatting to add text to a cell?
How can I create a dynamic label?
Working with Date and Time 1
Working with Date and Time 2
How does Excel recognise Date and Time?
How do I calculate duration?
How do I use EOMONTH?
How can custom formatting change the presentation of date and time?
What other date and time functions should I be aware of?
How does working with date and time increase the efficiency of Excel Users?
Data Validation and Error Resistant formulas
Data Validation Introduction
Data Validation
What is Data Validation?
How do I apply data validation?
How do I add an error alert?
Error Resistance
What is an error resistance formula?
What functions can I use to create error resistant formula?
Formula Auditing Tips
How to document an entire workbook
Financial Functions in Excel
Financial Functions Introduction
Future Value
How do I calculate Future value in Excel?
Simple and compound interest
Effective Interest Rate
How do I calculate effective interest?
Present Value
NPV
How do I calculate present value and NPV?
Payment
How do I use the PMT function from the borrowers view?
How do I use the PMT function from the lenders view and what is NPER?
Other financial functions
Introduction to Analysis in Excel
Introduction to Analysis
Working with tables theory
Filters and Tables
Extract data from table with 1 criteria
Extract data from table with 2 criteria
1 Variable What IF Analysis
How do I carry out a 1 Variable what if Analysis?
2 Variable What IF Analysis
How do I carry out a 2 variable what if analysis?
What is goal seek, scenario manager and solver?
Goal Seek
How do I use Goal Seek?
How do I use Scenario Manager?
Using Solver
How do I use Solver?
Advanced use of Solver
Correlation between two values
Rank and Reference Functions
Rank and Reference Introduction
Rank
Unique rank
What are reference functions and how do they benefit users?
What is the OFFSET function?
OFFSET Examples
How do I use OFFSET
Indirect Example
What is the Indirect Function?
How do I use INDIRECT and overcome errors?
COLUMN
How do I use ROWs and COLUMNs
Preview
Rank and Reference Activity
Rank and Reference Activity Solution
Named Ranges and User Defined Functions
Named Ranges and UDF Introduction
Dynamic Named Ranges
What are named ranges and what are the benefits of use?
How do I set up named ranges?
How to I find and edit and use named ranges?
How do I set up dynamic named ranges?
What you should know about User Defined Functions
UDF Activity
UDF Activity solutions
Pivot tables
What is a Pivot Table
How to set up a basic PivotTable
Setting up a Pivot Table
Basic Calculations in Pivot tables
Preview
How do I change the value settings
How do I add calculations to my Pivot tables
More on Pivot tables
How do I work with dates in Pivot tables?
How do I add a report filter?
Adding Calculated Fields
Adding Pivot Charts
Awesome Pivot table tricks
PIvot table Activities
Pivot table activity solutions
Course Conclusion
Back to Dashboard
No contents are available in this lesson!
No lessons available !
Back to Dashboard
Lesson contents locked
Enroll to unlock this lesson.
Enroll to unlock
Next Lesson