This course will teach you the logic behind functions and be your online library available to your help at anytime.The course consist of two sections: The first two modules (E1-E2) consists of lessons of 1-3 minutes teaching you the basics logics behind formulas and functions. Each lesson has a quiz where you can practice you skills. The rest of the modules (1-12) is short 1 minute lessons showing you about 95 different Excel functions. This part of the course has no quizes, but will be your online Excel library to help you save time and grow your Excel skills.
In this module we teach you the basics behind formulas and functions. This is important when you move on to the more advanced funcitons in the comming chapters. Each lesson in this module has a test where you can practice the content in the lesson.
The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.
- 1.1 COUNT – Counts the number of cells containing numbers
- 1.2 Count the nonblank cells with COUNTA
- 1.3 Count the number of blank cells with COUNTBLANK
- 1.4 Count cells based on a criteria with COUNTIF
- 1.5 Count cells based on multiple criteria with COUNTIFS
- 1.6 Count logical values
- 1.7 Count text occurences
- 1.8 Use SUM to sum up the values in a range of cells
- 1.9 Sum cells based on one criteria with SUMIF
- 1.10 Sum cells based on multiple criteria with SUMIFS
- 1.11 SUMPRODUCT sums the product of a range of cells Free Preview
Learn how to use Excel's logical functions such as the IF, AND and OR function.
Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.
Date and time can be tricky to work with in Excel, however in this chapter you will learn the smart ways to work with date and time in order to get save time and get it right.
Excel has many functions to offer when it comes to manipulating text strings. In this chapter we will show you the most powerful.
- 5.1 How to compare text in two different cells
- 5.2 JOIN and LEN
- 5.3 Capital or small letters with UPPER, LOWER and PROPER
- 5.4 Remove unwanted characters Free Preview
- 5.5 Extract characters with LEFT, RIGHT and MID
- 5.6 FIND and SEARCH characters in cells
- 5.7 SUBSTITUTE and REPLACE characters in cells
- 5.8 Count the number of words in cells
- 5.9 Number of instances a text or a number occurs in a cell
- 5.10 Separate strings of text from a cell
- 5.11 Use the Text to columns-guide
Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.
- 6.1 Search and return a value with VLOOKUP
- 6.2 Search and return a value with HLOOKUP
- 6.3 How to use VLOOKUP if you don’t have an exact reference
- 6.4 Use MATCH, INDEX, CHOOSE to find values in a list
- 6.5 Use INDEX/MATCH to return a value left of a reference
- 6.6 Get the cell reference for the cell with max value
- 6.7 Get a value using coordinates with OFFSET
- 6.8 Use INDEX/MATCH to lookup values in a two-dimensional range
- 6.9 Get values with an INDIRECT refernce
This chapter illustrates Excel's most popular financial functions.
An overview of some very useful statistical functions in Excel.
- 8.1 Get the average of values with AVERAGE/AVERAGEIF
- 8.2 Find the middle/most occuring value with MEDIAN, MODE
- 8.3 Identify the n:th largest/smallest number with LARGE/SMALL
- 8.4 Use STDEV to calculate standard deviation
- 8.5 RAND returns a random number between 0-1
- 8.6 RANDBETWEEN returns a random number between two numbers
- 8.7 Rank the numbers in a range with RANK, RANK.AVG
- 8.8 PERCENTILE returns a number corresponding to a percentile
- 8.9 QUARTILE returns the number corresponding to a quartile
- 8.10 Neutralise negative numbers in a list
- 8.11 Use FORECAST to predict the next number in a range
This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function.
This chapter teaches you how to deal with some common formula errors in Excel.
This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell.
- 11.1 Introduction to array formulas
- 11.2 Count the number of errors in a range
- 11.3 Count unique values in a range
- 11.4 Count cells with or and and criteria
- 11.5 Find the most frequent word in a range
- 11.6 Sum every nth row in a range
- 11.7 Sum the largest numbers in a range
- 11.8 Sum a range with error values
- 11.9 Sum cells with or and and criteria
- 11.10 Two-column lookup in an array formula
Excel offers five different depreciation functions. In each of the following video we will consider an asset with an initial cost of $15,000, a salvage value (residual value) of $1500 and a useful life of 10 periods (years). In the lessons you can find the results of all five functions, each function explained separately.