PURPOSE

Our Microsoft Excel Advanced Training online course is designed to transform you into an Excel power user. Whether you’re a professional seeking to streamline your work or a student aiming to excel in your academic pursuits, this course equips you with the advanced skills and knowledge you need.

COURSE CONTENT

Unit 1: Formula basics

  • Formula basics
  • Types of errors
  • Fixed referencing
 

Unit 2: Logical functions

  • Logical test
  • IF function
  • OR function
  • AND function
  • IFERROR function
 

Unit 3: Lookup function

  • Vlookup function
  •  MATCH formula
  • Using the MATCH formula to populate the column index number
  • INDEX
  • OFFSET
  • Using Vlookup to fix data with mapping tables
  • Using a True or 1 in the Vlookup
 

Unit 4: Reporting functions

  • ADDRESS formula
  • Pivot tables
  • Counting using pivot tables to generate lists
  • Updating values in a pivot table and adding multiple values
  • Using calculated fields in pivot tables
  • Changing the look and feel in pivot tables
  • Splitting pivot tables into multiple sheets and grouping data
  • Retrieving the field list when lost
  • Creating dashboards from pivot tables
  • SUMIFS formula
  • Recording macros and using macros to
    improve reporting
  • INDIRECT formula
 

Unit 5: Text formulas

  • FIND formula
  • CONCATENATE formula
  • LEFT, RIGHT and MID functions
  • TRIM functions
  • PROPER/LOWER formulas
  • UPPER formula
  • SUBSTITUTE formula
  • VALUE formula
  • LEN formula
 

Unit 6: Dates

  • Understanding dates and dynamic dates
  • Converting text to dates with formulas
  • NETWORKINGDAYS formula
  • END OF MONTH formula
  • Working with time
  • TEXT formula and dates
 

Unit 7: Financial functions

  • PMT function
  • IPMT function
  • FV function
  • NPV function
  • IRR function
 

Unit 8: Other

  • Using the find and replace option to edit formulas
  • Using conditional formatting to do reconciliations and finding duplicates 
  • COUNTIF formula and using COUNTIF to number data
  • Goal seek
 

Unit 9: Google sheets

  • Google sheets 
  • More Google sheets 
  • Import ranges 
  • Google forms

DELIVERY

Duration: 1 Day

Delivery: Classroom/Online

ACCREDITATION

5 Day Courses: Participants will receive a SpecCon Holdings certificate upon completion of all courses.

1 Day Courses: Participants will receive a SpecCon Holdings certificate upon completion of this course.

SHORT COURSES
Scroll to Top