Excel Advanced Course
R 599 Incl. VAT (3 Months Access)
Select the "Pay & Enrol" Button below for Online or Direct Deposit Payment Options
or Contact us for Group Enrolments


4.8 (325 Reviews)


Course Outline

From Goal Seek to Advanced Formulas to Pivot Tables and Recording a Macro, the objective of this course is to expose you to the many powerful Advanced Features that Excel has to offer, as well as cover some key principles that ensure good practice when working with data.

  • 1

    Introduction

    • 01 - Welcome to our Excel Advanced Course!

    • 02 - How did you find out about this course?

    • 03 - How to Get Started

    • 04 - Download the Excel Advanced Work Book

  • 2

    Section 1 - Key Essentials

    • 01 - Key Data Types

    • 02 - The Influence of Regional Settings

    • 03 - Auto Formats and Number Facades

  • 3

    Section 2 - Spreadsheet Integrity

    • 01 - Quick Check of Numerical Content

    • 02.1 - Show Formula

    • 02.2 - Trace Precedents and Dependents

    • 02.3 - Formula Error Checking

    • 03 - Blanks vs. Zeros

    • 04 - Excel's Golden Rule

    • 05 - Excel's 2nd Golden Rule

    • 06 - Running Totals

    • 07 - Audit Trails

    • 08 - Data Ranges

  • 4

    Section 3 - Cell References

    • 01 - Linking Data Across Sheets

    • 02 - Relative, Absolute and Mixed Cell References

  • 5

    Section 4 - Goal Seek

    • 01 - Goal Seek Intro

    • 02 - Goal Seek Examples

  • 6

    Section 5 - CSV Import

    • Download Two CSV Files for this Lesson

    • 01 - The Issues with Double Clicking

    • 02 - Enabling the Legacy Wizard

    • 03 - Legacy Wizard Import

    • 04 - The Resultant Connection

  • 7

    Section 6 - Merging & Splitting Data

    • 01 - Text to Columns - Delimiter

    • 02 - Text to Columns - Fixed Width

    • 03 - Merging using Formula

    • 04 - Merging using FLASHFILL

  • 8

    Section 7 - Text Formulas

    • 01 - Text Formula Overview - UPPER, LOWER, PROPER, LEFT, RIGHT, MID & CONCAT

    • 02 - Nested Formula using Helper Columns

    • 03 - Nested Formula Challenge - Client Description

    • 04 - Nested Formula in one go - DOB from SA ID Number

    • 05 - Data Cleanup using the Trim Function

    • 06 - Moving Formula Columns & Loading new Data

    • 07 - Fixing ID Number Formats using Text-to-Columns

  • 9

    Section 8 - Date Formulas

    • 01 - Date Formula Introduction

    • 02 - Date Component Functions - YEAR, MONTH, DAY, WEEKDAY, WEEKNUM

    • 03 - Date Projections - EDATE, EOMONTH, TODAY

    • 04 - Days between 2 Dates - YEARFRAC, NETWORKDAYS

    • 05 - Date Formula Example - Age Calculation from D.O.B.

    • 06 - Date Formula Example Extension

  • 10

    Section 9 - Logical Formulas

    • 01 - Logical Formula - IF, AND, OR

    • 02 - Logical Formula E.g 1 - Sales Rep Commission Calc.

    • 03 - Logical Formula E.g. 2 - IFERROR

  • 11

    Section 10 - Lookup Formulas

    • 01 - VLOOKUP - Using Ranges

    • 02 - VLOOKUP - Using Columns

    • 03 - VLOOKUP - Using Named Ranges

    • 04 - VLOOKUP - Using Tables

    • 05 - INDEX - MATCH - the alternative to VLOOKUP

    • 06 - XLOOKUP

  • 12

    Section 11 - Maths Formulas

    • 01 - Maths Formula Overview - SUM, COUNT, COUNTA, SUMIF(S), COUNTIF(S)

    • 02 - SUMIF v SUMIFS

    • 03 - SUMIF & COUNTIF example

    • 04 - SUMIFS example

    • 05 - COUNTIFS example

    • 06 - Using Pivot Tables instead

    • 07 - Maths FUNCTIONS v Pivot Tables

    • 08 - One last example

  • 13

    Section 12 - Data Validation & Worksheet Protection

    • 01 - Data Validation Basics

    • 02 - Lookup Lists and In Cell Drop downs

    • 03 - Worksheet Protection

    • 04 - The Copy Paste Issue

  • 14

    Section 13 - Pivot Tables & Pivot Charts

    • 01 - Pivot Table Data Structure

    • 02 - Creating a Pivot Table & Chart

    • 03 - Pivot Table Layout Options

    • 04 - Filters

    • 05 - Grouping

    • 06 - Calculated Fields

    • 07 - Multi Pivot Dashboard with Slicers

    • 08 - Adding Pivot Table Data

  • 15

    Section 14 - Recording a Macro for Repeatable Tasks

    • 01 - Macros Introduction

    • 02 - Recording a Macro

    • 03 - Macro E.g. 1 - Bank Statement Cleanup

    • 04 - Running a Macro

    • 05 - Saving a Macro Enabled Workbook

    • 06 - Macro E.g. 2 - Bank Statement Cleanup 2

  • 16

    Section 15 - Course Evaluation

    • 01 - Short Feedback Questionnaire

Course Reviews

"One word Invaluable . . . I will go and leave proper comments on LinkedIn, and promote heavily on all social pages accessed to me . . . Well Done with all three of these courses. A definite 5 Star rating. Thank you Mark!"
Eugene


"This course is fantastic. I did the first 2 and learned a few pointers but the Advanced one was wow. Thank you so much for the way you explain and show all the points."
Lezelle - Global Learning Services


"Very well structured , course material is excellent"
Vishal - Maritzburg College


"Professional!"
George - Castle Lead Works Zambia Limited


"Thanks for a great course!"
Nadia - Safire Insurance Company Limited


"Thanks for such an in-depth training."
Tracy - Safire Insurance Company Limited


"I enjoyed and learned a lot. Thank you."
Itumeleng


"I have learned so much in this course and I believe I will be more productive at work Thanks again for this great opportunity"
Sthembiso - Barloworld


"Brilliant, Informative & Friendly"
Callum - Student


"I really enjoyed the way it was presented and feel that I have learned a lot of helpful tricks in Excel which will certainly help me in my job. Thank you very much"
Gerry


"Well presented, easy to follow. Nice to stop and pause, go back to grasp a concept there and then. Has enabled me to expand my skills in excel so I can work quicker and smarter."
Carl


"This course is very helpful and interesting to learn as a beginner also. There are so many tips that would be useful. Thank you, Mark, and Looking Forward to the next one ."
Swapna