Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About EY hiring for Advanced Excel roles
EY is one of the Big Four professional services networks, offering assurance, tax, strategy and transactions, and consulting services. In India, EY GDS and EY member firms support global clients with technology consulting, data analytics, cybersecurity and risk work. It hires for analyst and consultant roles in data analytics, BI, testing, ERP and risk.
For Advanced Excel roles, this EY practice set focuses most on Logical, Text and Date Functions (5), Power Query and Data Cleaning (5), Pivot Tables and Data Summaries (4), Power Pivot, Data Model and DAX (4). It has 7 easy, 20 medium, 3 hard questions, so you can start easy and work up to the harder ones.
Company type
Consulting
Headquarters
London, United Kingdom
Main India offices
Bengaluru, Kolkata, Gurugram, Hyderabad, Kochi
EY Advanced Excel interview process
Online assessment (aptitude + technical)
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 EYStart 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
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.
02
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.
03
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.
04
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.
05
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.
06
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.
07
Write a month-over-month growth percentage measure.
Use DATEADD on the Date table to shift the period one month back:
Sales PM := CALCULATE([Total Sales],DATEADD('Date'[Date],-1,MONTH))
MoM % := DIVIDE([Total Sales]-[Sales PM],[Sales PM])
Place Month from the Date table in the pivot rows. For the first month in the data, Sales PM is blank, so DIVIDE returns blank rather than an error.
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.
09
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.
VLOOKUP can only look to the right of the key column, so it cannot return a value from a column on the left. The column index is a hard-coded number that breaks when columns are inserted. It returns only the first match, defaults to approximate match if the last argument is forgotten, and can be slow on big ranges. XLOOKUP or INDEX/MATCH solve all of these problems.
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.
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.
13
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.
14
What checks do you run when cleaning a new dataset?
Check row counts against the source, then look for duplicate keys, blanks in mandatory fields, inconsistent text such as Delhi and New Delhi, extra spaces and mixed case, dates stored as text or in the wrong locale, negative or extreme numbers that look like typos, and codes losing leading zeros. Power Query's Column Quality and Column Profile views show errors, empties and distinct counts for each column quickly.
15
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.
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.
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.
18
Describe a report you built with pivot tables that helped a decision.
Situation: our store team felt discounts were not driving sales. Task: check it with data. Action: I built a pivot from six months of bills with discount band in rows, month in columns, and sales and margin as values, plus a slicer for store. Result: the pivot showed discounts above 20 percent added little volume but cut margin sharply, and the manager capped discounts at 15 percent the following month.
19
An IF formula says 100 is not equal to 100. Why might that happen?
Usually one value is text and the other is a number, so =A2=B2 returns FALSE even though both show 100. Other causes are hidden spaces, or floating-point results like 99.9999999 that display as 100 because of formatting. Check with ISTEXT, LEN and by widening decimals. Fix with VALUE or TRIM on the text side, or compare with ROUND(A2,2)=ROUND(B2,2) for calculated numbers.
In Microsoft 365:
=COUNTA(UNIQUE(A2:A500))
=ROWS(UNIQUE(FILTER(A2:A500,A2:A500<>""))) ignores blanks
In older versions:
=SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&""))
The older formula slows down on large ranges, so for big data use a pivot table with Distinct Count via the Data Model.
21
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.
22
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.
23
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.
A named range gives a meaningful name, such as GST_Rate or Target_FY26, to a cell, range or constant. Formulas like =Amount*GST_Rate are easier to read and audit than =B2*$Z$1. Names are managed in Formulas > Name Manager and can be scoped to the workbook or a single sheet. They also work as navigation targets in the Name Box and as Data Validation sources.
Ctrl+Arrow jumps to the edge of data and Ctrl+Shift+Arrow selects to it. Ctrl+T creates a Table, Ctrl+Shift+L toggles filters, Ctrl+1 opens Format Cells, Alt+= inserts AutoSum, F4 toggles absolute references or repeats the last action, F2 edits a cell, Ctrl+; inserts today's date, Ctrl+Alt+V opens Paste Special and Ctrl+E runs Flash Fill. Mentioning a few you genuinely use is better than reciting a long list.
26
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.
27
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.
28
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],""))
29
Dates in an exported column are left-aligned and formulas like DATEDIF return #VALUE!. How do you fix them?
Left alignment usually means the dates are stored as text. Select the column, use Data > Text to Columns, choose Date with the correct order such as DMY and finish; Excel converts them to real dates. For mixed or messy text, DATEVALUE or DATE with LEFT, MID and RIGHT works. If the file comes regularly, set the column type in Power Query using Using Locale so day and month are never swapped.
30
When should a team move from an Excel dashboard to Power BI?
Excel dashboards work well for small teams, one-off analysis and data up to a few lakh rows. Move to Power BI when data comes from several live sources, needs scheduled refresh, row-level security for different users, mobile access, or a large audience who should not receive files. The Data Model and DAX skills learned in Power Pivot transfer directly, so the move is usually smooth.
Tips to crack the EY Advanced Excel interview
Practise explaining a data project in business terms, focusing on the problem, approach and recommendation for the client.
Revise Excel, SQL and Power BI, which appear frequently in EY analytics and consulting job descriptions.
Read about EY's service lines and its focus on technology, AI and sustainability consulting for fit questions.
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; EY interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the EY Advanced Excel interview?
Candidates usually go through 4 stages: Online assessment (aptitude + technical); 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 EY Advanced Excel interview?
This practice set leans on Logical, Text and Date Functions (5), Power Query and Data Cleaning (5), Pivot Tables and Data Summaries (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 EY interviewers ask you to walk through real work.
Are these the exact questions asked at EY?
No. These are curated practice questions that match the skills and difficulty usually tested in consulting interviews like EY's. They are not a leaked or verified list of past questions.
Where does EY hire Advanced Excel professionals in India?
EY has large teams in Bengaluru, Kolkata, Gurugram, Hyderabad, Kochi. Openings and locations change often, so check the EY careers page and filter by city and experience.