American Express Advanced Excel Interview Questions & Answers
Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About American Express hiring for Advanced Excel roles
American Express is a global payments and card company offering charge and credit cards, travel services and merchant acquiring. Its India centres in Gurugram and Bengaluru support technology, risk, analytics and servicing. It hires for software engineers, QA engineers, data analysts, data scientists and credit risk analysts.
For Advanced Excel roles, this American Express practice set focuses most on Pivot Tables and Data Summaries (7), Excel Fundamentals and Formatting (5), Logical, Text and Date Functions (4), Lookup and Dynamic Array Functions (4). It has 4 easy, 19 medium, 7 hard questions, so you can start easy and work up to the harder ones.
Company type
Banking & finance
Headquarters
New York, USA
Main India offices
Gurugram, Bengaluru
American Express Advanced Excel interview process
Online assessment (aptitude + coding or analytics)
Technical round
Case or domain round
Hiring manager round
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for American ExpressStart 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.
DIVIDE handles division by zero safely and returns blank, or a value you choose, instead of an error:
Margin % := DIVIDE([Total Profit],[Total Sales])
Margin % := DIVIDE([Total Profit],[Total Sales],0)
Blank results are hidden in pivots, which keeps reports clean when a product has no sales in a month. Use the / operator only when the denominator can never be zero, because it is slightly faster.
02
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#.
03
What is the difference between Protect Sheet, Protect Workbook and Encrypt with Password?
Protect Sheet stops edits to locked cells; you first unlock input cells through Format Cells > Protection so users can type only there. Protect Workbook locks the structure, so sheets cannot be added, deleted or renamed. Encrypt with Password (File > Info) is the only option that blocks opening the file. Sheet and workbook protection are about preventing accidents, not real security, because they are easy to bypass.
04
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!.
05
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.
06
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.
07
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.
08
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.
When you click a pivot cell while writing a formula, Excel inserts GETPIVOTDATA, which pulls a value by field names instead of cell position:
=GETPIVOTDATA("Amount",$A$3,"Region","North","Month","Apr")
It keeps working when the pivot layout changes, which suits KPI cards in dashboards. If you want plain references, turn it off in PivotTable Analyze > Options > Generate GetPivotData.
10
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.
11
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.
12
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.
13
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).
14
How do you handle two senior managers asking for conflicting versions of the same dashboard?
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.
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.
Paste Special (Ctrl+Alt+V) lets you paste only part of what was copied. Values removes formulas, Formats copies only styling, Transpose flips rows into columns, and Skip Blanks avoids overwriting with empty cells. The Operation options can multiply or divide a whole range by a copied number, for example converting rupees to lakhs by copying 100000 and choosing Divide.
17
What principles do you follow when designing an Excel dashboard?
Start from the questions the audience asks. Put three to five headline KPIs at the top, trends and breakdowns below, and filters such as slicers in one consistent place. Use one colour palette with a highlight colour for what matters, avoid 3D and decorative effects, label units clearly like Rs lakh, and fit the view on one screen. Keep raw data and calculations on separate hidden sheets.
18
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.
19
What are volatile functions and why should you limit them?
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.
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.
21
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.
22
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.
23
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.
24
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.
25
How do you stop a pivot from losing formatting and column widths on refresh?
Open PivotTable Options > Layout & Format, untick Autofit column widths on update and tick Preserve cell formatting on update. Set number formats through Value Field Settings > Number Format rather than formatting cells directly, because field-level formats survive refresh and layout changes. For a consistent look across reports, create a custom PivotTable Style and apply it to every pivot.
26
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.
27
How do you show only the top 10 customers by revenue in a pivot?
Put Customer in Rows and Revenue in Values. Click the Customer filter arrow > Value Filters > Top 10, choose Top, 10, Items, by Sum of Revenue. Then sort the Revenue column largest to smallest. The grand total by default still reflects only the visible rows; if you need overall totals, add a separate pivot or enable Include filtered items in totals when using the Data Model.
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
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
A column shows amounts rounded to whole rupees, but its total differs from a manual total of the displayed values by a few rupees. Why?
Number formatting only changes how a value looks; Excel still adds the full decimals stored in each cell. So the sum of hidden paise differs from the sum of the rounded figures shown. If the report must add up exactly as displayed, round the values with =ROUND(value,0) in the source formulas rather than using the Precision as displayed option, which permanently changes stored data for the whole workbook.
Tips to crack the American Express Advanced Excel interview
Learn credit card business basics like spend, revolve, delinquency and customer lifetime value for analytics interviews.
Practise SQL, Python and statistics, including model evaluation for risk or marketing use cases.
Read about American Express's Blue Box values and its strong focus on customer service.
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; American Express interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the American Express Advanced Excel interview?
Candidates usually go through 5 stages: Online assessment (aptitude + coding or analytics); Technical round; Case or domain round; Hiring manager 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 American Express Advanced Excel interview?
This practice set leans on Pivot Tables and Data Summaries (7), Excel Fundamentals and Formatting (5), Logical, Text and Date Functions (4), Lookup and Dynamic Array Functions (4). Revise those first, then practise explaining one or two of your own projects end to end, because most American Express interviewers ask you to walk through real work.
Are these the exact questions asked at American Express?
No. These are curated practice questions that match the skills and difficulty usually tested in banking & finance interviews like American Express's. They are not a leaked or verified list of past questions.
Where does American Express hire Advanced Excel professionals in India?
American Express has large teams in Gurugram, Bengaluru. Openings and locations change often, so check the American Express careers page and filter by city and experience.