Computergaga

Introduction to the Ultimate Excel Course

Download your Exercise Files

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

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

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

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

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

Introduction to Dynamic Array Formulas

The UNIQUE Function

SORT Function - Sort Formula Results Automatically

SORTBY Function - Sort by Any Column

FILTER Function - Lookup and Return Multiple Values

The SEQUENCE Function

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

Return Non-Adjacent Columns in an Array with INDEX and MATCH

XLOOKUP Function - The Successor to VLOOKUP & HLOOKUP

Multi-Column Lookup with XLOOKUP

Two-Way Lookup with XLOOKUP

Exercise: Using the VLOOKUP Function

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

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

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

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

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

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

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

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