Advanced Excel Interview Questions for 3 Years Experience
40 questions with answers for candidates with 3 years of experience — 3 easy, 33 medium, 4 hard.
What interviewers expect: 3 years experience
Three-year candidates are expected to design solutions, not just use features. Questions cover the Data Model, relationships, DAX measures for YTD and growth, dashboard performance and choosing between Power Query and VBA. Interviewers ask about stakeholder requirements and how you ensured data accuracy across many reports.
This set is weighted towards Pivot Tables and Data Summaries (10), Lookup and Dynamic Array Functions (8), Charts and Dashboards (5), Logical, Text and Date Functions (4).
Data Model and DAX Dashboard performance Automation design Stakeholder handling
Solved at this levelStart 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
What does SUMPRODUCT do and when would you use it?
SUMPRODUCT multiplies matching items in arrays and adds the results. It is ideal for weighted averages and for conditional sums in older Excel versions:
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10) weighted average price
=SUMPRODUCT((A2:A100="North")*(MONTH(D2:D100)=4)*C2:C100)
The second example handles a condition on MONTH, which SUMIFS cannot do directly. Avoid full-column references because SUMPRODUCT evaluates every row.
02
How do you show actual versus target in a compact bullet-style chart?
Create a clustered column chart with Target and Actual series. Format the Target series with no fill and a thick outline or a light colour, set Series Overlap to 100 percent so both bars sit on top of each other, and make the Actual bar narrower by putting it on the secondary axis with a larger gap width. Match both axis scales. Readers immediately see whether Actual falls short of or crosses the target.
03
A macro works on your computer but fails on a colleague's. What could be the reasons?
Common causes are hard-coded file paths or user names, missing library references shown as MISSING in Tools > References, macros blocked by Trust Center settings or because the file came from the internet, and 32-bit versus 64-bit Office, which needs PtrSafe in API declarations. Different regional settings for dates and decimals, or a renamed sheet, also break code. Use ThisWorkbook.Path and late binding to make macros portable.
04
Describe a time a formula mistake of yours reached a stakeholder. What did you do?
Situation: a regional sales summary I sent showed one zone 18% higher than actual. Task: correct it before decisions were taken. Action: I traced it to a SUMIFS range that stopped one row short, sent a corrected file within the hour with a short note explaining the cause, and added a check cell comparing the summary total to the raw data total. Result: the manager appreciated the quick ownership and the check now catches such gaps automatically.
05
Tell me about a time data from two teams did not match. How did you resolve it?
Situation: finance and sales reported different revenue for the same quarter. Task: find the gap before the review meeting. Action: I pulled both datasets, built a key on invoice number, and used lookups to list invoices present in only one file. Most of the gap came from credit notes that sales did not deduct and invoices booked on the last day. Result: both teams agreed a single definition and I added a reconciliation tab to the monthly pack.
06
You need product category in the Sales table to create a calculated column. How do you do it in the Data Model?
Create a relationship from Sales[ProductID] to Products[ProductID], then add a calculated column in Sales:
Category = RELATED(Products[Category])
RELATED follows the many-to-one relationship and works only in a row context. Often you do not need the column at all, because the pivot can use Products[Category] directly through the relationship, which saves memory.
When you click a pivot cell while writing a formula, Excel inserts GETPIVOTDATA, which pulls a value by field names instead of cell position:
=GETPIVOTDATA("Amount",$A$3,"Region","North","Month","Apr")
It keeps working when the pivot layout changes, which suits KPI cards in dashboards. If you want plain references, turn it off in PivotTable Analyze > Options > Generate GetPivotData.
08
Your Power Query output has 20 lakh rows and Excel says it cannot load it to a sheet. What do you do?
A worksheet holds at most 1,048,576 rows, so the result will not fit. Choose Close & Load To and select Only Create Connection with Add this data to the Data Model. The model compresses data and can handle many millions of rows, and you analyse it with a pivot table or DAX measures. If users only need summaries, group the data inside Power Query before loading.
09
How do you build a KPI card showing achievement against target with an up or down indicator?
Calculate achievement in a cell, for example =Actual/Target-1, and apply a custom number format that adds arrows and colours:
[Color10]▲ 0.0%;[Red]▼ 0.0%;0.0%
Place the actual value in a large font above it, link a text box to the cells for flexible placement, and keep labels short like Revenue vs Target. Conditional formatting icon sets are an alternative but are less precise.
Use On Error GoTo to jump to a handler, report the problem and clean up:
Sub ImportData()
On Error GoTo Fail
Application.ScreenUpdating = False
' ... main code ...
Done:
Application.ScreenUpdating = True
Exit Sub
Fail:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Done
End Sub
Avoid blanket On Error Resume Next, which hides real failures.
11
How do you use approximate match to apply discount or tax slabs?
Create a slab table with the lower limit of each band in ascending order and the rate next to it. Approximate match returns the row for the largest limit not greater than the value:
=VLOOKUP(B2,Slabs!$A$2:$B$6,2,TRUE)
=XLOOKUP(B2,Slabs[From],Slabs[Rate],,-1)
If B2 is 7.5 lakh and the bands start at 0, 3, 7 and 10 lakh, the 7 lakh row is returned. Unsorted data gives wrong results silently.
12
Walk through creating a pivot showing monthly sales by region.
Click inside the data, press Ctrl+T to make it a Table, then Insert > PivotTable > New Worksheet. Drag Region to Rows, Order Date to Columns and Amount to Values. Right-click a date and choose Group > Months (and Years if data covers more than one year). Set Value Field Settings > Number Format to #,##0 and add a slicer for Product if needed. Refresh after new data arrives.
CALCULATE evaluates an expression under modified filters. It is the most important DAX function:
North Sales := CALCULATE([Total Sales],Region[Region]="North")
Online Sales 2026 := CALCULATE([Total Sales],Sales[Channel]="Online",'Date'[Year]=2026)
Each filter argument replaces any existing filter on that column, while filters on other columns from the pivot still apply. Wrapping a filter in KEEPFILTERS adds to the existing filter instead of replacing it.
14
You are given raw sales data and asked to build an interactive dashboard. Walk through your approach.
Clarify the audience, KPIs and refresh frequency first. Load and clean the data through Power Query into a Table or the Data Model. Build pivots for each view on a calculation sheet, create pivot charts, and connect slicers for region, product and month to all pivots. Add KPI cards using GETPIVOTDATA, format consistently, protect the layout and test that Refresh All updates everything when new data is added.
15
What are slicers and timelines, and how do you connect one slicer to several pivots?
Slicers are clickable filter buttons for a field, and timelines are slider filters for dates by day, month, quarter or year. They make filtering obvious for dashboard users. To control several pivots, right-click the slicer and choose Report Connections, then tick each pivot. This works only for pivots built on the same source or data model, because they share the same pivot cache.
16
Build a live leaderboard of the top 5 sales reps by total sales using dynamic array functions.
Get unique reps, total their sales, sort descending and take the top five:
=LET(r,UNIQUE(Sales[Rep]),
t,SUMIFS(Sales[Amount],Sales[Rep],r),
TAKE(SORTBY(HSTACK(r,t),t,-1),5))
HSTACK puts names and totals side by side, SORTBY orders by totals, and TAKE keeps five rows. Without TAKE, use INDEX(...,SEQUENCE(5),{1,2}).
17
Your manager wants each product's share of sales within its region, not of the grand total. How do you show it?
Put Region and then Product in Rows, and add Sales to Values twice. For the second copy, open Value Field Settings > Show Values As > % of Parent Row Total. Each product then shows its share of its own region, and each region totals 100 percent. Using % of Grand Total instead would show share of the whole company, which answers a different question.
18
Commission is 2% up to 1 lakh, 5% up to 5 lakh and 8% above 5 lakh, applied as a flat rate on total sales. Write the formula.
With IFS:
=B2*IFS(B2>500000,8%,B2>100000,5%,TRUE,2%)
A cleaner and more maintainable option is a slab table (Lower: 0, 100001, 500001; Rate: 2%, 5%, 8%) with approximate match:
=B2*XLOOKUP(B2,Slab[Lower],Slab[Rate],,-1)
The -1 match mode returns the next smaller value, so changing rates only means editing the table.
19
What checks do you run when cleaning a new dataset?
Check row counts against the source, then look for duplicate keys, blanks in mandatory fields, inconsistent text such as Delhi and New Delhi, extra spaces and mixed case, dates stored as text or in the wrong locale, negative or extreme numbers that look like typos, and codes losing leading zeros. Power Query's Column Quality and Column Profile views show errors, empties and distinct counts for each column quickly.
20
Why is averaging a percentage column in a pivot often wrong, and what should you do instead?
An average of row-level margins gives each order equal weight, so a 100 rupee order counts as much as a 10 lakh order. The correct margin is total profit divided by total sales. Create a calculated field Margin = Profit / Sales, which Excel evaluates on summed values at every level, or a DAX measure =DIVIDE(SUM(Sales[Profit]),SUM(Sales[Amount])). Interviewers use this to test real analytical thinking.
21
Tell me about a time you got a last-minute analysis request. How did you handle it?
Situation: at 5 PM, my manager needed customer-wise outstanding for a client call at 9 AM. Task: deliver an accurate summary quickly. Action: I confirmed the exact question and cut-off date, pulled the ledger, built a pivot by customer and ageing bucket, cross-checked the total with the trial balance and flagged three unusual balances. Result: the report was ready by 7 PM, and my notes helped the manager answer follow-up questions in the call.
INDIRECT converts text into a reference, so =SUM(INDIRECT(B1&"!C2:C100")) sums the sheet whose name is typed in B1. It is useful for summary sheets that switch between months and for dependent dropdowns. The risks are that it is volatile, it breaks silently when sheets are renamed, it does not work with closed external workbooks, and formula auditing tools cannot trace it. Use it sparingly.
23
How do you look up a value using multiple criteria, such as Employee ID and Month?
In Microsoft 365, multiply logical tests so only the row matching all conditions becomes 1:
=XLOOKUP(1,(Pay[EmpID]=A2)*(Pay[Month]=B2),Pay[Salary],"Not found")
In older Excel use INDEX/MATCH entered as an array formula (Ctrl+Shift+Enter):
=INDEX(Pay[Salary],MATCH(1,(Pay[EmpID]=A2)*(Pay[Month]=B2),0))
For very large data a helper column joining ID and Month is faster.
24
What are dynamic arrays and what causes a #SPILL! error?
In Microsoft 365, a formula can return many values that spill into nearby cells, as with UNIQUE, FILTER, SORT and SEQUENCE. Other formulas can refer to the whole result with the spill operator, for example =COUNTA(E2#). #SPILL! appears when the spill area is blocked by data, the formula sits inside a Table, the area overlaps merged cells, or the result would run past the sheet edge. Clear the blocking cells to fix it.
25
How do you write a user-defined function (UDF) in VBA? Give an example.
Write a Public Function in a standard module and use it like any worksheet function:
Function GSTAmount(amount As Double, Optional rate As Double = 0.18) As Double
GSTAmount = Round(amount * rate, 2)
End Function
In a cell: =GSTAmount(B2) or =GSTAmount(B2,5%). UDFs cannot change other cells or formats, and they recalculate more slowly than built-in functions, so avoid them in very large ranges.
26
Return the Indian financial year (April to March) for a date, like FY 2026-27.
If the month is April or later, the FY starts in the same year; otherwise it started the previous year:
=LET(y,IF(MONTH(A2)>=4,YEAR(A2),YEAR(A2)-1),
"FY "&y&"-"&RIGHT(y+1,2))
For 15-May-2026 this gives FY 2026-27, and for 10-Feb-2026 it gives FY 2025-26. The fiscal quarter is =ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0).
27
A sales file has employee names but the master list has employee IDs, and some names repeat. How do you join them safely?
Do not look up by name alone, because a duplicate name returns the first match and silently assigns sales to the wrong person. First flag duplicates in the master with COUNTIF(names,name)>1. Then build a composite key, such as name plus branch or name plus email, in both tables and look up on that. Rows still ambiguous should go back to the data owner for an ID rather than be guessed.
28
How do you automatically highlight the highest and lowest points in a line chart?
Add two helper columns next to the data that show a value only at the max or min and #N/A elsewhere, because charts skip #N/A:
Max: =IF(B2=MAX($B$2:$B$13),B2,NA())
Min: =IF(B2=MIN($B$2:$B$13),B2,NA())
Add both as series, set them to markers only with distinct colours and data labels. When the data changes, the highlighted points move automatically.
29
Finance wants the sales pivot by quarter of the Indian financial year, starting in April. Date grouping only gives calendar quarters. How do you solve it?
Pivot date grouping uses calendar quarters, so add a helper column in the source data. For example, fiscal quarter ="Q"&ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0) and a fiscal year column. Use them as row fields instead of grouped dates. In Power Query or a Data Model, put these columns in a proper Date table so every report uses the same April to March logic.
Double-click any value cell and Excel creates a new sheet listing every source row that makes up that number. This drill-down is very useful for checking totals or answering a manager's why question. If the file is shared and you do not want people extracting raw data, untick Enable show details in PivotTable Options > Data, though this is not a strong security control.
31
Write a Power Query custom column that classifies orders into High, Medium and Low by amount.
Add Column > Custom Column and enter an M if expression; M is case-sensitive and uses lowercase if, then, else:
if [Amount] >= 100000 then "High"
else if [Amount] >= 25000 then "Medium"
else "Low"
Then set the new column type to Text. The Conditional Column dialog can build the same logic without typing, but writing M is faster for nested or combined conditions using and/or.
32
What is the difference between RELATED and RELATEDTABLE?
RELATED is used on the many side to fetch a single value from the one side, such as getting a product's category in each sales row. RELATEDTABLE is used on the one side to return all matching rows from the many side, so in Products you could write Order Count = COUNTROWS(RELATEDTABLE(Sales)). Both need an existing relationship and a row context.
33
What is the difference between a calculated field and a calculated item?
A calculated field creates a new value field from other fields, such as Margin = Profit / Sales, and it works on the summed totals. A calculated item creates a new member inside a field, such as North + East as a combined region. Calculated items cannot be used with the Data Model, can double-count in totals and slow pivots, so many analysts prefer grouping or a source column instead.
34
A column shows amounts rounded to whole rupees, but its total differs from a manual total of the displayed values by a few rupees. Why?
Number formatting only changes how a value looks; Excel still adds the full decimals stored in each cell. So the sum of hidden paise differs from the sum of the rounded figures shown. If the report must add up exactly as displayed, round the values with =ROUND(value,0) in the source formulas rather than using the Precision as displayed option, which permanently changes stored data for the whole workbook.
35
Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?
Load the data into Power Query, select the identifying columns such as Branch and Product, then right-click and choose Unpivot Other Columns. The month headers become an Attribute column and the numbers become a Value column; rename them to Month and Sales. Using Unpivot Other Columns rather than Unpivot Columns means new months added later are included automatically on refresh.
36
What principles do you follow when designing an Excel dashboard?
Start from the questions the audience asks. Put three to five headline KPIs at the top, trends and breakdowns below, and filters such as slicers in one consistent place. Use one colour palette with a highlight colour for what matters, avoid 3D and decorative effects, label units clearly like Rs lakh, and fit the view on one screen. Keep raw data and calculations on separate hidden sheets.
Both use the same VertiPaq engine and the DAX language, and both use Power Query for data preparation. A Power Pivot model can be imported into Power BI Desktop through File > Import, carrying queries, relationships and measures. This makes Advanced Excel with Power Pivot a natural stepping stone, since most of the model and DAX skills transfer directly to Power BI.
FILTER returns all rows that meet a condition and spills them into neighbouring cells:
=FILTER(Sales,Sales[Region]="South","No data")
=FILTER(Sales,(Sales[Region]=G1)*(Sales[Amount]>100000))
Multiplying conditions means AND; adding them with + means OR. Because the output updates live, FILTER is useful for searchable lists and drill-down sheets without needing pivot tables or macros. It requires Microsoft 365 or Excel 2021.
39
You receive 12 monthly sales files and need one pivot for the year. How do you build it?
Put all files in one folder and use Data > Get Data > From File > From Folder > Combine & Transform. Power Query appends them using the first file as a sample, adds the source file name as a column and applies the same cleaning steps. Load the result as a Table or to the Data Model and build the pivot on it. Next year, adding a file to the folder and clicking Refresh All updates everything.
40
Tell me about a time your automation failed after you handed it over. What did you do?
Situation: a report macro I wrote stopped working for the accounts team. Task: fix it quickly and stop it happening again. Action: I found that a sheet had been renamed and the macro used a fixed sheet name and file path. I fixed it with ThisWorkbook.Path and code names, added an error handler with a clear message, and wrote a short usage note. Result: it ran reliably for the rest of the year and I now test macros on another user's machine before handover.
Frequently asked questions
What is asked in Advanced Excel interviews for 3 years of experience?
Three-year candidates are expected to design solutions, not just use features. Questions cover the Data Model, relationships, DAX measures for YTD and growth, dashboard performance and choosing between Power Query and VBA. Interviewers ask about stakeholder requirements and how you ensured data accuracy across many reports.
How many questions should I practise before a Advanced Excel interview?
Work through all 40 questions here, then the company-wise sets for the employers you are targeting. Quality matters more than count: be able to explain why each answer is right.
Which topics matter most for 3 years of experience?
Focus first on Data Model and DAX, Dashboard performance, Automation design, Stakeholder handling.