It is time to master Excel. If you are a beginner and want to develop your Excel skills or have the desire to be an Excel guru, you have come to the right place.

This course starts with the basics of formulas and managing spreadsheets and quickly moves to advanced Excel techniques required for creating powerful, dynamic and visually appealing spreadsheets.

This course is perfect if you;

  • Use Excel everyday but want to improve your all-round skills and become the office expert

  • Have a basic knowledge but want to take the plunge and develop your Excel muscles

  • Learn all the essential skills in Excel to help get a new job or promotion

The course has been designed as a one-stop shop for all of the essential skills of Excel. It is very comprehensive and you can come back again and again to re-visit material.

The course will be consistently updated with the latest features and techniques of Excel. With big plans for Excel and amazing features on the horizon - you can be sure to find lessons added to this course so that you stay ahead of the game.

I teach in a hands-on way with unique examples demonstrating all of the essential features of Excel. You will not only understand HOW these Excel tools and formulas work, but see examples of WHY they are so awesome.

By the end of the course you will be a confident user of Excel with the skills to progress your career further, or become the office Excel guru.

During the course you will learn;

  • To use the power of PivotTables to summarise large amounts of data and produce dynamic reports in minutes.

  • Be confident in using over 30 of the most important Excel functions including INDEX, VLOOKUP, MID, IF, MATCH and COUNTIF.

  • Create charts in a flash that visualize data effectively.

  • Conditional Formatting to highlight data comparisons, progress and issues.

  • Utilise the awesomeness of Power Query to import, shape and transform data.

  • Clean up messy data with formulas, Flash Fill and other Excel features.

  • Understand the data model in Excel and PowerPivot.

  • To use macros to automate time-consuming tasks.

  • And much much more.

Course curriculum

  • 1

    Introduction

    • Introduction to the Ultimate Excel Course

    • Download your Exercise Files

  • 2

    Working with Large Worksheets

    • Keeping your Headings Visible as you Scroll

    • Splitting a Worksheet to Reference Two Different Areas

    • Hiding and Unhiding Columns

    • Sorting a Large List - Including a Super Shortcut

    • Filtering a List - One of the Most Useful Excel Skills you will EVER Need

  • 3

    Five Essential Tricks for Printing Spreadsheets

    • Scaling a Print to Fit onto Fewer Pages

    • Repeating your Headers Across Multiple Pages

    • Printing a Selected Range and Setting a Print Area

    • Aligning a Print on a Page

    • Using Page Breaks to Logically Break Up a Print

  • 4

    Getting Started with Excel Formulas

    • Introduction to Formulas - Writing your First Excel Formulas

    • The Order of Calculation - BODMAS

    • Calculating Percentages

    • Unleash the Power of Excel Functions

    • Referencing other Sheets and Workbooks

    • Calculating Date Difference including Working Days Only

    • Understand Absolute Cell Addresses

    • The COUNTIF Function - An Incredibly Useful Function

    • Exercise 1: Writing Formulas

    • Exercise 2: Absolute References

    • Exercise 3: Using Functions

    • Exercise 4: Calculating Percentage Increase/Decrease

  • 5

    Logical Functions: The Decision Making Formulas of Excel

    • IF Function 1 - Testing if a Value is Larger than a Specific Value

    • IF Function 2 - Testing if a Cell Contains Certain Text

    • IF Function 3 - Tracking Due Dates by Testing if they are Overdue

    • Using Multiple IF Functions - Nested IFs

    • AND and OR Functions for Testing Multiple Conditions

    • The IFS Function - No More Nested IFs

    • The SWITCH Function - Another Alternative to Complex Nested IFs

    • Exercise: Logical Functions

  • 6

    Conditional Formatting - Make your Data Come to Life

    • Create your First Conditional Formatting Rules

    • Managing Multiple Rules and Dealing with Conflicts

    • Conditional Formatting to Highlight Expired and Due Dates

    • Applying Data Bars to Compare Values or Show Progress

    • Create a Heat Map using Colour Scales

    • Using Icon Sets to Show Monthly Comparisons

    • Applying Conditional Formatting to the Entire Row

    • Testing Multiple Conditions with the AND Function

    • Exercise: Working with Conditional Formatting

  • 7

    Lookup Functions - The Powerful VLOOKUP Function and beyond

    • The VLOOKUP Function Explained

    • Using VLOOKUP for a Range Lookup

    • Advanced VLOOKUP with a Dynamic Column Index Number

    • Handling Error Messages with Lookup Formulas

    • A Versatile Lookup Formula - The INDEX and MATCH Combo

    • Two Way Lookup using INDEX and MATCH

    • Exercise: Using the VLOOKUP Function

  • 8

    Validating and Protecting Excel Data

    • Validate the Number of Characters

    • Create Date Validation Rules

    • Drop Down Lists for Easy Data Entry

    • Dependent Lists to Break up Large Lists

    • Create Custom Data Validation Messages

    • Encrypt a File from Unauthorised Access

    • Protect the Structure of a Workbook

    • Protect the Data on a Worksheet

    • Exercise: Validating Data Entry

  • 9

    Advanced Formulas for Analysing Data

    • Using SUMIF, COUNTIF and AVERAGEIF

    • Summing the Values between Two Dates

    • An Advanced Sum Formula with SUMPRODUCT

    • Summing Values by a Specific Weekday - SUMPRODUCT Example 2

    • The Secret AGGREGATE Function

    • An Advanced Array Example of AGGREGATE

    • Exercise: Analysing Data with Formulas

  • 10

    Charts - Simple and Effective Data Presentation

    • Creating your First Charts

    • Adding and Removing Chart Elements

    • Formatting Charts

    • Modify the Chart Axis

    • Save Time with Chart Templates

    • Combo Charts - Two Chart Types in One Chart

    • Chart Filters

    • Interactive Charts – Change Chart Data with a Drop Down List

    • Using Cell Values for Chart Labels

    • Conditional Formatting with Charts

    • Using Sparklines for Quick Data Insights

    • Exercise: Working with Charts

  • 11

    Manipulating Text in Excel

    • Splitting Text across Multiple Columns

    • The CONCAT and CONCATENATE Functions

    • The TEXTJOIN Function

    • Extracting Text from a cell

    • Using the MID and FIND Functions Together

    • The VALUE Function

    • The SUBSTITUTE Function to Fix a Common Dependent List Problem

    • The Awesome Flash Fill Tool

    • Exercise: Text to Columns

    • Exercise: Extracting Data with Text Functions

  • 12

    Formatting your Data Ranges as Tables for Easier Management

    • Formatting your Data Range as a Table

    • Exploring the Benefits of using Tables

    • Creating your own Table Style

    • Formulas using a Tables Structured References

    • Converting a Table back to a Normal Range

    • Exercise: Managing Data with Tables

  • 13

    Master PivotTables for Powerful Analysis and Reporting

    • Creating a PivotTable

    • Changing the Values Function

    • Formatting Values the Efficient Way

    • Creating a PivotTable Style

    • Sorting PivotTable Fields

    • Grouping Data in a PivotTable

    • Showing Values as a Percentage, Difference From and Ranking

    • Conditional Formatting with PivotTables

    • Filtering PivotTable Data

    • Refreshing a PivotTable

    • Creating a PivotChart

    • Using Slicers for Interactive PivotTables

    • Using the Timeline Slicer

    • Exercise: Using PivotTables

    • Exercise 2: Using PivotTables

  • 14

    Getting and Transforming Data with Power Query

    • What is Power Query?

    • Transforming Messy Data on a Worksheet - First Example of Power Query Magic

    • The Incredible Unpivot Feature of Power Query - Real Game Changer

    • Working with International Dates and Number Formats

    • Merge Queries - A VLOOKUP Alternative

    • Merge Queries - Exploring other Join Types

    • Combine and Append Multiple Worksheets into One

    • Importing Multiple Files from a Folder into One List - Incredibly Useful

    • Importing Data from the Web

    • Exercise: Import & Transform Text File

    • Exercise: Import Data from the Web

  • 15

    Working with the Data Model and PowerPivot

    • Introduction to the Data Model and PowerPivot

    • Adding Tables to the Data Model and Creating Relationships between Tables

    • Creating a PivotTable from the Data Model

    • Enabling the PowerPivot Add-In

    • Exploring the PowerPivot Window

    • Introduction to DAX - Calculated Columns

    • Introduction to DAX - Measures

  • 16

    Introduction to Macros and VBA

    • Recording your First Macro

    • Assigning your Macro to a Toolbar Button

    • Customising the Ribbon to Add your Macro Buttons

    • Editing your Macro Code 1

    • Using Relative References when Recording a Macro

    • Saving a Workbook Containing a Macro

    • Editing your Macro Code 2

    • Adding a Button to the Worksheet to Run your Macro

    • Exercise: Recording & Editing Macros

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 10 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.

The course includes the exact files that I use in the lessons to download and follow along, lifetime access and 1 on 1 instructor support every step of the way.

Don't delay. Take action. Grab a coffee and let's get started.

Your journey towards mastering Excel starts today.