Advanced Excel Interview Questions for 10 Years Experience
40 questions with answers for candidates with 10+ years of experience — 12 medium, 28 hard.
What interviewers expect: 10 years experience
At ten years, roles are usually MIS head, reporting manager or analytics lead. Interviews focus on strategy: building and managing a reporting team, setting KPI definitions with leadership, tool choices, automation roadmaps and reducing manual effort across departments. Excel depth is assumed, and you will be asked how you measured the business impact of your work.
This set is weighted towards Power Pivot, Data Model and DAX (10), Macros and VBA Automation (7), Logical, Text and Date Functions (6), Power Query and Data Cleaning (6).
Team leadership KPI governance Automation roadmap Business impact
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
Calculate working days between two dates excluding Sundays and a holiday list.
NETWORKDAYS excludes Saturday and Sunday by default. For a six-day week common in India, use NETWORKDAYS.INTL with weekend code 11 (Sunday only):
=NETWORKDAYS.INTL(A2,B2,11,Holidays[Date])
To find a due date 10 working days after A2:
=WORKDAY.INTL(A2,10,11,Holidays[Date])
Keep the holiday list in a Table so it can be updated each year.
02
Your team wants lookups to show Not Found instead of #N/A for missing codes, without hiding genuine formula errors. How do you do it?
Use a function that only handles missing values, not every error:
=XLOOKUP(A2, Codes[Code], Codes[Price], "Not Found")
or =IFNA(VLOOKUP(A2, Codes, 2, FALSE), "Not Found").
Avoid IFERROR here, because it also hides #REF! or #VALUE! caused by broken ranges. Add =COUNTIF(D:D, "Not Found") at the top so missing codes are counted and followed up.
03
What are UserForms in VBA and when would you use one?
A UserForm is a custom dialog box with text boxes, dropdowns, option buttons and command buttons, created in the VBA editor through Insert > UserForm. It is useful for controlled data entry, such as an employee or expense entry form that validates inputs before writing a row to a Table, or for letting users choose options before a report macro runs. It reduces errors from typing directly into sheets.
LET assigns names to intermediate results inside one formula, so a value is calculated once and the formula reads like steps:
=LET(sales,SUMIFS(Data[Amt],Data[Rep],A2),
target,XLOOKUP(A2,Targets[Rep],Targets[Target]),
IF(target=0,"",sales/target))
Without LET, the SUMIFS would be repeated in each branch. It improves speed on large sheets and makes complex formulas easier to review.
05
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).
06
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.
07
What is a star schema and why is it recommended for the Data Model?
A star schema has one central fact table holding events and numbers, such as Sales with date, product ID, customer ID and amount, surrounded by dimension tables that describe them, such as Date, Product and Customer. Each dimension links to the fact table with a one-to-many relationship. It keeps the model small, makes filters flow predictably and keeps DAX measures simple.
08
A dashboard with 20 pivots and 6 slicers takes several seconds to react on every click. How do you speed it up?
Make sure all pivots share one cache or the Data Model, so a slicer click filters one source. Reduce slicer cross-filtering by unticking Visually indicate items with no data, remove slicers that are rarely used, and replace pivots that show the same data with GETPIVOTDATA from one pivot. Remove volatile formulas, limit conditional formatting, and move large data to the Data Model with measures.
09
Your pivot chart changes whenever someone filters the pivot, but your manager wants a fixed chart for a presentation. What do you do?
A pivot chart always follows its pivot, so it cannot be frozen while the pivot is filtered. Options: copy the chart and paste it as a picture into the slide; or copy the pivot values to a normal range and build a regular chart from them; or create a second pivot from the same cache, set its filters once and protect the sheet. For a live report, keep the interactive version on a separate sheet.
10
How do you send personalised emails from Excel through Outlook using VBA?
Use late binding so no library reference is needed, and loop through the rows:
Sub SendMails()
Dim ol As Object, m As Object, r As Long
Set ol = CreateObject("Outlook.Application")
For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set m = ol.CreateItem(0)
m.To = Cells(r, 2).Value
m.Subject = "Payment reminder"
m.Body = "Dear " & Cells(r, 1).Value & ", your due amount is Rs " & Cells(r, 3).Value
m.Display
Next r
End Sub
Use .Display to review before switching to .Send.
11
A workbook with the Data Model has grown to 300 MB. How do you reduce its size?
VertiPaq size depends mostly on columns and how many unique values each has. Remove columns not used in reports, especially IDs and free text. Split date-time columns into date and time or drop the time, round decimals to required precision, and replace calculated columns with measures. Use integer keys instead of long text keys, and load only the years required. Filtering at Power Query level avoids loading unneeded rows.
12
A shared Excel file has grown to 40 MB and takes ages to recalculate. What do you check?
Start with Ctrl+End to see if the used range is far beyond the real data and delete the extra rows and formats. Then look for volatile functions like OFFSET, INDIRECT and TODAY, whole-column references inside SUMPRODUCT or array formulas, and duplicated conditional formatting rules. Replace repeated lookups with one helper column, move heavy transforms to Power Query, and save as .xlsb if size still matters.
13
How is the Scripting.Dictionary object used in VBA?
A Dictionary stores key-value pairs with fast lookups, ideal for unique lists and grouping:
Dim d As Object, i As Long, v As Variant
Set d = CreateObject("Scripting.Dictionary")
v = Range("A2:B10001").Value
For i = 1 To UBound(v)
d(v(i, 1)) = d(v(i, 1)) + v(i, 2)
Next i
Range("D2").Resize(d.Count, 1).Value = Application.Transpose(d.Keys)
This totals amounts per customer far faster than repeated SUMIF calls.
14
When should a team move from an Excel dashboard to Power BI?
Excel dashboards work well for small teams, one-off analysis and data up to a few lakh rows. Move to Power BI when data comes from several live sources, needs scheduled refresh, row-level security for different users, mobile access, or a large audience who should not receive files. The Data Model and DAX skills learned in Power Pivot transfer directly, so the move is usually smooth.
15
Write DAX measures for year-to-date sales and sales for the same period last year.
With a proper Date table marked as a date table and related to Sales:
Sales YTD := TOTALYTD([Total Sales],'Date'[Date],"3/31")
Sales LY := CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date]))
YoY % := DIVIDE([Total Sales]-[Sales LY],[Sales LY])
The "3/31" year-end argument gives an Indian April to March financial year; without it, YTD resets on 1 January.
16
What causes the Formula.Firewall error in Power Query and how do you resolve it?
The error appears when a query combines data from sources with different privacy levels, for example using a value from an Excel sheet to filter a web or database query, because Power Query prevents data from one source leaking to another. Fix it by setting correct privacy levels in Data Source Settings, restructuring so the combination happens in separate staging queries, or, for trusted personal files, choosing to ignore privacy levels.
17
What is LAMBDA and how do you create a custom function without VBA?
LAMBDA lets you define a reusable function from a formula. Create it in Name Manager, for example name GSTInclusive with:
=LAMBDA(amount,rate,ROUND(amount*(1+rate),2))
Then use =GSTInclusive(B2,18%) anywhere in the workbook. Helper functions like MAP, BYROW, REDUCE and SCAN apply a LAMBDA across arrays. Unlike VBA functions, LAMBDAs need no macro-enabled file, but they require Microsoft 365.
18
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.
19
Tell me about a time stakeholder feedback changed your dashboard.
Situation: I built a sales dashboard with eight charts for the regional team. Task: get it adopted. Action: in the review, managers said they only checked target achievement and the bottom five branches each morning, so I moved those to the top, removed four rarely used charts and added a branch slicer. Result: usage went up, the meeting started using it daily, and I learned to ask what decisions a dashboard supports before building it.
20
Tell me about a time you had to learn a new tool quickly to deliver a project.
Situation: our sales data crossed two million rows and normal pivots crashed. Task: deliver a monthly dashboard within two weeks. Action: I learned Power Pivot and DAX through documentation and practice files, built a star schema with a Date table, wrote measures for YTD and growth, and checked every number against old reports. Result: the dashboard refreshed in under a minute, and I ran a short session to train two colleagues.
21
Dates from a vendor file in dd/mm/yyyy format show as errors or swap day and month. How do you fix it?
The system locale is probably English (United States), which reads dates as mm/dd/yyyy, so 13/04/2026 becomes an error and 05/04/2026 becomes 4 May. In Power Query, right-click the column > Change Type > Using Locale, choose Date and English (India). This tells Power Query how to read the text regardless of the computer's settings, so the file refreshes correctly on any machine.
22
When does binary search in XLOOKUP or approximate VLOOKUP give wrong results?
Binary search halves the range repeatedly, assuming the data is sorted. If the lookup column is unsorted, has mixed text and numbers or contains blanks, it can stop at the wrong row and return a plausible but incorrect value with no error. Use it only on data you sort in a controlled step, such as a Power Query output, and test a few values against an exact-match lookup.
23
The pivot grand total does not match SUM of the source column. How do you investigate?
Check for active filters on any field, slicers or timelines, and whether the source range excludes recent rows. Look for text values in the amount column, which pivots ignore, and calculated items that double-count. Confirm date grouping is not hiding rows outside the grouped range. Then double-click the grand total to see the rows the pivot used and compare the row count with the source.
24
Calculate an employee's age or tenure in years and months from the joining date.
DATEDIF is an older hidden function that still works but does not appear in autocomplete:
=DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months"
Units: Y full years, M full months, D days, YM months after full years, MD days after full months. For a decimal tenure use =YEARFRAC(B2,TODAY()). The start date must be earlier than the end date or DATEDIF returns #NUM!.
25
Tell me about a time you had to explain a DAX number that looked wrong to a manager.
Situation: a manager said our margin percentage in the Data Model pivot did not match his calculator. Task: show whether the measure was right. Action: I took one region, showed that he was averaging row percentages while the measure divided total margin by total sales, and walked through both in a small table. Result: he agreed the measure was correct, and I added a note to the report explaining how the KPI is calculated.
26
Explain row context, filter context and context transition.
Filter context is the set of filters applied to a calculation, coming from pivot rows, columns, slicers and CALCULATE. Row context exists when DAX iterates rows, as in a calculated column or SUMX, and lets you refer to the current row's values; it does not filter other tables by itself. Context transition happens when CALCULATE, or a measure reference, is used inside a row context and turns the current row into an equivalent filter context.
27
Budgets are set by category and month, but sales are recorded by product and day. How do you compare them in one pivot?
Do not relate the two fact tables directly. Create shared dimension tables at a grain both can link to: a Category table and a Date table with a Month column. Relate Budget[Category] and Sales via Products to Category, and both date columns to the Date table, using the first day of the month for budgets. Then measures [Total Sales] and [Total Budget] work together when the pivot uses Category and Month.
28
When would you choose VBA, Power Query, Office Scripts or Python in Excel?
Power Query is best for importing, cleaning and combining data, because it is refreshable and needs no code security. VBA suits desktop automation such as formatting, emailing, file handling and interacting with other Office apps. Office Scripts, written in TypeScript, automate Excel on the web and work with Power Automate flows. Python in Excel suits statistics and advanced analysis inside a cell. Pick the simplest tool your users can run and maintain.
29
How do you create and use a custom function in Power Query?
Write a query that takes a parameter, then call it for each row or file:
(filePath as text) as table =>
let
Src = Excel.Workbook(File.Contents(filePath)),
Data = Src{[Item="Sales",Kind="Table"]}[Data]
in
Data
Save it as fnGetSales, then Add Column > Invoke Custom Function on a table of file paths and expand the result. This gives more control than the automatic Combine Files helper.
30
Tell me about a time you built a macro that others in your team relied on. How did you keep it maintainable?
Situation: I wrote a macro that split a master file into branch-wise reports for 30 branches. Task: others had to run it while I was on leave. Action: I added Option Explicit, comments, a settings sheet for paths and branch lists, error handling with clear messages and a short user guide. Result: colleagues ran it for months without my help, and adding new branches needed only a row in the settings sheet.
31
Refreshing a Power Query that combines 200 CSV files from a folder takes ten minutes. How do you speed it up?
Reduce work as early as possible: remove unneeded columns and filter rows in the sample file transform so every file is smaller. Delete redundant Changed Type steps and set types once. Avoid sorting and merges against large tables before the combine. Set helper queries to connection only. Copy files to a local drive if SharePoint is slow, and archive old years into a single file so only recent files are combined each time.
32
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.
33
A monthly process takes four hours of copy-paste from 30 branch files. How would you automate it?
First agree a fixed template with the branches, ideally a named Table. Save all files to one SharePoint or shared folder, then build a From Folder query that combines them, adds the branch name from the file name, standardises types and filters bad rows. Load to the Data Model, build the pivots and dashboard on it, and add a check sheet comparing row counts per branch. Monthly work becomes refresh and review.
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.
35
Why is INDEX preferred over OFFSET for building dynamic ranges?
OFFSET is volatile, so it recalculates whenever anything changes, which slows big models. INDEX returns a reference too but is not volatile:
OFFSET: =SUM(OFFSET($B$2,0,0,COUNTA($B:$B)-1,1))
INDEX: =SUM($B$2:INDEX($B:$B,COUNTA($B:$B)))
Both sum a growing column, but the INDEX version only recalculates when column B changes. Converting the data to a Table is simpler still.
36
A measure shows the same value on every row of the pivot. What is wrong?
The field in the rows is not filtering the table the measure uses. Usually there is no relationship between the two tables, the relationship is inactive, or it points the wrong way, for example the row field comes from the many side of a different fact table. Excel often shows a warning that relationships may be needed. Check Manage Relationships and use fields from dimension tables linked to the fact table.
37
Why are arrays faster than looping through cells in VBA?
Each read or write to a cell crosses between VBA and Excel, which is slow when repeated thousands of times. Reading a whole range into a Variant array, processing it in memory and writing it back once is many times faster:
Dim v As Variant, i As Long
v = Range("A2:C50001").Value
For i = 1 To UBound(v, 1)
v(i, 3) = v(i, 1) * v(i, 2)
Next i
Range("A2:C50001").Value = v
Query folding means Power Query translates your steps into the source's own query language, such as SQL, so filtering and grouping happen inside the database and only the result is sent to Excel. This is much faster for large sources. Steps like adding index columns or some custom M functions can break folding. Right-click a step and check View Native Query to see whether it still folds.
Switch off screen updates, automatic calculation and events while the macro runs, and always restore them:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' ... main code ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Also avoid Select, process data in arrays instead of cell by cell, and use an error handler so settings are restored on failure.
40
Write a running total measure for cumulative sales across dates.
Keep all dates up to the last date in the current filter:
Running Sales :=
CALCULATE([Total Sales],
FILTER(ALL('Date'[Date]),
'Date'[Date]<=MAX('Date'[Date])))
Place Month or Date from the Date table in the pivot rows. To restart every financial year, use TOTALYTD with the "3/31" year-end instead.
Frequently asked questions
What is asked in Advanced Excel interviews for 10+ years of experience?
At ten years, roles are usually MIS head, reporting manager or analytics lead. Interviews focus on strategy: building and managing a reporting team, setting KPI definitions with leadership, tool choices, automation roadmaps and reducing manual effort across departments. Excel depth is assumed, and you will be asked how you measured the business impact of your work.
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 10+ years of experience?
Focus first on Team leadership, KPI governance, Automation roadmap, Business impact.