Skip to content
RED SAGSkills

Excel interview questions and answers

Excel tests are common for analyst, finance and operations roles. Questions check formulas, lookups and summarising data, plus the mistakes that quietly give wrong numbers.

Below are 60 Excel questions with the correct answer and a short explanation, grouped from fresher to experienced level. Try each one before you open the answer.

Take the Excel MCQ testPractise 203+ Excel questions for points

What Excel interviews usually check

  • ✓XLOOKUP, VLOOKUP, INDEX and MATCH
  • ✓SUMIFS, COUNTIFS and conditional logic
  • ✓Pivot tables and summarising data
  • ✓Absolute vs relative references
  • ✓Text, date and financial functions, and cleaning data

Excel interview questions for freshers easy

1. What #DIV/0! means

A margin column uses =C2/B2 and shows #DIV/0! for some rows. What is the cause?

  1. A.C2 contains a negative number
  2. B.B2 is zero or blank in those rows
  3. C.The formula has a spelling mistake
  4. D.The column is too narrow to show it
Show answer

Answer: B. B2 is zero or blank in those rows

#DIV/0! appears when a formula divides by zero, and a blank cell counts as zero in a division. Check the data, or handle it with =IF(B2=0,"",C2/B2). Hiding every error with IFERROR can also hide real mistakes, so be specific.

2. What causes #NAME?

A user types =SUMM(B2:B20) and sees #NAME?. Why?

  1. A.The range B2:B20 contains text values, not numbers
  2. B.One of the cells has a #DIV/0! error
  3. C.The result is too large to display
  4. D.Excel does not recognise SUMM as a function
Show answer

Answer: D. Excel does not recognise SUMM as a function

#NAME? means Excel found a name it does not know: a misspelt function, an undefined named range, or text missing its quotes. Picking functions from the autocomplete list avoids typos. It can also appear when a file using new functions opens in an older Excel version.

3. The 3 in a VLOOKUP

In =VLOOKUP(A2,$F$2:$J$500,3,FALSE), what does the 3 mean?

  1. A.Return the value from the 3rd column of F:J, column H
  2. B.Return the value from column C of the sheet
  3. C.Look up the value in the 3rd row of the range F2:J500
  4. D.Allow up to 3 errors in the range before returning #N/A
Show answer

Answer: A. Return the value from the 3rd column of F:J, column H

col_index_num counts columns inside the table range, starting from its first column. In F:J, column 1 is F, so 3 is H. VLOOKUP always searches the first column of the range, and FALSE asks for an exact match.

4. Friendly message when an ID is missing

Which formula shows "Not listed" instead of #N/A when the ID in F2 is not found?

  1. A.=XLOOKUP(F2,A2:A400,C2:C400,0,"Not listed")
  2. B.=XLOOKUP("Not listed",F2,A2:A400,C2:C400)
  3. C.=XLOOKUP(F2,A2:A400,"Not listed",C2:C400)
  4. D.=XLOOKUP(F2,A2:A400,C2:C400,"Not listed")
Show answer

Answer: D. =XLOOKUP(F2,A2:A400,C2:C400,"Not listed")

The 4th argument of XLOOKUP, if_not_found, is returned when there is no match. It only handles missing values, so other errors such as a broken range still show. That is safer than wrapping everything in IFERROR.

5. State code from a GSTIN

A2 holds a GSTIN like 27AAPFU0939F1ZV. The first two digits are the state code. Which formula extracts it?

  1. A.=RIGHT(A2,2)
  2. B.=LEFT(A2,2)
  3. C.=MID(A2,2,2)
  4. D.=LEN(A2,2)
Show answer

Answer: B. =LEFT(A2,2)

LEFT(text, n) returns the first n characters, here "27" (Maharashtra). Note the result is text, so compare it with "27", not 27, or wrap it in VALUE. MID(A2,3,10) would return the PAN part.

6. Splitting "City, State" into two columns

Column A holds "Pune, Maharashtra" style values. Which built-in tool splits them into two columns at the comma?

  1. A.Home > Merge & Center
  2. B.Data > Text to Columns
  3. C.Review > Spelling and Grammar
  4. D.View > Split Window
Show answer

Answer: B. Data > Text to Columns

Text to Columns splits cells by a delimiter like a comma, or by fixed width. It overwrites columns to the right, so leave space first. In Microsoft 365, TEXTSPLIT does the same with a formula.

7. Listing only one region's rows

In Microsoft 365, data is in A2:D500 and regions are in column B. Which formula lists every row where the region is "South"?

  1. A.=FILTER(B2:B500,"South",A2:D500)
  2. B.=FILTER("South",A2:D500)
  3. C.=FILTER(A2:D500,B2:B500="South")
  4. D.=VLOOKUP("South",A2:D500,4,FALSE)
Show answer

Answer: C. =FILTER(A2:D500,B2:B500="South")

FILTER takes the data to return and a TRUE/FALSE test of the same height. Matching rows spill into the cells below and to the right. Unlike the Filter button, the result updates by itself when the data changes.

8. A list of cities without repeats

Column C has 3,000 orders with city names repeated many times. Which formula returns each city once?

  1. A.=DISTINCT(C2:C3001)
  2. B.=UNIQUE(C2:C3001)
  3. C.=COUNTIF(C2:C3001,C2)
  4. D.=SORT(C2:C3001,1)
Show answer

Answer: B. =UNIQUE(C2:C3001)

UNIQUE returns each distinct value once as a spilled list, and updates as data changes. Wrap it in SORT for an A–Z list: =SORT(UNIQUE(C2:C3001)). There is no DISTINCT function in Excel.

9. What #SPILL! means

A UNIQUE formula in E2 shows #SPILL!. Clicking it shows a dashed border over E2:E15, and E9 contains a note someone typed. What is the fix?

  1. A.Change the formula so it starts from row 1 instead
  2. B.Clear E9 so the results have room to spill
  3. C.Turn calculation to Manual and then back to Automatic
  4. D.Convert the formula result into text
Show answer

Answer: B. Clear E9 so the results have room to spill

Dynamic array formulas need every cell in their spill range to be empty. If anything is in the way, even a space, they show #SPILL!. Clear or move the blocking content and the results appear.

10. Referring to a whole spilled list

E2 holds =UNIQUE(C2:C3001), which spills down. What does =COUNTA(E2#) count?

  1. A.Only the value in cell E2
  2. B.The number of # signs typed anywhere in column E
  3. C.All values in E2's spill range, at any length
  4. D.Every non-empty cell in column E, including headers
Show answer

Answer: C. All values in E2's spill range, at any length

The # after a cell refers to the whole spill range of the formula in that cell. When the list grows or shrinks, E2# adjusts automatically. It is handy for charts, data validation lists and further formulas.

11. Highlighting overdue amounts

You want amounts above ₹1,00,000 in column D to turn red automatically. Which feature should you use?

  1. A.Data Validation > Decimal, greater than 100000
  2. B.Format Cells > Number > Currency
  3. C.Review > Track Changes for column D only
  4. D.Conditional Formatting > Highlight Cells Rules
Show answer

Answer: D. Conditional Formatting > Highlight Cells Rules

Conditional formatting changes how a cell looks based on its value or a formula, and updates as values change. Data validation controls what can be typed in, but does not colour existing values. Currency format only changes how numbers are displayed.

12. Sort by state, then by city

You want rows sorted by State A–Z, and within each state by City A–Z. What is the simplest way?

  1. A.Sort by City first, then filter by State
  2. B.Use Ctrl + T, which sorts tables automatically
  3. C.Data > Sort with two levels: State, then City
  4. D.Sort State and City columns separately
Show answer

Answer: C. Data > Sort with two levels: State, then City

The Sort dialog lets you add levels; the first level sorts and later levels break ties. Sorting columns separately would break rows apart and mix up the data. Always select the whole table so rows stay together.

13. Chart for monthly revenue trend

You need to show how monthly revenue changed over the last 24 months. Which chart type fits best?

  1. A.Pie chart
  2. B.Doughnut chart
  3. C.Radar chart
  4. D.Line chart
Show answer

Answer: D. Line chart

Line charts show change over a continuous time axis clearly, and many months still read well. Pie and doughnut charts show shares of one whole and get unreadable with many slices. Radar charts are for comparing several measures.

14. Sending numbers without formulas

You must send a sheet to a client with the final numbers only, and no formulas that show your workings. What should you do on a copy?

  1. A.Change the font colour of the formulas to white
  2. B.Format all formula cells as Text
  3. C.Hide the formula bar in View settings
  4. D.Copy the cells and use Paste Special > Values
Show answer

Answer: D. Copy the cells and use Paste Special > Values

Paste Special > Values (Ctrl + Alt + V, then V) replaces each formula with its current result. Hiding or colouring formulas does not remove them, and anyone can still see them. Keep your original working file separately.

15. GST formula gives a huge number

B2 holds a price of ₹2,500. A user writes =B2*18 to get 18% GST and gets 45,000. Which formula gives the correct GST of ₹450?

  1. A.=B2*18/10
  2. B.=B2+18%
  3. C.=B2*18%
  4. D.=B2*1.18
Show answer

Answer: C. =B2*18%

18% is the number 0.18, so =B2*18% or =B2*0.18 gives ₹450. =B2*1.18 gives the price including GST (₹2,950), not the tax itself. =B2+18% adds 0.18 to the price.

16. Growth from last year

Sales were ₹80,000 last year (B2) and ₹92,000 this year (C2). Which formula gives the growth of 15%?

  1. A.=(C2-B2)/B2
  2. B.=(C2-B2)/C2
  3. C.=C2/B2
  4. D.=(B2-C2)/B2
Show answer

Answer: A. =(C2-B2)/B2

Percentage change is (new − old) ÷ old: 12,000 ÷ 80,000 = 15%. Dividing by the new value gives about 13%, and C2/B2 gives 115%, the ratio rather than the change. Format the result as a percentage.

17. Where to put assumptions in a model

What is good practice for the assumptions in a business case spreadsheet?

  1. A.Type numbers straight into each formula
  2. B.Keep them on one clearly labelled input sheet
  3. C.Hide them so nobody changes them
  4. D.Repeat them on every sheet
Show answer

Answer: B. Keep them on one clearly labelled input sheet

Keeping inputs in one place, with sources and units, makes the model easy to check and update, and lets you run sensitivity tests quickly. Numbers typed inside formulas are hard to find and are a common cause of model errors.

18. Find the break-even input

You want to know what adoption rate makes the project NPV exactly zero. Which Excel tool finds this most directly?

  1. A.Goal Seek
  2. B.Conditional formatting
  3. C.Remove Duplicates
  4. D.Text to Columns
Show answer

Answer: A. Goal Seek

Goal Seek changes one input until a formula reaches a target value, such as NPV = 0. The result tells decision makers the minimum adoption needed, which is often easier to judge than the full NPV.

19. Checking where a number comes from

A reviewer wants to see every cell that feeds into the EBITDA total. Which Excel tool helps most?

  1. A.Remove Duplicates
  2. B.Text to Columns
  3. C.Conditional Formatting
  4. D.Trace Precedents
Show answer

Answer: D. Trace Precedents

Trace Precedents (on the Formulas tab) draws arrows to the cells a formula uses, and Trace Dependents shows what uses a cell. Together with Evaluate Formula, they let a reviewer follow the logic without rebuilding it.

20. IFERROR in a ratio sheet

In an Excel ratio sheet, what does this formula do when the cell C9 (current liabilities) is zero?

=IFERROR(C5/C9, "n/a")
  1. A.Shows "n/a" instead of a #DIV/0! error
  2. B.Returns zero as the ratio
  3. C.Divides C9 by C5 instead
  4. D.Deletes the row from the sheet
Show answer

Answer: A. Shows "n/a" instead of a #DIV/0! error

Dividing by zero gives #DIV/0!. IFERROR catches any error from the first argument and shows the second instead, here "n/a". It keeps summary tables clean, though it can also hide genuine formula mistakes.

Excel intermediate interview questions medium

21. IFS returns #N/A for some rows

Scores below 50 show #N/A with this formula. Why?

  1. A.IFS cannot compare numbers to text
  2. B.IFS only allows two conditions
  3. C.The grade results must be numbers, not letters
  4. D.No condition is true and there is no catch-all
Show answer

Answer: D. No condition is true and there is no catch-all

IFS returns the value for the first true condition, and #N/A if none is true. Add a final catch-all pair with TRUE as the test: ...,TRUE,"Fail"). This plays the role of the ELSE branch in a nested IF.

22. Summing the same cell on many sheets

A workbook has sheets Jan, Feb and Mar in that order. A Summary sheet uses =SUM(Jan:Mar!B5). Someone inserts a sheet named "Notes" between Feb and Mar. What happens?

  1. A.Notes is ignored as it is not a month
  2. B.The formula shows #REF! until fixed
  3. C.B5 on Notes is now included in the total
  4. D.Only Jan and Mar are summed by the formula from now on
Show answer

Answer: C. B5 on Notes is now included in the total

A 3-D reference covers every sheet positioned between the first and last sheet named, by tab order, not by name. Any sheet moved into that range is included. Many models use blank "Start" and "End" sheets to mark the range clearly.

23. Total is off by 0.01

Three cells each contain =10/3 and are formatted to 2 decimals, showing 3.33. Their SUM shows 10.00, but 3.33 + 3.33 + 3.33 is 9.99. Why?

  1. A.Formatting hides decimals; the stored values are not rounded
  2. B.SUM always rounds results up to the next whole number
  3. C.Excel adds 0.01 to fix display rounding errors
  4. D.The cells were formatted as text before the SUM was entered
Show answer

Answer: A. Formatting hides decimals; the stored values are not rounded

Number formatting changes only what you see; each cell still holds 3.333333... and the true sum is 10. If figures must add up as shown, round the values themselves with =ROUND(10/3,2). This matters for invoices and GST lines.

24. Rounding prices up to the next ₹5

Prices like ₹121, ₹123 and ₹125 must be rounded up to the next multiple of ₹5 (125, 125, 125). Which formula does this?

  1. A.=ROUND(A2,5)
  2. B.=CEILING.MATH(A2,5)
  3. C.=ROUNDUP(A2,-1)+5
  4. D.=MROUND(A2,10)
Show answer

Answer: B. =CEILING.MATH(A2,5)

CEILING.MATH rounds up to the nearest multiple of the given number, so 121 and 123 become 125 and 125 stays 125. ROUND(A2,5) means 5 decimal places, ROUNDUP(A2,-1)+5 rounds up to tens and adds 5 (135), and MROUND rounds to the nearest multiple, not always up.

25. Commission rate from slabs

A slab table has sales thresholds sorted ascending in F2:F5 (0, 1,00,000, 5,00,000, 10,00,000) and rates in G2:G5. For sales of 7,20,000, what does =VLOOKUP(720000,F2:G5,2,TRUE) return?

  1. A.The rate for the 10,00,000 slab
  2. B.#N/A, as 7,20,000 is not in the table
  3. C.The rate for the 5,00,000 slab
  4. D.The rate for the 0 slab
Show answer

Answer: C. The rate for the 5,00,000 slab

With TRUE (approximate match) and sorted data, VLOOKUP finds the largest threshold less than or equal to the value: 5,00,000. This is the right tool for slabs and grade bands. XLOOKUP does the same with match_mode -1.

26. The 0 at the end of MATCH

In =MATCH("Kochi",A2:A200,0), what does the final 0 do?

  1. A.Returns 0 if Kochi is not found anywhere in the range
  2. B.Starts counting positions from zero
  3. C.Searches from the bottom of the range
  4. D.Requires an exact match, with any order of data
Show answer

Answer: D. Requires an exact match, with any order of data

match_type 0 finds the first exact match and works on unsorted data. 1 (the default) and -1 are approximate matches that need sorted data. Forgetting the 0 is a common cause of wrong results.

27. Paying an hourly rate

A2 holds hours worked as a time, 7:30. The rate is ₹200 per hour. Which formula gives ₹1,500?

  1. A.=A2*200
  2. B.=A2/24*200
  3. C.=HOUR(A2)*200
  4. D.=A2*24*200
Show answer

Answer: D. =A2*24*200

7:30 is stored as 0.3125 of a day, so multiply by 24 to get 7.5 hours, then by the rate. =A2*200 gives about 62.5. HOUR(A2) returns only the whole hours (7), losing the 30 minutes.

28. FILTER returns #CALC!

=FILTER(A2:D500,B2:B500="Goa") shows #CALC! because there are no Goa rows. How do you show "No orders" instead?

  1. A.=IFNA(FILTER(A2:D500,B2:B500="Goa"),"No orders")
  2. B.=FILTER(A2:D500,B2:B500="Goa","No orders")
  3. C.=FILTER(A2:D500,"No orders",B2:B500="Goa")
  4. D.=FILTER(A2:D500,B2:B500="Goa")&"No orders"
Show answer

Answer: B. =FILTER(A2:D500,B2:B500="Goa","No orders")

FILTER returns #CALC! for an empty result, and its third argument, if_empty, sets what to show instead. IFNA would not catch it, as #CALC! is not #N/A. Many dashboards use "" to show a clean blank.

29. Months spread across columns

A budget sheet has one row per account and twelve month columns, Apr to Mar. To analyse it in a pivot, you want one row per account per month. Which Power Query step does this?

  1. A.Select the account column, then Unpivot Other Columns
  2. B.Select the twelve month columns, then use Pivot Column
  3. C.Transpose the whole table, then promote headers
  4. D.Group By account and sum all twelve months
Show answer

Answer: A. Select the account column, then Unpivot Other Columns

Unpivoting turns column headers into values in an Attribute column, with figures in a Value column. Choosing "Unpivot Other Columns" on the account column also handles any new months added later. Pivot Column does the opposite, from long to wide.

30. EMI for many rates and tenures

You want a grid of loan EMIs for interest rates in a column and tenures in a row, all from one PMT formula. Which feature builds this?

  1. A.A pivot table with Rate in Rows and Tenure in the Columns area
  2. B.What-If Analysis > Data Table with row and column inputs
  3. C.Goal Seek run once for each cell of the grid
  4. D.Flash Fill from one manually calculated EMI
Show answer

Answer: B. What-If Analysis > Data Table with row and column inputs

A two-variable Data Table puts your formula in the top-left corner and substitutes each row and column value into the chosen input cells. It shows every combination at once. A pivot only summarises existing data and cannot recalculate the formula.

31. Revenue and margin % on one chart

You plot monthly revenue (₹ crores) and profit margin (8–15%) on one chart. The margin line is flat along the bottom. What fixes this?

  1. A.A pie chart with both of the series combined into one
  2. B.Formatting the margin as a whole number
  3. C.A combo chart with margin on a secondary axis
  4. D.Sorting the months from highest revenue
Show answer

Answer: C. A combo chart with margin on a secondary axis

Values in crores and in percent cannot share one scale, so the small one looks flat. A combo chart can show revenue as columns on the main axis and margin as a line on a secondary axis. Label both axes clearly so readers are not misled.

32. Stacking two sheets with a formula

Sheets Jan and Feb have the same three columns with different row counts. Which Microsoft 365 formula stacks both into one list?

  1. A.=HSTACK(Jan!A2:C50,Feb!A2:C60)
  2. B.=VSTACK(Jan!A2:C50,Feb!A2:C60)
  3. C.=SUM(Jan:Feb!A2:C60)
  4. D.=CONCAT(Jan!A2:C50,Feb!A2:C60)
Show answer

Answer: B. =VSTACK(Jan!A2:C50,Feb!A2:C60)

VSTACK appends arrays vertically, one below the other, and HSTACK places them side by side. A 3-D SUM adds numbers across sheets, and CONCAT joins text into one string. For many files, Power Query Append is usually better.

33. A 2-million-row CSV

You open a 2-million-row CSV in Excel and get a warning that the file was not loaded completely. What happened, and what is a good alternative?

  1. A.Excel needs more RAM; close the other programs and retry
  2. B.The CSV is corrupt; ask for it to be exported again
  3. C.CSV files hold only 65,536 rows; save it as .xlsx
  4. D.A sheet holds 1,048,576 rows; load it to the Data Model
Show answer

Answer: D. A sheet holds 1,048,576 rows; load it to the Data Model

A worksheet has a hard limit of 1,048,576 rows (and 16,384 columns), so the rest is dropped. Power Query can load the full file into the Data Model, where pivots and DAX can analyse many millions of rows. Or summarise it before loading.

34. Markup and margin on the same item

An item costs ₹80 and sells for ₹100. What are its gross margin and markup?

  1. A.Margin 25%, markup 20%
  2. B.Both are 20%
  3. C.Both are 25%
  4. D.Margin 20%, markup 25%
Show answer

Answer: D. Margin 20%, markup 25%

Margin is profit ÷ selling price: 20 ÷ 100 = 20%. Markup is profit ÷ cost: 20 ÷ 80 = 25%. Mixing them up when setting prices leads to lower profits than planned.

35. STDEV.S or STDEV.P

You measure delivery times for 50 orders picked at random from 20,000 orders this month, to estimate the spread for all orders. Which function fits?

  1. A.STDEV.P, as the 50 are the population
  2. B.VAR.P, as variance equals spread
  3. C.AVERAGE, as spread is the mean
  4. D.STDEV.S, as the 50 are a sample
Show answer

Answer: D. STDEV.S, as the 50 are a sample

STDEV.S divides by n − 1, which corrects the bias when estimating a population's spread from a sample. STDEV.P divides by n and is right only when you have every item in the population. With 50 values the difference is small but real.

36. Where the initial investment goes in NPV

A project costs ₹5,00,000 today (B1) and returns cash in years 1–5 (B2:B6). The discount rate is 10%. Which formula gives the correct NPV?

  1. A.=NPV(10%,B1:B6)
  2. B.=NPV(10%,B2:B6)-B1
  3. C.=NPV(10%,B1)+B2:B6
  4. D.=NPV(10%,B2:B6)+B1
Show answer

Answer: D. =NPV(10%,B2:B6)+B1

Excel's NPV assumes the first value is one period in the future, so including today's outflow in the range discounts it by a year. Add the time-zero amount separately; B1 is already negative, so you add it. This is one of the most common modelling errors.

37. Straight-line depreciation

A machine costs ₹5,00,000, has a salvage value of ₹50,000 and a useful life of 5 years. What does =SLN(500000,50000,5) return?

  1. A.₹90,000
  2. B.₹1,00,000
  3. C.₹1,10,000
  4. D.₹45,000
Show answer

Answer: A. ₹90,000

Straight-line depreciation = (cost − salvage) ÷ life = 4,50,000 ÷ 5 = ₹90,000 a year. ₹1,00,000 ignores the salvage value. Under Indian Companies Act Schedule II, useful lives are prescribed by asset type.

38. Reading a CORREL result

=CORREL(B2:B37,C2:C37) between monthly ad spend and sales returns 0.92. What can you conclude?

  1. A.Ad spend causes 92% of all the sales made each month
  2. B.Strong positive linear link, not proof of cause
  3. C.Sales rise ₹0.92 for each ₹1 of ad spend
  4. D.The data has 92% accuracy for forecasts
Show answer

Answer: B. Strong positive linear link, not proof of cause

Correlation runs from −1 to 1 and measures how closely two series move together in a straight line. 0.92 is strong, but both could be driven by something else, such as festive seasons. The slope (₹ of sales per ₹ of spend) comes from SLOPE or a regression, not CORREL.

39. Weighted average selling price

Units sold are in B2:B6 and the price of each product in C2:C6. Which formula gives the volume-weighted average price?

  1. A.=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)
  2. B.=AVERAGE(C2:C6)
  3. C.=SUM(C2:C6)/COUNT(B2:B6)
  4. D.=SUMPRODUCT(B2:B6,C2:C6)/COUNT(C2:C6)
Show answer

Answer: A. =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)

SUMPRODUCT gives total revenue (units × price summed), and dividing by total units gives the average price per unit sold. A plain AVERAGE treats a product that sold 10 units the same as one that sold 10,000.

40. Wrapping formulas in IFERROR

A model wraps most formulas in IFERROR(..., 0). Why do reviewers often object?

  1. A.IFERROR makes the workbook file too large to send by email
  2. B.IFERROR only works on text cells, not on number cells
  3. C.It turns real mistakes into zeros that look like valid data
  4. D.It stops the model from being printed or saved as PDF
Show answer

Answer: C. It turns real mistakes into zeros that look like valid data

IFERROR catches every error type, including broken links and #REF! from deleted rows, and quietly shows 0. Totals then look fine while being wrong. Use it only where an error is expected (such as a missing lookup), and keep checks that flag unexpected errors.

Excel interview questions for experienced candidates hard

41. Grand total of an average pivot

A pivot shows Average of Sales by region. East has 9 rows averaging 100 and West has 1 row of 200. What does the Grand Total row show?

  1. A.150
  2. B.300
  3. C.100
  4. D.110
Show answer

Answer: D. 110

The pivot grand total averages the underlying rows, not the region averages. Total sales are 9 x 100 + 200 = 1,100 over 10 rows, which gives 110. The average of the two subtotals (150) would weight one West row as much as nine East rows.

42. SUMIF adds the wrong rows

Regions are in A2:A100 and amounts in B2:B100. What does this formula actually add up?

  1. A.Exactly the North amounts, as ranges align
  2. B.Nothing, it returns a #VALUE! error
  3. C.The amount one row below each North row
  4. D.Every amount in B3:B101, ignoring criteria
Show answer

Answer: C. The amount one row below each North row

SUMIF uses sum_range only from its top-left cell, and lines it up with the criteria range row by row. Starting at B3 shifts everything by one row, so each match adds the next row's amount, with no error shown. SUMIFS is stricter and gives #VALUE! when sizes differ.

43. Creating a GST function with LAMBDA

In Name Manager you define AddGST as =LAMBDA(amount, rate, amount*(1+rate)). What does =AddGST(1000, 18%) return in a cell?

  1. A.18
  2. B.1018
  3. C.#NAME?
  4. D.1180
Show answer

Answer: D. 1180

LAMBDA defines parameters and a calculation, and saving it under a name makes it callable like a built-in function. 1000 × (1 + 0.18) = 1180. It works in Microsoft 365 and Excel 2024 without any VBA.

44. XLOOKUP with a partial name

A user types "Reliance*" in F2 to find the first company whose name starts with Reliance. Which XLOOKUP works?

  1. A.=XLOOKUP(F2,A:A,B:B,"None",0)
  2. B.=XLOOKUP(F2,A:A,B:B,"None",1)
  3. C.=XLOOKUP(F2,A:A,B:B,"None",-1)
  4. D.=XLOOKUP(F2,A:A,B:B,"None",2)
Show answer

Answer: D. =XLOOKUP(F2,A:A,B:B,"None",2)

XLOOKUP only treats * and ? as wildcards when match_mode is 2. With the default 0 it looks for the literal text "Reliance*". 1 and -1 are exact-or-next-larger and exact-or-next-smaller matches. VLOOKUP, by contrast, always allows wildcards in exact mode.

45. Lookup on two conditions

You need the price for the product in F2 and the city in G2. Products are in A2:A900, cities in B2:B900 and prices in C2:C900. Which formula works in Microsoft 365?

  1. A.=XLOOKUP(F2&G2,A2:A900,B2:B900,C2:C900)
  2. B.=XLOOKUP(1,(A2:A900=F2)*(B2:B900=G2),C2:C900)
  3. C.=XLOOKUP(F2,A2:A900,C2:C900)*(B2:B900=G2)
  4. D.=XLOOKUP(F2,A2:A900,C2:C900)+XLOOKUP(G2,B2:B900,C2:C900)
Show answer

Answer: B. =XLOOKUP(1,(A2:A900=F2)*(B2:B900=G2),C2:C900)

Each comparison returns TRUE/FALSE per row; multiplying them gives 1 only where both are true. XLOOKUP then looks for the first 1 and returns the matching price. Another way is to join keys: XLOOKUP(F2&G2, A2:A900&B2:B900, C2:C900).

46. Week number of 1 January 2027

What do =WEEKNUM(DATE(2027,1,1)) and =ISOWEEKNUM(DATE(2027,1,1)) return? (1 January 2027 is a Friday.)

  1. A.WEEKNUM 1, ISOWEEKNUM 1
  2. B.WEEKNUM 53, ISOWEEKNUM 1
  3. C.WEEKNUM 52, ISOWEEKNUM 52
  4. D.WEEKNUM 1, ISOWEEKNUM 53
Show answer

Answer: D. WEEKNUM 1, ISOWEEKNUM 53

WEEKNUM's default system starts week 1 on 1 January. ISO weeks start on Monday, and week 1 is the week with the year's first Thursday, so Friday 1-Jan-2027 belongs to the last ISO week of 2026. 2026 began on a Thursday, so it has 53 ISO weeks.

47. Wrong values after inserting a column

A sheet uses =VLOOKUP(A2,Data!A:F,4,FALSE) to return Price. Someone inserts a new column in the Data sheet between B and C. What happens?

  1. A.It updates the 4 to 5 and still returns Price
  2. B.It returns #REF! until the file is saved
  3. C.It returns #N/A for every row
  4. D.It now returns the old 3rd column, not Price
Show answer

Answer: D. It now returns the old 3rd column, not Price

The range grows to A:G, but the hard-coded 4 does not change, so the formula now returns what was column C before, with no error. XLOOKUP or INDEX/MATCH point at the return column itself, so they keep working after column inserts.

48. First day of the month from any date

Which formula returns the first day of the month for the date in A2, as a real date?

  1. A.=EOMONTH(A2,-1)+1
  2. B.=EOMONTH(A2,0)-30
  3. C.=DAY(A2)-1
  4. D.=TEXT(A2,"01-mm-yyyy")
Show answer

Answer: A. =EOMONTH(A2,-1)+1

EOMONTH(A2,-1) is the last day of the previous month; adding 1 gives the first of the current month. Subtracting 30 only works for some months, DAY returns a number, and TEXT returns text that will not sort or group as a date. =DATE(YEAR(A2),MONTH(A2),1) also works.

49. Total Sales that follows slicers

In the Excel Data Model, you need "Total Sales" that adjusts to whatever slicers and pivot rows are selected. Should it be a measure or a calculated column?

  1. A.A measure, calculated at query time for the current filters
  2. B.A calculated column, stored once for each row at every refresh
  3. C.A calculated column, since measures cannot use SUM
  4. D.Either one, as both behave the same in pivots
Show answer

Answer: A. A measure, calculated at query time for the current filters

A measure is evaluated for each pivot cell in its filter context, so it responds to rows, columns and slicers. A calculated column is computed per row at refresh and stored, which suits row-level attributes such as a price band. Measures also save memory.

50. A summary table with one formula

In current Microsoft 365, what does this formula return?

  1. A.A new pivot table on a separate worksheet
  2. B.The single largest regional total in the data
  3. C.Each row of the data sorted by region name
  4. D.A spilled table of total sales for each region
Show answer

Answer: D. A spilled table of total sales for each region

GROUPBY groups by the first argument (regions), aggregates the second (sales) with the function given, and spills a summary with a total row by default. It updates automatically like other dynamic array formulas. PIVOTBY adds a column field as well.

51. Production mix with limits

A factory wants to choose quantities of three products to maximise profit, with limits on machine hours, raw material and minimum orders. Which Excel tool suits this?

  1. A.Goal Seek, which changes inputs to hit a target
  2. B.A Data Table with three input cells at once
  3. C.Solver, which changes many inputs under constraints
  4. D.Scenario Manager with the three product quantities saved
Show answer

Answer: C. Solver, which changes many inputs under constraints

Solver (an add-in) optimises an objective by changing several decision cells while respecting constraints, using methods such as Simplex LP for linear models. Goal Seek changes only one input to reach a fixed value. Data Tables handle at most two inputs and do not optimise.

52. Model slows down after adding data tables

A model with several large What-If data tables now takes seconds to recalculate after every edit. Which setting helps most without removing them?

  1. A.Calculation: Automatic except for Data Tables
  2. B.Turning off iterative calculation in Options
  3. C.Saving the whole file in the binary .xlsb format
  4. D.Turning off the formula bar
Show answer

Answer: A. Calculation: Automatic except for Data Tables

Each data table recalculates the model once for every cell in it, so big tables are expensive. "Automatic except for Data Tables" keeps normal formulas live and updates the tables only when you press F9. Binary format helps file size, not this recalculation.

53. Highlight lands one row off

A rule for A2:H300 uses the formula =$F1="Overdue". Rows are highlighted one row above the overdue ones. Why?

  1. A.Conditional formatting always skips the first row
  2. B.The $ sign moves the rule one row upwards
  3. C.Excel evaluates the rule from the bottom up
  4. D.The formula is written for A2 but points at row 1
Show answer

Answer: D. The formula is written for A2 but points at row 1

A formula rule is evaluated as if written in the first cell of the range, A2, and copied relatively. Pointing it at row 1 means each row checks the row above it. Change it to =$F2="Overdue", matching the first row of the applied range.

54. Chart loses data when rows are hidden

You hide some helper rows in the source range and the chart's lines suddenly lose points. Why?

  1. A.Hidden rows are deleted when you save
  2. B.Charts skip hidden rows by default
  3. C.Charts only plot the first 100 rows
  4. D.Hiding rows changes the values to text
Show answer

Answer: B. Charts skip hidden rows by default

Excel charts plot only visible cells unless you change it. Select the chart, then Select Data > Hidden and Empty Cells > "Show data in hidden rows and columns". The same setting also decides how blank cells appear: gaps, zero or connected lines.

55. A project with two IRRs

Cash flows are −100, +230, −132 (in ₹ lakh) over years 0, 1 and 2. Both 10% and 20% make the NPV zero. What does this mean for IRR?

  1. A.The true IRR is the average, 15%
  2. B.The project's IRR is always the higher, 20%
  3. C.IRR is ambiguous; judge the project by NPV
  4. D.IRR is undefined, so the project loses money
Show answer

Answer: C. IRR is ambiguous; judge the project by NPV

When cash flows change sign more than once, there can be several rates that make NPV zero, and Excel's IRR returns whichever one its search finds from the guess. Here 1.1 and 1.2 both solve 100x² − 230x + 132 = 0. Use NPV at your actual cost of capital, or MIRR, to decide.

56. Double-declining depreciation in year 1

A machine costs ₹5,00,000 with salvage ₹50,000 and a 5-year life. What does =DDB(500000,50000,5,1) return?

  1. A.₹90,000
  2. B.₹2,00,000
  3. C.₹1,80,000
  4. D.₹1,00,000
Show answer

Answer: B. ₹2,00,000

Double-declining balance uses twice the straight-line rate: 2 ÷ 5 = 40% of the opening book value. Year 1 is 40% × 5,00,000 = ₹2,00,000; salvage only limits later years so book value does not drop below it. ₹1,80,000 wrongly applies 40% to cost minus salvage.

57. Forecasting the next month

Sales for months 1–4 were 100, 120, 140 and 160. What does =FORECAST.LINEAR(5,B2:B5,A2:A5) return, with months in A and sales in B?

  1. A.160
  2. B.200
  3. C.130
  4. D.180
Show answer

Answer: D. 180

FORECAST.LINEAR fits a straight line by least squares and extends it to x = 5. The data rise by exactly 20 a month, so the line gives 180. Real data are noisier, so check the fit and consider FORECAST.ETS for seasonal patterns.

58. GST total differs by a paisa

An invoice has three lines of ₹10.30 each at 18% GST. Rounding GST per line gives a different total from rounding GST on the invoice total. What are the two totals?

  1. A.₹5.56 per line, ₹5.55 on the total
  2. B.Both are ₹5.56
  3. C.₹5.55 per line, ₹5.56 on the total
  4. D.₹5.40 per line, ₹5.56 on the total
Show answer

Answer: C. ₹5.55 per line, ₹5.56 on the total

Each line: 10.30 × 18% = 1.854, rounded to ₹1.85, so three lines give ₹5.55. On the total: 30.90 × 18% = 5.562, rounded to ₹5.56. Both methods are used in practice; pick one, apply it consistently, and match what the billing system and GST returns use.

59. The Excel NPV function trap

Cell B1 holds the year 0 investment (−10,00,000) and B2:B4 hold the year 1–3 savings. An analyst writes the formula below. What is wrong with it?

=NPV(10%, B1:B4)
  1. A.Nothing; it is the correct formula for this layout
  2. B.Year 0 gets discounted; use =B1 + NPV(10%, B2:B4)
  3. C.NPV needs the rate written as 10, not as 10%
  4. D.It should use the IRR function instead of NPV
Show answer

Answer: B. Year 0 gets discounted; use =B1 + NPV(10%, B2:B4)

Excel’s NPV function assumes the first value arrives at the end of period 1, so the year 0 investment gets discounted too. That makes the cost look smaller and overstates NPV. Keep the time 0 cash flow outside the function and add it, or use XNPV with real dates.

60. Monthly rate from an annual rate

A monthly model needs a monthly growth rate equivalent to 12% compound annual growth. Which rate is correct?

  1. A.Exactly 1.00% a month
  2. B.About 0.95% a month
  3. C.About 1.20% a month
  4. D.About 0.83% a month
Show answer

Answer: B. About 0.95% a month

The equivalent rate is (1.12)^(1/12) − 1 ≈ 0.949% a month. Dividing by 12 gives 1%, which compounds to about 12.68% a year, overstating growth. The same care applies to converting interest and discount rates.

Ready to test yourself?

Take a 15-question Excel MCQ test with a score at the end, or practise the full Excel question bank and earn points on the leaderboard.

Start the Excel testTake a mock interview