Sign Up

Advanced Excel
Click here to download
Click here to download brochure

Excel has become very much more than just a simple spreadsheet. Although its roots are still in number management, today Excel’s uses cover a wide gamut – from simple data entry to complicated financial models and analysis. This course adds on to the participants’ knowledge of Microsoft Excel. Formulae form an integral part of the course and participants will learn how to use complex functions to model their data and to generate a variety of answers and reports.

Course Outline

Working with Ranges

  • Cells & Values, Naming Cells & Functions, Naming Relative & Absolute Values (Columns & Rows), Effective Utilisation of Names

20 per Special OfferDatabase/List Operations

  • Introduction to Excel Databases, Using Lists, Creating a list (Modifying list ranges, Removing Duplicates)
  • Filtering: Using the AutoFilter, Advanced filters, Using filter criteria, Using comparison operators
  • Database Functions, Create & edit database functions, Summarize Data using Subtotals, Grouping & Outlining Data

Data Validation

Data Consolidation

  • 3D Formulae: Using Multi-Dimension Worksheets & Workbooks
  • Consolidating Data: By position, By category

Auditing Worksheets

  • Tracking Errors in Excel, Understanding Precedents & Dependencies, Viewing & Hiding Tracer arrows, Resolving Circular References, Using the Watch Window, Evaluate Formulas

Advanced Functions

  • Nesting of Functions: Nesting IF conditions, Boolean criteria, Conditional sum, count & average
  • Lookups & Referencing: VLOOKUP, HLOOKUP, Combining Functions

Data Analysis

  • What-If Analysis, One-input data tables, Two-input data tables, Using Goal Seek, Running Goal Seek, Loading & using Solver add-in, Analysing in Solver, Conditions & permutations
  • Using the Scenario Manager: Saving & displaying options, Creating scenario summary, Merging scenarios

Pivot Tables

  • Understanding Pivot Tables, Building a Pivot Table, Pivot Table Formatting, Creating Pivot Charts, Printing a Pivot Report

Collaborating

  • Sharing Workbooks, Creating shared workbooks, Tracking changes, Showing history of changes, Merging Workbooks


Methodologies

A good foundation in Excel is essential for this course. Participants should be familiar with the program and be able to write basic formulae. Having attended an essentials course is advantageous.

A combination of lectures, explicit teaching, practical demonstrations and peer teaching is applied for this course. Experiential learning approach is adopted where the learners will

A combination of lectures, explicit teaching, practical demonstrations and peer teaching is applied for this course. Experiential learning approach is adopted where the learners will