Microsoft Excel Advanced

Who should attend?

This course is suitable for anyone who has attended the Intermediate course or has a good knowledge of Excel functions.

Course Aims & Objectives

The purpose of this course is to give delegates the confidence in using the essential features of Excel and to build on what was learnt in the Basic and Intermediate courses.

By the end of this course delegates will be able to:

  • Work with Pivot Tables
  • Understand advanced formulas
  • Record, edit and delete Macros
  • Analyse data effectively

Course Outline

  • Importing Data
    • From text
    • From the web
  • Cleaning and Organising Data
    • Various data cleansing tasks
    • Using text functions
    • Flash Fill (2013 only)
  • Validating data entry
    • Creating drop down list
    • Creating a dependent drop down list
    • Validating date and number entries
    • Creating you own error alerts
  • Advanced Formulas for Analysing Data
    • SUMIFS, COUNTIFS
    • SUBTOTALS
    • AGGREGATE
    • INDEX and MATCH
  • Formatting Data as Table
    • Benefits of formatting a range as a table
    • Using structured references in formulas
  • Pivot Tables
    • Creating Pivot Tables and Pivot Charts
    • Sorting, filtering and grouping fields
    • Changing the aggregate function
    • Changing Pivot Table settings
    • Using Slicers (2010+)
  • Macros
    • Recording, Playing and Deleting
    • Basic Editing of Visual Basic Code

Certification

Certificates of attendance will be awarded to delegates upon completion of the course.

Duration

1 day

Location

We can offer courses on company premises for a maximum of 8 delegates or upcoming open courses in your local area.

View as PDF

Share this Course