Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About ZS Associates hiring for Advanced Excel roles
ZS is a management consulting and technology firm known for its work with pharmaceutical and life sciences companies on sales, marketing and analytics. Its India offices in Pune, Gurugram and Bengaluru handle analytics, data engineering and technology delivery. It hires for business analysts, decision analytics associates, data engineers and data scientists.
For Advanced Excel roles, this ZS Associates practice set focuses most on Power Pivot, Data Model and DAX (6), Lookup and Dynamic Array Functions (5), Pivot Tables and Data Summaries (5), Logical, Text and Date Functions (5). It has 8 easy, 19 medium, 3 hard questions, so you can start easy and work up to the harder ones.
Company type
Consulting
Headquarters
Evanston, Illinois, USA
Main India offices
Pune, Gurugram, Bengaluru
ZS Associates Advanced Excel interview process
Online assessment (aptitude + analytics)
Case interview
Technical interview
Managerial round
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for ZS AssociatesStart 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 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.
02
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.
03
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.
04
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.
05
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.
An asterisk matches any number of characters, a question mark matches exactly one, and a tilde escapes a real * or ?.
=COUNTIF(A:A,"*Pvt Ltd*") names containing Pvt Ltd
=SUMIFS(C:C,B:B,"INV-2026-????") invoice codes of a fixed length
=COUNTIF(A:A,"*~?*") cells that contain a question mark
Wildcards work only on text criteria, not on numbers.
07
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.
08
A manager wants a 50-row branch report where every row also shows a small trend of the last 12 months. What do you use?
Use sparklines: select the 12 monthly values for all rows, choose Insert > Sparklines > Line and set the location to a column next to the data. Turn on high and low point markers, and under Axis choose the same minimum and maximum for all sparklines if branches should be compared fairly. They fit inside a cell, print well and update when the data changes.
09
Tell me about a time you built a macro that others in your team relied on. How did you keep it maintainable?
Situation: I wrote a macro that split a master file into branch-wise reports for 30 branches. Task: others had to run it while I was on leave. Action: I added Option Explicit, comments, a settings sheet for paths and branch lists, error handling with clear messages and a short user guide. Result: colleagues ran it for months without my help, and adding new branches needed only a row in the settings sheet.
10
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.
11
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.
12
Tell me about a time you had to explain a DAX number that looked wrong to a manager.
Situation: a manager said our margin percentage in the Data Model pivot did not match his calculator. Task: show whether the measure was right. Action: I took one region, showed that he was averaging row percentages while the measure divided total margin by total sales, and walked through both in a small table. Result: he agreed the measure was correct, and I added a note to the report explaining how the KPI is calculated.
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.
14
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.
15
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.
16
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).
17
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.
18
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.
19
The pivot shows Count of Sales instead of Sum of Sales when you drag the field. Why?
Excel defaults to Count when a value column contains blanks or text, such as NA or a number stored as text. Change it through Value Field Settings > Sum, but also fix the source: replace text placeholders with zero or blank, convert text numbers to real numbers, and refresh. Otherwise the text entries are silently left out of the sum and totals will be understated.
20
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.
21
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.
22
You added new rows of data but the pivot does not show them. Why, and how do you prevent it?
The pivot's source is probably a fixed range like A1:H500 and the new rows are below it, or the pivot has not been refreshed. Fix it with PivotTable Analyze > Change Data Source, then Refresh. To prevent it, convert the source to an Excel Table so the range expands automatically, use Data > Refresh All, and enable Refresh data when opening the file in PivotTable Options.
SUMIF handles one condition and puts the sum range last; SUMIFS handles many conditions and puts the sum range first:
=SUMIF(A:A,"North",C:C)
=SUMIFS(C:C,A:A,"North",B:B,"Laptop",D:D,">="&DATE(2026,4,1))
Because of the different order, many people use SUMIFS everywhere, even for a single condition, to stay consistent. COUNTIFS and AVERAGEIFS follow the same pattern.
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
What is the difference between a calculated column and a measure in DAX?
A calculated column is computed row by row when data is refreshed and stored in the table, like Sales[Margin] = Sales[Amount] - Sales[Cost]. It uses memory and can be used in rows, columns and slicers. A measure is computed on the fly for each cell of a pivot based on its filters, like Total Margin = SUM(Sales[Margin]). Use measures for aggregations and calculated columns only for attributes you slice by.
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
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.
28
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.
29
Tell me about a time you had to explain a complex Excel report to a non-technical manager.
Situation: my manager was unsure why the new incentive sheet showed lower payouts. Task: explain the logic without formulas. Action: I added a one-page summary with the slab table, walked through two real employees step by step, and used colour to show which inputs drove the result. Result: the manager understood it in one meeting, approved the sheet and asked me to add the same summary to future models.
30
Write basic DAX measures for total sales, order count and average order value.
In Power Pivot > Measures > New Measure, or in the pivot field list:
Total Sales := SUM(Sales[Amount])
Orders := DISTINCTCOUNT(Sales[OrderID])
Avg Order Value := DIVIDE([Total Sales],[Orders])
Referencing measures inside other measures keeps logic in one place. Set the number format on the measure itself, so every pivot that uses it shows the same format.
Tips to crack the ZS Associates Advanced Excel interview
Learn pharma commercial basics like sales force effectiveness, market share and prescriber data for case questions.
Practise case interviews and guesstimates with a clear structure, as consulting rounds are central at ZS.
Revise Excel, SQL and basic statistics, since associate roles involve heavy data 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; ZS Associates interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the ZS Associates Advanced Excel interview?
Candidates usually go through 5 stages: Online assessment (aptitude + analytics); Case interview; Technical 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 ZS Associates Advanced Excel interview?
This practice set leans on Power Pivot, Data Model and DAX (6), Lookup and Dynamic Array Functions (5), Pivot Tables and Data Summaries (5), Logical, Text and Date Functions (5). Revise those first, then practise explaining one or two of your own projects end to end, because most ZS Associates interviewers ask you to walk through real work.
Are these the exact questions asked at ZS Associates?
No. These are curated practice questions that match the skills and difficulty usually tested in consulting interviews like ZS Associates's. They are not a leaked or verified list of past questions.
Where does ZS Associates hire Advanced Excel professionals in India?
ZS Associates has large teams in Pune, Gurugram, Bengaluru. Openings and locations change often, so check the ZS Associates careers page and filter by city and experience.