# Lessons Archive

## Intermediate MS Excel

Free Preview

Length: 960 minutesComplexity: Standard

Details about the Intermediate MS Excel Online portion of this bundled course.  With over 133 lessons, 260 video clips and 48 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course builds on the Fundamental knowledge from the beginner course and teaches you to solve problems in Excel with a particular focus on the tools available.

## Beginner MS Excel

Free Preview

Length: 480 minutesComplexity: Easy

Details about the Online Beginner MS Excel portion of the Course. With over 60 lessons, 130 video clips, and 28 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course will give you the fundamentals to make sure it is easy to learn anything about Excel.

Complexity: Easy

Complexity: Easy

## The basic “IF” function

Length: 10 minutesComplexity: Easy

The basic “IF” function

## Simplify your formula using Excel’s inbuilt functions

Length: 11 minutesComplexity: Easy

Use Excel's inbuilt tools and functions

## Formula impact with hidden rows and columns

Length: 11 minutesComplexity: Easy

What can go wrong with hidden rows and columns

## Formula risk with a insert or delete of a cell

Length: 11 minutesComplexity: Easy

Don't insert a cell (or at least be very careful)

## The risks on formula when inserting columns and rows

Length: 11 minutesComplexity: Easy

The risks with inserting columns and rows (and how to spot them)

## What can go wrong with a formula when copying/ pasting

Length: 11 minutesComplexity: Easy

What can go wrong with a copy/ paste

## Determine what day of the week a date is

Length: 11 minutesComplexity: Easy

Determine what day of the week a date is

## What week number is a date

Length: 11 minutesComplexity: Easy

What week number is a date

## Pull the day, month or year out of the date

Length: 11 minutesComplexity: Easy

Pull the day, month or year out of the date

Length: 11 minutesComplexity: Easy

## Date Examples- Intermediate

Length: 11 minutesComplexity: Easy

Date Examples

## VLOOKUP Examples

Length: 20 minutesComplexity: Easy

VLOOKUP Examples

Length: 20 minutesComplexity: Easy

Build your first VLOOKUP and understand what each component means and how to use it.

## VLOOKUP explained in simple terms

Length: 5 minutesComplexity: Easy

Vlookup explained in simple terms so that you obtain a fundamental knowledge that you can apply to the more advanced uses of VLOOKUP

## Text Examples-Intermediate

Length: 10 minutesComplexity: Easy

Text Examples

## Remove unnecessary spaces

Length: 10 minutesComplexity: Easy

Remove unnecessary spaces

## Force Excel to see the value (instead of text)

Length: 10 minutesComplexity: Easy

Force Excel to see the value (instead of text)

## Change text to lower, UPPER or Proper case

Length: 10 minutesComplexity: Easy

Change text to lower, UPPER or Proper case

## Extract parts of the text from cells

Length: 10 minutesComplexity: Easy

Extract parts of the text from cells

## Join text from many cells

Length: 10 minutesComplexity: Easy

Join text from many cells

## IF Examples- Intermediate

Length: 10 minutesComplexity: Easy

IF this then that Examples

## Dollar Sign (\$A\$1) Examples- Intermediate

Length: 10 minutesComplexity: Easy

Dollar Sign (\$A\$1) Examples

## Formula Auditing Examples

Length: 10 minutesComplexity: Easy

Formula Auditing Examples

## Enter formula into many cells (at the same time)

Length: 10 minutesComplexity: Easy

Enter formula into many cells (at the same time)

## Protection Examples

Length: 20 minutesComplexity: Easy

Protection Examples

## Trick: Protect rows and columns without protecting sheet

Length: 20 minutesComplexity: Easy

The problem with the Protect Sheets is that it does apply some limits and some users want everything to be open EXCEPT that they don't want users to insert or delete rows and columns (this is a very common cause of spreadsheet errors or 'templates' corrupting). This is a trick to disable the insert/ delete option without protecting the sheet and no VBA.

## Hide formulas in Excel

Length: 20 minutesComplexity: Easy

When you protect a sheet you typically stop users from changing your formulas. However, you can also control whether a user can see a formula on a cell. If you want the formula to be hidden you need to follow a slightly different process.

## Trick: Allow user to only see their information in Excel

Length: 20 minutesComplexity: Easy

This is a trick to be able to send out one spreadsheet which contains everyone's information, but set it up so that a single person can only see their data and not the other users.

## SUMIFS et al Examples- partial matches

Length: 20 minutesComplexity: Easy

SUMIFS et al Examples

## SUMIF et al Examples

Length: 20 minutesComplexity: Easy

SUMIFS et al Examples- sales by area, recon lists, duplicate bank accounts

## SUMIFS et al Examples- Bigger or less than

Length: 20 minutesComplexity: Easy

SUMIFS et al Examples- count profitable months, stop duplicate entry

## SUMIFS et al Examples- multiple conditions

Length: 20 minutesComplexity: Easy

SUMIFS et al Examples- multiple conditions, duplicate bank account numbers

## VLOOKUP Examples- fuzzy logic

Length: 20 minutesComplexity: Easy

VLOOKUP Examples- fuzzy logic

## Sum, count or average if certain cells match

Length: 15 minutesComplexity: Easy

Sum, count or average if certain cells match

## Sum, count or average cells e.g. bigger than

Length: 15 minutesComplexity: Easy

Sum, count or average cells e.g. bigger than

## Sum, count or average with partial matches

Length: 15 minutesComplexity: Easy

Sum, count or average with partial matches

## Sum, count or average with many conditions

Length: 15 minutesComplexity: Easy

Sum, count or average with many conditions

## Create a find/ replace formula

Length: 10 minutesComplexity: Easy

Replace text in excel using formula

## Vlookup approximate match (True instead of False)

Length: 15 minutesComplexity: Easy

How to use vlookup approximate match to categories data, use banding and get Excel to guesstimate results

## VLOOKUP Examples- categorise, bands

Length: 20 minutesComplexity: Easy

VLOOKUP Examples- categorise, bands

## Vlookup partial text from single cell- fuzzy lookup

Length: 15 minutesComplexity: Easy

Vlookup partial text from single cell- fuzzy lookup

Length: 5 minutesComplexity: Easy

## Trace where a formula comes from and goes to (even across sheets)

Length: 10 minutesComplexity: Easy

Track where a formula comes from

## How to use this course

Free Preview

Length: 5 minutesComplexity: Easy

How to use this course

## Function Wizard- why you should use it

Length: 10 minutesComplexity: Easy

Function Wizard- the Excel cheat tool

## EXERCISE: CREATING A CALCULATED ITEM

Complexity: Easy

How to add a new calculated field to a pivot table so that you can create calculations directly into the Pivot Table