Excel test questions and answers

Question 1 Task

The monthly sales are in B2:B4. Enter a formula in B5 that totals them.

Answer: =SUM(B2:B4) in B5 returns 3530. SUM adds every number in the range. Type it as a formula so the total follows the data, rather than typing 3530.

See also: SUM function guide

Question 2 Multiple choice

In D2 you write =B2*C1 and fill the formula down. In D3 Excel shows =B3*C2. What change keeps the reference to C1 fixed?

1. Retype the formula in every row by hand
2. Write C1$ instead of C1
3. Change C1 to $C$1, for example by pressing F4 with the cursor on it
4. Move the formula to column C

Answer: C) Change C1 to $C$1, for example by pressing F4 with the cursor on it. The dollar signs lock the column and the row, so the reference stays on C1 when the formula is copied. A dollar sign goes in front of the part it locks, so C1$ is not a valid form.

See also: Absolute vs relative references

Question 3 Task

Last year's figure is in A2 and this year's is in B2. Show the percent change to one decimal place.

Answer: =(B2-A2)/A2 in C2 returns 12.5%. Subtract the old value from the new, divide by the old value, then format the cell as a percent. The division is by last year's figure, not this year's.

See also: ROUND function guide

Question 4 Multiple choice

What does =2+3*4 return?

1. 20
2. 24
3. 9
4. 14

Answer: D) 14. Multiplication happens before addition, so 3*4 is 12 and 12 plus 2 is 14. Brackets change the order: =(2+3)*4 gives 20.

See also: SUM function guide

Question 5 Task

Round the value in A2 to the nearest hundred.

Answer: =ROUND(A2,-2) in B2 returns 1200. A negative number of digits rounds to the left of the decimal point: -1 is tens, -2 is hundreds. The value is 1,234.567, which is closer to 1,200 than to 1,300.

See also: ROUND function guide, ROUNDDOWN function guide

Question 6 Task

The SKU in B5 is known and the product name is in column A, to the left of the SKU. Return the product name in C5.

Answer: =INDEX(A2:A4,MATCH(B5,B2:B4,0)) in C5 returns Ink. MATCH finds the position of P-30 in B2:B4, which is 3, and INDEX returns the third name in A2:A4. VLOOKUP cannot look to the left, but this pair can. The 0 asks for an exact match.

See also: INDEX function guide, MATCH function guide

Question 7 Multiple choice

A VLOOKUP returns #N/A even though the ID can be seen in the first column of the table. Which cause should you check first?

1. The column number is 2
2. A trailing space, or an ID stored as text on one side and as a number on the other
3. Calculation is set to Automatic
4. The table sits on the same sheet

Answer: B) A trailing space, or an ID stored as text on one side and as a number on the other. Values that look equal but differ in a space or in data type do not match. Test with LEN on both keys, and clean with TRIM or VALUE.

See also: The #N/A error, TRIM function guide

Question 8 Task

Look up the code in A5 in A2:A4 and return its rate. If the code is not listed, show No rate.

Answer: =XLOOKUP(A5,A2:A4,B2:B4,"No rate") in B5 returns No rate. The fourth argument of XLOOKUP is what to show when nothing matches. D is not in the list, so the message appears instead of #N/A. XLOOKUP needs Microsoft 365 or Excel 2021.

See also: XLOOKUP function guide, IFNA function guide

Question 9 Multiple choice

Which function returns the position of a value within a list, rather than the value itself?

1. INDEX
2. FIND
3. MATCH
4. LOOKUP

Answer: C) MATCH. MATCH returns a position, such as 3 for the third item. INDEX does the opposite and returns the item at a position. FIND does the same job for characters inside text.

See also: MATCH function guide

Question 10 Task

The months run across row 1. Return the Sales figure for the month typed in A4.

Answer: =HLOOKUP(A4,B1:D3,2,FALSE) in B4 returns 120. HLOOKUP searches the top row of the table B1:D3 for Feb and returns the value from row 2 of the table, which is the Sales row. Row numbers count from the first row of the table you give it.

See also: HLOOKUP function guide

Question 11 Task

Return the domain of the email address in A2, the part after the at sign.

Answer: =RIGHT(A2,LEN(A2)-FIND("@",A2)) in B2 returns acme.com. FIND locates the at sign at position 4. The address has 12 characters, so RIGHT takes the last 12 minus 4, which is 8: acme.com.

See also: RIGHT function guide, FIND function guide

Question 12 Multiple choice

Cell A5 holds the text " Kim " with a space before and after. Which formula returns Kim with the spaces removed?

1. =TRIM(A5)
2. =CLEAN(A5)
3. =PROPER(A5)
4. =LEN(A5)

Answer: A) =TRIM(A5). TRIM removes leading and trailing spaces and collapses repeats between words. CLEAN removes non-printing characters, PROPER changes capitals, and LEN counts characters.

See also: TRIM function guide

Question 13 Task

The birth date is in A2. How many complete years old is the person on March 1, 2026?

Answer: =DATEDIF(A2,DATE(2026,3,1),"y") in B2 returns 35. DATEDIF with the "y" unit counts whole years. The person turns 36 on June 15, 2026, so on March 1 they are still 35. Dividing the day count by 365 can be off by one near a birthday.

See also: DATEDIF function guide

Question 14 Multiple choice

What does =EDATE(DATE(2026,1,31),1) return?

1. March 3, 2026
2. February 28, 2026
3. February 31, 2026
4. An error

Answer: B) February 28, 2026. EDATE moves a date by whole months and stays inside the target month. There is no February 31, so it returns the last day, February 28 in 2026.

See also: EDATE function guide, EOMONTH function guide

Question 15 Task

The date in A2 is March 15, 2026. Show it as the month name and year, such as Mar 2026.

Answer: =TEXT(A2,"mmm yyyy") in B2 returns Mar 2026. TEXT applies a format code and returns text. mmm gives the three-letter month and yyyy the four-digit year. The result is text, so it cannot be used in date arithmetic.

See also: TEXT function guide

Question 16 Task

Convert the score in A2 to a grade: A for 90 and above, B for 80 to 89, C for 70 to 79, otherwise F.

Answer: =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C","F"))) in B2 returns C. Test the strictest band first. 72 is not 90 or more, not 80 or more, but is 70 or more, so the formula returns C. In Microsoft 365, IFS can write the same rule without nesting.

See also: IF function guide, IFS function guide

Question 17 Multiple choice

A1:A5 holds 10, the text n/a, an empty cell, 25 and 7. What do COUNT(A1:A5) and COUNTA(A1:A5) return?

1. 4 and 5
2. 3 and 5
3. 3 and 4
4. 4 and 4

Answer: C) 3 and 4. COUNT counts only numbers: 10, 25 and 7 make 3. COUNTA counts every non-empty cell, so it adds the text n/a for 4. The empty cell is counted by neither.

See also: COUNT function guide, COUNTA function guide

Question 18 Task

Average the Sales for the East region and the Pen product only.

Answer: =AVERAGEIFS(C2:C5,A2:A5,"East",B2:B5,"Pen") in E2 returns 120. AVERAGEIFS starts with the range to average, then takes pairs of range and condition. Rows 2 and 5 are East and Pen, so the average of 100 and 140 is 120.

See also: AVERAGEIFS function guide

Question 19 Multiple choice

Which formula counts the orders in B2:B9 that are over 50?

1. =COUNTIF(B2:B9,>50)
2. =COUNT(B2:B9>50)
3. =SUMIF(B2:B9,">50")
4. =COUNTIF(B2:B9,">50")

Answer: D) =COUNTIF(B2:B9,">50"). The condition is text, so it goes in quotation marks with the operator inside. SUMIF would add the amounts rather than count them.

See also: COUNTIF function guide

Question 20 Task

Return the second-lowest score in A2:A6.

Answer: =SMALL(A2:A6,2) in B2 returns 81. SMALL takes the range and a position counted from the bottom. The scores sort to 75, 81, 88, 92 and 96, so position 2 is 81.

See also: SMALL function guide, LARGE function guide

Question 21 Multiple choice

A cell in a wide-number column shows ##### instead of a number. What is the right fix?

1. Retype the number with fewer digits
2. Widen the column, for example by double-clicking the column border
3. Change the formula to use ROUND
4. Delete the cell and enter the value again

Answer: B) Widen the column, for example by double-clicking the column border. The value is fine and the column is too narrow to show it. AutoFit, or a drag of the column border, brings the number back. Changing the number or the formula would change the data.

See also: The ##### display, How to AutoFit columns

Question 22 Multiple choice

Which keyboard shortcut turns the Filter drop-down arrows on and off in Excel for Windows?

1. Ctrl+F
2. Ctrl+T
3. Ctrl+Shift+L
4. Alt+F4

Answer: C) Ctrl+Shift+L. Ctrl+Shift+L toggles the filter arrows on the selected table. Ctrl+F opens Find, Ctrl+T creates a Table, and Alt+F4 closes the program.

See also: How to filter by a condition

Question 23 Task

How many different customers appear in A2:A6?

Answer: =COUNTA(UNIQUE(A2:A6)) in C2 returns 3. UNIQUE returns Ana, Kim and Raj once each, and COUNTA counts that list. UNIQUE needs Microsoft 365 or Excel 2021. In an older version, copy the column and use Remove Duplicates, then count what is left.

See also: UNIQUE function guide, How to remove duplicates

Question 24 Multiple choice

A formula that used to work now shows =#REF!*C2 in the formula bar. What happened?

1. A number is stored as text
2. The workbook is in manual calculation mode
3. The formula uses a function Excel does not know
4. A cell or column it pointed to was deleted

Answer: D) A cell or column it pointed to was deleted. When a cell a formula refers to is deleted, Excel replaces the reference with #REF!. Undo with Ctrl+Z if you can, otherwise retype the reference by clicking the right cell.

See also: The #REF! error

Question 25 Task

Total the sales for every product whose name starts with P.

Answer: =SUMIF(A2:A5,"P*",B2:B5) in D2 returns 70. The asterisk stands for any run of characters, so P* matches Pen, Pad and Paper. Their sales are 10, 20 and 40, which add to 70. Ink does not start with P.

See also: SUMIF function guide