# Lessons Archive

## Vlookup #ref error

Length: 10 minutesComplexity: Easy

The reasons for the vlookup #Ref error and where you should look to correct it both where the #REF error only appears in the value, or where it appears in the value and formula

## Vlookup approximate match error exercise

Length: 5 minutesComplexity: Standard

Exercise in understanding reasons that the VLOOKUP is giving the incorrect answer

## Bands in excel exercise 2

Length: 10 minutesComplexity: Standard

In this example you are calculating the tax liability based on various salaries. The tax table is provided and a series of salaries are provided. You need to calculate what the tax liability will be on these amounts.

## Bands in excel exercise

In this example we have a situation where different levels of sales result in different commission rates. Calculate the commission % allowed and the commission earned

## Categorize data in excel- exercise 2

Length: 10 minutesComplexity: Easy

Another example in using VLOOKUP approximate match to categorize items in Excel.

## Categorize data in excel using VLOOKUP exercise

Length: 10 minutesComplexity: Easy

Example in using VLOOKUP to categorize data in excel.

## Vlookup approximate match

Length: 15 minutesComplexity: Easy

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

## Vlookup exercises download

Length: 5 minutesComplexity: Easy

VLOOKUP exercises download for use in the rest of the VLOOKUP course with practical examples and detailed explanations of the solutions.

## Join databases with VLOOKUP

Length: 10 minutesComplexity: Easy

A common requirement in Excel is to join databases with VLOOKUP. This often happens when there is a common item in the databases. Examples of this are when you want to show product codes and names or show a client number and client name if they are on different lists.

## Find missing items with Excel Exercise

Length: 10 minutesComplexity: Easy

We can also use VLOOKUP to find items that are missing from a list. This is very useful when reconciling accounts.

## Anchoring cells in Excel exercise

Length: 10 minutesComplexity: Easy

A useful feature of VLOOKUP is its ability to anchor cells. A common problem is that data appears in different cells at different times (each time you download a report a key cost item you are looking for appears on a different line). You can use VLOOKUP to find the cost item and make it appear on a set row. You can then build ratios and graphs off this static row

## Vlookup #n/a Exercise

Length: 10 minutesComplexity: Standard

An exercise with VLOOKUP to understand why it is not giving the correct answer and one of the causes of a #n/a error when using VLOOKUP.

## Automatic Sorting in Excel Exercise

Length: 15 minutesComplexity: Standard

An exercise on how to create automatic sorting in excel so that the information automatically sorts itself when changes are made (no VBA or Macros)

## Create multi variable data tables in excel

Free Preview

Length: 25 minutesComplexity: Hard

Data tables are an exceptionally useful function in Excel which allows you to (quickly) run sensitivities on you financial models. The limitation however is that at best you can only run a 2 variable data table. This can be limiting as often a financial model has 3 or 4 key inputs which you want to test. Below is a method to create multi variable data tables in excel.

## 3 way data tables in excel- template downloads

Length: 20 minutesComplexity: Standard

In this lesson you will be able to download the various templates which allow for 3 and more way data tables. Watch the video clip to see how to use them in you own financial models for enhanced sensitivity analysis

## Excel functions wizard

Free Preview

Length: 10 minutesComplexity: Easy

How to use the Excel functions wizard to find, understand and use all the various functions and formula available in Excel

## Function Wizard Exercise

Free Preview

Length: 5 minutesComplexity: Easy

An exercise in using the Excel Function Wizard to find a function that allows you to find the 2nd biggest number in a list.

## Formula Auditing Tool

Free Preview

Length: 15 minutesComplexity: Easy

How to use the Formula Auditing Tool to check which cells are relied on in a formula, identify differences in rows and columns, and check cell importance before we delete it

Free Preview

## Excel \$ sign

Free Preview

Length: 20 minutesComplexity: Standard

How to use the Excel \$ sign to create formula that can be copied to other cells and they will maintain their integrity. This will allow you to build a single formula to accommodate multiple situations instead of building each formula separately

## Using \$ in excel formulas- Tips and Tricks

Free Preview

Length: 10 minutesComplexity: Easy

Tips and tricks for using \$ in Excel formulas including how to safely and easily create them

## Excel \$ sign exercises

Free Preview

Exercises on using the Excel \$ sign in formula

## IF Function

Free Preview

Length: 20 minutesComplexity: Standard

The IF function is one of first formula you should learn to use. In basic English it says: "IF something happens do this, otherwise do that". In Excel it can be used in a number of places.

## Excel IF exercises

Free Preview

Excel IF Exercises showing you the various options and possibilities with the IF function

## COA Overview

Length: 5 minutesComplexity: Easy

The National Consumer Tribunal Consent Order Assistant is to assist Debt Councillors in providing the NCT with the required information to assess the case. The tool is built in Microsoft Excel and consists of the following sheets with a brief description of each sheet.

## Initial Setup

Length: 10 minutesComplexity: Easy

Initial setup instructions for the COA from NCT

## Finalising the letters

Length: 10 minutesComplexity: Easy

Finalising the letters based on the information captured into the COA

## Checking the financial details captured

Length: 10 minutesComplexity: Easy

Checking the financial details captured onto the COA before sending it to the NCT with calculation checks and a debt repayment graph

## Step 2

Length: 10 minutesComplexity: Easy

Step 2 in the process to capture a case onto the COA

## Step 1

Length: 10 minutesComplexity: Easy

Step 1 of the process to capture a case on the COA

## Overarching rules of the COA

Length: 10 minutesComplexity: Easy

Overarching rules of the COA which are applied throughout the spreadsheet

## SAMEPERIODLASTYEAR ()

Length: 20 minutesComplexity: Standard

## Time Intelligence in PowerPivot

Complexity: Standard

## ALL(), ALLEXCEPT()

Complexity: Standard

## COUNTROWS

Length: 20 minutesComplexity: Standard

## DISTINCTCOUNT

Length: 20 minutesComplexity: Standard

## CALCULATE function in PowerPivot

Length: 20 minutesComplexity: Standard

## PowerPivot SUM and AVERAGE functions

Length: 20 minutesComplexity: Standard

## Creating DAX Measures

Complexity: Standard

## Understanding DAX Measures with an example

Length: 20 minutesComplexity: Hard

## PowerPivot Relationships

Length: 20 minutesComplexity: Standard

## Calculated Column Exercise

Exercise to be completed for calculated columns

## Calculated Columns in PowerPivot

Length: 20 minutesComplexity: Standard

## Build your first VLOOKUP

Free Preview

Length: 20 minutesComplexity: Easy

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

## VLOOKUP explained in simple terms

Free Preview

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

## PowerPivot Import Data Exercise

Length: 20 minutesComplexity: Standard