Excel: Using Advance Formulas and Functions

Excel: Using Advance Formulas and Functions

Learn to use advance formulas and functions within Excel to improve efficiency and productivity.

Prerequisites

  • Knowledge of writing basic formulas and fucntion in Excel or completed Excel Intermediate course.

Learning Objectives

By the end of the course, you should be able to:

  • Use logical, text and date functions
  • Use Lookup and reference Functions
  • Create nested conditional formulas
  • Use the What If analysis tools
  • Check Cell Values

Technical Requirements

  • Access to Microsoft Excel.

Topics

  • Fundamentals of Formula & Functions
    • Using Basic Functions (Sum, Average, Min, Max and Count)
  • Working with Auditing and Error Checking Tools
    • Using Absolute Cell Reference
    • Using Relative Cell Reference
    • Using Named Range
  • Using Statistical functions (Median, Mode, Large, Small, Rank, Frequency)
  • Working with logical functions
    • Using IF Function
    • Using AND and OR
    • Using SUMIF function
    • Using Count, Countif, Counifs functions
  • Working for Lookup Functions
    • Using VLookup and HLookup functions
    • Using Index and Match Function
  • Working with date and time functions
    • Format data and time
    • Calculating date and time difference
    • Using Date and time functions
    • Formatting date using Year, Month, Day and Text functions.
    • Working with Text Functions
  • Using Function to Split and merge text
    • Using Trim, Upper, Lower, Left, Right and Mid functions
    • Using Concatenate Function
  • Working with Auditing and Error Checking Tools
    • Using ISBlank, IsNumber, IsText and IsError functions
  • Working with Data Analysis Tools
    • Creating Scenarios
    • Using What If analysis
    • Using Goal Seek
    • Using Solver
    • Generating report with alternate solutions

Schedule