-
Conditional Calculations
18 Lessons-
StartHow to take this course
-
StartConditional Calculations Introduction
-
PreviewLogical Tests
-
StartWhat are logical tests and comparative operators?
-
StartAND and OR
-
StartWhat is the use of Logical Operators and IF statement ?
-
StartHow do I construct an IF statement?
-
StartNested IF Statement
-
StartConditional Calculations - Time to think
-
StartActivity
-
StartAre there any shortcuts when it comes to IF statement?
-
StartOther IF and IFS functions
-
StartHow can I SUM with conditions attached?
-
StartHow can I SUM with multiple conditions attached?
-
StartAVERAGEIF and COUNTIF
-
PreviewIFS functions to replace Nested IF Statements
-
StartMINIFS and MAXIFS
-
StartConditional Calculations Final activity
-
-
Lookup Functions
17 Lessons-
StartLookup Functions Introduction
-
PreviewThe basics of VLOOKUP
-
StartWhat is HLookup and Vlookup and what limitations does it pose?
-
StartINDEX and MATCH
-
StartWhat is INDEX and MATCH
-
StartCan I combine LOOKUP and MATCH?
-
StartLOOKUP and MATCH
-
StartCan I combine INDEX and MATCH?
-
StartLOOKUP to the left
-
StartHow can I lookup to the left?
-
StartLookup Activity
-
StartThe CHOOSE function
-
PreviewLOOKUP with TEXT
-
StartSWITCH
-
StartXLOOKUP - Exact Match
-
StartXLOOKUP - Match mode
-
StartXLOOKUP - search mode
-
-
Relative and Absolute cell References and Rounding
15 Lessons-
StartRelative and Absolute cell referencing introduction
-
StartHow can one become more efficient at writing formula?
-
StartRelevant and Absolute cell references
-
StartWhat is a relative cell reference?
-
StartWhat is an absolute cell reference?
-
StartHow do I lock in cells going down the rows?
-
StartHow do I lock in cells going across the columns?
-
StartWhy is rounding so important?
-
StartRounding in Excel
-
StartHow do I round a a number to a specified number of digits?
-
StartHow do I round up?
-
StartHow do I round down?
-
StartHow do I round to CEILING or FLOOR?
-
StartHow do I round to an integer, odd or even number?
-
StartSMALL and LARGE
-
-
Working with Date, Time and Text in Excel
21 Lessons-
StartDate Time and Text in Excel Introduction
-
StartWorking with Text 1
-
StartTEXTJOIN Function
-
StartWorking with Text 2
-
StartAdvanced TEXTJOIN with Array IF Statements
-
StartWhen would a user work with Text in Excel?
-
StartHow do I extract text using LEFT, RIGHT and MID?
-
StartHow do I clean up and format my text?
-
StartHow can I search and amend a text string?
-
StartCustom Formatting
-
StartWhat is custom formatting?
-
StartHow can I use custom formatting to add text to a cell?
-
StartHow can I create a dynamic label?
-
StartWorking with Date and Time 1
-
StartWorking with Date and Time 2
-
StartHow does Excel recognise Date and Time?
-
StartHow do I calculate duration?
-
StartHow do I use EOMONTH?
-
StartHow can custom formatting change the presentation of date and time?
-
StartWhat other date and time functions should I be aware of?
-
StartHow does working with date and time increase the efficiency of Excel Users?
-
-
Data Validation and Error Resistant formulas
10 Lessons-
StartData Validation Introduction
-
StartData Validation
-
StartWhat is Data Validation?
-
StartHow do I apply data validation?
-
StartHow do I add an error alert?
-
StartError Resistance
-
StartWhat is an error resistance formula?
-
StartWhat functions can I use to create error resistant formula?
-
StartFormula Auditing Tips
-
StartHow to document an entire workbook
-
-
Financial Functions in Excel
13 Lessons-
StartFinancial Functions Introduction
-
StartFuture Value
-
StartHow do I calculate Future value in Excel?
-
StartSimple and compound interest
-
StartEffective Interest Rate
-
StartHow do I calculate effective interest?
-
StartPresent Value
-
StartNPV
-
StartHow do I calculate present value and NPV?
-
StartPayment
-
StartHow do I use the PMT function from the borrowers view?
-
StartHow do I use the PMT function from the lenders view and what is NPER?
-
StartOther financial functions
-
-
Introduction to Analysis in Excel
17 Lessons-
StartIntroduction to Analysis
-
StartWorking with tables theory
-
StartFilters and Tables
-
StartExtract data from table with 1 criteria
-
StartExtract data from table with 2 criteria
-
Start1 Variable What IF Analysis
-
StartHow do I carry out a 1 Variable what if Analysis?
-
Start2 Variable What IF Analysis
-
StartHow do I carry out a 2 variable what if analysis?
-
StartWhat is goal seek, scenario manager and solver?
-
StartGoal Seek
-
StartHow do I use Goal Seek?
-
StartHow do I use Scenario Manager?
-
StartUsing Solver
-
StartHow do I use Solver?
-
StartAdvanced use of Solver
-
StartCorrelation between two values
-
-
Rank and Reference Functions
14 Lessons-
StartRank and Reference Introduction
-
StartRank
-
StartUnique rank
-
StartWhat are reference functions and how do they benefit users?
-
StartWhat is the OFFSET function?
-
StartOFFSET Examples
-
StartHow do I use OFFSET
-
StartIndirect Example
-
StartWhat is the Indirect Function?
-
StartHow do I use INDIRECT and overcome errors?
-
StartCOLUMN
-
PreviewHow do I use ROWs and COLUMNs
-
StartRank and Reference Activity
-
StartRank and Reference Activity Solution
-
-
Named Ranges and User Defined Functions
9 Lessons-
StartNamed Ranges and UDF Introduction
-
StartDynamic Named Ranges
-
StartWhat are named ranges and what are the benefits of use?
-
StartHow do I set up named ranges?
-
StartHow to I find and edit and use named ranges?
-
StartHow do I set up dynamic named ranges?
-
StartWhat you should know about User Defined Functions
-
StartUDF Activity
-
StartUDF Activity solutions
-
-
Pivot tables
15 Lessons-
StartWhat is a Pivot Table
-
StartHow to set up a basic PivotTable
-
StartSetting up a Pivot Table
-
PreviewBasic Calculations in Pivot tables
-
StartHow do I change the value settings
-
StartHow do I add calculations to my Pivot tables
-
StartMore on Pivot tables
-
StartHow do I work with dates in Pivot tables?
-
StartHow do I add a report filter?
-
StartAdding Calculated Fields
-
StartAdding Pivot Charts
-
StartAwesome Pivot table tricks
-
StartPIvot table Activities
-
StartPivot table activity solutions
-
StartCourse Conclusion
-
The Ultimate Excel Formulas Course Excel 365
Master Excel Formula and Functions From Beginner to Advance
Course Summary
*** Course access includes files, quizzes & activities and articles for handy reference ***
Updated to include XLOOKUP
Hands on, reinforced learning to ensure you Master Excel Formulas Becoming a master of Excel means you have to master both simple and complex formula. This course is designed to ensure you achieve your true potential when using Excel. With Over 90 functions and formulas covered, this course will take you from the basic formulas and build on this to more advanced and master level formulas.Updated to include XLOOKUP
Paula Guilfoyle
After over 10 years in Accounting and Operations Paula turned her career to subject matter expert, consultant, content creation and online education. In 2010 Paula began teaching online and can now boast over 250K students in 186 different countries. In 2016 Paula began her learning journey around Blockchain and Cryptocurrency. By 2022 Paula pivoted her consultancy, content creation and online education to focus on Blockchain, Web3 and Digital Assets.
