ICICI 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 ICICI Bank hiring for Advanced Excel roles
ICICI Bank is a large Indian private sector bank offering retail, corporate, digital and international banking. Its technology, analytics and operations teams work on products like iMobile Pay, credit decisioning, fraud detection and customer analytics. It hires for data analysts, risk analysts, testers, software engineers and management trainees.
For Advanced Excel roles, this ICICI Bank practice set focuses most on Macros and VBA Automation (6), Excel Fundamentals and Formatting (5), Pivot Tables and Data Summaries (5), Power Query and Data Cleaning (3). It has 5 easy, 19 medium, 6 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, Hyderabad, Chennai, Bengaluru
ICICI 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 ICICI 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
What are Applied Steps and the M language in Power Query?
Each transformation you click, like removing columns or changing types, is saved as a step in the Applied Steps pane, and the steps run in order on every refresh. Behind them is M, a functional formula language visible in the Advanced Editor. You can rename steps, delete or reorder them, and edit M directly for logic the interface does not provide, such as custom functions or dynamic column lists.
02
What is the difference between relative, absolute and mixed cell references?
A relative reference like A1 shifts when a formula is copied, an absolute reference like $A$1 never shifts, and a mixed reference like $A1 or A$1 locks only the column or only the row. In a multiplication grid, =$A2*B$1 copied across and down keeps the header column and header row fixed. Press F4 while editing a reference to cycle through the four forms.
A histogram groups numeric values into bins and shows how often each bin occurs, for example how many orders fall between 0 to 1,000 rupees or 1,000 to 5,000 rupees. A Pareto chart sorts categories by frequency and adds a cumulative percentage line, showing the vital few, such as the 20 percent of complaint types causing 80 percent of complaints. Both are available under Insert > Statistic Charts.
04
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.
05
What is the difference between a workbook, a worksheet and a range?
A workbook is the whole file (.xlsx, .xlsm or .xlsb). A worksheet is one tab inside it with 1,048,576 rows and 16,384 columns. A range is any group of cells such as A2:D100 that a formula, chart or format refers to. Good practice is to keep raw data, calculations and output on separate worksheets of the same workbook so each layer is easy to audit.
06
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.
07
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.
08
How do you highlight an entire row when the Status column says Overdue?
Select the full data range, for example A2:H500, then Home > Conditional Formatting > New Rule > Use a formula. Enter:
=$F2="Overdue"
Choose a fill colour and click OK. The dollar before F locks the column, while the relative row 2 lets the rule move down row by row, so every column in that row gets coloured when column F matches.
09
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.
10
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.
11
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.
12
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}).
13
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.
14
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.
15
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.
16
Write VBA to loop through all worksheets and apply the same formatting.
Use For Each over the Worksheets collection and skip any sheet you do not want to touch:
Sub FormatAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
ws.Rows(1).Font.Bold = True
ws.UsedRange.Columns.AutoFit
End If
Next ws
End Sub
17
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.
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.
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.
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.
21
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.
22
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.
23
Write VBA to consolidate the first sheet of every Excel file in a folder into one sheet.
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
24
Count orders from the North region with amount above 50,000 placed in January 2026.
Use COUNTIFS with one pair of range and criteria per condition, with dates built using DATE so the formula does not depend on regional settings:
=COUNTIFS(Orders[Region],"North",
Orders[Amount],">50000",
Orders[Date],">="&DATE(2026,1,1),
Orders[Date],"<"&DATE(2026,2,1))
Using less than 1 February is safer than less than or equal to 31 January when dates contain time values.
25
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.
26
List the steps to build a sales and margin percentage combo chart.
Select the Month, Sales and Margin % columns and go to Insert > Recommended Charts > All Charts > Combo. Set Sales to Clustered Column and Margin % to Line with Markers, ticking Secondary Axis for Margin %. Add axis titles, format the secondary axis as 0%, add data labels only to the line if needed, and remove gridline clutter. Give it a title that states the insight, such as Margin fell while sales grew.
Recorded macros use lines like Range("A1").Select followed by Selection.Copy, which are slow, flicker the screen and break if the user clicks another sheet while the macro runs. Refer to objects directly instead: Worksheets("Data").Range("A1:D100").Copy Worksheets("Report").Range("A1"). Fully qualifying the workbook and worksheet, often with a With block, makes the code faster and predictable.
28
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).
29
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.
30
Your macro accidentally deleted data, and Undo does not work. How do you prevent this in future?
Macro actions clear Excel's undo history, so prevention matters. Add a confirmation prompt before destructive steps, test on a copy of the file, and have the macro save a backup copy with a timestamp before it changes data. Restrict deletion to clearly identified ranges such as a named Table, not ActiveSheet. For files on OneDrive or SharePoint, version history can usually restore the earlier copy.
Tips to crack the ICICI Bank Advanced Excel interview
Learn Indian banking basics such as loan types, NPAs, CASA ratio and RBI regulations for interviews.
Use iMobile Pay and think about features you would test or improve as a QA or analyst candidate.
Revise SQL and Excel with examples on customer segmentation, credit risk or campaign analysis.
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; ICICI Bank interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the ICICI 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 ICICI Bank Advanced Excel interview?
This practice set leans on Macros and VBA Automation (6), Excel Fundamentals and Formatting (5), Pivot Tables and Data Summaries (5), Power Query and Data Cleaning (3). Revise those first, then practise explaining one or two of your own projects end to end, because most ICICI Bank interviewers ask you to walk through real work.
Are these the exact questions asked at ICICI Bank?
No. These are curated practice questions that match the skills and difficulty usually tested in banking & finance interviews like ICICI Bank's. They are not a leaked or verified list of past questions.
Where does ICICI Bank hire Advanced Excel professionals in India?
ICICI Bank has large teams in Mumbai, Hyderabad, Chennai, Bengaluru. Openings and locations change often, so check the ICICI Bank careers page and filter by city and experience.