Do you want to learn the advanced Excel tricks used by the power users?

This course is all about learning the advanced techniques you do not get taught.

This course will give you a deep understanding of the most powerful formulas and functions in Excel, including multiple examples to demonstrate what makes them so blooming AWESOME.

It also delves into advanced PivotTable, chart, and custom formatting tricks to take your skills to another planet.

During the course you will learn;

  • The very best functions of Excel including SUMPRODUCT, INDEX, COUNTIF, MOD and more in over 50 examples that others do not teach.

  • Advanced charting techniques to create interactive and visually powerful charts.

  • Deep PivotTable settings and features to have them working exactly as you need.

  • Awesome custom formatting techniques for stunning visuals and learn why it can be better than Conditional Formatting.

  • Mega useful tricks you can perform with day to day Excel features to supercharge your productivity.

Course curriculum

  • 1

    Introduction

    • Introduction to the Course

    • Download your Exercise Files
  • 2

    Neat Tricks to Supercharge your Productivity

    • 10 Paste Special Tricks used by the Pros

    • Excel's Best Kept Secret - and 3 Ways to Use it

    • The Unsung Hero of Excel for Cleaning Messy Data

    • Edit Multiple Sheets at the Same Time with Ease

    • 5 Reasons your Excel Formulas are not Calculating

    • TOP SECRET - How to Make Sheets VERY Hidden

    • My Favourite Excel Shortcuts

    • HOMEWORK: Chapter 2
  • 3

    Using Wildcards with Formulas

    • Using Wildcards with VLOOKUP

    • Using wildcards with COUNTIF and SUMIF

    • Validate that Text is a Specific Number of Characters

    • HOMEWORK: Chapter 3
  • 4

    The Awesome SUMPRODUCT Function

    • The Definitive Guide to the SUMPRODUCT Function

    • Example 1 - Count and Sum the Sales from a Specific Month

    • Example 2 - Count the Occurrences of a Specific Word in a Range

    • Example 3 - Count the Unique Values ONLY in a Range

    • Example 4 - Sum the Top 3 Values

    • Example 5 - Two Way Lookup Formula with SUMPRODUCT

    • HOMEWORK: Chapter 4
  • 5

    Advanced PivotTable Tricks

    • Two Reasons your PivotTable Counts instead of Sums

    • You Must Do this One Thing

    • Grouping Dates and Times

    • Using the GETPIVOTDATA Function to Pull Data from a PivotTable

    • Going Beyond the Sum - 3 More PivotTable Calculations

    • Calculating the Difference to Previous Years/Months/Weeks

    • Create your own Calculated Fields

    • Using Conditional Formatting with PivotTables

    • Show Top Ten Results

    • Five Useful PivotTable Settings

    • The Power of Slicers

    • 7 Slicer Settings you Will Want to Change

    • HOMEWORK: Chapter 5
  • 6

    The Hidden Power of the MOD Function

    • An Introduction to the MOD Function

    • Example 1 - Extracting the Time from a Date-Time Cell

    • Example 2 - Prevent the Entry of Odd Numbers in a Range

    • Example 3 - Calculate Total Balls Bowled from Overs in Cricket

    • Example 4 - Sum Every 3rd Row in a List

    • HOMEWORK: Chapter 6
  • 7

    The Magic of the INDIRECT Function

    • Example 1 - INDIRECT with Named Ranges

    • Example 2 - Referencing other Sheets with INDIRECT

    • Example 3 - Return the Last Value from a Row

    • Example 4 - INDIRECT with VLOOKUP

    • Example 5 - Create Dependent Drop Down Lists

    • HOMEWORK: Chapter 7
  • 8

    The Incredible INDEX Function

    • Example 1 - Using INDEX and MATCH for an Advanced Lookup

    • Example 2 - Create a Picture Lookup

    • Example 3 - Return the Value from the Last Row

    • Example 4 - Create a Dynamic Named Range

    • Example 5 - Sum the Last 6 Values Only

    • Example 6 - Retrieve a Range from a List of Ranges

    • HOMEWORK: Chapter 8
  • 9

    COUNTIF Function - A Mega Useful Function

    • Using Cell Values with COUNTIF

    • Count Values Between Two Dates

    • Prevent Duplicates in a Range

    • Compare Two Lists to Identify Missing Items

    • VLOOKUP for the Last Match in a List

    • Uniquely Rank Items

    • HOMEWORK: Chapter 9
  • 10

    Introducing Custom Number Formatting

    • Combine Text and Numbers in a Cell

    • Show the Weekday of a Date

    • Keep the Leading Zeroes of a Value

    • Display Negative Values in Red

    • Show Zero Values as Blank Cells
  • 11

    Advanced Custom Number Formatting Examples

    • Display Symbols using Custom Formatting to Show Change

    • Conditional Colour with Custom Formatting

    • Setting Thresholds for your Custom Formatting

    • HOMEWORK: Chapter 11
  • 12

    Advanced Chart Tricks for Dynamic Interactive Charts

    • The Easiest Way to Create a Dynamic Data Range

    • Drop Down Menu to Select a Chart

    • Get Creative by Building a Dynamic Chart Title

    • Highlight the Max and Min Values Differently

    • Create a Scrollable Chart

    • Use Cell Values for Data Labels to Show what YOU want

    • Rolling Chart for the Last 6 Months Only

    • Visualise Targets v's Actuals

    • Using Check boxes to Show/Hide Chart Data

    • HOMEWORK: Chapter 12

Your Instructor

Why learn from me?

I have been training business all around the world on how to get the most out of Excel for over 20 years.

 Learning and teaching Excel is my passion. I am a full-time Excel trainer and consultant, so every day I am in the thick of the action teaching others to master Excel.

I set up the Computergaga blog and YouTube channel 8+ years ago where I am lucky enough to have taught millions of people across the globe to increase their skills and overcome everyday real world Excel problems.

You can download the files I use in the lessons to follow along and receive 1 on 1 instructor support every step of the way.