Advanced Excel Interview Questions for 7 Years Experience
40 questions with answers for candidates with 7 years of experience — 1 easy, 19 medium, 20 hard.
What interviewers expect: 7 years experience
Seven-year candidates are judged on reporting architecture and leadership: how reports are structured across teams, data governance, migration from Excel to Power BI, and controls that prevent errors. Technical questions test judgement, such as when Excel is the wrong tool, rather than syntax. Expect detailed discussion of past projects and results.
This set is weighted towards Power Pivot, Data Model and DAX (9), Power Query and Data Cleaning (8), Lookup and Dynamic Array Functions (7), Macros and VBA Automation (5).
Reporting architecture Excel to Power BI migration Data governance Project outcomes
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
How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?
Select the column, choose Split Column > By Delimiter, enter space-hyphen-space as a custom delimiter and split at each occurrence. This gives ID, Name and City columns. Then use Transform > Format > Trim and Clean to remove extra spaces and hidden characters, Capitalize Each Word for names, and set correct data types. Split by Number of Characters handles fixed-width codes like state and branch prefixes.
02
What are VBA events? Give an example of Worksheet_Change.
Events run code automatically when something happens, such as opening a workbook (Workbook_Open) or editing a cell (Worksheet_Change). In the sheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("F:F")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Application.EnableEvents = True
End If
End Sub
This timestamps column G when status in F changes.
When a pivot is created, Excel stores a snapshot of the source data called the pivot cache, and the pivot reads from it, not directly from the sheet. That is why changes need a refresh. Pivots built from the same Table share one cache, which keeps file size down and lets slicers connect across them. Creating pivots from different copies of the data creates separate caches and makes the file heavier.
04
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.
05
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.
06
Explain relationships, cardinality and filter direction in the Data Model.
A relationship links a key column in one table to a matching column in another. Cardinality describes how many rows match: one-to-many is the standard case, where each product appears once in Products but many times in Sales. In Excel's Data Model, filters flow from the one side to the many side, so selecting a category in Products filters Sales, but not the reverse.
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.
08
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.
09
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.
10
Get the latest price of each product from a transaction log sorted by date.
If the log is sorted oldest to newest, search from the bottom:
=XLOOKUP(A2,Log[Product],Log[Price],,0,-1)
If it is not sorted, find the latest date first and then match on both:
=LET(d,MAXIFS(Log[Date],Log[Product],A2),
XLOOKUP(1,(Log[Product]=A2)*(Log[Date]=d),Log[Price]))
The second version is order-independent and safer for appended data.
11
Lookups across 5 lakh rows make your workbook extremely slow. How do you speed it up?
Avoid repeating the same search: run one MATCH in a helper column and use several INDEX formulas on that position. Sort the lookup table and use XLOOKUP binary search (search_mode 2), which is much faster than a linear scan. Replace full-column references with Tables, set calculation to manual while editing, and for regular jobs move the join into Power Query or the Data Model with relationships.
12
How do you create a chart that automatically shows the last 12 months as new data is added?
Create named ranges with INDEX so they move with the data, assuming dates in column A from row 2:
LastRow: =COUNTA(Data!$A:$A)
ChartDates: =INDEX(Data!$A:$A,LastRow-11):INDEX(Data!$A:$A,LastRow)
ChartSales: =INDEX(Data!$B:$B,LastRow-11):INDEX(Data!$B:$B,LastRow)
Then edit the chart series to =Book1.xlsx!ChartSales and the axis to ChartDates. A Table-based chart grows automatically but shows all months, not a rolling window.
13
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.
14
Users of your Data Model pivot keep dragging Year, Quarter and Month separately and make mistakes. How do you make it easier?
Create a hierarchy in the Date table in Power Pivot diagram view, such as Year > Quarter > Month, so users drag one field and drill down. Set Month Name to sort by Month Number so months do not appear alphabetically. Hide key columns and raw fields that should not be used directly, and give measures clear business names. This keeps the field list small and the pivots consistent.
15
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.
16
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.
17
What is the difference between Reference and Duplicate in Power Query?
Duplicate copies all steps of a query into an independent new query, so changes to the original do not affect it. Reference creates a new query whose source is the output of the original, so it inherits any future changes. Use Reference to build several outputs from one cleaned staging query, such as a monthly summary and a customer list, so the cleaning logic lives in one place.
18
How do you build a dependent dropdown, where the City list changes based on the selected State?
Create a list of cities under each state name and give each list a named range matching the state, for example Maharashtra or Karnataka (no spaces). In B2 make a State list. In C2 use Data Validation > List with source:
=INDIRECT(B2)
In Microsoft 365 you can avoid named ranges with a spill formula in a helper cell:
=FILTER(Cities[City],Cities[State]=B2)
and point the validation at that spill range using =$H$2#.
19
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.
20
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.
21
How do you make a chart title change automatically with the selected region or month?
Build the title text in a cell with a formula, for example in B1:
="Sales trend - "&G1&" - FY 2026-27"
where G1 is the selected region. Click the chart title, click in the formula bar, type = and select B1, then press Enter:
=Dashboard!$B$1
The title now updates whenever G1 changes. The same trick works for data labels and text boxes.
22
What is a macro and what file format is needed to save it?
A macro is a recorded or written set of instructions in VBA (Visual Basic for Applications) that automates repeated tasks, such as formatting a report or exporting PDFs. Normal .xlsx files cannot store macros, so the workbook must be saved as .xlsm (macro-enabled) or .xlsb. Macros can be run from the Developer tab, a button, a keyboard shortcut or automatically through events.
23
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.
24
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.
25
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}).
26
Your manager wants a fixed-format P&L with rows in a custom order, but the data is in the Data Model and the pivot keeps reshaping itself. What do you do?
Use CUBE functions instead of a pivot. Select the pivot and use PivotTable Analyze > OLAP Tools > Convert to Formulas, or write them directly, for example CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]", $A5, B$3). Each cell becomes an independent formula, so you can reorder rows, insert subtotal lines and apply fixed formatting, while numbers still refresh from the model.
27
A report exported from PDF repeats the header row on every page and has page-footer lines. How do you clean it?
Use Get Data > From PDF or import the converted file, then promote the first row to headers. Filter out rows where the first column equals the header text, such as Name, and rows containing footer text like Page or Printed on, using Text Filters > Does Not Contain. Remove blank rows, Fill Down any group labels, and set types. The steps then run automatically on next month's PDF.
28
How do you get a distinct count, such as unique customers, in a pivot table?
A normal pivot only offers Count, which counts every row. When inserting the pivot, tick Add this data to the Data Model. Then in Value Field Settings choose Distinct Count, which counts unique customer IDs per region or month. Alternatively, create a DAX measure =DISTINCTCOUNT(Sales[CustomerID]) in Power Pivot, which can be reused across pivots.
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
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.
31
Write a DAX measure for each product's percentage of total sales.
Remove the product filter in the denominator so it always returns the overall total:
% of All Products :=
DIVIDE([Total Sales],
CALCULATE([Total Sales],ALL(Products)))
Use ALLSELECTED(Products) instead of ALL if the percentage should respect slicer selections, so the visible products add up to 100 percent. Other filters such as region or month still apply in both versions.
32
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.
33
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.
34
How do you do a two-way lookup that picks both a row and a column?
Use two MATCH functions inside INDEX, one for the row and one for the column:
=INDEX($B$2:$M$50,MATCH($P$2,$A$2:$A$50,0),MATCH($Q$2,$B$1:$M$1,0))
Here P2 holds the product and Q2 the month. With XLOOKUP you can nest one inside another:
=XLOOKUP(P2,A2:A50,XLOOKUP(Q2,B1:M1,B2:M50))
The inner XLOOKUP returns the whole month column, and the outer one picks the product row.
35
Fifteen people edit the same tracker and you keep getting conflicting versions over email. How would you fix the process?
Move the file to OneDrive or SharePoint so everyone co-authors one live copy, and stop emailing attachments. Restructure it into a single input Table with Data Validation, lock formula columns with sheet protection, and give each person a filtered view or their own sheet view. Use version history for recovery. If edits keep growing, move the data to a SharePoint list or database and keep Excel for reporting.
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.
37
A few very large values make every other bar in your chart look tiny. How do you present the data clearly?
Options depend on the message. Sort bars and show the top items with the rest grouped as Others; split into two charts, one for the giants and one for the rest; add data labels so small values are still readable; or use a logarithmic axis for technical audiences, clearly labelled. Avoid cutting the axis on bar charts, because bar length then stops representing value and misleads readers.
38
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.
39
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.
40
Your weekly dashboard must go to 40 people who cannot access the source files. How do you distribute it safely?
Decide what they need: a view or a working file. For a view, export the dashboard sheet to PDF or share a view-only link from SharePoint or OneDrive. For a file, save a copy, break external links, paste pivots or formulas as values if needed, remove hidden sheets and personal data with Inspect Document, and protect the sheet. Automate the steps with a macro or Office Script so every week is identical.
Frequently asked questions
What is asked in Advanced Excel interviews for 7 years of experience?
Seven-year candidates are judged on reporting architecture and leadership: how reports are structured across teams, data governance, migration from Excel to Power BI, and controls that prevent errors. Technical questions test judgement, such as when Excel is the wrong tool, rather than syntax. Expect detailed discussion of past projects and results.
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 7 years of experience?
Focus first on Reporting architecture, Excel to Power BI migration, Data governance, Project outcomes.