Advanced Excel – Beginner to Ninja level (includes Charts)

Course Curriculum

Chapter 1: Level 1 (A) – Warm Up, Case Study Files + eBook (zipped folder)

Lecture 1: Introduction + Downloadable files

Lecture 2: Warm-up – Key Shortcuts

Lecture 3: Formula Tricks – SUM, AUTOSUM, MAX, MIN, AVERAGE

Lecture 4: Formula Tricks – SUMPRODUCT, POWER, ROUND, MROUND

Lecture 5: Formula Tricks – COUNT vs. COUNTA

Lecture 6: Formatting Tricks: Table Concept, Using Format Painter uninterruptedly etc.

Lecture 7: Auto fill options – Variations & Settings

Lecture 8: Paste Special – Value, Transpose; Formula TRANSPOSE()

Lecture 9: Range Selection; GO TO – Special

Chapter 2: Level 1 (B) – Sort, Filter, Dates, Dashboard tricks

Lecture 1: Multi-level Sort, Custom Sort, Color Sort

Lecture 2: Filter & SUBTOTAL() formula

Lecture 3: Advanced Filter I (Differential Filter Criteria)

Lecture 4: Advanced Filter II (Differential Filter Criteria)

Lecture 5: Cell Referencing using $ – Introduction

Lecture 6: Cell Referencing using $ – Practice #1

Lecture 7: Cell Referencing using $ – Practice #2

Lecture 8: Cell Referencing using $ – Practice #3

Lecture 9: Cell Referencing using $ – Special Trick

Lecture 10: Date Concepts and Format

Lecture 11: Date Formulas-DAY, MONTH, YEAR, DATE, EDATE, EOMONTH, TEXT, WORKDAY, NETWORKDAYS

Lecture 12: Date Cleaning techniques using Text-to-Columns

Lecture 13: Dashboard I: Grouping, Data Validation (List), Cell-Range Naming

Lecture 14: Dashboard II: Form Controls Buttons

Lecture 15: Dashboard III: Hide/Unhide Columns-Rows, Freeze Panes

Chapter 3: Level 1 (C) – Lookups

Lecture 1: VLOOKUP Introduction – Where/Why/How to use

Lecture 2: VLOOKUP – Pre-requisites and Common Pitfalls

Lecture 3: VLOOKUP – True (1) vs. False (0)

Lecture 4: VLOOKUP Practice with IFERROR

Lecture 5: HLOOKUP

Lecture 6: Using MATCH with VLOOKUP/HLOOKUP – 2×2 matrix lookup

Lecture 7: VLOOKUP with MATCH – Practice #1

Lecture 8: VLOOKUP with MATCH – Practice #2

Lecture 9: HLOOKUP with MATCH – Practice #3

Lecture 10: SUMIFS – Introduction

Lecture 11: SUMIF vs. SUMIFS, AVERAGEIFS, COUNTIFS

Lecture 12: COUNTIFS for duplicate detection; Remove Duplicate

Chapter 4: Level 2 (A) – Reverse Lookup & Pivot Table

Lecture 1: Data Validation (Numbers, Dates, Text length)

Lecture 2: INDEX with MATCH – Reverse 2-way Lookup

Lecture 3: INDEX with MATCH – Practice #1

Lecture 4: INDEX with MATCH – Version 2

Lecture 5: Pivot Table #1 (Payroll)

Lecture 6: Pivot Table #2 (Payroll)

Lecture 7: Pivot Table #3 (Payroll)

Lecture 8: Pivot Table – Practice (Inventory)

Lecture 9: SubTotal – Automatic row-wise subtotal #1

Lecture 10: SubTotal – Automatic row-wise subtotal #2

Lecture 11: SubTotal – Automatic row-wise subtotal #3

Chapter 5: Level 2 (B) – Data Cleansing functions

Lecture 1: Using CONCATENATE, & – to join data strings

Lecture 2: Text-to-Columns – Delimited

Lecture 3: Text-to-Columns – Fixed Width

Lecture 4: Text-to-Columns – Tricks

Lecture 5: Find & Replace – Advanced

Lecture 6: Text Formulas I – UPPER, PROPER, LOWER, TRIM, T, N, REPT

Lecture 7: Text Formulas II – LEFT-RIGHT-MID, LEN, SEARCH

Lecture 8: Text Formulas III – SEARCH vs. FIND

Lecture 9: Text Formulas IV – REPLACE, SUBSTITUTE

Chapter 6: Level 2 (C) – Logical Formulas and Conditional Formatting

Lecture 1: Logical formulas I – IF, Nested IFs

Lecture 2: Logical formulas II – AND, OR with IF

Lecture 3: Logical formulas III – more case studies

Lecture 4: ISERROR, ISBLANK, ISNUMBER, ISTEXT, IFERROR

Lecture 5: Conditional Formatting I (Blanks, Errors, Values, Duplicates)

Lecture 6: Conditional Formatting II (Formula-based)

Lecture 7: Conditional Formatting III (Data Bars, Color Scales, Icon Sets)

Chapter 7: Level 2 (D) – What If Analysis

Lecture 1: What IF Analysis – Scenario Manager

Lecture 2: What IF Analysis – Goal Seek

Lecture 3: What IF Analysis – Data Tables

Lecture 4: What IF Analysis – Using Form Control Buttons

Chapter 8: Level 3 – 3D Data Consolidation, Print tricks, Password, Formula Audit etc

Lecture 1: Tables – Concept and Applications

Lecture 2: 3-D Data Consolidation from same/different Workbooks #1

Lecture 3: 3-D Data Consolidation from same/different Workbooks #2

Lecture 4: Formula Auditing techniques

Lecture 5: File Security & Password Protection #1

Lecture 6: File Security & Password Protection #2

Lecture 7: Printing

Lecture 8: Comments – Picture and Text

Lecture 9: Split Windows, Viewing multiple Windows

Lecture 10: Hyperlinking

Chapter 9: Level 4 – Charts

Lecture 1: Charts – Basic Concepts and Elements

Lecture 2: Basic Charts – Bar, Column, Pie

Lecture 3: Special Charts: Thermometer Charts

Lecture 4: Special Charts: Multi-axis charts

Lecture 5: Special Charts: Exploded Pie charts

Lecture 6: Chart tips-n-tricks #1

Lecture 7: Chart tips-n-tricks #2

Chapter 10: Level 5 – Macros Basics

Lecture 1: Macros – Overview, Developer tab, Settings

Lecture 2: Macros – Recording, Running; Using Buttons to run Macros

Chapter 11: Level 6 – INDIRECT, OFFSET, MATCH

Lecture 1: INDIRECT()

Lecture 2: OFFSET() with MATCH()

Chapter 12: Bonus – Advanced Tricks