USEFUL EXCEL FORMULAS AND FUNCTIONS

Useful Excel Formulas and Functions 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.

COURSE OBJECTIVES

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 CONTENTS

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