Free Excel test: practice for an Excel assessment

A. 7120

SUM(C2:C8) adds every number from C2 down to C8: 7,120.

• B. 2200: A comma lists separate cells, so this adds only C2 and C8. A colon (C2:C8) means every cell in between.
• C. 6120: The range stops one row short and misses Gus. Check the last row of any range you drag-select.
• D. 7: COUNT tells you how many numbers there are (7). It does not add them.

Study: Lesson: Counting · COUNT vs COUNTA vs COUNTBLANK

D. 3

AVERAGE adds 4 + 0 + 6 + 2 = 12 and divides by the 4 cells that hold numbers. Empty cells are skipped; zeros count.

• A. 12: That is the total of 12 hours, not the average.
• B. 4: That leaves out Ben's 0 as well. AVERAGE counts a typed 0; it only skips cells that are truly empty.
• C. 2.4: That treats Cara's empty cell as a zero. AVERAGE skips empty cells, so it divides by 4, not 5.

Study: Lesson: Counting · COUNT vs COUNTA vs COUNTBLANK

A. 4

COUNT counts only cells that hold numbers: 8, 7, 10 and 9. Text and empty cells are not counted.

• B. 34: SUM adds the scores (34). COUNT counts how many of them are numbers.
• C. 5: COUNTA counts every non-empty cell, so it includes the text "n/a" and gives 5.
• D. 6: ROWS counts the rows in the range (6), filled or not.

Study: Lesson: Counting · COUNT vs COUNTA vs COUNTBLANK

A. 8

COUNTA counts every cell that is not empty, and the range includes the header in A1. Start the range at A2 to count only reps.

• B. 7: That is the right count of reps (7), but it is a different formula. A1:A8 starts on the header row, so "Rep" is counted too.
• C. 0: COUNT only counts numbers. Names are text, so COUNT returns 0.
• D. 24: That counts every filled cell in all three columns (24).

Study: Lesson: Counting · COUNT vs COUNTA vs COUNTBLANK

B. 0

Filled down, =B2*F1 becomes =B3*F2. F2 is empty, so the result is 0. Lock the rate with $F$1 so every row uses it.

• A. #REF!: #REF! appears when a reference points at a deleted cell. F2 exists, it is just empty, so Excel multiplies by 0.
• C. 45: That is what you wanted, and what you get when the rate is locked as $F$1 before filling down.
• D. 60: Filling down copies the formula, not the value. The references move down one row with it.

Study: Lesson: Absolute references · Absolute vs relative cell references in Excel

C. =B6*$F$1

Relative parts move with the copy (B2 becomes B6). Parts with $ stay put, so $F$1 stays $F$1.

• A. =B6*$F$5: The $ before the row number locks the row, so $F$1 never becomes $F$5.
• B. =B2*$F$1: Only the $ parts stay fixed. B2 has no $, so it moves down with the copy to B6.
• D. =B6*F5: Copying keeps the $ signs exactly as they were. They are what hold F1 in place.

Study: Lesson: Absolute references · Absolute vs relative cell references in Excel

A. It keeps the formula pointing at B1 when you copy it

$ locks the column letter, the row number, or both, so that part of the reference does not move when the formula is copied.

• B. They format B1 as currency: Number formats are separate from references. A $ inside a reference has nothing to do with money.
• C. They stop anyone from editing B1: That is cell protection (Review > Protect Sheet). $ only changes how a reference behaves when copied.
• D. They turn B1 into a number: A reference returns whatever B1 holds. The $ only fixes which cell it points at.

Study: Lesson: Absolute references · Absolute vs relative cell references in Excel

A. No bonus

> means strictly greater than, and 1,000 is not greater than 1,000. "1,000 or more" needs >=.

• B. FALSE: The test is FALSE, but IF then returns your value_if_false, "No bonus".
• C. Bonus: That is the result with >=, which matches the rule. With > the value 1,000 itself fails.
• D. #VALUE!: Comparing a number with 1000 is fine. Nothing here is text that needs converting.

Study: Lesson: AND and OR · How to use IF with AND and OR in Excel

D. Done

Text comparisons with = are not case-sensitive, so "paid" = "Paid" is TRUE and IF returns "Done".

• A. #NAME?: That is what happens when Paid has no quotes: Excel looks for a name called Paid and shows #NAME?. Text in a formula needs double quotes.
• B. TRUE: IF returns one of the two values you gave it, not TRUE or FALSE.
• C. Chase: That is what EXACT gives, because EXACT is case-sensitive. A plain = ignores case.

Study: Lesson: AND and OR · How to use IF with AND and OR in Excel

A. Retake

AND is TRUE only when every test is TRUE. The score passes but attendance is "No", so IF returns "Retake".

• B. #VALUE!: Mixing a number test and a text test inside AND is fine.
• C. FALSE: AND does return FALSE here, but IF turns that into your value_if_false text, "Retake".
• D. Pass: That is what OR gives: it passes when either test is TRUE. AND needs both.

Study: Lesson: AND and OR · How to use IF with AND and OR in Excel

C. 3

COUNTIF counts the cells in B2:B8 equal to "East": Ana, Cara and Fay.

• A. 0: Without quotes Excel treats East as a name, finds none and shows #NAME?.
• B. 7: COUNTA counts every filled cell in the column (7), whatever the region.
• D. 3270: SUMIF adds the East sales (3,270). COUNTIF counts the rows.

Study: Lesson: Multiple conditions · SUMIF with multiple criteria in Excel (SUMIFS)

A. 2050

SUMIF(range, criteria, sum_range) checks B2:B8 for "West" and adds the matching sales in C: 950 + 1,100.

• B. 7120: That adds every region (7,120).
• C. 0: With no third argument SUMIF adds the cells it tested. Those are region names (text), so the result is 0.
• D. 2: COUNTIF counts the West rows (2). It does not add their sales.

Study: Lesson: Multiple conditions · SUMIF with multiple criteria in Excel (SUMIFS)

B. 48

VLOOKUP finds P-103 in the first column of A2:C6 and returns the value from the 3rd column, Price. FALSE means exact match.

• A. #N/A: VLOOKUP searches the first column of the table. B2:C6 starts at Product, where no code is found: #N/A.
• C. Monitor stand: Column 2 of the table is Product, so that returns the name, not the price.
• D. 3: MATCH gives the position of the code (3rd row), not the price.

Study: Lesson: VLOOKUP · VLOOKUP vs XLOOKUP

A. #N/A

#N/A means "not available": an exact-match lookup found no match. Check for typos and extra spaces, or catch it with IFNA.

• B. #VALUE!: VLOOKUP shows #VALUE! when the column number is below 1.
• C. #REF!: VLOOKUP shows #REF! when the column number is wider than the table, such as 4 here.
• D. 0: A missing code is an error, not zero. Wrap the lookup in IFNA to show your own text instead.

Study: Lesson: Reading errors · How to use IFERROR in Excel

A. INV

LEFT(text, n) returns the first n characters: the first 3 of INV-2041-EU are INV.

• B. 2041: MID starts in the middle. This one returns the number part, "2041".
• C. -EU: RIGHT takes characters from the end: "-EU".
• D. INV-: Four characters include the dash: "INV-".

Study: Lesson: Slicing text · How to extract text before a character in Excel

D. =A2&" "&B2

& joins text. The " " in the middle is a space in quotes, so the result is Maria Lopez.

• A. =A2+" "+B2 (returns #VALUE!): + is for math. Adding text gives #VALUE!. Use & to join text.
• B. =A2&B2 (returns MariaLopez): That joins the names with nothing between them: MariaLopez.
• C. =CONCAT(A2,",",B2) (returns Maria,Lopez): That puts a comma between the names: Maria,Lopez.

Study: Lesson: Slicing text · How to extract text before a character in Excel

D. 11

Dates are numbers in Excel (days since 1900), so subtracting them gives the days between: 11.

• A. 12: Adding 1 counts both the start and the end day (12). Do that only when both days should count.
• B. -11: Subtracting the later date from the earlier one gives a negative number.
• C. 10: NETWORKDAYS counts weekdays only, including both ends (10).

Study: Lesson: Date basics · How to calculate days between two dates in Excel

B. It shows tomorrow's date: TODAY updates whenever the sheet recalculates

TODAY is volatile: it returns the current date each time the sheet calculates. For a fixed date, press Ctrl+; (Windows) to type it as a value.

• A. It shows #VALUE! until you edit the cell: TODAY needs no arguments and does not error.
• C. It shows the date the file was created: Excel has no function that returns the creation date. TODAY is always the current date.
• D. It still shows today's date, because the file was saved: TODAY is recalculated every time the workbook calculates. To stamp a date that never changes, press Ctrl+; instead.

Study: Lesson: Date basics · How to calculate days between two dates in Excel

D. 4.68

ROUND(number, 2) rounds to 2 decimal places. The third decimal is 8, so 4.6782 rounds up to 4.68.

• A. 4.7: The second argument is the number of decimals. 1 gives 4.7.
• B. 4: INT drops the decimals completely: 4.
• C. 4.67: ROUNDDOWN always cuts toward zero: 4.67.

Study: Lesson: MEDIAN and ROUND · ROUND vs ROUNDUP vs ROUNDDOWN in Excel

C. 11

ROUNDUP always rounds away from zero, so 10.4 boxes becomes 11. Use it whenever a part has to count as a whole.

• A. 5: MOD gives the remainder: the 5 items left after 10 full boxes.
• B. 10.5: The second argument is the number of decimals. 1 gives 10.5, not a whole number of boxes.
• D. 10: 125 / 12 is about 10.4, so ROUND gives 10 and 5 items are left without a box.

Study: Lesson: MEDIAN and ROUND · ROUND vs ROUNDUP vs ROUNDDOWN in Excel

A. 25%

Percent change = (new - old) / old. The 200 increase divided by 800 is 0.25, which shows as 25%.

• B. 125%: That shows this month as a share of last month (125%), not the change.
• C. 80%: That shows last month as a share of this month.
• D. 20%: That divides by the new value. Percent change is measured from the old value.

Study: Lesson: Percent change · How to calculate percent change in Excel

D. 45.9%

Share of total = part / total. 3,270 / 7,120 is 0.459, which shows as 45.9%. The $ keeps the total fixed when you fill down.

• A. 217.7%: That is upside down: the total divided by East.
• B. 4592.7%: Multiplying by 100 and then formatting as a percentage does it twice. The % format already multiplies by 100.
• C. 23.0%: SUM(B2:B5) includes the Total row, so the total is counted twice and the share halves.

Study: Lesson: Percent change · How to calculate percent change in Excel

C. 68

Paying 85% of the price is the same as taking 15% off: 80 * (1 - 0.15) = 68.

• A. 79.85: 15% is stored as 0.15, so this takes 15 cents off.
• B. 92: That adds 15% instead of taking it off.
• D. 12: That is the discount amount (12), not the new price.

Study: Lesson: Percent change · How to calculate percent change in Excel

B. #DIV/0!

Dividing by zero (or by an empty cell) gives #DIV/0!.

• A. #NUM!: #NUM! is for impossible numbers, such as the square root of a negative.
• C. #VALUE!: #VALUE! comes from math on text, such as a word typed in the Orders cell.
• D. 0: Excel never treats division by zero as 0. Use =IF(C2=0,"",B2/C2) to show a blank instead.

Study: Lesson: Reading errors · How to use IFERROR in Excel

B. #NAME?

#NAME? means Excel does not recognize a name in the formula: a misspelled function, or text missing its quotes.

• A. #REF!: #REF! means a cell reference is broken, usually after deleting rows or columns.
• C. 119: Excel does not guess what you meant. It shows an error until the function name is fixed.
• D. #VALUE!: #VALUE! means a value has the wrong type. Here the function name itself is unknown.

Study: Lesson: Reading errors · How to use IFERROR in Excel

D. The names reorder but the sales stay where they were, so rows no longer match

Sorting one column on its own breaks every row. Click one cell inside the list and sort, or pick "Expand the selection".

• A. Excel refuses to sort until you select both columns: Excel warns you, but it lets you continue.
• B. Both columns sort together, so each name keeps its sales: That is what "Expand the selection" does. Continuing with the current selection sorts only column A.
• C. Column B sorts too, largest first: Nothing in column B moves.

Study: How to create a pivot table in Excel

C. Insert a PivotTable with Region in Rows and Sales in Values

A PivotTable (Insert > PivotTable) summarizes a list by category in a few clicks: one row per region, with total sales.

• A. View > Freeze Panes: Freeze Panes keeps headers visible while you scroll.
• B. Conditional Formatting on the Sales column: Conditional formatting changes how cells look. It does not summarize.
• D. Data > Remove Duplicates on the Region column: That deletes rows. It does not total anything.

Study: How to create a pivot table in Excel

B. Filter (Data > Filter), then pick East in the Region column

A filter hides the rows that do not match. Clear it and every row comes back.

• A. Data > Remove Duplicates: That permanently deletes repeated rows.
• C. Sort (Data > Sort) by Region: Sorting groups the East rows together but still shows every row.
• D. Data > Text to Columns: Text to Columns splits one column into several.

Study: How to create a pivot table in Excel

B. =$A4*D$1

$A keeps the hours column, and $1 keeps the rates row. Everything else moves with the copy, so one formula fills the whole grid.

• A. =$A4*B$1: B$1 locks only the row. Its column is free, so it moves from B to D.
• C. =A4*D1: Copying keeps every $ exactly where it was.
• D. =$A2*D$1: $A2 locks only the column. Its row is free, so it moves from 2 to 4.

Study: Lesson: Absolute references · Absolute vs relative cell references in Excel

A. B$1

The $ sits in front of the part it locks. In B$1 the row (1) is locked and the column (B) is free.

• B. $B$1: Both parts are locked, so the column never changes either.
• C. $B1: This locks the column and lets the row move: the opposite.
• D. B1: Nothing is locked, so the row changes when you copy down.

Study: Lesson: Absolute references · Absolute vs relative cell references in Excel

B. 620

The start ($B$2) is locked and the end (B2) moves, so in C6 the formula is =SUM($B$2:B6): the total so far, 620.

• A. 200: That is what you get without the $ on the start of the range: both ends move, so each row adds only itself.
• C. 120: With both ends locked, every row adds only B2.
• D. 420: That is the running total in C5. In C6 the end of the range has moved to B6.

Study: Lesson: Absolute references · Absolute vs relative cell references in Excel

C. $A$1

F4 adds $ to both parts first, then cycles through the mixed versions. On a Mac, use Cmd+T.

• A. A$1: That is the second press. F4 cycles $A$1, A$1, $A1, then back to A1.
• B. $A1: That is the third press.
• D. It stays A1 and the cell is locked: F4 changes the reference while you edit a formula. Locking cells is done with sheet protection.

Study: Lesson: Absolute references · Absolute vs relative cell references in Excel

B. 8.5

AVERAGE ignores text and empty cells in a range: (8 + 7 + 10 + 9) / 4 = 8.5.

• A. 6.8: COUNTA counts "n/a" as an entry, so this divides by 5 and drags the average down.
• C. 34: That is the total of the scores (34).

Study: Lesson: Counting · COUNT vs COUNTA vs COUNTBLANK

C. =COUNTA(B2:B7)

COUNTA counts every cell that is not empty, numbers and text alike.

• A. =COUNT(B2:B7) (returns 4): COUNT only counts numbers, so it misses the "n/a" answer.
• B. =COUNTBLANK(B2:B7) (returns 1): COUNTBLANK counts the empty cells: the people who did not answer.
• D. =ROWS(B2:B7) (returns 6): ROWS counts every row in the range, answered or not.

Study: Lesson: Counting · COUNT vs COUNTA vs COUNTBLANK

C. B

Nested IFs are checked in order. 84 fails the 90 test, passes the 80 test, and IF returns "B". Put the highest cutoff first.

• A. C: That is the same tests in the wrong order. IF stops at the first TRUE test, and 84 >= 70 is TRUE, so everyone over 70 gets a C.
• B. FALSE: An IF with no value_if_false returns FALSE when the test fails, a sign the last argument is missing.
• D. F: F is returned only when every test fails.

Study: Lesson: AND and OR · How to write a nested IF in Excel

B. Escalate

OR is TRUE when at least one test is TRUE. The priority is High, so the ticket escalates.

• A. #VALUE!: OR can mix text and number tests.
• C. TRUE: OR returns TRUE here, and IF turns it into "Escalate".
• D. Queue: AND needs both tests to be TRUE. The ticket has only been open 2 days.

Study: Lesson: AND and OR · How to use IF with AND and OR in Excel

C. 2

COUNTIFS counts rows where every condition is true: Ana (1,200) and Cara (1,430). Fay is East but under 1,000.

• A. 2630: SUMIFS adds those sales (2,630) instead of counting the rows.
• B. 6: Adding two COUNTIFs counts rows that pass either test, and rows that pass both are counted twice.
• D. 3: That counts every East rep (3) and ignores the sales condition.

Study: Lesson: Multiple conditions · SUMIF with multiple criteria in Excel (SUMIFS)

A. =SUMIFS(C2:C8,B2:B8,"East",C2:C8,">1000")

SUMIFS puts the range to add first, then pairs of criteria range and criteria. SUMIF puts the range to add last: easy to mix up.

• B. =COUNTIFS(B2:B8,"East",C2:C8,">1000") (returns 2): COUNTIFS counts the matching rows instead of adding their sales.
• C. =SUMIF(C2:C8,">1000",C2:C8) (returns 3730): That adds every sale over 1,000 in any region.
• D. =SUMIF(B2:B8,"East",C2:C8) (returns 3270): That adds every East sale and ignores the over-1,000 condition.

Study: Lesson: Multiple conditions · SUMIF vs SUMIFS: what is the difference?

B. 3

">"&F1 builds the text ">1000", so COUNTIF counts sales above the target: Ana, Cara and Eli.

• A. 0: Inside quotes, F1 is just the text "F1", not the cell, so nothing matches. Join the operator to the cell with &.
• C. 4: >= also counts Gus, who sold exactly 1,000.
• D. 1: With no operator, COUNTIF counts cells equal to 1,000.

Study: Lesson: Multiple conditions · SUMIF with multiple criteria in Excel (SUMIFS)

A. 3

* stands for any characters, so "*Ltd*" matches Ltd anywhere in the name: two that end with it and one that starts with it.

• B. 1: A * only at the end means "starts with Ltd".
• C. 0: Without wildcards COUNTIF looks for cells that are exactly "Ltd".
• D. 2: A * only at the start means "ends with Ltd", which misses Ltd Lending Group.

Study: Lesson: Multiple conditions · SUMIF with multiple criteria in Excel (SUMIFS)

B. 59

XLOOKUP(what, where to look, what to return) finds P-104 in A2:A6 and returns the matching cell from C2:C6.

• A. #VALUE!: The lookup and return ranges must be the same size; when they are not, XLOOKUP shows #VALUE!.
• C. 48: The return range starts on the header row, so every answer comes from one row too high.
• D. Webcam: The return range B2:B6 is the Product column, so that returns the name.

Study: Lesson: VLOOKUP · VLOOKUP vs XLOOKUP

C. Not listed

XLOOKUP's if_not_found argument replaces the #N/A, without wrapping the formula in IFNA.

• A. 42: XLOOKUP matches exactly by default, so it never falls back to a nearby row.
• B. 0: XLOOKUP does not return 0 for a missing value.
• D. #N/A: That is what XLOOKUP returns without the 4th argument. The 4th argument is what to show when nothing is found.

Study: Lesson: VLOOKUP · VLOOKUP vs XLOOKUP

D. 48

MATCH finds P-103 in position 3 of A2:A6; INDEX returns the 3rd value of C2:C6. The 0 means exact match.

• A. Monitor stand: INDEX reads from the Product column here, so it returns the name.
• B. 59: MATCH counts from A1 here, so the position is one higher than INDEX expects. Both ranges must start on the same row.
• C. 3: MATCH on its own returns the position, 3.

Study: Lesson: VLOOKUP · How to use INDEX MATCH in Excel

D. Cara

Leaving out the 4th argument means TRUE, approximate match. On a sorted list it returns the row for the largest value at or below 1045. For IDs, always use FALSE.

• A. Dev: Approximate match never rounds up. It takes the largest ID that is not bigger than 1045.
• B. #N/A: That is what FALSE gives. Without it, VLOOKUP does an approximate match and quietly returns a near row.
• C. 1040: Column 1 shows the ID it actually landed on: 1040, not 1045.

Study: Lesson: VLOOKUP · VLOOKUP not working: 7 causes and fixes

B. 305

The column number counts from the first column of the table you give. In B2:D4, Name is 1, Dept is 2 and Ext is 3.

• A. #N/A: With the table starting at A, VLOOKUP looks for Ben among the IDs and finds nothing: #N/A.
• C. #REF!: The column number counts from the table's first column (B), not from A. B2:D4 has only 3 columns, so 4 gives #REF!.
• D. Finance: Column 2 of the table B2:D4 is Dept.

Study: Lesson: VLOOKUP · VLOOKUP vs XLOOKUP

A. 2041

MID(text, start, length) returns 4 characters starting at character 5: I-N-V-dash are 1 to 4, so it returns 2041.

• B. -204: Starting at character 4 picks up the dash: "-204".
• C. INV-: LEFT counts from the start: "INV-".
• D. 1-EU: RIGHT counts from the end: "1-EU".

Study: Lesson: Slicing text · How to extract text before a character in Excel

C. 9

TRIM removes spaces at the start and end and leaves single spaces between words: "Ana Lopez" is 9 characters.

• A. 8: SUBSTITUTE removes every space, including the one between the names.
• B. 10: TRIM also squeezes the double space between words down to one.
• D. 12: Without TRIM, every space is counted: 12 characters.

Study: Lesson: Slicing text · How to remove spaces in Excel (extra or all spaces)

D. Maria

FIND finds the space at position 6, and LEFT takes the 5 characters before it.

• A. 6: FIND on its own returns the position of the space: 6.
• B. Maria L: +1 takes two characters too many: "Maria L".
• C. Lopez: That is the formula for the last name.

Study: Lesson: Slicing text · How to separate first and last name in Excel

B. 10

NETWORKDAYS counts Monday to Friday days between two dates, including both ends: two full work weeks.

• A. 11: Subtracting counts every day, weekends included, and leaves out the start day.
• C. 8: NETWORKDAYS counts both the start and the end date.
• D. 12: That counts every calendar day from start to end, weekends included.

Study: Lesson: Date basics · How to count workdays between two dates in Excel

C. Mar 31, 2026

EOMONTH(date, months) returns the last day of the month that many months away. 0 means the same month.

• A. Feb 28, 2026: -1 goes back to the end of the previous month.
• B. Apr 30, 2026: 1 moves one month ahead: the end of April.
• D. Apr 1, 2026: Adding 1 to the end of the month gives the first day of the next month.

Study: Lesson: Date basics · How to find the last day of the month in Excel

B. 6

DATEDIF with "y" counts complete years only. August 2019 to March 2026 is 6 full years.

• A. 78: "m" counts full months (78), not years.
• C. 7: Subtracting the years counts 2019 to 2026 as 7, even though the 7th anniversary is not until August.
• D. 2390: Subtracting dates gives the days between them (2,390).

Study: Lesson: Date basics · How to calculate years of service in Excel

D. 2349

Formulas always use the full stored value, 2.349. To calculate with the shown value, round it with ROUND first.

• A. 2300: That rounds to one decimal first.
• B. 2.35: Formulas use the stored value, and multiplying by 1,000 changes it.
• C. 2350: You would get 2,350 only if the value itself were rounded. A number format changes how a cell looks, not what it holds.

Study: Lesson: MEDIAN and ROUND · ROUND vs ROUNDUP vs ROUNDDOWN in Excel

D. 86.4

A price after an 8% increase is 108% of the old price: 80 * 1.08 = 86.40.

• A. 80.08: 8% is stored as 0.08, so this adds 8 cents.
• B. 73.6: That takes 8% off instead of adding it.
• C. 6.4: That is the increase on its own (6.40).

Study: Lesson: Percent change · How to calculate percent change in Excel

D. #VALUE!

Math operators like + need numbers. Text that cannot be read as a number gives #VALUE!.

• A. #N/A: Typed "N/A" is ordinary text, not the #N/A error value. #N/A comes from lookups that find nothing.
• B. 500: That is what SUM gives: it skips text in a range instead of erroring. The + operator does not.
• C. #NAME?: #NAME? is about the formula itself, such as a misspelled function. This formula is fine; the data is the problem.

Study: Lesson: Reading errors · How to fix the #VALUE! error in Excel

B. 30

SUM ignores text, even text that looks like a number. Convert it (Data > Text to Columns, or the warning icon > Convert to Number) and the total becomes 42.

• A. 42: That is what + gives: it converts "12" to a number. SUM skips text in a range, which is why the two disagree.
• C. #VALUE!: SUM does not error on text in a range. It skips it silently, which is why this is easy to miss.
• D. 2: COUNT shows how many real numbers there are (2), a quick way to spot the text one.

Study: Lesson: Reading errors · How to convert numbers stored as text in Excel

C. Every row, including the ones the filter hides

SUM ignores filters. Use =SUBTOTAL(9,C2:C200), or a Table's Total Row, to add only the visible rows.

• A. Nothing until you clear the filter: SUM keeps working while a filter is on.
• B. Only the hidden rows: No function adds only the hidden rows.
• D. Only the rows you can see: That is SUBTOTAL(9, C2:C200) or AGGREGATE. SUM includes filtered-out rows.

Study: How to sum only visible cells in Excel

D. =SUBTOTAL(9,C2:C200)

SUBTOTAL with function number 9 sums, and it always leaves out rows hidden by a filter.

• A. =SUMIF(C2:C200,"visible"): SUMIF tests cell values. It cannot tell whether a row is hidden.
• B. =SUM(C2:C200): SUM adds every row in the range, hidden or not.
• C. =COUNTA(C2:C200): COUNTA counts filled cells; it does not add them.

Study: How to sum only visible cells in Excel

C. Data > Sort: sort by Region, then Add Level and sort by Sales, Largest to Smallest

The Sort dialog applies levels top to bottom: first by Region, and within each region by Sales.

• A. Sort by Sales, then filter by Region: A filter shows one region at a time. It does not group them.
• B. Create a PivotTable: A PivotTable summarizes the rows; it does not reorder the list itself.
• D. Sort by Region A to Z; the sales inside each region sort themselves: A one-level sort orders by that column only. Rows inside each region keep whatever order they had.

Study: How to sort by multiple columns in Excel

A. 105

The first MATCH finds the row (West is 2nd), the second finds the column (Q3 is 3rd), and INDEX returns where they cross.

• B. 3: That is only the column position, 3.
• C. 80: INDEX takes the row number first, then the column. Swapped, it returns North in Q2.
• D. 130: The column MATCH starts at A1 but INDEX starts at B, so it lands one column to the right (Q4).

Study: Lesson: VLOOKUP · How to do a two-way lookup in Excel

C. 3%

match_mode -1 means "exact match, or the next smaller value": 7,200 falls in the tier that starts at 5,000.

• A. 5%: match_mode 1 takes the next larger value, which is the 10,000 tier.
• B. #N/A: XLOOKUP matches exactly by default. 7,200 is not in column D, so it shows #N/A.
• D. 2%: 2% is the tier from 0 to 4,999.

Study: Lesson: VLOOKUP · How to look up which range a number falls in

A. 95

search_mode -1 searches from the bottom up, so XLOOKUP returns the last match: Ana's most recent order.

• B. 150: MAXIFS returns Ana's largest order, not her latest.
• C. 120: By default XLOOKUP searches from the top and returns Ana's first order.
• D. 365: SUMIF adds all of Ana's orders.

Study: Lesson: VLOOKUP · How to find the last value in a column in Excel

B. 110

Each test gives TRUE or FALSE per row; multiplying them gives 1 only where both are TRUE. XLOOKUP then finds that 1.

• A. 98: That finds the first West row and ignores the quarter.
• C. 208: That adds both West quarters.
• D. 135: That finds the first Q2 row and ignores the region.

Study: Lesson: VLOOKUP · XLOOKUP with multiple criteria

B. #N/A

The number 1040 and the text "1040" are different values, so the exact match fails. Convert the IDs to numbers, or look up TEXT(value,"0").

• A. #VALUE!: A type mismatch in a lookup means "not found", not a #VALUE! error.
• C. Cara: That works, because "1040" in quotes is text, like the IDs. The number 1040 never equals the text "1040".
• D. 0: A failed exact-match lookup is #N/A, never 0.

Study: Lesson: VLOOKUP · VLOOKUP not working: 7 causes and fixes

C. 650

Two conditions on the same date column make a range: on or after March 1 and on or before March 31. The rows do not need to be sorted.

• A. 0: Inside quotes E1 and E2 are just text, so no date matches. Join the operator to the cell with &.
• B. 380: > and < leave out the first and last day of the month.
• D. 940: That adds every row, February and April included.

Study: Lesson: Multiple conditions · SUMIFS between two dates in Excel

C. 268

SUMPRODUCT multiplies row by row (4*12 + 10*3 + 2*45 + 5*20) and adds the results: the order total, with no helper column.

• A. 101: That adds quantities and prices together, which means nothing.
• B. 1680: That multiplies the two totals, not each row's quantity by its own price.
• D. 420: The average price times the total quantity ignores which items sold more.

Study: Lesson: Multiple conditions · How to use SUMPRODUCT in Excel

A. 4

Two conditions on the same column make a band: at least 70 and below 80. Getting the = on each edge right is the whole question.

• B. 7: With no upper limit it counts every score from 70 up.
• C. 5: <=80 lets the score of 80 in.
• D. 3: >70 leaves out the score of exactly 70.

Study: Lesson: Multiple conditions · COUNTIFS between two numbers in Excel

B. #DIV/0!

With no third argument, AVERAGEIF averages the matching cells in B, which are words. No numbers means dividing by zero: #DIV/0!.

• A. 3270: That is the East total, not an average.
• C. 1090: That is the intended formula, with the range to average as the third argument.
• D. 0: Without the average range, AVERAGEIF averages the cells it tested. They are text, so there is nothing to divide by.

Study: Lesson: Multiple conditions · How to use AVERAGEIF and AVERAGEIFS in Excel

D. #N/A

IFS returns the value for the first TRUE test. When no test is TRUE it returns #N/A, so end it with TRUE and a default value.

• A. Bronze: That is the fix: a final TRUE pair catches everything else.
• B. FALSE: That is the nested IF version: with no final value it returns FALSE. IFS has no default at all.
• C. 0: IFS never returns 0 on its own.

Study: Lesson: AND and OR · IFS vs nested IF vs SWITCH in Excel

B. Review

AND fails because quality is 85%, below 90%. The inner IF then checks sales alone, which pass, so the result is "Review".

• A. FALSE: Each IF here has both a value_if_true and a value_if_false, so the result is always one of the three labels.
• C. Bonus: With OR, the sales test alone is enough for a bonus.
• D. None: The inner IF runs because AND failed, and sales are over 5,000, so it returns "Review".

Study: Lesson: AND and OR · How to use IF with AND and OR in Excel

A. Ana, Ben, Dev

TEXTJOIN(delimiter, ignore_empty, text) puts ", " between the values. TRUE skips the empty A4.

• B. Ana,Ben,Dev: The delimiter is exactly what you type: "," has no space after the comma.
• C. AnaBenDev: CONCAT has no delimiter, so the names run together.
• D. Ana, Ben, , Dev: FALSE keeps empty cells, which leaves a doubled comma.

Study: Lesson: Slicing text · CONCAT vs TEXTJOIN vs &: which should you use?

D. Due: 46112

A date is stored as a serial number (46112 is March 31, 2026). & joins that number. Wrap the date in TEXT to keep it readable.

• A. Due: Mar 31: That is the fix: TEXT turns the date into formatted text before joining.
• B. #VALUE!: Joining text and a number is allowed: Excel turns the number into text.
• C. Due: Mar 31, 2026: Joining with & uses the stored value. The date format belongs to B2, not to the value.

Study: Lesson: Slicing text · How to concatenate dates in Excel

C. northwind.com

FIND locates the @ (position 12). MID starts one character later and takes up to 100 characters, which is everything to the end.

• A. @northwind.com: Without +1 the result starts at the @ sign itself.
• B. orthwind.com: RIGHT counts from the end, but FIND counts from the start, so the length is off and the first letter is cut.
• D. maria.lopez: That is the part before the @, the user name.

Study: Lesson: Slicing text · How to extract the domain from an email address in Excel

A. 9

NETWORKDAYS counts Monday to Friday, both ends included (10 days), and the third argument removes holidays: 9.

• B. 10: Without the holiday list, April 3 counts as a working day.
• C. 12: That counts every calendar day, weekends included.
• D. 11: Plain subtraction counts weekends and the holiday, and leaves out the start day.

Study: Lesson: Date basics · WORKDAY vs NETWORKDAYS: what is the difference?

A. 46112

Excel stores dates as numbers of days since 1900. In a General cell you see the number itself: 46112 is March 31, 2026.

• B. Mar 31, 2026: That is what B2 shows once you format it as a date. EOMONTH returns a serial number.
• C. #VALUE!: The formula is fine. Only the display is surprising.
• D. 31: EOMONTH returns the whole date, not the day. DAY(EOMONTH(A2,0)) gives 31.

Study: Lesson: Date basics · How to find the last day of the month in Excel

C. Mar 1, 2026

EOMONTH(A2,-1) is the last day of last month. One day later is the first day of this month.

• A. Apr 1, 2026: The end of this month plus one day is the first day of next month.
• B. Feb 27, 2026: Subtracting goes the wrong way: the day before the end of February.
• D. Feb 28, 2026: That is the last day of the previous month, one day short.

Study: Lesson: Date basics · How to get the first day of the month in Excel

B. 1200

A negative number of digits rounds to the left of the decimal point: -2 means the nearest hundred.

• A. 1300: ROUNDUP always goes away from zero, to 1,300.
• C. 1234.57: A positive 2 rounds to 2 decimal places.
• D. 1230: -1 rounds to the nearest ten.

Study: Lesson: MEDIAN and ROUND · ROUND vs ROUNDUP vs ROUNDDOWN in Excel

A. 18.99

ROUNDUP(18.2, 0) is 19, and 19 - 0.01 is 18.99.

• B. 17.99: ROUND goes to the nearest dollar, which is down to 18 here.
• C. 18.19: 2 means two decimal places, and 18.2 already has fewer.
• D. 19: Taking the cent off first and then rounding up lands back on a whole dollar.

Study: Lesson: MEDIAN and ROUND · ROUND vs ROUNDUP vs ROUNDDOWN in Excel

A. 25%

Going from 4% to 5% is up 1 percentage point, which is a 25% increase. Reports mix these up; say which one you mean.

• B. 1%: That is the change in percentage points (1 point), not the percent change.
• C. 125%: That is this year as a share of last year (125%).
• D. 20%: Percent change divides by the old value, not the new one.

Study: Lesson: Percent change · How to calculate percent difference in Excel

A. =A2/(1+B2)

The new price is 120% of the old one, so divide by 1.2: 120 / 1.2 = 100.

• B. =A2*(1-B2) (returns 96): 20% of the new price is more than 20% of the old one, so taking it off goes too far (96).
• C. =A2-B2 (returns 119.8): 20% is stored as 0.2, so this takes 20 cents off.
• D. =A2*B2 (returns 24): That is 20% of the new price.

Study: Lesson: Percent change · How to calculate percent change in Excel

A. 10.0%

Compound annual growth is (end / start) ^ (1 / years) - 1. Growing 10% a year for 5 years turns 10,000 into 16,105.10.

• B. 12.2%: Dividing total growth by the years ignores compounding, so it overstates the yearly rate.
• C. 983.5%: Raising to the 5th power instead of the 1/5th compounds the growth again.
• D. 61.1%: That is the total growth over all 5 years.

Study: Lesson: Percent change · How to calculate CAGR in Excel

D. It also hides real mistakes, such as a wrong range or a typo, and shows 0 as if it were a price

Prefer IFNA (or XLOOKUP's if_not_found) to catch only "not found". Other errors usually mean the formula itself needs fixing.

• A. None: a 0 is always better than an error: A 0 looks like real data. A visible error tells you something is wrong.
• B. IFERROR only catches #N/A, so other errors still show: That describes IFNA. IFERROR catches every error type, which is the problem.
• C. IFERROR makes the lookup slower than VLOOKUP alone: Speed is not the issue. Hidden errors are.

Study: Lesson: Reading errors · How to use IFERROR in Excel

B. Something is already typed in the cells the results need to spill into

Dynamic array formulas need empty cells below or beside them. Clear what is in the way and the results appear.

• A. The list has no duplicates to remove: UNIQUE simply returns every value then. It does not error.
• C. A2:A50 contains blank cells: Blank cells give a 0 in the results, not #SPILL!.
• D. UNIQUE only works inside an Excel Table: UNIQUE works on any range.

Study: Lesson: Reading errors · #SPILL! error in Excel

A. =SUBTOTAL(109,B2:B100)

SUBTOTAL's 100-series codes (109 for sum) skip both filtered and manually hidden rows. The 1 to 11 codes skip only filtered rows.

• B. =SUM(B2:B100): SUM adds every row, hidden or not.
• C. =SUMIF(B2:B100,"<>hidden"): SUMIF checks values, not whether a row is hidden.
• D. =SUBTOTAL(9,B2:B100): Function 9 ignores rows hidden by a filter but still adds rows hidden by hand.

Study: How to sum only visible cells in Excel

C. Refresh it, and change its data source if the source is a plain range rather than a Table

Build PivotTables on an Excel Table (Ctrl+T): the Table grows with new rows, so a refresh is all you need.

• A. Sort the new rows to the top of the data: Row order does not matter to a PivotTable.
• B. Delete the PivotTable and build a new one: Refresh, and Change Data Source if needed, keeps your layout.
• D. Nothing: PivotTables update by themselves: A PivotTable shows a snapshot until you refresh it (PivotTable Analyze > Refresh).

Study: How to create a pivot table in Excel

B. Row 3, the first one

Remove Duplicates keeps the first row of each value from the top. To keep the newest record, sort newest first before you run it.

• A. Neither: both rows are deleted: One copy of every value is always kept.
• C. Both, until you sort the list first: Sorting is not needed. It does change which row comes first, so sort before removing if you want to keep, say, the newest.
• D. Row 9, the last one: Remove Duplicates keeps the first occurrence and deletes the ones below it.

Study: How to remove duplicates in Excel

A. =SUM(B2:B12), now in B13

Excel adjusts references when you insert rows: a range that spans the new row grows to include it.

• B. =SUM(B2:B11) plus a #REF! warning: Inserting never breaks references. Deleting the cells a formula points at does.
• C. =SUM(B2:B5,B7:B12): Excel keeps one continuous range and simply makes it longer.
• D. =SUM(B2:B11), still in B12: The new row is inside the range, so the range grows and the total itself moves down a row.

Study: Lesson: Absolute references · Absolute vs relative cell references in Excel

B. It shows #REF!, because the cell it pointed at no longer exists

Deleting a cell a formula uses turns that reference into #REF!. Clear the contents instead of deleting, or update the formula first.

• A. It shifts to =B2*D2: References only shift when cells move. The cell itself was deleted.
• C. It becomes =B2 and keeps working: Excel does not drop parts of a formula. The deleted reference becomes #REF! inside the formula.
• D. Excel blocks the delete: Excel deletes the column and leaves the formula broken. Undo (Ctrl+Z) is the quickest fix.

Study: Lesson: Absolute references · #REF! error in Excel