Introduction
  • Introduction to the Ultimate Excel Course
  • Download your Exercise Files
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
5 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
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
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
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
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
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
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
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
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
Formatting your Data Ranges as Tables for Easier Management
  • Formatting your Data Range as a Table
  • Exploring the Benefits of using Tables