20 Advanced Excel Questions Asked In Interviews
You may not remember that they asked you to recite the arguments of INDEX, but you will remember that you were asked to explain what happens if you insert a column while the interviewer leant back. This is a real test. Interviewers want to see that you have had experience with a particular method and know where it can fail. Below are 20 questions that come up in analyst interviews, finance and operations interviews. For each question, I have indicated what the interviewer is looking for, and what a good answer should include. Some of the functions mentioned (XLOOKUP, FILTER, UNIQUE, SORT, LET) are only available in newer versions of Excel. Similarly, TEXTSPLIT and LAMBDA only work with Microsoft 365. However, if you include an alternative function, you’ll show that you’ve used Excel in environments where half of the team have an old version.
Table of Contents
Lookups and Formulas
1. What is the difference between VLOOKUP and XLOOKUP?
What they are looking for: Knowing what the limitations of VLOOKUP are.
VLOOKUP doesn’t have an option to search backwards. You search the first column and return a value to the right. You count the column number manually and if a column is added later, you won’t be aware that you’re now using the incorrect column number. Also if you forget the final argument, it will default to an approximate match.
XLOOKUP allows you to define a lookup range and a return range so you can search in both directions. It defaults to an exact match and it has an option to include an ‘if not found’ value.
=XLOOKUP(F2,A2:A100,C2:C100,”Not found”)
However, if you share a file with someone on an old version this will fail. So VLOOKUP or INDEX-MATCH will still be useful.
Read More: What Is Data Fluency? Why It Matters for Every Business
2. Why might someone still use INDEX-MATCH? How do you do a two way lookup?
What they are looking for: Knowing there are alternatives to the latest function. INDEX-MATCH will work on all versions. It can search backwards and forwards. The formula doesn’t rely on counting columns as the ranges are directly referenced.
=INDEX(C2:C100,MATCH(F2,A2:A100,0))
For a two-way lookup you’ll need to use two MATCH functions.
=INDEX(B2:M50,MATCH(product,A2:A50,0),MATCH(month,B1:M1,0))
3. How do you do a lookup with multiple criteria?
What they are looking for: Knowing how to overcome the limitations of a function. There are two ways to do this. In newer versions of Excel you can use:
=XLOOKUP(1,(A2:A100=F2)(B2:B100=G2),C2:C100)
Using multiplication creates a 1 only when both criteria are met. The means you multiply the results. In older versions create a helper column combining the two values (=A2&”|”&B2) and then look up the combined value. If you’re only doing a sum then SUMIFS can perform multiple criteria.
4. How do SUMIFS and COUNTIFS work? What are common mistakes?
What they are looking for: Attention to detail.-The SUMIF function has different arguments to SUMIFS. SUMIF has the range, then criteria and then the sum range. SUMIFS has the sum range first. When creating criteria from cells:
=SUMIFS(D2:D500,A2:A500,”North”,B2:B500,”>=”&F1)
The ‘>=’&F1 part is what they want to see. If you just type the value into the formula then someone will have to alter it every month.
Read More: What Happens When Data Is Inaccurate?
5. What is SUMPRODUCT? When would you use it?
What they are looking for: Knowing how to use array functions without helpers.-This function multiplies arrays and sums the result. It can be used to calculate quantity x price = =SUMPRODUCT(B2:B10,C2:C10). It has the ability to do multiple criteria:
=SUMPRODUCT((A2:A100=”North”)(B2:B100=”Q1″)C2:C100)
It is the simplest way to calculate a weighted average. =SUMPRODUCT(values,weights)/SUM(weights). If someone asks for a weighted average this is what they’ll be looking for.
6. What does the $ symbol do in a formula?
What they are looking for: Understanding of references.-$$A$1 will lock both the column and row. $$A1` locks only the column. `A$1 will only lock the row. A good example is to create a multiplication table. You’ll have one formula that you can drag across and down. You would have a formula like =$$A2B$1. This locks the column for the row headers and the row for the column headers. If you can explain why the $ symbols are in those positions you’ve passed this question, which trips up surprising amount of people.
7. What is the difference between IFERROR and IFNA? What are common errors?
What they are looking for: Knowing how to handle errors.-IFERROR covers all errors. This is a great feature but also a dangerous one as it can hide errors that you may have in your file. IFNA will only cover #N/A errors. This is what will happen if your lookup doesn’t find a value so it is usually the better option. Some errors to know: #N/A = Value not found. #REF! = Referenced cell has been deleted. #VALUE! = Wrong type of data. #DIV/0! = Divided by zero. #NAME? = Misspelled function/name. #SPILL! = Dynamic array has something in the way.
8. What are dynamic functions? e.g. FILTER, SORT and UNIQUE.
What they are looking for: Have you used modern Excel.-These functions will return multiple values. The values will spill down.
=FILTER(A2:C100,B2:B100>1000,”None”)
=SORT(UNIQUE(A2:A100)) If there is something in the spill range you’ll get a #SPILL! error. To fix this just clear those cells. You can also reference an entire spilled range by adding a # at the end of the cell reference. This will show that you’ve used this before.
Read More: Why Your Business Dashboard Might Be Misleading You
Cleaning and Structure
9. How can you identify and remove duplicates safely?
What they are looking for: Caution.-Before removing duplicates, highlight the duplicates using conditional formatting. Go to Highlight Cells Rules > Duplicate Values. Create a helper column with a formula =COUNTIF($$A$2:A2,A2)>1. This will show all duplicates. You can use the UNIQUE function to create a clean list. The Remove Duplicates button under the Data tab is quick but permanent. Clean your data first. “John Smith” and “john smith ” (space at the end) will look like two separate people. Often saying “I back up my data” will make your answer sound experienced.
10. How can you clean and split messy data?
What they are looking for: Practical skills.-Use the TRIM function to remove extra spaces. The CLEAN function will remove non-printable characters. Often when data is copied from the web it will have non-breaking spaces. To remove these use SUBSTITUTE(A2,CHAR(160),” “). To split a full name use TEXTSPLIT (only in 365). =TEXTSPLIT(A2,” “). In older versions use LEFT, FIND and MID functions. Or use Text to Columns. Or Flash Fill (Ctrl+E). To merge cells use TEXTJOIN.
11. Why would you use Excel Tables and structured references?
What they are looking for: Creating workbooks that are sustainable-By pressing Ctrl+T you can convert a range into an Excel Table. This means that when you add new rows it will automatically expand. Formulas will automatically fill down. You can create formulas that look like sentences. =SUM(Sales[Revenue]). PivotTables and charts based on an Excel Table will automatically include new data. It also removes an entire category of error where a formula will stop one row short.
12. How do you create a dependent dropdown?
What they are looking for: Data Validation-The traditional way to create this is to name your lists for each city. Then use INDIRECT(A2) as the source for the second dropdown. (Note: Names cannot have spaces so need to be dealt with.) In newer versions you can use a helper cell with the formula =UNIQUE(FILTER(Cities,Countries=A2)). Then use this as the source in your validation. By mentioning both methods you’ll show you know the pros and cons. INDIRECT will work in all versions but can be fragile and constantly recalculate. The dynamic array version is neater but requires a newer version of Excel.
13. How can you use a formula in conditional formatting?
What they are looking for: Mixed references-Go to New Rule. Use a formula to determine which cells to format. To highlight an entire row if the status is overdue:
=$D2=”Overdue”
Apply this to $$A$2:$$F$100. The $ symbol will lock the column so all the cells in the row will look at column D. The row will remain unlocked so each row will check its own status. If you can explain this in a sentence you’ve shown that you understand this feature and the references.
Analysis and Modelling
14. How can you use PivotTables beyond the basics?
What they are looking for: Depth-Group dates into months, quarters and years. Use calculated fields such as Profit = Revenue – Cost. Use Show Values As to show % of total. Slicers and timelines. Common mistake – PivotTables will not update automatically. If you base your PivotTable on an Excel Table you can refresh to solve this issue. Most people will have this issue at least once.
15. When would you use Power Query instead of formulas?
What they are looking for: Knowing when to use the right tool-Power Query (Data > Get Data) will record the steps you have taken to clean your data. This means you can click refresh and it will do everything in one click. It is ideal for combining multiple files from a folder, unpivot data, combine tables and anything you’ll need to do again next month. Formulas are ideal for small calculations that are live.
If VBA comes up, Power Query is for transforming data whereas VBA is for automating actions in Excel. Be honest with how much you know. Usually bluffing won’t pay off.
16. What are the Data Model and Power Pivot? Why are they important?
What they are looking for: Have you moved beyond single sheet Excel-This allows you to import multiple tables and link them using relationships. You can create one PivotTable from all the tables without any lookup formulas. It can also handle more rows than a worksheet can. (1,048,576). You can create calculations using measures in DAX.
Total Sales := SUM(Sales[Amount])
Even having a basic knowledge of relationships and measures puts you ahead.
17. What are Goal Seek, Data Tables, Scenario Manager and Solver?
What they are looking for: Knowing which tool to use.-These are what-if tools. Goal Seek will adjust one input to get to a desired result. E.g. What sales do we need to reach a profit of ₹10 lakh? Data Tables will show results for one or two inputs. E.g. Loan payments for different interest rates and terms. Scenario Manager will save named scenarios. E.g. Best case, base case, worst case. Solver can be used for optimisation with multiple variables and constraints. It is more important to know which tool to use.
18. How do you handle dates?
What they are looking for: Experience with real reporting.-Dates are effectively serial numbers so you can subtract them. Useful functions to know are EOMONTH(date,0) to get the last day of the month, NETWORKDAYS(start date, end date, holidays) to get working days, DATEDIF(start date, end date,”Y”) to get completed years (This works even though it isn’t listed) and TEXT(date,”mmm-yyyy”) to create labels. In India you’re likely to be asked about fiscal years. This formula will return the year the fiscal year ends in (April to March):
=YEAR(A2)+(MONTH(A2)>=4)
Performance and Trust
19. Why is my workbook slow? How can I improve it?
What they are looking for: Real world experience.-First check for volatile functions. These functions include NOW, TODAY, RAND, OFFSET and INDIRECT. These will recalculate every time something changes. A few won’t cause any issues. However, thousands will slow your workbook down. Often OFFSET can be replaced with INDEX Avoid referencing entire columns in large array formulas. Reduce the ranges used in conditional formatting. Change to manual calculation while editing. Paste final results as values. Move any heavy transformations to Power Query or the Data Model. LET (Newer versions) can be used. This allows you to name part of your formula. Excel will calculate this once.
=LET(sales,SUM(B2:B100),cost,SUM(C2:C100),(sales-cost)/sales)
20. How can I ensure a workbook created by someone else is accurate?
What they are looking for: Judgement. Use Trace Precedents and Trace Dependents to see where the numbers are coming from. Use Evaluate Formula to step through a complex formula. Press Ctrl+` to view all formulas. Go To Special to find any hard coded numbers. Check Name Manager, hidden sheets, hidden rows and external links (Data > Edit Links). Ensure the main totals match an independent source. Try extreme values. A workbook can look very professional but it may not be accurate.
How to Prepare
Although reading through a list like this will help you for the evening, it is best to practice. Type out all the formulas. Create a small dataset and break everything. Every error message will teach you something. Practice on a messy file. Interview questions will have blank cells, inconsistent text and duplicate rows. Don’t practice on a clean file. Practice saying your answers out loud. Being able to explain why you would use XLOOKUP over VLOOKUP in two sentences is a different skill. Add “and the risk is…” to your answers. Often this will make your answer sound experienced. Have one project ready to talk about. Being able to walk through one piece of work is more valuable than knowing 20 functions. This is something we encourage at Ankashram. We teach Excel through full, slightly messy projects. This is where people will discover why these functions are useful. Usually an interviewer will be able to tell if you have done the work within a minute.
Frequently Asked Questions
Q1.Will they expect me to know the exact syntax of formulas?
Not always. Many interviews will be open book. The interviewer will mainly be looking at how you’re thinking. Explaining why you would use a certain function even if you struggle with the arguments will usually be better than knowing them perfectly.
Q2.What are the most common advanced Excel topics?
Lookups (VLOOKUP, XLOOKUP, INDEX-MATCH), SUMIFS, COUNTIFS, PivotTables, cleaning data and conditional formatting. Power Query and dynamic array functions are becoming more common, particularly for analyst roles.
Q3.What if they use an old version of Excel?
Mention the older alternative. E.g. INDEX-MATCH. This shows you know about compatibility.
Q4.Is VBA/macros still asked about?
Sometimes. Particularly in finance and operations roles where there is lots of repetitive reporting. However, Power Query has taken over many of these roles for many analyst roles. Knowing the difference and being honest about your level of VBA will usually be sufficient.
Q5.How long will it take me to prepare?
It will depend on your current level. If you use Excel at work then a couple of weeks of focused practice on messy datasets will be enough. If you are starting from the basics it will take longer. Creating one or two full projects will help more.
Q6.How can Ankashram help me prepare for Excel/data analysis interviews?
We teach using hands-on projects based on realistic, imperfect data. This means you will practice cleaning, analysing and explaining the result.