Skip to content

Excel for Business Analysts Interview Questions for Business Analytics

43 Excel for Business Analysts questions with answers for business analytics roles — 14 easy, 26 medium, 3 hard.

About Excel for Business Analysts

Excel is still the daily tool for most business analysts in India. Interviews check lookups, SUMIFS and dynamic arrays, PivotTables, what-if analysis, data cleaning and building simple models and dashboards. Expect live formula questions and scenarios about reconciling files, fixing errors and automating repetitive monthly reports with Power Query and tables.

Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

How do you create a dashboard in Excel?

Save to ▾
Show answer
Prepare clean data, build PivotTables and PivotCharts, add KPI cards and slicers, and arrange everything on one sheet with a clear layout.
02

Which Excel functions do business analysts use daily?

Save to ▾
Show answer
VLOOKUP or XLOOKUP, INDEX-MATCH, SUMIFS, COUNTIFS, IF, text functions, PivotTables and charts.
03

XLOOKUP vs VLOOKUP?

Save to ▾
Show answer
XLOOKUP can look left or right, uses exact match by default, returns a custom value if nothing is found and doesn't break when columns move. VLOOKUP only looks right and needs a column number.
04

What is the difference between absolute, relative and mixed cell references in Excel?

Save to ▾
Show answer
A relative reference like A1 shifts when the formula is copied. An absolute reference like $A$1 stays fixed, useful for a tax rate or target cell. A mixed reference fixes only the column ($A1) or only the row (A$1), which is handy in multiplication grids or when copying a formula across months and products. Press F4 to cycle through the four options while editing a formula.
05

What are Excel Tables (Ctrl+T) and why should analysts use them?

Save to ▾
Show answer
An Excel Table turns a range into a named, structured object. It expands automatically when rows are added, so formulas, charts, PivotTables and Power Query sources pick up new data without range edits. It supports structured references like =SUM(Sales[Amount]), calculated columns that fill down automatically, built-in filters, banded formatting and a total row. Tables make reports far less error-prone than fixed ranges like A2:F5000.
06

What is conditional formatting and how would you use it in a business report?

Save to ▾
Show answer
Conditional formatting changes a cell's look based on rules or formulas. In reports you can highlight targets missed in red, use data bars for sales size, colour scales for a region heatmap, icon sets for trend direction, or a formula rule like =$E2<$F2 to colour an entire row. Use it sparingly and with a legend so the eye goes only to the exceptions that need action.
07

A lookup formula returns #N/A for some rows even though the values look present. What could be wrong?

Save to ▾
Show answer
Usually the values only look the same. Common causes are trailing or leading spaces, numbers stored as text in one sheet and as numbers in the other, non-breaking spaces from web exports, or different spellings. Check with =LEN() and =ISNUMBER(), clean with TRIM, CLEAN or VALUE, and confirm the lookup range covers all rows and uses exact match. Wrap the final formula in IFNA to label true misses.
08

Write an Excel formula that labels sales as High (100000 or more), Medium (50000 or more) or Low.

Save to ▾
Show answer
=IFS(B2>=100000, "High", B2>=50000, "Medium", TRUE, "Low") // older Excel versions: =IF(B2>=100000, "High", IF(B2>=50000, "Medium", "Low"))
09

Write an Excel formula to extract the domain from an email address.

Save to ▾
Show answer
// Excel 365 =TEXTAFTER(A2, "@") // any version =MID(A2, FIND("@", A2) + 1, LEN(A2)) // Flash Fill (Ctrl+E) also works for one-off cleanup
10

Write an Excel formula for working days between two dates excluding weekends and holidays.

Save to ▾
Show answer
// A2 = start, B2 = end, Holidays = named range of holiday dates =NETWORKDAYS(A2, B2, Holidays) // if the weekend is only Sunday (common in India) =NETWORKDAYS.INTL(A2, B2, 11, Holidays)
11

Write an Excel formula for the weighted average selling price across orders.

Save to ▾
Show answer
// B2:B200 = price per unit, C2:C200 = units sold =SUMPRODUCT(B2:B200, C2:C200) / SUM(C2:C200) // a simple AVERAGE(B2:B200) would wrongly give a small order // the same weight as a large one
12

Users in a shared Excel tracker keep typing region names with different spellings. How do you stop this?

Save to ▾
Show answer
Use Data Validation with a List rule. Keep the allowed regions in a separate sheet as an Excel Table, name it, and set the validation source to that range so the dropdown grows when you add a region. Turn on an error alert so free text is rejected. Then clean existing entries once with Find and Replace or a mapping lookup. For stronger control, protect the sheet and unlock only the input cells, so the list cannot be edited by mistake.
13

Write an Excel formula that lists all North region orders above 50,000 rupees.

Save to ▾
Show answer
=FILTER(Orders[[OrderID]:[Amount]], (Orders[Region]="North") * (Orders[Amount]>50000), "No matching orders") # Multiplying the two conditions acts as AND. # Use + instead of * for OR logic. # Wrap with SORT(..., 3, -1) to sort by the third column descending.
14

You added new rows to the source data, but your PivotTable does not show them even after refresh. Why?

Save to ▾
Show answer
The PivotTable source is probably a fixed range such as A1:F500, so rows added below 500 are outside it. The fix is to convert the data to an Excel Table with Ctrl+T and change the pivot's data source to the table name, so it expands automatically. Also check for blank header cells, filters on the pivot hiding new items, and whether the new rows are actually text values in a numeric column, which can change grouping.
15

What is a data table in Excel?

Save to ▾
Show answer
A what-if tool that recalculates a formula for different inputs, for example loan payments across different rates and tenures.
16

How do you build a PivotTable to analyse sales by region and product?

Save to ▾
Show answer
Create a table, insert a PivotTable, drag region to rows, product to columns, sales to values, and add slicers for month or channel.
17

How do you compute CAGR in Excel?

Save to ▾
Show answer
CAGR = (End value / Start value)^(1/years) - 1, entered as =(B2/A2)^(1/n)-1.
18

What is sensitivity analysis?

Save to ▾
Show answer
Testing how changes in key inputs such as price or volume affect the output, in Excel using data tables or scenario manager.
19

How do you chart a trend with a target line?

Save to ▾
Show answer
Create a line or column chart of actuals and add the target as another series formatted as a line.
20

Build a break-even analysis in Excel.

Save to ▾
Show answer
Break-even units = Fixed costs ÷ (Price − Variable cost per unit). Add a data table to test different price and volume scenarios.
21

How do you build a forecast in Excel?

Save to ▾
Show answer
Use historical data with FORECAST.ETS or trend lines, state assumptions, compare against actuals and show best, base and worst scenarios.
22

How would you forecast next quarter's sales in Excel?

Save to ▾
Show answer
Use FORECAST.ETS or a trendline on monthly history, check seasonality, compare with a simple moving average and present a range, not a single number.
23

Which what-if analysis tools does Excel have?

Save to ▾
Show answer
Goal Seek (find the input for a target output), Data Tables (see outputs for many inputs) and Scenario Manager (save best, base and worst cases).
24

What is the use of the IFERROR function?

Save to ▾
Show answer
It returns a custom value when a formula errors out, keeping reports clean, for example =IFERROR(VLOOKUP(...), "Not found").
25

How do you handle large datasets in Excel?

Save to ▾
Show answer
Use tables, Power Query, remove volatile functions, filter before calculating and move to Power Pivot or a database when rows reach the millions.
26

How do you calculate the percentage contribution of each category?

Save to ▾
Show answer
SUM(sales) per category divided by SUM(SUM(sales)) OVER () and multiplied by 100 gives each category's share of the total.
27

What is the difference between XLOOKUP and INDEX-MATCH?

Save to ▾
Show answer
Both look up values flexibly; XLOOKUP is simpler with built-in not-found handling, while INDEX-MATCH works in older Excel versions.
28

How do you use Excel's Scenario Manager?

Save to ▾
Show answer
Define named scenarios such as best, base and worst with different input values and compare the outputs in a summary report.
29

How do you protect a worksheet or workbook?

Save to ▾
Show answer
Review > Protect Sheet or Workbook with a password, lock cells, hide formulas and allow only selected cells to be edited.
30

How would you build a simple financial model in Excel?

Save to ▾
Show answer
Separate inputs, calculations and outputs, link all formulas to input cells, add scenarios and checks, and avoid hard-coded numbers inside formulas.
31

When would you use SUMPRODUCT instead of SUMIFS?

Save to ▾
Show answer
SUMIFS is faster and easier for sums with AND conditions on ranges. SUMPRODUCT multiplies arrays and sums the result, so it handles cases SUMIFS cannot: weighted totals like price times quantity, OR logic, conditions on calculated values such as MONTH(date)=4, or comparing two columns row by row. On very large sheets SUMPRODUCT is slower, so prefer SUMIFS or a helper column when possible.
32

What are dynamic array functions in Excel and how do they help analysts?

Save to ▾
Show answer
Dynamic array functions return several values that spill into neighbouring cells. UNIQUE lists distinct values, SORT and SORTBY order data, FILTER returns rows meeting conditions, SEQUENCE builds number series, and XLOOKUP can return whole rows. For example =SORT(UNIQUE(FILTER(Sales[Region], Sales[Year]=2026))) builds a live region list. They replace many helper columns and Ctrl+Shift+Enter array formulas, and the # operator references a full spill range.
33

You receive two customer lists from sales and billing and must reconcile them. How do you do it?

Save to ▾
Show answer
Standardise both first: trim spaces, fix case, convert IDs to the same type and remove duplicates. Use a unique key like customer ID, or a combined key of phone and email if IDs differ. Then use XLOOKUP or COUNTIFS to mark each record as matched, only in sales or only in billing, or a Power Query merge with anti joins. Summarise mismatches by type and share the exception list with both owners.
34

Your monthly Excel report takes a full day of manual work. How would you automate it?

Save to ▾
Show answer
Map the steps first: which files arrive, what cleaning is done and which outputs are needed. Move the import and cleaning into Power Query so a refresh replaces copy-paste. Base calculations on Excel Tables and PivotTables that update automatically, and keep a fixed template for the output. Use a small macro or Office Script only for the remaining formatting or emailing. Add checks such as row counts and totals so errors surface quickly.
35

Write Excel formulas to find the second-highest sales value and the name of the rep who achieved it.

Save to ▾
Show answer
// second highest value =LARGE(C2:C100, 2) // rep name for that value =INDEX(B2:B100, MATCH(LARGE(C2:C100, 2), C2:C100, 0)) // with ties, SORTBY gives a clean list: =TAKE(SORTBY(B2:C100, C2:C100, -1), 2)
36

Write an Excel formula to count the number of unique customers in a column.

Save to ▾
Show answer
// Excel 365 =COUNTA(UNIQUE(A2:A500)) // unique customers in the North region only =COUNTA(UNIQUE(FILTER(A2:A500, B2:B500="North"))) // older versions (no blanks in range) =SUMPRODUCT(1/COUNTIF(A2:A500, A2:A500))
37

Write an Excel formula for a two-way lookup that finds sales for a given product and month.

Save to ▾
Show answer
// products in A2:A50, months in B1:M1, data in B2:M50 // G1 = product, G2 = month =INDEX(B2:M50, MATCH(G1, A2:A50, 0), MATCH(G2, B1:M1, 0)) // Excel 365 alternative =XLOOKUP(G1, A2:A50, XLOOKUP(G2, B1:M1, B2:M50))
38

Write an Excel formula for month-to-date sales up to today.

Save to ▾
Show answer
// Dates in Sales[Date], amounts in Sales[Amount] =SUMIFS(Sales[Amount], Sales[Date], ">=" & EOMONTH(TODAY(), -1) + 1, Sales[Date], "<=" & TODAY()) // EOMONTH(TODAY(),-1)+1 gives the first day of the current month
39

Tell me about a time you found an error in a spreadsheet just before it went to management.

Save to ▾
Show answer
Situation: an hour before a quarterly review I noticed regional totals were higher than the company total. Task: find the issue without delaying the meeting. Action: I traced it to a SUMIFS range that stopped at row 5000 while new data went beyond it, plus one duplicated region. I fixed both, converted the data to an Excel Table, and added a check cell comparing totals. Result: the correct deck went out on time and the check became part of our template.
40

The totals in your Excel report differ from the finance system by a few rupees. What could cause this?

Save to ▾
Show answer
Usually rounding. Excel may display rounded values while summing the full-precision numbers, or line items were rounded individually in the source system and the total was computed differently. Check whether finance rounds each invoice line before summing, and apply ROUND at the same level in Excel. Other causes include floating-point artifacts, values stored as text being skipped by SUM, or hidden filtered rows. Agree and document one rounding rule with finance.
41

What is Excel Solver and give a business example of using it.

Save to ▾
Show answer
Solver is an optimisation add-in that finds the best value of a target cell by changing decision cells within constraints. Unlike Goal Seek, it handles many variables and constraints. Example: choose how many units of three products to make to maximise profit, subject to machine hours, raw material and minimum order limits. You set the objective, the variable cells, constraints and a method such as Simplex LP for linear problems.
42

Finance asks you to build a monthly cohort retention table in Excel from raw order data. How do you approach it?

Save to ▾
Show answer
Add a helper column for each customer's first order month using MINIFS on customer ID, and another for the order month. Compute the month index as the difference in months between them. Then build a PivotTable or COUNTIFS grid with cohort month as rows, month index as columns and distinct customers as values. Divide each cell by the month-zero count to get retention percentages and add a colour scale to show decay.
43

How can LET and LAMBDA make complex Excel formulas easier to maintain? Give an example.

Save to ▾
Show answer
// LET names intermediate values so they are calculated once =LET(rev, SUM(C2:C100), cost, SUM(D2:D100), IF(rev=0, 0, (rev - cost) / rev)) // LAMBDA saved in Name Manager as MARGIN =LAMBDA(rev, cost, IF(rev=0, 0, (rev - cost) / rev)) // then use =MARGIN(C2, D2) anywhere in the workbook

Frequently asked questions

How important is Excel for Business Analysts in Business Analytics interviews?

Excel is still the daily tool for most business analysts in India. Interviews check lookups, SUMIFS and dynamic arrays, PivotTables, what-if analysis, data cleaning and building simple models and dashboards. Expect live formula questions and scenarios about reconciling files, fixing errors and automating repetitive monthly reports with Power Query and tables.

How should I use the Excel for Business Analysts sheet?

Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.