18.2 Deloitte

M1. You have sales data for many regions. How would you quickly find the top 5 regions?

Reported for: Deloitte [S1] · also Mu Sigma [S7]

Build a PivotTable with Region in Rows and Sum of Sales in Values, then use Row Labels › Value Filters › Top 10… › Top 5 Items by Sum of Sales and sort descending. With formulas in Microsoft 365: =TAKE(SORTBY(UNIQUE(Region),SUMIFS(Sales,Region,UNIQUE(Region)),-1),5). In older versions, a SUMIFS summary plus LARGE and INDEX-MATCH works. On our data the top city is Pune (₹18,42,500 in March 2026).

M2. How do you identify and handle missing values in a dataset?

Reported for: Deloitte [S1] · also Mu Sigma [S7], MathCo [S15]

Identify: COUNTBLANK per column, Home › Find & Select › Go To Special › Blanks, or a filter on (Blanks). Handle according to business meaning: flag and leave blank, fill down repeated labels (Go To Special › Blanks, type = and the cell above, Ctrl + Enter), use "Unknown" for missing text, or impute a median only when justified and documented. I avoid replacing missing numbers with 0 because it distorts averages and counts.

M3. How would you calculate year-over-year growth with formulas?

Reported for: Deloitte [S1] · also Mu Sigma [S7]

=IFERROR((ThisYear-LastYear)/LastYear,"") formatted as a percentage. With a monthly table I pull both years with SUMIFS, e.g. =SUMIFS(Sales,Year,2026,Month,B$1)/SUMIFS(Sales,Year,2025,Month,B$1)-1. In a pivot, Show Values As › % Difference From › Base field Year › (previous).

M4. Your workbook is very slow. How would you optimise it?

Reported for: Deloitte [S1] · also Mu Sigma [S7], Amazon [S8], Accenture [S3]

Check the used range (Ctrl + End) and delete unused rows/columns and excess formatting; replace volatile functions (OFFSET, INDIRECT, TODAY/NOW in many cells); avoid whole-column references in heavy formulas; replace repeated lookups with one helper column or LET; convert data to Tables and move heavy transformations to Power Query or the Data Model; set Formulas › Calculation Options › Manual while editing; remove unnecessary PivotTable caches; and save as .xlsb.

M5. How would you extract e-mail IDs, domain names or phone numbers from messy text?

Reported for: Deloitte [S1]

Domain: =MID(A2,FIND("@",A2)+1,100) or =TEXTAFTER(A2,"@") (Microsoft 365). An e-mail inside a sentence: find the word containing "@" with TEXTSPLIT and FILTER, or use Flash Fill (Ctrl + E) after typing two examples. Phone numbers: remove spaces, dashes and "+91" with SUBSTITUTE, then =RIGHT(clean,10) and check LEN()=10. Power Query's Extract and Split Column options do the same repeatably.

M6. How do you consolidate data from multiple Excel files into one sheet?

Reported for: Deloitte [S1]

Best: put the files in one folder › Data › Get Data › From File › From Folder › Combine & Transform – Power Query appends them, and next month you only add files and click Refresh. Alternatives: Data › Data Tools › Consolidate for identical summary layouts, or a VBA macro that opens each file and copies the rows.

M7. Can you build a PivotTable from multiple ranges or tables? How?

Reported for: Deloitte [S1]

Yes. Convert each range to a Table, then either (1) load them to the Data Model (tick Add this data to the Data Model), create relationships in Data › Data Tools › Relationships or Power Pivot, and build one pivot using fields from all tables; or (2) append similar tables in Power Query first and pivot the result. (The old Multiple consolidation ranges wizard, Alt, D, P, still exists but is limited.)

M8. Explain VLOOKUP vs INDEX-MATCH, with use cases for each.

Reported for: Deloitte [S1] · also Accenture [S2], TCS [S4], Mu Sigma [S7], MathCo [S15]

VLOOKUP is simple and fine for a quick exact-match lookup where the key is the first column and the layout won't change. INDEX-MATCH can look left, doesn't depend on a hard-coded column number (so inserted columns don't break it), references only the columns needed and supports two-way lookups: =INDEX(tblStores[City Manager],MATCH(F2,tblStores[Store ID],0)). I prefer INDEX-MATCH (or XLOOKUP in Microsoft 365) for models that others will edit.

M9. How would you perform regression analysis in Excel?

Reported for: Deloitte [S1]

Quick view: scatter chart › Add Trendline › display equation and R². Functions: SLOPE, INTERCEPT, RSQ, LINEST (multiple regression). Full output: enable the Analysis ToolPak (File › Options › Add-ins) › Data › Analysis › Data Analysis › Regression, which gives coefficients, p-values and residuals. Example: delivery minutes vs distance for Pune stores.

M10. How do you use What-If Analysis, Scenario Manager and Goal Seek?

Reported for: Deloitte [S1] · also Mu Sigma [S7]

All are under Data › Forecast › What-If Analysis. Goal Seek finds one input for a target result – in Module 11, the Wakad store needs about 507 orders/day to break even. Scenario Manager saves named input sets (Normal, Ganeshotsav, Monsoon) and produces a summary report. Data Tables show the result across a range of one or two inputs.

M11. What is an absolute cell reference? Give an example.

Reported for: Deloitte [S1] · also TCS [S4], Amazon [S8]

A reference with $ signs ($F$1) that does not change when the formula is copied. Example: =G2*$F$1 where F1 holds 5% GST; copied down, G2 becomes G3, G4… but $F$1 stays. Relative references (G2) change; mixed references ($F1, F$1) fix only one part. F4 toggles between them.

M12. How do you find and remove duplicates – including with a formula instead of the built-in tool?

Reported for: Deloitte [S1] · also TCS [S4], EY [S6], Mu Sigma [S7], Amazon [S8], KPMG [S10], MathCo [S15]

Find: Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values, or a helper =COUNTIF($A$2:A2,A2)>1 that marks second and later occurrences. Remove: Data › Data Tools › Remove Duplicates on the key columns. Formula-only: =UNIQUE(A2:D500) in Microsoft 365, or filter out the helper's TRUE rows in older versions. In Power Query: Home › Remove Rows › Remove Duplicates. Standardise text (TRIM/UPPER) first so "blk-9001" and "BLK-9001" are treated as the same.

M13. What is Power Query and how does it help in data cleaning?

Reported for: Deloitte [S1] · also Amazon [S8]

Power Query (Data › Get & Transform Data) imports data from files, folders, databases and the web and cleans it with recorded steps: trim, change case, replace values, split columns, change types with locale, remove duplicates, fill down, unpivot, merge and append. The steps are saved, so the same cleaning runs again with Refresh – ideal for recurring exports.

M14. What is the difference between Power Pivot and a normal PivotTable?

Reported for: Deloitte [S1]

A normal PivotTable summarises a single table or range. Power Pivot adds the Data Model: multiple tables with relationships, compressed storage for millions of rows, and DAX measures (Distinct Count, ratios, time intelligence) that are reusable across pivots.

M15. What are array formulas? Give an example with SUMPRODUCT.

Reported for: Deloitte [S1] · also Mu Sigma [S7], MathCo [S15]

Array formulas calculate on a set of values at once. =SUMPRODUCT((C2:C11="Pune")*(I2:I11="Delivered")*G2:G11) multiplies two TRUE/FALSE arrays with the amounts and adds the result – 324 on our mini dataset. In Microsoft 365, dynamic arrays (FILTER, UNIQUE, SORT) spill multiple results without Ctrl + Shift + Enter.

M16. How would you use IFERROR and ISERROR?

Reported for: Deloitte [S1]

IFERROR(value, value_if_error) returns an alternative when a formula errors: =IFERROR(G2/H2,0). ISERROR(value) returns TRUE/FALSE and is used inside IF or conditional formatting to test for errors, e.g. highlighting rows where a lookup fails. I prefer IFNA or XLOOKUP's if_not_found for lookups so real mistakes such as #REF! are not hidden.

M17. How would you use Data Validation to restrict user input?

Reported for: Deloitte [S1] · also Mu Sigma [S7]

Data › Data Tools › Data Validation: a List for City (the six cities from a Table column), Whole number 1–20 for Qty, Date within the month for Order Date, or Custom with a formula such as =COUNTIF($A:$A,A2)=1 to block duplicate Order IDs. Add an Input Message and an Error Alert. Circle Invalid Data finds existing wrong entries.

M18. How do you handle dynamic ranges in formulas (OFFSET, INDEX)?

Reported for: Deloitte [S1] · also MathCo [S15]

Simplest: use a Table – tblOrders[Amount] grows automatically. Classic named range: =OFFSET($G$2,0,0,COUNTA($G:$G)-1,1) (volatile), or the non-volatile =$G$2:INDEX($G:$G,COUNTA($G:$G)). These are useful for charts and validation lists that must include new rows.

M19. How would you create a dashboard in Excel? What are the key steps?

Reported for: Deloitte [S1] · also Accenture [S3], EY [S6], Mu Sigma [S7], Amazon [S8], MathCo [S15]

Define audience, questions and KPIs → clean data into Tables → build PivotTables on a hidden Calc sheet → KPI cards linked to GETPIVOTDATA/SUMIFS cells → PivotCharts (line for trend, bar for ranking) → slicers and a timeline connected to all pivots → one-screen layout with the most important KPIs top-left → data date, cross-check formulas, protection. (Module 13.)

M20. Which chart types would you use, and when?

Reported for: Deloitte [S1] · also Mu Sigma [S7], Amazon [S8]

Line for trends over time (daily sales in Diwali week), column/bar for comparing categories (sales by city), pie/doughnut for part-of-whole with few slices (Blinkit vs Amazon Now), scatter for relationships, histogram for distributions, combo with a secondary axis for two measures on different scales, waterfall for bridges.

M21. How would you automate repetitive tasks in Excel?

Reported for: Deloitte [S1] · also Mu Sigma [S7], Amazon [S8]

Power Query for repeated import-and-clean steps; Tables, structured formulas and pivots so reports refresh; the macro recorder for simple formatting; and VBA for loops and logic (split by city, combine sheets, export PDFs). I save macro files as .xlsm, add error handling and keep a backup because macros can't be undone.

M22. How would you identify frequent buyers from customer purchase data?

Reported for: Deloitte [S1]

Pivot with Customer in Rows and Count of Order ID (and Sum of Amount) in Values, sorted descending, with a Value Filter such as Count ≥ 5 in the month. With formulas: =COUNTIFS(tblOrders[Customer],A2,tblOrders[Order Date],">="&DATE(2026,10,1),tblOrders[Order Date],"<="&DATE(2026,10,31)) next to a UNIQUE customer list. Recency and average order value can be added for an RFM-style view.

M23. You must prepare an MIS report for management. Which Excel features would you use?

Reported for: Deloitte [S1]

Tables and Power Query for the data, SUMIFS/XLOOKUP for the fixed report layout, PivotTables for breakdowns, conditional formatting for exceptions (below target in red), charts and sparklines for trends, data validation for a month selector, protection for the formulas, and page setup/PDF export for distribution.

M24. Which Excel functions help in fraud detection on transactions?

Reported for: Deloitte [S1]

COUNTIFS to find the same card/phone used many times in a short period; duplicates checks on transaction IDs and amounts; ABS/z-scores or the IQR rule for unusual amounts; ROUND(x,-3)=x to flag suspiciously round amounts; WEEKDAY/HOUR to flag odd times; lookups against a blocked list; and conditional formatting to highlight flagged rows for review.

M25. A client wants a KPI dashboard in Excel instead of Power BI. How would you structure it?

Reported for: Deloitte [S1]

Separate sheets: Data (Tables or Power Query output), Calc (pivots and KPI formulas), Dashboard (cards, charts, slicers), Notes (definitions and refresh steps). Keep it to one screen, connect slicers to all pivots, use Refresh All (or a small refresh macro), protect the structure and document KPI definitions so the numbers match other reports.