## USEFUL EXCEL FORMULAS AND FUNCTIONS

By LIVE ONLINE PROGRAM

Date:

12 & 13 Oct 2022 (Every session 9am - 5pm)

Wed & Thurs

Venue:

LIVE ZOOM

INTRODUCTION FOR USEFUL EXCEL FORMULAS AND FUNCTIONS

The course focuses on practical examples that will help participants easily transition to using Excel’s most powerful formulas and functions in real-world scenarios. Excel formulas and functions are the heart of Microsoft Excel spreadsheet. They are essential to manipulating data and obtaining useful information from your Excel workbooks. Participants will learn all the skills to master the formulas and Functions.

LEARNING OUTCOME FOR USEFUL EXCEL FORMULAS AND FUNCTIONS

Upon completion of the training, participants should gain renewed confidence in applying the skills and able to:

• Display and highlight formulas
• Convert formulas to values
• Tabulate data from multiple sheets
• understand the hierarchy of operations in formulas
• Use absolute and relative references
• Create and expand nested IF statements
• Look up information with VLOOKUP, MATCH, and INDEX
• Use the powerful COUNTIF family of functions
• Analyse data with statistical functions
• Calculate dates and times
• Analyse data with array formulas and functions
• Extract data with text function
• Create dynamic formulas using OFFSET
• Solve problem using combination of functions and formulas

COURSE CONTENT

Day 1

Formula Basics

• Function vs Formula
• Operators
• Arguments of a Function
• Using Optional arguments

References

• Relative, absolute and mixed references
• Creating formulas that link to other worksheets
• Creating 3-D reference

Named Ranges

• Creating a named range
• Creating a named constant
• Scopes of a named range

Text Formulas

• Joining Text with Numbers
• Keeping numbers formatted
• Stripping extra spaces and line breaks
• Changing case with formulas
• Extracting text from the Right, Left or Middle
• Finding and Replacing text with a formula

Dates and Times

• How Excel stores dates and times
• Displaying current date and time
• Taking apart a date with a formula
• Calculating the number of days between dates, including or excluding weekends or holidays
• Calculating years and months between dates
• Hidden DATEDIF Functions

Statistics

• Counting both numbers and text
• Count with one or more conditions
• Sum with and without conditions
• Calculating an average instantly, including or excluding blank or zero values
• Display maximum and minimum values
• Ranking items

Logical Function

• Using IF function
• Using AND, OR with IF
• Using Nested IFS (new version 2016)
• Using CHOOSE
• Using IFS (new)

Day 2

Audit and Troubleshoot

• Finding and highlighting all formulas
• Tracing formula relationships
• Tracing a formula error back to it’s source
• Checking and debugging a formula with F9
• Formula that handling error
• Trapping #DIV/0 or #VALUE in formulas

Lookup Part 1

• VLOOKUP function
• Wildcard matches in VLOOKUP
• Handling #N/A in VLOOKUP
• HLOOKUP function

Lookup Part 2

• Looking things up with INDEX
• Using MATCH
• Performing 2-way lookup with INDEX and MATCH
• Using XLOOKUP (new)

Dynamic Named Ranges/ Formulas

• Creating dynamic named ranges
• Using TABLE in Dynamic Named Ranges
• Using OFFSET for dynamic formula
• Handling ranges with errors
• Using INDIRECT function for more powerful data references
• Using AI Functions (new)

Array Function

• With or Without Array Formulas
• Understanding Array Formula Rules
• Simple Examples of Array Formulas
• Solving complicated problem using Array Functions

Exercise and Case Studies # ENQUIRY form

ORGANIZATION DETAILS

PERSON-IN-CHARGE DETAILS