Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About KPMG hiring for Advanced Excel roles
KPMG is one of the Big Four professional services networks, providing audit, tax and advisory services. KPMG in India and its global delivery centres support clients with technology consulting, data and analytics, risk advisory and cybersecurity. It hires in India for analysts and consultants in data analytics, governance and risk, IT audit, testing and ERP implementation.
For Advanced Excel roles, this KPMG practice set focuses most on Charts and Dashboards (7), Lookup and Dynamic Array Functions (5), Power Query and Data Cleaning (5), Pivot Tables and Data Summaries (3). It has 4 easy, 24 medium, 2 hard questions, so you can start easy and work up to the harder ones.
Company type
Consulting
Headquarters
Amstelveen, Netherlands
Main India offices
Gurugram, Bengaluru, Mumbai, Hyderabad, Pune
KPMG Advanced Excel interview process
Online assessment or screening test
Technical interview
Managerial or case round
Partner or HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for KPMGStart 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
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.
02
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.
03
Your weekly dashboard must go to 40 people who cannot access the source files. How do you distribute it safely?
Decide what they need: a view or a working file. For a view, export the dashboard sheet to PDF or share a view-only link from SharePoint or OneDrive. For a file, save a copy, break external links, paste pivots or formulas as values if needed, remove hidden sheets and personal data with Inspect Document, and protect the sheet. Automate the steps with a macro or Office Script so every week is identical.
04
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).
05
Tell me about a time a lookup gave wrong results that nobody noticed at first.
Situation: a monthly incentive sheet used VLOOKUP with the last argument left blank, so it ran an approximate match. Task: I was asked to verify payouts before release. Action: I spotted employees getting a neighbour's target, changed every lookup to exact match with XLOOKUP, and added a count of missing IDs. Result: twelve wrong payouts were corrected before payment, and exact match became our team's standard rule.
06
A monthly process takes four hours of copy-paste from 30 branch files. How would you automate it?
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.
07
Tell me about a time you had to clean up a messy spreadsheet someone else built.
Situation: I inherited a sales tracker with merged cells, hard-coded totals and three different date formats. Task: my manager needed reliable weekly numbers. Action: I kept a backup copy, converted the data into an Excel Table, fixed dates with Text to Columns, replaced typed totals with SUMIFS and added data validation dropdowns. Result: weekly reporting time fell from two hours to twenty minutes and the totals stopped being questioned.
08
Tell me about a time your automation failed after you handed it over. What did you do?
Situation: a report macro I wrote stopped working for the accounts team. Task: fix it quickly and stop it happening again. Action: I found that a sheet had been renamed and the macro used a fixed sheet name and file path. I fixed it with ThisWorkbook.Path and code names, added an error handler with a clear message, and wrote a short usage note. Result: it ran reliably for the rest of the year and I now test macros on another user's machine before handover.
Select Transform > Group By, choose one or more grouping columns such as Region and Month, then add aggregations like Sum of Amount, Count Rows and Max of Order Date. The Advanced option allows several groupings and aggregations together. The All Rows operation keeps the detail rows as a nested table, useful for ranking within groups or picking the top record per customer.
10
How does INDEX/MATCH work and why is it often preferred over VLOOKUP?
MATCH finds the position of a value in a single row or column, and INDEX returns the item at that position from another range:
=INDEX(Emp!$C:$C,MATCH(A2,Emp!$F:$F,0))
The return column can be to the left of the lookup column, inserting columns does not break it, and it only scans the two columns used. It works in every Excel version, which matters when files go to clients on older Excel.
11
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.
12
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.
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.
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.
15
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.
16
Ten regional heads each need the same dashboard but must see only their own region. How do you deliver it?
Since Excel files cannot restrict data inside a workbook securely, create separate files. Add a Region parameter cell that drives Power Query filters or pivot filters, then loop through regions with a short VBA macro that sets the region, refreshes, removes other data and saves a copy or PDF per region. For regular use at scale, Power BI with row-level security is the better solution.
17
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#.
18
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.
19
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.
20
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.
21
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.
22
Why do you need a separate Date table in the Data Model?
Time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a continuous date column with no gaps, covering full years. A Date table also adds columns like Month Name, Fiscal Year and Quarter for slicing. Create it with Power Query or DAX, for example:
Date = CALENDAR(DATE(2024,4,1),DATE(2027,3,31))
Then mark it as the date table in Power Pivot and relate it to every fact table's date column.
23
What is Power Query and why use it instead of manual cleaning?
Power Query (Data > Get & Transform) is Excel's tool to import, clean and reshape data from files, folders, databases and the web. Every action is recorded as a step, so next month you just click Refresh and the same cleaning runs again on new data. This removes repeated copy-paste work, reduces errors and keeps the original source untouched, which manual cleaning cannot offer.
24
How do you do a two-way lookup that picks both a row and a column?
Use two MATCH functions inside INDEX, one for the row and one for the column:
=INDEX($B$2:$M$50,MATCH($P$2,$A$2:$A$50,0),MATCH($Q$2,$B$1:$M$1,0))
Here P2 holds the product and Q2 the month. With XLOOKUP you can nest one inside another:
=XLOOKUP(P2,A2:A50,XLOOKUP(Q2,B1:M1,B2:M50))
The inner XLOOKUP returns the whole month column, and the outer one picks the product row.
25
A sales file has employee names but the master list has employee IDs, and some names repeat. How do you join them safely?
Do not look up by name alone, because a duplicate name returns the first match and silently assigns sales to the wrong person. First flag duplicates in the master with COUNTIF(names,name)>1. Then build a composite key, such as name plus branch or name plus email, in both tables and look up on that. Rows still ambiguous should go back to the data owner for an ID rather than be guessed.
26
How do you build a KPI card showing achievement against target with an up or down indicator?
Calculate achievement in a cell, for example =Actual/Target-1, and apply a custom number format that adds arrows and colours:
[Color10]▲ 0.0%;[Red]▼ 0.0%;0.0%
Place the actual value in a large font above it, link a text box to the cells for flexible placement, and keep labels short like Revenue vs Target. Conditional formatting icon sets are an alternative but are less precise.
27
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
28
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.
29
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.
30
What are the main elements of an Excel chart and how do you format them?
A chart has a chart title, plot area, horizontal and vertical axes, axis titles, data series, data labels, legend and gridlines. Click the plus button next to a chart to switch elements on or off, and select any element and press Ctrl+1 to open its format pane. Removing unnecessary gridlines and legends, and labelling series directly, usually makes charts cleaner.
Tips to crack the KPMG Advanced Excel interview
Learn basics of IT audit, internal controls and risk frameworks, since advisory roles often combine data with governance work.
Practise structured answers for guesstimates and business problems, explaining assumptions clearly step by step.
Prepare examples of working with stakeholders and meeting deadlines, which are common themes in Big Four interviews.
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; KPMG interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the KPMG Advanced Excel interview?
Candidates usually go through 4 stages: Online assessment or screening test; Technical interview; Managerial or case round; Partner or 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 KPMG Advanced Excel interview?
This practice set leans on Charts and Dashboards (7), Lookup and Dynamic Array Functions (5), Power Query and Data Cleaning (5), Pivot Tables and Data Summaries (3). Revise those first, then practise explaining one or two of your own projects end to end, because most KPMG interviewers ask you to walk through real work.
Are these the exact questions asked at KPMG?
No. These are curated practice questions that match the skills and difficulty usually tested in consulting interviews like KPMG's. They are not a leaked or verified list of past questions.
Where does KPMG hire Advanced Excel professionals in India?
KPMG has large teams in Gurugram, Bengaluru, Mumbai, Hyderabad, Pune. Openings and locations change often, so check the KPMG careers page and filter by city and experience.