HDFC Bank Advanced Excel Interview Questions & Answers
Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About HDFC Bank hiring for Advanced Excel roles
HDFC Bank is one of India's largest private sector banks, offering retail banking, loans, cards, wholesale banking and digital banking. Its analytics, risk and technology teams work on credit decisions, customer analytics, fraud detection and digital channels. It hires for data analysts, credit and risk analysts, testers and technology roles.
For Advanced Excel roles, this HDFC Bank practice set focuses most on Lookup and Dynamic Array Functions (7), Logical, Text and Date Functions (7), Macros and VBA Automation (4), Power Pivot, Data Model and DAX (4). It has 6 easy, 20 medium, 4 hard questions, so you can start easy and work up to the harder ones.
Company type
Banking & finance
Headquarters
Mumbai, India
Main India offices
Mumbai, Chennai, Bengaluru
HDFC Bank Advanced Excel interview process
Online assessment (aptitude + domain)
Technical or functional interview
Managerial round
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for HDFC BankStart 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
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}).
02
SUM returns 0 on a column of numbers exported from software. How do you fix it?
The numbers are most likely stored as text, often with leading apostrophes, trailing spaces or non-breaking spaces (CHAR(160)). Check with =ISTEXT(A2) or LEN. Quick fixes are Data > Text to Columns > Finish, the green error triangle > Convert to Number, or Paste Special > Multiply by 1. For stubborn cases use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) or set the column type to Decimal in Power Query.
03
Write VBA to save each worksheet as a separate PDF.
Use ExportAsFixedFormat inside a loop:
Sub SheetsToPDF()
Dim ws As Worksheet, p As String
p = ThisWorkbook.Path & "\"
For Each ws In ThisWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=p & ws.Name & ".pdf", _
Quality:=xlQualityStandard, IgnorePrintAreas:=False
Next ws
End Sub
Set print areas and page setup first so each PDF fits the page properly.
04
What is a pivot table and when should you use one?
A pivot table summarises large tabular data by dragging fields into rows, columns, values and filters, without writing formulas. Use it when you need totals, counts or averages broken down by categories such as region, product and month, and when the breakdown changes often. It works best on clean data with one header row, no blank rows and one record per row, ideally stored in an Excel Table.
05
How do form controls like dropdowns, check boxes and scroll bars make a dashboard interactive?
Form controls are added from the Developer tab > Insert. Each control writes a value into a linked cell: a combo box writes the position of the selected item, a check box writes TRUE or FALSE, and a scroll bar writes a number within a set range. Formulas such as INDEX, CHOOSE or FILTER read that cell to change the chart data, so the dashboard reacts without any macros.
The Data Model is an in-memory database inside the workbook, powered by the VertiPaq engine, which compresses data by column and can handle millions of rows, far beyond the sheet limit. Power Pivot is the window used to manage it: import tables, create relationships, and write DAX measures and calculated columns. Pivot tables built on the Data Model can combine several related tables without VLOOKUP.
07
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.
08
VLOOKUP returns #N/A even though you can see the value in the table. What do you check?
Check that the last argument is FALSE, then compare the two values directly with =A2=Sheet2!A5. Common causes are trailing or non-breaking spaces, one side being a number and the other text (like employee codes), and the lookup value sitting outside the first column of the range. Use LEN to spot hidden characters, and fix with TRIM, CLEAN, VALUE or by making both columns the same data type.
09
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.
10
Explain the match_mode and search_mode arguments of XLOOKUP.
match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. search_mode: 1 first to last (default), -1 last to first, 2 binary search ascending, -2 binary search descending.
=XLOOKUP("*Infra*",Cust[Name],Cust[ID],,2) wildcard match
=XLOOKUP(A2,Log[Item],Log[Price],,0,-1) last occurrence
Binary modes are fast but give wrong answers if the data is not sorted.
11
A macro produces wrong totals because a misspelt variable name was silently treated as a new empty variable. How do you prevent this?
Add Option Explicit at the top of every module, and enable Tools > Options > Require Variable Declaration so new modules get it automatically. VBA then refuses to run code that uses an undeclared variable and highlights the typo at compile time. Also declare proper types such as Long or Double instead of Variant, which catches type mistakes and makes the code faster and easier to read.
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.
13
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.
14
How do you group dates and numbers in a pivot, and why does grouping sometimes fail?
Right-click a date in Rows and choose Group to group by days, months, quarters or years; for numbers, group by a range such as 0 to 100000 in steps of 10000 to create sales bands. Grouping fails with the message Cannot group that selection when the column contains blanks, text values or dates stored as text. Clean the source column, refresh, and grouping works.
15
How do you return all matching values for a lookup, not only the first?
In Microsoft 365, FILTER returns every match as a spilled list, and TEXTJOIN can join them into one cell:
=FILTER(Orders[OrderID],Orders[Customer]=A2)
=TEXTJOIN(", ",TRUE,FILTER(Orders[OrderID],Orders[Customer]=A2,""))
In older versions, use TEXTJOIN with IF as an array formula:
=TEXTJOIN(", ",TRUE,IF(Orders[Customer]=A2,Orders[OrderID],""))
16
Describe a time you found an error in a report just before it was sent.
Situation: a monthly MIS was due to leadership in an hour. Task: I was doing a final review. Action: I noticed the region totals did not match the grand total, traced it to a SUMIFS range that stopped at row 500 while data had grown to 620 rows, converted the source to a Table and re-checked totals against the raw export. Result: the report went out on time with correct numbers, and I added a reconciliation check cell to the template.
SUMIF handles one condition and puts the sum range last; SUMIFS handles many conditions and puts the sum range first:
=SUMIF(A:A,"North",C:C)
=SUMIFS(C:C,A:A,"North",B:B,"Laptop",D:D,">="&DATE(2026,4,1))
Because of the different order, many people use SUMIFS everywhere, even for a single condition, to stay consistent. COUNTIFS and AVERAGEIFS follow the same pattern.
18
What is the difference between nested IF and IFS? Show an example.
Nested IF places one IF inside another, which becomes hard to read after three levels. IFS (Excel 2019 and later) tests conditions in order and returns the value for the first true one:
=IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Fail")
The final TRUE acts as the else part. For many bands, a lookup table with XLOOKUP approximate match is easier to maintain than either.
19
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!.
20
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.
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.
22
Tell me about a time you presented data to senior leadership.
Situation: I had to present quarterly branch performance to regional directors. Task: make twenty pages of tables understandable in ten minutes. Action: I built one dashboard sheet with four KPIs against target, a trend chart and a ranked bar chart of branches, and kept detail tables as backup. I opened with the key message and the two branches needing action. Result: the meeting ended with clear decisions, and the format became the standard quarterly review pack.
23
You need to reconcile a bank statement with the ledger and list entries missing from either side. How?
Create a common key, for example date plus amount plus cheque or UTR number. On each sheet add =IF(COUNTIFS(Other[Key],[@Key])=0,"Missing","Matched") and filter the Missing rows. Duplicated amounts need COUNTIFS on both sides so counts are compared, not just presence. For repeated monthly work, load both into Power Query and use Left Anti and Right Anti merges, which list unmatched rows directly.
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.
25
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.
26
How do you combine all files in a folder using Power Query?
Place files with the same structure in one folder. Go to Data > Get Data > From File > From Folder, select the folder and click Combine & Transform. Choose the sheet or table from the sample file; Power Query creates a helper function and appends all files, adding a Source.Name column with the file name. Apply cleaning steps once, Close & Load, and later just drop new files in and Refresh.
27
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.
28
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.
29
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.
30
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.
Tips to crack the HDFC Bank Advanced Excel interview
Learn Indian banking basics like CASA, NPAs, credit scores, KYC and RBI regulations for domain questions.
Revise SQL, Excel and basic statistics for analytics and risk roles with banking examples.
Understand UPI, net banking and mobile banking flows if applying for digital channel testing roles.
Keep a one-minute story ready for every project on your CV: problem, your part, tools, result.
Practise the hard questions on this page aloud; HDFC Bank interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the HDFC Bank Advanced Excel interview?
Candidates usually go through 4 stages: Online assessment (aptitude + domain); Technical or functional interview; Managerial round; HR round. The exact rounds change with the role, team, location and your experience, so confirm the process with the recruiter.
Which topics should I prepare for the HDFC Bank Advanced Excel interview?
This practice set leans on Lookup and Dynamic Array Functions (7), Logical, Text and Date Functions (7), Macros and VBA Automation (4), Power Pivot, Data Model and DAX (4). Revise those first, then practise explaining one or two of your own projects end to end, because most HDFC Bank interviewers ask you to walk through real work.
Are these the exact questions asked at HDFC Bank?
No. These are curated practice questions that match the skills and difficulty usually tested in banking & finance interviews like HDFC Bank's. They are not a leaked or verified list of past questions.
Where does HDFC Bank hire Advanced Excel professionals in India?
HDFC Bank has large teams in Mumbai, Chennai, Bengaluru. Openings and locations change often, so check the HDFC Bank careers page and filter by city and experience.