MICROSOFT EXCEL ADVANCED

  • PURPOSE

  • COURSE CONTENT

  • DELIVERY

  • ACCREDITATION

SpecCon’s Advanced Microsoft Excel course is called ‘advanced’ for a reason; it’s not for amateurs! It is jam-packed with over 60 different functions and formulas. It teaches you to combine formulas and enables to take these to a new level. The course enables you to learn and discover further functions for yourself.

  • Unit 1: Formula basics
  1. Formula basics
  2. Types of errors
  3. Fixed referencing
  • Unit 2: Logical functions
  1. Logical test
  2. IF function
  3. OR function
  4. AND function
  5. IFERROR function
  • Unit 3: Lookup function
  1. Vlookup function
  2.  MATCH formula
  3. Using the MATCH formula to populate the column index number
  4. INDEX
  5. OFFSET
  6. Using Vlookup to fix data with mapping tables
  7. Using a True or 1 in the Vlookup
  • Unit 4: Reporting functions
  1. ADDRESS formula
  2. Pivot tables
  3. Counting using pivot tables to generate lists
  4. Updating values in a pivot table and adding multiple values
  5. Using calculated fields in pivot tables
  6. Changing the look and feel in pivot tables
  7. Splitting pivot tables into multiple sheets and grouping data
  8. Retrieving the field list when lost
  9. Creating dashboards from pivot tables
  10. SUMIFS formula
  11. Recording macros and using macros to
    improve reporting
  12. INDIRECT formula
  • Unit 5: Text formulas
  1. FIND formula
  2. CONCATENATE formula
  3. LEFT, RIGHT and MID functions
  4. TRIM functions
  5. PROPER/LOWER formulas
  6. UPPER formula
  7. SUBSTITUTE formula
  8. VALUE formula
  9. LEN formula
  • Unit 6: Dates
  1. Understanding dates and dynamic dates
  2. Converting text to dates with formulas
  3. NETWORKINGDAYS formula
  4. END OF MONTH formula
  5. Working with time
  6. TEXT formula and dates
  • Unit 7: Financial functions
  1. PMT function
  2. IPMT function
  3. FV function
  4. NPV function
  5. IRR function 
  • Unit 8: Other
  1. Using the find and replace option to edit formulas
  2. Using conditional formatting to do reconciliations and finding duplicates 
  3. COUNTIF formula and using COUNTIF to number data
  4. Goalseek
  • Unit 9: Google sheets
  1. Google sheets 
  2. More Google sheets 
  3. Import ranges 
  4. Google forms

 

 

  •  

 

Duration: 1 Day

Delivery: Classroom/Online

 

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.