Course Syllabus: Advanced Excel Course:

Module 1: Automate your spreadsheet analysis by using the power of combining these functions.

VLOOKUP
VLOOKUP + Helper columns,
INDEX and MATCH
SUMPRODUCT
SUMIF & SUMIFS
COUNTIF & COUNTIFS,
OFFSET & INDIRECT FunctionsCase studies:
We will look at real life examples and models using these techniques.

Module 2: Automate Decision making processes:

IF and nested IF Functions
AND, OR, NOT Functions

Module 3: How to build complex powerful Formulas with an easy step by step method.
Module 4: Cleaning your data:

Text and Date Functions with examples of combining Text functions.

Module 5: Data Analysis: Use techniques to analyze your data instantly.

Advanced Filters
Using List Features
Dynamic Lists
The new Sumifs and Countifs Functions

Pivot Tables

Using the Pivot Table Wizard
Changing the pivot table layout
Formatting
Grouping items
Inserting calculated fields
Pivot Table Options
Display and hide data in fields
Lay out reports directly on worksheet
Pivot Charts
Pivot Tables Slicers
20 Pivot Tables Recipes for powerful reports
Pivot Tables Macros
Creatng Independent Pivot table

Module 6: What If Analysis

Scenarios
Custom Views
Reports
Goal Seek
Data Tables

Module 7: Macros

Purpose of Macros
Recording macros
Where to save macros
Absolute and relative record
Running macros:
Custom buttons, menu items, keyboard shortcuts

Case Studies: Examination of Macros to save time and automate your work.