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

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

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.

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

3-way-data-table

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

3-way-data-table

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

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

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

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