What do Excel interviews for analyst roles test?
| Area | Typical question | What a good answer shows |
|---|---|---|
| Lookups | "Bring the price from another sheet" | Exact-match habits, handling missing values |
| Pivot tables | "Revenue by region and month" | Speed, grouping, % of total, refreshing |
| Conditional formulas | "Total North laptop sales" | SUMIFS/COUNTIFS with several criteria |
| Conditional formatting | "Highlight orders above ₹50,000" | Formula-based rules, mixed references |
| Data cleaning | "Why does this lookup fail?" | Spaces, numbers stored as text, duplicates |
| Charts | "Show the trend and the share" | Choosing a chart for the message |
The examples below use this sales table in A1:G11 (headers in row 1). It is the same data we use in our guide to 30 advanced Excel formulas, so you can reuse one practice file.
| OrderID | Date | Region | Rep | Product | Units | Revenue (₹) |
|---|---|---|---|---|---|---|
| 1001 | 02-Apr-2026 | North | Priya | Laptop | 2 | 90,000 |
| 1002 | 05-Apr-2026 | South | Rahul | Mouse | 10 | 5,000 |
| 1003 | 09-Apr-2026 | West | Priya | Monitor | 3 | 36,000 |
| 1004 | 15-Apr-2026 | North | Aman | Laptop | 1 | 45,000 |
| 1005 | 20-Apr-2026 | East | Rahul | Keyboard | 5 | 7,500 |
| 1006 | 03-May-2026 | South | Aman | Monitor | 2 | 24,000 |
| 1007 | 08-May-2026 | West | Priya | Mouse | 8 | 4,000 |
| 1008 | 14-May-2026 | North | Rahul | Laptop | 3 | 1,35,000 |
| 1009 | 21-May-2026 | East | Aman | Keyboard | 4 | 6,000 |
| 1010 | 28-May-2026 | South | Priya | Laptop | 1 | 45,000 |
A price list sits in J1:K5: Laptop 45000, Monitor 12000, Keyboard 1500, Mouse 500.
Excel lookup interview questions
1. What is the difference between VLOOKUP, XLOOKUP and INDEX/MATCH?
VLOOKUP searches the first column of a range and returns a column to its right, chosen by number. XLOOKUP takes separate lookup and return ranges, can look left, defaults to an exact match and has a built-in "if not found" argument. INDEX/MATCH does the same job as XLOOKUP and works in every version. XLOOKUP needs Excel 2021, 2024 or Microsoft 365.
=VLOOKUP("Monitor", J2:K5, 2, FALSE)
=XLOOKUP("Monitor", J2:J5, K2:K5, "Not listed")
=INDEX(K2:K5, MATCH("Monitor", J2:J5, 0))All three return 12000.
2. Why does VLOOKUP return #N/A when the value is clearly there?
The usual causes are: extra spaces ("Laptop " is not "Laptop"), numbers stored as text in one table and as numbers in the other, a missing FALSE so it does an approximate match on unsorted data, or a lookup range that does not start at the key column. Check with =LEN(A2) and =ISNUMBER(A2), then clean with TRIM or VALUE.
3. Why does VLOOKUP return a wrong value without any error?
Almost always because the last argument was left out. It then defaults to an approximate match, which assumes the first column is sorted and quietly returns the nearest smaller value. Always write FALSE (or 0) for exact matches.
4. How do you look up with two conditions?
With XLOOKUP or INDEX/MATCH on a multiplied condition, or with a helper column that joins the keys:
=XLOOKUP(1, (C2:C11="South")*(E2:E11="Laptop"), G2:G11)This returns 45000 (order 1010). If several rows can match and you want a total, use SUMIFS instead of a lookup.
Pivot table interview questions
5. What is a pivot table and when would you use one?
A pivot table summarises a flat table by dragging fields into Rows, Columns, Values and Filters. Use it for fast exploration and standard summaries such as revenue by region and month. Formulas are better when you need a fixed report layout that other cells refer to.
6. How do you show each rep's share of total revenue?
Put Rep in Rows and Revenue in Values, then right-click a value → Show Values As → % of Grand Total. On our data: Priya 44.0%, Rahul 37.1%, Aman 18.9%.
7. How do you summarise daily dates by month in a pivot table?
Right-click a date in the pivot → Group → choose Months (and Years, if data spans more than one year, so that April 2025 and April 2026 are not merged). If Group is greyed out, the column probably contains text or blank "dates". Fix the source data.
8. Your pivot table is not showing new rows. Why?
Pivot tables do not update automatically. Click Refresh. If the new rows are outside the original source range, change the data source, or better, format the source as an Excel Table (Ctrl+T) so that the range grows on its own.
9. How do you count distinct customers in a pivot table?
When creating the pivot, tick Add this data to the Data Model. Then choose Distinct Count in Value Field Settings. Without the Data Model, pivot tables only offer Count. With dynamic arrays, =COUNTA(UNIQUE(D2:D11)) gives the distinct count of reps (3) as a formula.
Conditional formula and reference questions
10. What is the difference between relative, absolute and mixed references?
A2 changes when copied in both directions; $A$2 never changes; $A2 locks the column only, and A$2 locks the row only. Mixed references are what make conditional formatting rules and multiplication grids work. Press F4 to cycle through them.
11. How do you total revenue for North laptops only?
=SUMIFS(G2:G11, C2:C11, "North", E2:E11, "Laptop")Result: 270000. Note that the sum range comes first in SUMIFS but last in the older SUMIF, which is a classic trick question.
12. What is the difference between COUNT, COUNTA, COUNTBLANK and COUNTIF?
COUNT counts numbers, COUNTA counts non-empty cells, COUNTBLANK counts empty cells, and COUNTIF/COUNTIFS count cells that meet conditions. =COUNT(A1:A11) returns 10 because the header is text, while =COUNTA(A1:A11) returns 11.
Turn Excel practice into interview-ready work
The ISS Data & Business Intelligence program starts with business metrics and Excel reporting before moving on to SQL and Python, and includes mock interviews in its career support. Review the curriculum, or download the free Data Analyst Starter Kit on this page.
View Data & Business Intelligence curriculum →Conditional formatting interview questions
13. How do you highlight the entire row when revenue is above ₹50,000?
Select A2:G11 → Conditional Formatting → New Rule → Use a formula, and enter:
=$G2>50000The $ locks column G, so every cell in the row checks the revenue column. The row reference stays relative, so each row checks itself. Rows for orders 1001 and 1008 are highlighted.
14. How do you highlight duplicates?
For a single column, use Highlight Cells Rules → Duplicate Values. For duplicates across two columns, such as the same rep selling the same product, use a formula rule:
=COUNTIFS($D$2:$D$11, $D2, $E$2:$E$11, $E2)>1On our data this flags orders 1001 and 1010 (Priya, Laptop).
15. What other conditional formatting do analysts use?
Top/Bottom rules (top 10 items or top 10%), data bars to show size inside a table, and colour scales for heatmaps, such as sales by region and month. Keep it restrained: if everything is coloured, nothing stands out.
Data cleaning interview questions
16. How would you clean a messy export before analysis?
A good answer is a sequence: keep a raw copy, then remove blank rows and repeated headers, trim spaces (TRIM, CLEAN), fix numbers and dates stored as text, standardise categories ("Bangalore" vs "Bengaluru"), remove or flag duplicates, and check totals against the source. If this happens every week, do it once in Power Query and refresh.
17. How do you split one column into several?
Use Data → Text to Columns for a one-time split, TEXTSPLIT or TEXTBEFORE/TEXTAFTER for a formula that updates (Excel 2024 and Microsoft 365), or Flash Fill (Ctrl+E) for pattern-based extraction. Flash Fill is quick but static, so check its output.
18. What is Power Query and why is it useful?
Power Query (Data → Get & Transform) records cleaning steps such as removing columns, changing types, merging and unpivoting, and replays them on new files when you refresh. It also underpins Power BI, so the skill transfers directly; see our Power BI interview questions.
19. How do you stop bad data being entered in the first place?
Use Data Validation: dropdown lists for categories, whole-number or date limits, and input messages. Combined with an Excel Table and protected formula cells, it prevents most manual-entry errors in shared trackers.
Excel chart interview questions
20. Which chart would you use for trend, comparison and share?
Line or column chart for a trend over time; bar chart, sorted, for comparing categories; stacked bar or a simple table for share. Use pie charts only for 2–4 parts. For revenue and order count on one chart, use a combo chart with a secondary axis, and label both axes clearly.
21. How do you make a chart update automatically as data grows?
Build it from an Excel Table or a pivot table (a PivotChart). New rows flow into the chart after a refresh, with no manual range editing.
Practical Excel test: can you complete it in 30 minutes?
Many companies send a small file and a list of tasks. Use the 10-row table above and try these without looking at the answers. Time yourself.
- What is total revenue?
- Build a pivot table of revenue by Region (rows) and Month (columns).
- What share of total revenue did each rep bring in?
- What was month-on-month revenue growth from April to May?
- Add a Price column using a lookup from J1:K5, then add a check column that confirms Units × Price = Revenue for every row.
- Highlight whole rows where revenue is above ₹50,000.
- Flag rows where the same rep sold the same product more than once.
- What is the average number of units per laptop order?
- Which product brings in the most revenue?
- Write two sentences for a manager summarising what you found.
Answer key
| Task | Answer | One way to get it |
|---|---|---|
| 1 | ₹3,97,500 | =SUM(G2:G11) |
| 2 | April / May: East 7,500 / 6,000; North 1,35,000 / 1,35,000; South 5,000 / 69,000; West 36,000 / 4,000. Totals 1,83,500 / 2,14,000 | Pivot table with Date grouped by Months |
| 3 | Priya 44.0%, Rahul 37.1%, Aman 18.9% | Show Values As → % of Grand Total |
| 4 | +16.6% | =(214000-183500)/183500 |
| 5 | All 10 checks TRUE | =XLOOKUP(E2,$J$2:$J$5,$K$2:$K$5) then =F2*H2=G2 |
| 6 | Orders 1001 and 1008 | Formula rule =$G2>50000 |
| 7 | Orders 1001 and 1010 | =COUNTIFS($D$2:$D$11,D2,$E$2:$E$11,E2)>1 |
| 8 | 1.75 units | =AVERAGEIFS(F2:F11,E2:E11,"Laptop") |
| 9 | Laptop, ₹3,15,000 (79% of revenue) | Pivot by Product, sorted descending |
| 10 | Example: "Revenue grew 16.6% from April to May, driven by South (up from ₹5,000 to ₹69,000) while West fell. Laptops make up about 79% of revenue, so results depend heavily on a few large orders." | Your own words; lead with the number |
We checked every answer by recomputing it from the table in a script. Task 10 is often what separates candidates: interviewers want the "so what", not only the numbers.
How should you prepare for an Excel interview?
- Rebuild the practice file and do the test twice: once with formulas, once with pivot tables.
- Learn keyboard shortcuts you will use under time pressure: Ctrl+T (Table), Ctrl+Shift+L (filters), F4 (lock references), Ctrl+E (Flash Fill), Alt+N+V (insert pivot table, in Windows Excel).
- Ask which Excel version the company uses. If it is older than 2021, practise INDEX/MATCH rather than XLOOKUP.
- Prepare SQL and a BI tool too. Most analyst roles test more than Excel; see our SQL interview questions and the broader data analytics interview questions guide.
Frequently Asked Questions
What Excel skills are tested in a data analyst interview?
Lookups (VLOOKUP, XLOOKUP, INDEX/MATCH), pivot tables, SUMIFS and COUNTIFS, conditional formatting, data cleaning and basic charts. Many companies also give a short practical task on a sample file and ask you to explain what you found.
Is VLOOKUP still asked in Excel interviews?
Yes. VLOOKUP is still common in company files, so interviewers often ask about it, including why it returns #N/A or a wrong value. Knowing XLOOKUP and INDEX/MATCH as well shows you can work in both old and new versions.
Do I need to know VBA or macros for an analyst interview?
Usually not for entry-level analyst roles, unless the job description mentions automation or VBA. Pivot tables, formulas and Power Query are more commonly tested. Check the job post.
How long is a typical Excel practical test?
It varies by company, from a short task during the interview to a take-home file. Practise completing a 10-task test like the one in this guide in about 30 minutes, then explain your findings in two or three sentences.
What is the difference between Excel and Power Query?
Excel formulas calculate values in cells. Power Query records a series of cleaning and reshaping steps and reapplies them whenever you refresh, which makes it better for recurring messy data. Power Query is built into Excel and Power BI.
Sources and methodology
Function behaviour and version availability were checked on Microsoft Support pages in September 2026. The sample data is made up, and every answer in the practice test was recomputed from it in a script.
- Microsoft Support: XLOOKUP (versions; not available in Excel 2016 or 2019), UNIQUE, TEXTSPLIT and TEXTBEFORE (Excel 2024 and Microsoft 365), checked September 2026.
- Microsoft Support, About Power Query in Excel, checked September 2026.
- Microsoft Support, Count unique values among duplicates, checked September 2026.
The question list, the test format and the preparation tips are ISS editorial guidance based on common analyst interview formats, not survey data.
Next steps
Excel (PivotTables, lookup formulas and business reports) is Week 2 of the Data & Business Intelligence program, followed by SQL, Python and dashboards. Career support includes mock interviews and resume reviews; ISS does not guarantee placement. The free Data Analyst Starter Kit on this page adds a skills checklist and more interview questions.
Applying is free, and you pay only after accepting an offer. Apply here.