Excel Formulas and Data Analysis Course 2026

This course is for people who know basic Excel (SUM, simple IF and cell references) and want to analyze data at work. It covers logical formulas, conditional totals, lookups, text and date functions, conditional formatting, data validation, duplicates, PivotTables and charts. Study each lesson and try its practical exercise and topic quiz, then take the timed final assessment. Score 80% or more to earn a certificate of completion.

Cursus
Canada
96 vragen
Gecontroleerd op 13 oktober 2026
Feiten gecontroleerd op 13 oktober 2026

Kort antwoord

Deze gratis cursus heeft ongeveer 240 minuten lessen, 6 themaquizzen zonder tijdslimiet (96 vragen in totaal) en een getimede eindtoets van 35 willekeurig gekozen vragen. Haal 80% of meer op de eindtoets om een bewijs van deelname te downloaden (privé, niet geaccrediteerd). Gecontroleerd aan de hand van officiële bronnen op 13 oktober 2026.

Begin met de lessenExcel Formulas and Data Analysis Course: Lessons: ongeveer 240 minuten leestijd. Elke testvraag wordt in de lessen behandeld.

Wat je leert

Make decisions in formulas with IF, IFS, AND, OR and NOT
Add, count and average data that meets several conditions with SUMIFS, COUNTIFS and AVERAGEIFS
Look up values with XLOOKUP, VLOOKUP and INDEX/MATCH, and handle missing values with IFERROR and IFNA
Split, clean and join text, and calculate with dates and working days
Highlight data with conditional formatting, control entries with data validation and remove duplicates safely
Summarize data with PivotTables and choose charts that answer a business question

Haal 80% of meer om je certificaat te downloaden.

Je studieplan

Lees elke les, maak de themaquiz (zonder tijdslimiet, met uitleg) en daarna de getimede eindtoets. Elke keer wordt een nieuwe mix van vragen gekozen. Je voortgang wordt in deze browser opgeslagen.

Themaquiz 1: Logical formulas: IF, IFS, AND and OR

Vraag 1 van 15
What does =AND(5>3, 2>4) return?
- #VALUE!
- FALSE
- 0
- TRUE
Toon antwoord
Antwoord: B. FALSE
AND returns TRUE only when every test is TRUE. 2>4 is FALSE, so the result is FALSE.
Vraag 2 van 15
What does =OR(5>8, 3<4) return?
- #N/A
- FALSE
- 1
- TRUE
Toon antwoord
Antwoord: D. TRUE
OR returns TRUE when at least one test is TRUE. 3<4 is TRUE, so the result is TRUE.
Vraag 3 van 15
In the sheet below, what does =NOT(D3="South") return?
A B C D
1 Name Sales Accounts Gewest
2 Ana 52000 14 North
3 Ben 47500 22 South
4 Chloé 61000 9 North
5 Dev 39000 18 East
6 Eli 58000 25 South
7 Fatima 45000 11 East
- Ben
- FALSE
- South
- TRUE
Toon antwoord
Antwoord: B. FALSE
D3 holds South, so D3="South" is TRUE. NOT reverses it, so the result is FALSE.
Vraag 4 van 15
In the sheet below, E2 contains =IF(AND(B2>=50000,C2>=12),"Bonus","No bonus") and is copied down to E7. How many cells show "Bonus"?
A B C D
1 Name Sales Accounts Gewest
2 Ana 52000 14 North
3 Ben 47500 22 South
4 Chloé 61000 9 North
5 Dev 39000 18 East
6 Eli 58000 25 South
7 Fatima 45000 11 East
- 1
- 2
- 4
- 3
Toon antwoord
Antwoord: B. 2
Both tests must be TRUE. Only Ana (52,000 and 14) and Eli (58,000 and 25) pass both.
Vraag 5 van 15
In the sheet below, E2 contains =IF(OR(B2>=50000,C2>=20),"Bonus","No bonus") and is copied down to E7. How many cells show "Bonus"?
A B C D
1 Name Sales Accounts Gewest
2 Ana 52000 14 North
3 Ben 47500 22 South
4 Chloé 61000 9 North
5 Dev 39000 18 East
6 Eli 58000 25 South
7 Fatima 45000 11 East
- 2
- 5
- 4
- 3
Toon antwoord
Antwoord: C. 4
OR needs just one TRUE test. Ana, Chloé and Eli pass on sales, and Ben passes on accounts (22), so 4 people get "Bonus".
Vraag 6 van 15
What must you put around text such as North when you use it inside a formula like =IF(D2="North","Yes","No")?
- Parentheses
- Double quotation marks
- Single quotation marks
- Square brackets
Toon antwoord
Antwoord: B. Double quotation marks
Text in a formula goes inside double quotation marks. Without them, Excel tries to read the word as a name and usually shows #NAME?.
Vraag 7 van 15
How many conditions can you test in one IFS function?
- 127
- 255
- 7
- 64
Toon antwoord
Antwoord: A. 127
Microsoft says IFS can test up to 127 different conditions. (AND and OR can each take up to 255.)
Vraag 8 van 15
In the sheet below, what does =IFS(B5>=60000,"Gold",B5>=50000,"Silver") return?
A B C D
1 Name Sales Accounts Gewest
2 Ana 52000 14 North
3 Ben 47500 22 South
4 Chloé 61000 9 North
5 Dev 39000 18 East
6 Eli 58000 25 South
7 Fatima 45000 11 East
- 0
- FALSE
- #N/A
- Silver
Toon antwoord
Antwoord: C. #N/A
Dev's sales (39,000) make neither test TRUE. When no condition in IFS is TRUE, it returns the #N/A error.
Vraag 9 van 15
You want IFS to return "Bronze" when none of the earlier tests are TRUE. What do you put as the last test?
- TRUE
- "Else"
- An empty pair of quotation marks
- FALSE
Toon antwoord
Antwoord: A. TRUE
To give IFS a default result, enter TRUE as the final logical test and the default value after it.
Vraag 10 van 15
In the sheet below, E2 contains =IFS(B2>=60000,"Gold",B2>=50000,"Silver",TRUE,"Bronze") and is copied down. What does E6 show?
A B C D
1 Name Sales Accounts Gewest
2 Ana 52000 14 North
3 Ben 47500 22 South
4 Chloé 61000 9 North
5 Dev 39000 18 East
6 Eli 58000 25 South
7 Fatima 45000 11 East
- Bronze
- Gold
- Silver
- #N/A
Toon antwoord
Antwoord: C. Silver
E6 checks Eli's 58,000. It is not 60,000 or more, but it is 50,000 or more, so IFS stops at "Silver".
Vraag 11 van 15
In the sheet below, E4 contains =IF(B4>=50000,"Silver",IF(B4>=60000,"Gold","Bronze")). What does it show for Chloé?
A B C D
1 Name Sales Accounts Gewest
2 Ana 52000 14 North
3 Ben 47500 22 South
4 Chloé 61000 9 North
5 Dev 39000 18 East
6 Eli 58000 25 South
7 Fatima 45000 11 East
- Gold
- Bronze
- FALSE
- Silver
Toon antwoord
Antwoord: D. Silver
IF stops at the first TRUE test. 61,000 is already 50,000 or more, so "Silver" is returned and the Gold test is never reached. Put the highest tier first.
Vraag 12 van 15
In the sheet below, E2 contains =IF(NOT(OR(D2="North",D2="South")),"Check","OK") and is copied down to E7. How many cells show "Check"?
A B C D
1 Name Sales Accounts Gewest
2 Ana 52000 14 North
3 Ben 47500 22 South
4 Chloé 61000 9 North
5 Dev 39000 18 East
6 Eli 58000 25 South
7 Fatima 45000 11 East
- 2
- 4
- 6
- 0
Toon antwoord
Antwoord: A. 2
OR is TRUE for North or South, and NOT flips it. Only the two East rows (Dev and Fatima) give "Check".
Vraag 13 van 15
In the timesheet below, D2 contains =IF(B2>40,(B2-40)*C2*1.5,0). What does D2 show?
A B C
1 Name Hours Rate
2 Gus 44 22
3 Hana 38 24
4 Ivan 40 21
- 968
- 88
- 0
- 132
Toon antwoord
Antwoord: D. 132
Gus worked 44 hours, which is more than 40. Overtime pay is (44-40) x 22 x 1.5 = 132.
Vraag 14 van 15
In the timesheet below, D4 contains =IF(B4>40,(B4-40)*C4*1.5,0). What does D4 show?
A B C
1 Name Hours Rate
2 Gus 44 22
3 Hana 38 24
4 Ivan 40 21
- 31.5
- 21
- 0
- 1260
Toon antwoord
Antwoord: C. 0
Ivan worked exactly 40 hours. 40>40 is FALSE (> means strictly greater), so IF returns the value_if_false, 0.
Vraag 15 van 15
In the sheet below, E3 contains =IF(C3>=20,IF(B3>=50000,"A","B"),"C"). What does E3 show?
A B C D
1 Name Sales Accounts Gewest
2 Ana 52000 14 North
3 Ben 47500 22 South
4 Chloé 61000 9 North
5 Dev 39000 18 East
6 Eli 58000 25 South
7 Fatima 45000 11 East
- B
- FALSE
- A
- C
Toon antwoord
Antwoord: A. B
Ben has 22 accounts, so the outer test is TRUE and Excel checks the inner IF. His sales (47,500) are under 50,000, so it returns "B".

	A	B	C	D
1	Name	Sales	Accounts	Gewest
2	Ana	52000	14	North
3	Ben	47500	22	South
4	Chloé	61000	9	North
5	Dev	39000	18	East
6	Eli	58000	25	South
7	Fatima	45000	11	East

	A	B	C	D
1	Name	Sales	Accounts	Gewest
2	Ana	52000	14	North
3	Ben	47500	22	South
4	Chloé	61000	9	North
5	Dev	39000	18	East
6	Eli	58000	25	South
7	Fatima	45000	11	East

	A	B	C	D
1	Name	Sales	Accounts	Gewest
2	Ana	52000	14	North
3	Ben	47500	22	South
4	Chloé	61000	9	North
5	Dev	39000	18	East
6	Eli	58000	25	South
7	Fatima	45000	11	East

	A	B	C	D
1	Name	Sales	Accounts	Gewest
2	Ana	52000	14	North
3	Ben	47500	22	South
4	Chloé	61000	9	North
5	Dev	39000	18	East
6	Eli	58000	25	South
7	Fatima	45000	11	East

	A	B	C	D
1	Name	Sales	Accounts	Gewest
2	Ana	52000	14	North
3	Ben	47500	22	South
4	Chloé	61000	9	North
5	Dev	39000	18	East
6	Eli	58000	25	South
7	Fatima	45000	11	East

	A	B	C	D
1	Name	Sales	Accounts	Gewest
2	Ana	52000	14	North
3	Ben	47500	22	South
4	Chloé	61000	9	North
5	Dev	39000	18	East
6	Eli	58000	25	South
7	Fatima	45000	11	East

	A	B	C	D
1	Name	Sales	Accounts	Gewest
2	Ana	52000	14	North
3	Ben	47500	22	South
4	Chloé	61000	9	North
5	Dev	39000	18	East
6	Eli	58000	25	South
7	Fatima	45000	11	East

	A	B	C
1	Name	Hours	Rate
2	Gus	44	22
3	Hana	38	24
4	Ivan	40	21

	A	B	C
1	Name	Hours	Rate
2	Gus	44	22
3	Hana	38	24
4	Ivan	40	21

	A	B	C	D
1	Name	Sales	Accounts	Gewest
2	Ana	52000	14	North
3	Ben	47500	22	South
4	Chloé	61000	9	North
5	Dev	39000	18	East
6	Eli	58000	25	South
7	Fatima	45000	11	East

Wat je moet weten voor het examen

IFS can test up to 127 conditions. If none is TRUE it returns #N/A, so enter TRUE as the last test to give a default result.
In SUMIFS the range to add comes first, but in SUMIF it comes third. SUMIFS accepts up to 127 range/criteria pairs.
XLOOKUP uses an exact match by default and can return a value from a column on either side. VLOOKUP only looks from left to right.
IFERROR catches the #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL! errors.
Excel stores dates as serial numbers so they can be used in calculations. January 1, 1900 is serial number 1.
Data validation shows messages and blocks invalid entries only when people type in a cell. Copied or filled data can get past it.
Remove Duplicates deletes data permanently, so Microsoft suggests copying the original data to another worksheet first.
In a PivotTable, Count is the default summary for value fields that have text or blank cells; Sum is the default for numbers.

Veelgestelde vragen

What certificate do I get, and what is it not?

If you score 80% or more on the final assessment, you can download a certificate of completion for this course. It shows you finished a general skills course. It is not an accredited qualification, not a Microsoft certification such as Microsoft Office Specialist, and not an employer or government certification.

Should I take the Excel for beginners course first?

If you are new to formulas, yes. This course assumes you can already use SUM, AVERAGE, COUNTIF, a simple IF and absolute references ($A$1).

Which version of Excel do I need?

The lessons describe Excel for Microsoft 365. XLOOKUP needs Excel for Microsoft 365 or Excel 2021 and later; it is not in Excel 2016 or 2019. IFS and TEXTJOIN need Excel 2019 or later. In older versions, use VLOOKUP or INDEX and MATCH, and nested IF.

How does the final assessment work?

It has 35 questions picked at random from the course question bank, and you have 35 minutes. Many questions show a small spreadsheet and ask what a formula returns. You get a new mix each time.

Can I practise with my own work files?

Use the practice sheets in the lessons. If you use real work data, follow your employer's policies and privacy rules, and work on a copy so you do not change the original.

Is het certificaat geaccrediteerd of officieel erkend?

Nee. Het is een privé bewijs van deelname, alleen voor je kennis. Het is niet erkend, geaccrediteerd of goedgekeurd door een overheid, vergunningsinstantie, toezichthouder of onderwijsinstelling, en het vervangt geen wettelijk verplichte vergunning, certificering of opleiding.