Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About Amazon hiring for Advanced Excel roles
Amazon is a global technology company spanning e-commerce, cloud computing through AWS, devices, advertising and streaming. In India it runs large engineering, operations and analytics teams supporting both Amazon.in and global businesses. It hires for software development engineers, SDETs, quality assurance engineers, business intelligence engineers, data engineers and data scientists.
For Advanced Excel roles, this Amazon practice set focuses most on Power Query and Data Cleaning (7), Charts and Dashboards (6), Macros and VBA Automation (5), Lookup and Dynamic Array Functions (4). It has 1 easy, 22 medium, 7 hard questions, so you can start easy and work up to the harder ones.
Company type
Product
Headquarters
Seattle, USA
Main India offices
Bengaluru, Hyderabad, Chennai, Pune, Gurugram
Amazon Advanced Excel interview process
Online assessment (coding + work style survey)
Technical round 1 (DSA)
Technical round 2 (DSA or system design)
Bar raiser round
Hiring manager round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for AmazonStart 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
Finance wants the sales pivot by quarter of the Indian financial year, starting in April. Date grouping only gives calendar quarters. How do you solve it?
Pivot date grouping uses calendar quarters, so add a helper column in the source data. For example, fiscal quarter ="Q"&ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0) and a fiscal year column. Use them as row fields instead of grouped dates. In Power Query or a Data Model, put these columns in a proper Date table so every report uses the same April to March logic.
02
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.
03
Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?
The source file's header changed, for example Amount became Amount (Rs), and a step that refers to the old name, often Changed Type or Renamed Columns, breaks. Open the query, click through the steps to find the first error, and update the name or rename the column back early in the query. To prevent repeats, agree a fixed template with the data owner and avoid steps that list every column unnecessarily.
04
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.
05
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.
06
Your Power Query output has 20 lakh rows and Excel says it cannot load it to a sheet. What do you do?
A worksheet holds at most 1,048,576 rows, so the result will not fit. Choose Close & Load To and select Only Create Connection with Add this data to the Data Model. The model compresses data and can handle many millions of rows, and you analyse it with a pivot table or DAX measures. If users only need summaries, group the data inside Power Query before loading.
07
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.
08
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
09
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.
10
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.
11
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.
12
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.
13
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.
14
Why are data types important in Power Query, and what problems does the automatic Changed Type step cause?
Data types decide how values are sorted, summed and loaded, so a number stored as text will not total and a date stored as text will not group by month. Power Query adds an automatic Changed Type step that hard-codes every column name, so a renamed or removed column later causes refresh errors. Many analysts delete it and set types once, deliberately, after the structure is final.
15
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.
16
Describe a time you convinced your team to move from manual cleaning to Power Query.
Situation: our team spent a day each month cleaning vendor files by hand. Task: I believed Power Query could fix it but the team feared a new tool. Action: I rebuilt one month's cleanup as a query, then ran it side by side with the manual process and showed identical results in five minutes. I documented each step and trained two colleagues. Result: the team adopted it, saving about eight hours a month.
17
You are given raw sales data and asked to build an interactive dashboard. Walk through your approach.
Clarify the audience, KPIs and refresh frequency first. Load and clean the data through Power Query into a Table or the Data Model. Build pivots for each view on a calculation sheet, create pivot charts, and connect slicers for region, product and month to all pivots. Add KPI cards using GETPIVOTDATA, format consistently, protect the layout and test that Refresh All updates everything when new data is added.
18
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.
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.
20
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.
21
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.
22
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.
23
You change input values but the totals do not update until you press something. What is happening and how do you fix it?
The workbook is probably in Manual calculation mode, often inherited from another file opened first in the same session. Press F9 to recalculate now, then set Formulas > Calculation Options > Automatic. If only some cells stay stale, check whether they are formatted as Text or whether Show Formulas is on. For very heavy files, manual mode can be kept deliberately, but tell users to press F9 before reading results.
24
A shared Excel file has grown to 40 MB and takes ages to recalculate. What do you check?
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.
25
Your manager asks you to replace all VLOOKUPs in a legacy workbook with XLOOKUP. How do you approach it?
First confirm everyone who opens the file has Microsoft 365 or Excel 2021, because older versions show #NAME? for XLOOKUP. Save a backup, list the VLOOKUPs using Find with Look in: Formulas, and replace them sheet by sheet, keeping an exact-match default. Compare key totals before and after, document the change in a notes sheet, and keep a version that still uses INDEX/MATCH if external clients need it.
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.
27
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.
28
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.
A waterfall chart shows how a starting value moves to an ending value through a series of positive and negative steps. It is common in finance for profit bridges, such as last year's profit, plus volume growth, minus price discount, minus higher costs, giving this year's profit. Excel 2016 and later include it under Insert > Waterfall; set the first and last bars as totals so they start from zero.
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 Amazon Advanced Excel interview
Prepare STAR-format stories for each Amazon Leadership Principle, such as Customer Obsession, Ownership and Dive Deep.
Practise medium-level DSA problems on arrays, trees, graphs and dynamic programming, explaining time and space complexity aloud.
For BIE or data roles, practise SQL window functions and metric-driven case questions on e-commerce funnels and delivery operations.
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; Amazon interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the Amazon Advanced Excel interview?
Candidates usually go through 5 stages: Online assessment (coding + work style survey); Technical round 1 (DSA); Technical round 2 (DSA or system design); Bar raiser round; Hiring manager 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 Amazon Advanced Excel interview?
This practice set leans on Power Query and Data Cleaning (7), Charts and Dashboards (6), Macros and VBA Automation (5), Lookup and Dynamic Array Functions (4). Revise those first, then practise explaining one or two of your own projects end to end, because most Amazon interviewers ask you to walk through real work.
Are these the exact questions asked at Amazon?
No. These are curated practice questions that match the skills and difficulty usually tested in product interviews like Amazon's. They are not a leaked or verified list of past questions.
Where does Amazon hire Advanced Excel professionals in India?
Amazon has large teams in Bengaluru, Hyderabad, Chennai, Pune, Gurugram. Openings and locations change often, so check the Amazon careers page and filter by city and experience.