The Excel Functions Guide

1 Functions with Count And Sum
COUNT – Counts the number of cells containing numbers
00:35 min
Count the nonblank cells with COUNTA
00:37 min
Count the number of blank cells with COUNTBLANK
00:35 min
Count cells based on a criteria with COUNTIF
00:32 min
Count cells based on multiple criteria with COUNTIFS
00:51 min
Count logical values
01:09 min
Count text occurrences
01:20 min
Use SUM to sum up the values in a range of cells
00:35 min
Sum cells based on one criteria with SUMIF
00:36 min
Sum cells based on multiple criteria with SUMIFS
00:56 min
SUMPRODUCT sums the product of a range of cells
01:00 min
2 Logical
The IF-function
00:58 min
Combine the IF-function with AND / OR
01:20 min
Combine multiple IF-functions
01:17 min
3 Cell references
Copy exact formulas
00:57 min
How to mix relative and absolute references
01:28 min
Sum values from multiple sheets with 3D references
01:25 min
01:23 min
4 Date and time
Learn the tricks for Date and Time
01:40 min
Get the number of days between two dates with DATEDIF
01:36 min
Day of the year
01:02 min
Days until anniversity
01:23 min
Last day of the month
01:12 min
Quarter
00:58 min
Weekdays
01:12 min
Number of NETWORKDAYS between two dates
00:42 min
NETWORKDAY returns a date after a number of weekdays
00:54 min
5 Text
How to compare text in two different cells
01:03 min
JOIN and LEN
00:55 min
Remove unwanted characters
00:56 min
Capital or small letters with UPPER, LOWER and PROPER
00:58 min
Extract characters with LEFT, RIGHT and MID
01:12 min
FIND and SEARCH characters in cells
01:32 min
SUBSTITUTE and REPLACE characters in cells
01:17 min
Count the number of words in cells
01:25 min
Number of instances a text or a number occurs in a cell
01:29 min
Separate strings of text from a cell
01:05 min
Use the Text to columns-guide
00:55 min
6 Lookup and reference
Search and return a value with VLOOKUP
01:01 min
Search and return a value with HLOOKUP
00:55 min
How to use VLOOKUP if you don’t have an exact reference
01:19 min
Use MATCH, INDEX, CHOOSE to find values in a list
01:17 min
Use INDEX/MATCH to return a value left of a reference
01:11 min
Get the cell reference for the cell with max value
01:02 min
Get a value using coordinates with OFFSET
01:09 min
Use INDEX/MATCH to lookup values in a two-dimensional range
01:09 min
Get values with an INDIRECT reference
01:17 min
7 Financial
8 Statistical
Get the average of values with AVERAGE/AVERAGEIF
01:07 min
Use FORECAST to predict the next number in a range
00:47 min
Find the middle/most common value with MEDIAN, MODE
00:57 min
Identify the n:th largest/smallest number with LARGE/SMALL
01:01 min
Use STDEV to calculate standard deviation
00:46 min
RAND returns a random number between 0-1
01:03 min
RANDBETWEEN returns a random number between two numbers
01:01 min
Rank the numbers in a range with RANK, RANK.AVG
01:21 min
PERCENTILE returns a number corresponding to a percentile
01:13 min
QUARTILE returns the number corresponding to a quartile
01:07 min
Neutralise negative numbers in a list
00:55 min
9 Round
ROUND numbers to integers or decimals
00:54 min
Use ROUNDUP/ROUNDDOWN to round numbers
01:08 min
Round to the closest EVEN or ODD number
00:57 min
Delete decimals with INT/TRUNC
00:58 min
10 Formula errors
How to find and correct circular references
01:04 min
How to deal with #NAME?, #VALUE! and ####
00:59 min
How to deal with #DIV/0! and #REF!
01:03 min
Trace dependent/Precedents cell references
01:11 min
Show and Evaluate formula, Error checking
01:09 min
Correct errors with ISERROR
01:08 min
11 Array formulas
Introduction to array formulas
01:20 min
Count the number of errors in a range
01:03 min
Count unique values in a range
01:10 min
Count cells with or and and criteria
01:18 min
Find the most frequent word in a range
01:12 min
Sum every nth row in a range
01:21 min
Sum the largest numbers in a range
00:59 min
Sum a range with error values
00:56 min
Sum cells with or and and criteria
01:23 min
Two-column lookup in an array formula
01:16 min
12 Depreciation
Introduction to depreciation
01:03 min
SLN (Straight Line)
01:01 min
SYD (Sum-of-Years’ Digits)
01:00 min
DB (Fixed Declining Balance Method)
01:07 min
DDB (Double Declining Balance Method)
01:12 min
VDB (Variable Declining Balance)
01:26 min
13 Dynamic Array Functions
Introduction to Dynamic Array Functions
02:09 min
SORT
02:07 min
SORTBY
02:03 min
UNIQUE
01:45 min
FILTER
01:56 min
RANDARRAY
02:01 min
SEQUENCE
02:40 min
XMATCH
02:26 min
Show the nth largest or smallest values
02:08 min
Filter every nth row
02:25 min
Use FILTER to remove columns
02:06 min
Randomly sort a list
01:38 min
Get common values
01:58 min