Start free trial
Home
Website Designs Vcards Templates CV Templates
Profiles Pricing Blog FAQs Contact Features How it works
Resume builder Resume checker Cover letter generator Resume job match CTC to in-hand calculator PDF tools Image tools All 52 free tools
Resume templates Resume examples Resume format by country Cover letter format by country Cover letter examples Resume headline examples Profile summary examples Skills for your resume Website templates
Interview preparation Interview questions by job Skill tests Interview practice Readiness check Tell me about yourself Salary expectations guide
Log in Start free trial →
Job skill test · free, with answers

Excel Skills Test

This Excel skills test has 25 multiple-choice questions of the kind used in interviews for analyst, finance, accounts, HR, sales and marketing roles. It covers formulas (IF, COUNTIF, SUMIFS, VLOOKUP, XLOOKUP, INDEX/MATCH), absolute and relative references, pivot tables, shortcuts, text and date functions, and data cleaning. You get 20 minutes, and each answer shows the exact formula result.

  • 25 questions
  • 20 minutes
  • 10 easy · 10 medium · 5 hard

Topics: Formula basics · Cell references · Shortcuts · Math functions · Counting functions · Conditional sums · Logical functions · Lookup functions · Pivot tables · Data cleaning · Text functions · Dates

Start the test ↓ See answers
Answer key: all 25 questions with answers and explanations

  1. 1. Which character must a formula start with in Excel?

    1. A. #
    2. B. =
    3. C. @
    4. D. $

    Answer: B. =

    Every Excel formula starts with an equals sign, for example =A1+B1. Without it, Excel treats the entry as plain text or a number.

  2. 2. Cell C1 contains =A1*$B$1. You copy C1 and paste it into C2. What formula will C2 contain?

    1. A. =A1*$B$1
    2. B. =A2*$B$2
    3. C. =A2*$B$1
    4. D. =A2*B2

    Answer: C. =A2*$B$1

    A1 is a relative reference, so moving one row down changes it to A2. $B$1 is absolute (both column and row locked), so it stays $B$1. Result: =A2*$B$1.

  3. 3. Cell C3 contains =$A1+B$1. You copy C3 and paste it into D5 (one column right, two rows down). What formula will D5 contain?

    1. A. =$A3+C$1
    2. B. =$A1+C$1
    3. C. =$A3+B$3
    4. D. =$B3+C$1

    Answer: A. =$A3+C$1

    In $A1 the column is locked and the row is free, so it becomes $A3 (row +2). In B$1 the row is locked and the column is free, so it becomes C$1 (column +1). Result: =$A3+C$1.

  4. 4. While editing a formula in Excel for Windows, which key cycles the selected reference through $A$1, A$1, $A1 and A1?

    1. A. F2
    2. B. F9
    3. C. F5
    4. D. F4

    Answer: D. F4

    F4 toggles the reference type: A1 → $A$1 → A$1 → $A1 → back to A1. F2 enters edit mode, F5 opens Go To, and F9 recalculates.

  5. 5. A1 = 10, A2 = 20, A3 = the text "abc", A4 = 30. What does =SUM(A1:A4) return?

    1. A. #VALUE!
    2. B. 30
    3. C. 60
    4. D. 0

    Answer: C. 60

    SUM ignores text inside a range, so it adds only the numbers: 10 + 20 + 30 = 60. You would get #VALUE! only with a formula like =A1+A3 that adds text directly.

  6. 6. A1 = 5, A2 = the text "Pune", A3 is blank, A4 = 12, A5 = TRUE (typed as a logical value). What does =COUNT(A1:A5) return?

    1. A. 2
    2. B. 3
    3. C. 4
    4. D. 5

    Answer: A. 2

    COUNT counts only cells containing numbers. In a range it skips text, blanks and logical values like TRUE. So only 5 and 12 are counted: 2. (COUNTA would return 4.)

  7. 7. B2:B7 contain 45, 60, 50, 72, 38, 90. What does =COUNTIF(B2:B7,">50") return?

    1. A. 4
    2. B. 2
    3. C. 5
    4. D. 3

    Answer: D. 3

    Only values strictly greater than 50 count: 60, 72 and 90. The 50 itself is not greater than 50, so the result is 3. Use ">=50" to include it.

  8. 8. Region in A2:A6: North, South, North, East, North. Sales in B2:B6: 100, 200, 150, 300, 250. Month in C2:C6: Jan, Jan, Feb, Jan, Jan. What does =SUMIFS(B2:B6,A2:A6,"North",C2:C6,"Jan") return?

    1. A. 500
    2. B. 350
    3. C. 250
    4. D. 400

    Answer: B. 350

    SUMIFS adds sales only where both conditions are true. North and Jan together are row 2 (100) and row 6 (250). Row 4 is North but Feb, so it is left out. 100 + 250 = 350.

  9. 9. A1 = 40. What does =IF(A1>=40,"Pass","Fail") return?

    1. A. Fail
    2. B. TRUE
    3. C. Pass
    4. D. 40

    Answer: C. Pass

    The test A1>=40 checks “greater than or equal to 40”. Since 40 is equal to 40, the test is TRUE and IF returns the second argument, "Pass".

  10. 10. A1 = 62. What does =IF(A1>=75,"A",IF(A1>=50,"B","C")) return?

    1. A. A
    2. B. C
    3. C. FALSE
    4. D. B

    Answer: D. B

    The first test, 62>=75, is FALSE, so Excel moves to the inner IF. The second test, 62>=50, is TRUE, so the result is "B".

  11. 11. Cells A2:C5 hold this table (A = ID, B = Name, C = Department): A2: 101 | B2: Asha | C2: Sales A3: 102 | B3: Ravi | C3: HR A4: 103 | B4: Neha | C4: Finance A5: 104 | B5: Imran | C5: IT What does =VLOOKUP(103,A2:C5,3,FALSE) return?

    1. A. Neha
    2. B. Finance
    3. C. 103
    4. D. HR

    Answer: B. Finance

    VLOOKUP finds 103 in the first column of A2:C5 (row 4) and returns the value from the 3rd column of the table, column C. That is "Finance".

  12. 12. In =VLOOKUP(lookup_value, table, col_index, FALSE), what does FALSE as the last argument do?

    1. A. Finds an exact match only
    2. B. Finds the nearest smaller value
    3. C. Ignores upper and lower case
    4. D. Returns FALSE when nothing is found

    Answer: A. Finds an exact match only

    FALSE (or 0) forces an exact match; if the value is not found, VLOOKUP returns #N/A. TRUE or leaving it out gives an approximate match on sorted data, which causes wrong results on unsorted lists.

  13. 13. Cells A2:C5 hold this table (A = ID, B = Name, C = Department): A2: 101 | B2: Asha | C2: Sales A3: 102 | B3: Ravi | C3: HR A4: 103 | B4: Neha | C4: Finance A5: 104 | B5: Imran | C5: IT What does =VLOOKUP(105,A2:C5,2,FALSE) return?

    1. A. 0
    2. B. Imran
    3. C. #N/A
    4. D. #REF!

    Answer: C. #N/A

    105 is not in the ID column, and FALSE demands an exact match, so VLOOKUP returns #N/A (value not available). #REF! appears when the column number is larger than the table width.

  14. 14. Cells A2:C5 hold this table (A = ID, B = Name, C = Department): A2: 101 | B2: Asha | C2: Sales A3: 102 | B3: Ravi | C3: HR A4: 103 | B4: Neha | C4: Finance A5: 104 | B5: Imran | C5: IT What does =INDEX(C2:C5,MATCH("Imran",B2:B5,0)) return?

    1. A. 104
    2. B. 4
    3. C. Finance
    4. D. IT

    Answer: D. IT

    MATCH("Imran",B2:B5,0) finds Imran in the 4th position of B2:B5. INDEX(C2:C5,4) returns the 4th value of C2:C5, which is "IT".

  15. 15. What is a key advantage of INDEX/MATCH over VLOOKUP?

    1. A. It can return a value from a column to the left of the lookup column
    2. B. It works only on sorted data
    3. C. It can only return numbers
    4. D. It does not need a lookup value

    Answer: A. It can return a value from a column to the left of the lookup column

    VLOOKUP always searches the first column and returns from columns to its right. INDEX/MATCH searches any column and returns from any other, including one on the left. It also does not break when columns are inserted.

  16. 16. Cells A2:C5 hold this table (A = ID, B = Name, C = Department): A2: 101 | B2: Asha | C2: Sales A3: 102 | B3: Ravi | C3: HR A4: 103 | B4: Neha | C4: Finance A5: 104 | B5: Imran | C5: IT What does =XLOOKUP(110,A2:A5,B2:B5,"Not found") return?

    1. A. #N/A
    2. B. Not found
    3. C. Imran
    4. D. 0

    Answer: B. Not found

    110 is not in A2:A5. XLOOKUP’s fourth argument is the value to return when nothing matches, so the result is "Not found" instead of #N/A. XLOOKUP uses exact match by default.

  17. 17. Cells A2:C5 hold this table (A = ID, B = Name, C = Department): A2: 101 | B2: Asha | C2: Sales A3: 102 | B3: Ravi | C3: HR A4: 103 | B4: Neha | C4: Finance A5: 104 | B5: Imran | C5: IT What does =MATCH("Ravi",B2:B5,0) return?

    1. A. 3
    2. B. Ravi
    3. C. 2
    4. D. 102

    Answer: C. 2

    MATCH returns the position of the value within the range, not the worksheet row number. Ravi is the 2nd cell in B2:B5, so the result is 2 (even though Ravi is on row 3 of the sheet).

  18. 18. You have a sales table with Region, Product and Amount columns. In a pivot table, how do you show total Amount for each Region?

    1. A. Region in Values, Amount in Rows
    2. B. Region in Filters, Amount in Columns
    3. C. Both fields in Rows
    4. D. Region in Rows, Amount in Values (Sum)

    Answer: D. Region in Rows, Amount in Values (Sum)

    The field you want to group by (Region) goes in Rows, and the number you want to total (Amount) goes in Values, summarised as Sum. The pivot then shows one row per region with its total.

  19. 19. You added new rows at the bottom of the source data, but your pivot table does not show them. What should you do?

    1. A. Delete and rebuild the whole workbook
    2. B. Refresh the pivot table, after making sure its source range includes the new rows
    3. C. Press Ctrl+Z
    4. D. Sort the source data

    Answer: B. Refresh the pivot table, after making sure its source range includes the new rows

    Pivot tables do not update automatically. Use Refresh (PivotTable Analyze > Refresh or Alt+F5). If the source is a fixed range, extend it with Change Data Source. Formatting the source as an Excel Table makes new rows flow in on refresh.

  20. 20. A1 contains " Rahul Sharma " (extra spaces at the start, middle and end). What does =TRIM(A1) return?

    1. A. "RahulSharma"
    2. B. "Rahul Sharma"
    3. C. "Rahul Sharma"
    4. D. " Rahul Sharma "

    Answer: C. "Rahul Sharma"

    TRIM removes all leading and trailing spaces and reduces runs of spaces between words to a single space. The result is "Rahul Sharma". It does not remove the single space between words.

  21. 21. A list of customers has some rows repeated exactly. Which built-in Excel feature removes the repeated rows in one step?

    1. A. Data > Remove Duplicates
    2. B. Home > Clear > Clear Formats
    3. C. Data > Text to Columns
    4. D. Review > Protect Sheet

    Answer: A. Data > Remove Duplicates

    Data > Remove Duplicates deletes rows that repeat across the columns you select, keeping the first occurrence. Text to Columns splits data, and Clear Formats only removes formatting.

  22. 22. A1 contains the text "INV-7781". What does =MID(A1,5,4) return?

    1. A. "-778"
    2. B. "INV-"
    3. C. "781"
    4. D. "7781"

    Answer: D. "7781"

    MID(text, start, length) returns length characters starting at position start. Position 5 is the first 7 (I-N-V-hyphen take positions 1 to 4), and 4 characters from there give "7781".

  23. 23. A1 contains the date 10 March and B1 contains 25 March of the same year, both entered as real Excel dates. What does =B1-A1 return (with the cell formatted as General)?

    1. A. 14
    2. B. 15
    3. C. 16
    4. D. #VALUE!

    Answer: B. 15

    Excel stores dates as serial numbers, one per day, so subtracting two dates gives the number of days between them: 25 − 10 = 15.

  24. 24. A1:A3 contain 2, 3, 4 and B1:B3 contain 5, 6, 7. What does =SUMPRODUCT(A1:A3,B1:B3) return?

    1. A. 56
    2. B. 27
    3. C. 45
    4. D. 210

    Answer: A. 56

    SUMPRODUCT multiplies matching pairs and adds the results: (2 × 5) + (3 × 6) + (4 × 7) = 10 + 18 + 28 = 56.

  25. 25. What does =ROUND(1234.567,-2) return?

    1. A. 1234.57
    2. B. 1230
    3. C. 1200
    4. D. 1300

    Answer: C. 1200

    A negative second argument rounds to the left of the decimal point. -2 rounds to the nearest hundred. 1234.567 is closer to 1200 than to 1300, so the result is 1200.

How this test is scored

Each correct answer earns one mark and there is no negative marking. A score of 20 or more out of 25 shows the working Excel level most analyst, accounts and MIS roles expect.

On the result screen, below 50% means the topics need more practice, 50–75% is a good base to build on, and above 75% means you are ready for questions like these. These bands are for your own practice only.

How to prepare

  • Practise in a real sheet, not just on paper. Type each formula from this test, change the inputs, and watch how the result changes.
  • Master references first: know exactly what $A$1, A$1 and $A1 do when copied. Most formula errors in interviews come from this.
  • Be ready to explain when you would use VLOOKUP, XLOOKUP or INDEX/MATCH, and what #N/A means.
  • Build a pivot table from a raw sales list with at least two row fields and a filter, then refresh it after adding data.
  • Learn ten shortcuts you will use daily, such as Ctrl+Shift+L for filters, Ctrl+T for tables, Ctrl+Arrow keys to jump, and F4 for references.
  • Clean a messy list end to end: TRIM, PROPER, Text to Columns, Remove Duplicates, and fixing numbers stored as text.

Tests are one part of the process. Read the interview preparation guide and practise answering out loud with the free interview practice tool.

For your job

Interview preparation by profession

📉 Data Analyst Interview Questions and Answers 📊 Chartered Accountant Interview Questions and Answers 🏦 Banker Interview Questions and Answers 🤝 HR Professional Interview Questions and Answers 📞 Sales Professional Interview Questions and Answers 💹 Financial Advisor Interview Questions and Answers 📈 Consultant Interview Questions and Answers 📣 Digital Marketer Interview Questions and Answers
FAQ

Questions about the Excel Skills Test

Most Excel tests for analyst, finance, HR and sales roles check lookup formulas (VLOOKUP, XLOOKUP, INDEX/MATCH), conditional formulas (IF, COUNTIF, SUMIFS), absolute and relative references, pivot tables, and basic data cleaning. Senior roles may add charts, conditional formatting, data validation and simple macros. Many companies give a practical task in a real sheet rather than only multiple-choice questions.

Learn both. XLOOKUP is simpler and more flexible: it can look left, uses exact match by default and has a built-in “not found” value. But it is only available in newer versions of Excel, and many offices still use older files built on VLOOKUP. Interviewers often ask you to explain the difference.

#N/A means “value not available”. Lookup functions such as VLOOKUP, MATCH and XLOOKUP return it when they cannot find the lookup value. Common causes are extra spaces, numbers stored as text, or looking in the wrong range. You can handle it with IFERROR or XLOOKUP’s if_not_found argument, but fix the data first.

Name specific functions and what you did with them, for example “Built a monthly sales dashboard with pivot tables and SUMIFS, cutting report time from a day to two hours.” Avoid vague labels like “advanced Excel” on their own. Be ready to demonstrate anything you list in a live test.

Keep practising

More free skill tests

📒 Accounting Basics Test 25 questions · 25 minutes 🗄️ SQL Basics Test 25 questions · 25 minutes 🐍 Python Basics Test 25 questions · 20 minutes 📈 Digital Marketing Test 25 questions · 20 minutes 🩺 Nursing Fundamentals Test 25 questions · 25 minutes 🍎 Teaching and Pedagogy Test 25 questions · 25 minutes 🤝 Customer Service Skills Test 25 questions · 20 minutes 🧑‍💼 HR Basics Test 25 questions · 20 minutes 📝 All skill tests 🎯 Interview preparation guide

Show your skills with a personal website

Put your resume, projects and certificates on one link recruiters can open on any phone — live in about five minutes.

● Live in 5 minutes · free to start · no auto-renew

Recruiters Google you before the interview

Get a page that shows up: your experience, projects and contact details at your own link. Live in minutes.

Start free
Chat on WhatsApp