Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Hard Advanced Excel Interview Questions

45 hard questions with answers. Design, trade-off and deep-dive questions asked for senior roles and at top product companies.

Topics in the hard set

Power Pivot, Data Model and DAX: 16 Power Query and Data Cleaning: 6 Macros and VBA Automation: 6 Lookup and Dynamic Array Functions: 4 Charts and Dashboards: 4 Excel Fundamentals and Formatting: 3 Logical, Text and Date Functions: 3 Pivot Tables and Data Summaries: 3
Solved — hardStart 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

A shared Excel file has grown to 40 MB and takes ages to recalculate. What do you check?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
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.
02

What are volatile functions and why should you limit them?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
Volatile functions recalculate every time anything in the workbook changes, not only when their inputs change. Examples are NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, CELL and INFO. One TODAY cell is harmless, but thousands of OFFSET or INDIRECT formulas force the whole dependency chain to recalculate on every keystroke. Replace OFFSET with INDEX-based ranges or Tables, and put TODAY in a single cell that others reference.
03

Fifteen people edit the same tracker and you keep getting conflicting versions over email. How would you fix the process?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
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.
04

What is the LET function and why is it useful?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
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

What is LAMBDA and how do you create a custom function without VBA?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
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.
06

Your monthly report formulas break every time a column is added to the export. How do you make them robust?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
Avoid hard-coded column numbers and fixed ranges. Load the export into a Table or through Power Query, so columns are referenced by name. Replace VLOOKUP with column index 7 by XLOOKUP or INDEX/MATCH, keep constants like rates in labelled assumption cells, and use LET for readability. Add a check cell that compares the report total to the raw total, so any break is caught immediately.
07

How do you look up a value using multiple criteria, such as Employee ID and Month?

Lookup and Dynamic Array FunctionsHard
Save to ▾
Show answer
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.
08

Why is INDEX preferred over OFFSET for building dynamic ranges?

Lookup and Dynamic Array FunctionsHard
Save to ▾
Show answer
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.
09

Lookups across 5 lakh rows make your workbook extremely slow. How do you speed it up?

Lookup and Dynamic Array FunctionsHard
Save to ▾
Show answer
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.
10

When does binary search in XLOOKUP or approximate VLOOKUP give wrong results?

Lookup and Dynamic Array FunctionsHard
Save to ▾
Show answer
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.
11

You have Sales, Products and Regions in three tables. How do you build one pivot from all of them?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
Instead of merging with VLOOKUP, load each Table into the Data Model (Insert > PivotTable > Add this data to the Data Model, or Power Pivot). In Data > Relationships, link Sales[ProductID] to Products[ProductID] and Sales[RegionID] to Regions[RegionID]. The pivot field list then shows all three tables, so category and zone come from the lookup tables while amounts come from Sales.
12

The pivot grand total does not match SUM of the source column. How do you investigate?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
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.
13

Why is averaging a percentage column in a pivot often wrong, and what should you do instead?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
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.
14

How do you create a chart that automatically shows the last 12 months as new data is added?

Charts and DashboardsHard
Save to ▾
Show answer
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.
15

A dashboard with 20 pivots and 6 slicers takes several seconds to react on every click. How do you speed it up?

Charts and DashboardsHard
Save to ▾
Show answer
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.
16

When should a team move from an Excel dashboard to Power BI?

Charts and DashboardsHard
Save to ▾
Show answer
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.
17

How do you handle two senior managers asking for conflicting versions of the same dashboard?

Charts and DashboardsHard
Save to ▾
Show answer
Situation: sales wanted revenue by booking date while finance wanted it by invoice date. Task: avoid two dashboards with different numbers. Action: I met both, wrote down each definition and its use, and proposed one dashboard with a clearly labelled toggle and a note explaining the difference. Result: both agreed, the numbers stopped being argued over in meetings, and the definition was added to our data glossary.
18

Write a Power Query custom column that classifies orders into High, Medium and Low by amount.

Power Query and Data CleaningHard
Save to ▾
Show answer
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.
19

What is query folding in Power Query?

Power Query and Data CleaningHard
Save to ▾
Show answer
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.
20

A monthly process takes four hours of copy-paste from 30 branch files. How would you automate it?

Power Query and Data CleaningHard
Save to ▾
Show answer
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.
21

What causes the Formula.Firewall error in Power Query and how do you resolve it?

Power Query and Data CleaningHard
Save to ▾
Show answer
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.
22

Refreshing a Power Query that combines 200 CSV files from a folder takes ten minutes. How do you speed it up?

Power Query and Data CleaningHard
Save to ▾
Show answer
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.
23

How do you create and use a custom function in Power Query?

Power Query and Data CleaningHard
Save to ▾
Show answer
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.
24

What is a star schema and why is it recommended for the Data Model?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
25

Explain relationships, cardinality and filter direction in the Data Model.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
26

What does CALCULATE do in DAX?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
27

Explain row context, filter context and context transition.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
28

What is the difference between SUM and SUMX?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
SUM adds a single column. SUMX iterates a table, evaluates an expression for each row and adds the results: Revenue := SUMX(Sales,Sales[Qty]*Sales[Price]) This avoids storing a Qty times Price calculated column. Other X functions such as AVERAGEX, MAXX and COUNTX work the same way. Iterating a very large table with complex expressions can be slower, so keep the expression simple.
29

Write DAX measures for year-to-date sales and sales for the same period last year.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
30

Write a DAX measure for each product's percentage of total sales.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
31

Explain ALL, ALLEXCEPT, ALLSELECTED and REMOVEFILTERS.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
ALL removes all filters from a table or column and can also return all its rows. REMOVEFILTERS does the same filter removal but is clearer in intent and can only be used as a CALCULATE modifier. ALLEXCEPT removes filters from a table except the columns you name, such as keeping Region. ALLSELECTED removes filters coming from inside the pivot but keeps outer slicer and filter selections, which suits visual totals.
32

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?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
33

A measure shows the same value on every row of the pivot. What is wrong?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
34

Write a month-over-month growth percentage measure.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Use DATEADD on the Date table to shift the period one month back: Sales PM := CALCULATE([Total Sales],DATEADD('Date'[Date],-1,MONTH)) MoM % := DIVIDE([Total Sales]-[Sales PM],[Sales PM]) Place Month from the Date table in the pivot rows. For the first month in the data, Sales PM is blank, so DIVIDE returns blank rather than an error.
35

Tell me about a time you had to explain a DAX number that looked wrong to a manager.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
36

A workbook with the Data Model has grown to 300 MB. How do you reduce its size?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
37

Write a running total measure for cumulative sales across dates.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
38

Tell me about a time you had to learn a new tool quickly to deliver a project.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
39

Budgets are set by category and month, but sales are recorded by product and day. How do you compare them in one pivot?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
40

How do you speed up a slow VBA macro?

Macros and VBA AutomationHard
Save to ▾
Show answer
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.
41

Write VBA to consolidate the first sheet of every Excel file in a folder into one sheet.

Macros and VBA AutomationHard
Save to ▾
Show answer
Loop with Dir, open each file, copy its data below the last row and close it: Sub Consolidate() Dim f As String, wb As Workbook, dest As Worksheet, r As Long Const P As String = "C:\Reports\" Set dest = ThisWorkbook.Worksheets("All") f = Dir(P & "*.xlsx") Do While f <> "" Set wb = Workbooks.Open(P & f, ReadOnly:=True) r = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row + 1 wb.Worksheets(1).UsedRange.Offset(1).Copy dest.Cells(r, 1) wb.Close False f = Dir Loop End Sub
42

Why are arrays faster than looping through cells in VBA?

Macros and VBA AutomationHard
Save to ▾
Show answer
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
43

How do you send personalised emails from Excel through Outlook using VBA?

Macros and VBA AutomationHard
Save to ▾
Show answer
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.
44

How is the Scripting.Dictionary object used in VBA?

Macros and VBA AutomationHard
Save to ▾
Show answer
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.
45

When would you choose VBA, Power Query, Office Scripts or Python in Excel?

Macros and VBA AutomationHard
Save to ▾
Show answer
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.

Frequently asked questions

Who should practise hard Advanced Excel questions?

Candidates targeting senior roles or top product companies, and anyone who finds the medium set easy.

How should I practise these?

Answer each question aloud before opening the answer, mark it as solved only when your answer matches the key idea, and save misses to Last-Minute Revision.

Other difficulty levels

Easy questionsMedium questions

More ways to practise

Company-wise Advanced Excel questionsExperience-wise Advanced Excel questionsRole-wise Advanced Excel questionsCity-wise Advanced Excel questionsMost asked Advanced Excel questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsAdvanced Excel mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsAdvanced Excel topic sheets