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

Quiz Excel Function Guide – Chapter 7