EXCEL TRAINING COURSES

EXCEL ADVANCED

Target Audience

This course can either be treated as a follow-on from our Excel Intermediate course or will be suited to someone who is at intermediate level (see Intermediate Outline) and is wishing to take the next step to Excel proficiency.

The course encompasses Advanced Data Analysis using Pivot Tables/ Charts and Power Pivot. Learn how to properly Validate and Protect your data as well as combine data from different sources using Data Consolidate and Linking. Advanced formulas such as Index/Match and SumProduct get a stage. This course introduces Excel automation using macros and more.

The outline below is for contemporary versions of Excel (2013, 2016, 2019 and 365). We do run courses on earlier versions when required. Please mention this when booking.

Prerequisites

A sound, intermediate knowledge of Excel and familiarity with the Windows operating system are prerequisites for this course. If in doubt, please refer to our Excel Intermediate Outline.

Duration:

1 Day

Price:

Prices start from $300 per day but may vary based on location.

Course Dates - CLASSROOM ( Valla Training Centre ):

Mar 18, 2021 ENQUIRE

 

** Other dates may be available **
If the dates above are not suitable, please contact us:

Schedule a Date


Course Dates - ONLINE-LIVE:

Dates are available for this course

Please Contact Us to schedule one that works for you

Schedule a Date

 

Course Files:

Download Course Files

 

COURSE OUTLINE

Protecting Data

  • Understanding Data Protection
  • Providing Total Access to Cells
  • Protecting a Worksheet
  • Working With a Protected Worksheet
  • Disabling Worksheet Protection
  • Providing Restricted Access to Cells
  • Password Protecting a Workbook
  • Opening a Password Protected Workbook
  • Removing a Password From a Workbook

Importing and Exporting

  • Understanding Data Importing
  • Importing Text Files of Various Formats
  • Importing Access Data
  • Working With Connected Data
  • Unlinking Connections
  • Exporting to Microsoft Word
  • Exporting Data as Text

Data Linking

  • Understanding Data Linking
  • Linking Between Worksheets
  • Linking Between Workbooks
  • Updating Links Between Workbooks

Grouping and Outlining

  • Understanding Grouping and Outlining
  • Creating an Automatic Outline
  • Working With an Outline
  • Creating a Manual Group
  • Grouping by Columns

Data Consolidation

  • Understanding Data Consolidation
  • Consolidating With Identical Layouts
  • Creating a Linked Consolidation
  • Consolidating From Different Layouts
  • Consolidating Data Using the SUM Function

Data Tables and Arrays

  • Array Formulas vs. Standard Formulas
  • Understanding Data Tables and What-If Models
  • Creating a One-Variable Table
  • Creating a Two-Variable Data Table
  • Using Data Tables

Pivot Tables

  • Understanding PivotTables
  • Recommended PivotTables
  • Creating Your Own PivotTable
  • Defining the PivotTable Structure
  • Filtering a PivotTable
  • Clearing a Report Filter
  • Switching PivotTable Fields
  • Formatting a PivotTable
  • Understanding Slicers
  • Creating Slicers

 

Pivot Table Features

  • Using Compound Fields
  • Counting in a PivotTable
  • Formatting PivotTable Values
  • Working With PivotTable Grand Totals
  • Working With PivotTable Subtotals
  • Finding the Percentage of Total
  • Finding the Difference From
  • Grouping in PivotTable Reports
  • Creating Running Totals
  • Creating Calculated Fields
  • Providing Custom Names
  • Creating Calculated Items
  • PivotTable Options
  • Sorting in a PivotTable

Pivot Charts

  • Inserting a PivotChart
  • Defining the PivotChart Structure
  • Changing the PivotChart Type
  • Using the PivotChart Filter Field Buttons
  • Moving PivotCharts to Chart Sheets

Introduction to Power Pivot

  • Why use Power Pivot?
  • Installing the Power Pivot Add-In
  • Understanding Relational Data (Data Modelling)
  • Connecting to Data to the Data Model
  • Linking Tables in the Data Model
  • Creating a Power Pivot Table

Validating Data

  • Understanding Data Validation
  • Creating a Number Range Validation
  • Testing a Validation
  • Creating an Input Message
  • Creating an Error Message
  • Creating a Drop Down List
  • Using Formulas as Validation Criteria
  • Circling Invalid Data
  • Removing Invalid Circles

Introduction to Macros

  • Understanding Excel Macros
  • Setting Macro Security
  • Saving a Document as Macro Enabled
  • Recording a Simple Macro
  • Running a Recorded Macro
  • Relative Cell References
  • Running a Macro With Relative References
  • Viewing a Macro
  • Editing a Macro
  • Assigning a Macro to the Toolbar
  • Running a Macro From the Toolbar
  • Assigning a Macro to the Ribbon
  • Assigning a Keyboard Shortcut to a Macro
  • Deleting a Macro
  • Copying a Macro