Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About Deloitte hiring for Advanced Excel roles
Deloitte is one of the Big Four professional services networks, offering audit, tax, consulting, risk advisory and financial advisory services. Its Indian entities, including Deloitte USI, support global clients with technology consulting, analytics, cyber and testing work. It hires in India for analyst and consultant roles in data analytics, business intelligence, QA, ERP and risk advisory.
For Advanced Excel roles, this Deloitte practice set focuses most on Power Query and Data Cleaning (7), Macros and VBA Automation (5), Pivot Tables and Data Summaries (4), Logical, Text and Date Functions (4). It has 4 easy, 26 medium questions, so you can start easy and work up to the harder ones.
Company type
Consulting
Headquarters
London, United Kingdom
Main India offices
Hyderabad, Bengaluru, Mumbai, Gurugram, Pune
Deloitte Advanced Excel interview process
Online assessment (aptitude + technical)
Group discussion or case discussion
Technical interview
Partner or HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for DeloitteStart 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
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.
02
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.
03
Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?
Load the data into Power Query, select the identifying columns such as Branch and Product, then right-click and choose Unpivot Other Columns. The month headers become an Attribute column and the numbers become a Value column; rename them to Month and Sales. Using Unpivot Other Columns rather than Unpivot Columns means new months added later are included automatically on refresh.
04
You need to find and remove duplicate customer records but keep the original data safe. How do you do it?
First highlight duplicates with Conditional Formatting > Highlight Cells Rules > Duplicate Values to review them. Then work on a copy of the sheet and use Data > Remove Duplicates, selecting the key columns such as Mobile and Email. In Microsoft 365 a non-destructive option is =UNIQUE(A2:C500) on a new sheet. Always record how many rows were removed so the change can be explained later.
05
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.
06
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.
07
What are parameters in Power Query and when do you use them?
A parameter is a named value, created in Manage Parameters, that queries can reference instead of a hard-coded value. A common use is a FolderPath parameter so the same workbook works when moved to another computer or shared drive. Parameters can also hold a cut-off date or a region filter. Changing one value updates every query that uses it, which makes files easier to hand over.
08
Why does a date show as a number like 46023 when joined with text, and how do you fix it?
Excel stores dates as serial numbers, so joining with & uses the raw number instead of the formatted view. Wrap the date in TEXT with a format code:
="Report date: "&TEXT(A2,"dd-mmm-yyyy")
="Sales: Rs "&TEXT(B2,"#,##0")
TEXT returns text, so use it only for labels and titles, never for values that need further calculation.
09
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
10
What is Data Validation and how do you create a dropdown list?
Data Validation restricts what users can type in a cell, which prevents bad input in forms and trackers. For a dropdown, select the cells, go to Data > Data Validation, choose Allow: List and set Source to a range such as =$K$2:$K$10 or a typed list like Open,Closed,On Hold. Pointing the source at a Table column keeps the list updated automatically when new options are added.
11
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.
12
A report exported from PDF repeats the header row on every page and has page-footer lines. How do you clean it?
Use Get Data > From PDF or import the converted file, then promote the first row to headers. Filter out rows where the first column equals the header text, such as Name, and rows containing footer text like Page or Printed on, using Text Filters > Does Not Contain. Remove blank rows, Fill Down any group labels, and set types. The steps then run automatically on next month's PDF.
13
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.
14
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.
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.
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.
17
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.
18
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.
19
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.
20
What are UserForms in VBA and when would you use one?
A UserForm is a custom dialog box with text boxes, dropdowns, option buttons and command buttons, created in the VBA editor through Insert > UserForm. It is useful for controlled data entry, such as an employee or expense entry form that validates inputs before writing a row to a Table, or for letting users choose options before a report macro runs. It reduces errors from typing directly into sheets.
21
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.
22
A macro produces wrong totals because a misspelt variable name was silently treated as a new empty variable. How do you prevent this?
Add Option Explicit at the top of every module, and enable Tools > Options > Require Variable Declaration so new modules get it automatically. VBA then refuses to run code that uses an undeclared variable and highlights the typo at compile time. Also declare proper types such as Long or Double instead of Variant, which catches type mistakes and makes the code faster and easier to read.
23
What is the difference between RELATED and RELATEDTABLE?
RELATED is used on the many side to fetch a single value from the one side, such as getting a product's category in each sales row. RELATEDTABLE is used on the one side to return all matching rows from the many side, so in Products you could write Order Count = COUNTROWS(RELATEDTABLE(Sales)). Both need an existing relationship and a row context.
24
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.
25
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.
26
What are the limitations of the macro recorder, and what is relative recording?
The recorder writes code for every click, including unnecessary Select statements and fixed cell addresses, so the macro breaks when data size changes. It cannot record loops, conditions or input boxes. By default it records absolute references; turning on Use Relative References records moves relative to the active cell. The recorder is great for learning syntax, but recorded code should be cleaned up by hand.
27
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.
28
Describe a time a formula mistake of yours reached a stakeholder. What did you do?
Situation: a regional sales summary I sent showed one zone 18% higher than actual. Task: correct it before decisions were taken. Action: I traced it to a SUMIFS range that stopped one row short, sent a corrected file within the hour with a short note explaining the cause, and added a check cell comparing the summary total to the raw data total. Result: the manager appreciated the quick ownership and the check now catches such gaps automatically.
29
How do you remove duplicate customers but keep the most recent record in Power Query?
Sort by Updated Date descending, then buffer the table so the sort order is respected before removing duplicates on the key:
Sorted = Table.Sort(Source,{{"UpdatedOn", Order.Descending}}),
Buffered = Table.Buffer(Sorted),
Latest = Table.Distinct(Buffered,{"CustomerID"})
Without Table.Buffer, Power Query may not keep the sorted order, so the record kept could be an older one.
30
How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?
Select the column, choose Split Column > By Delimiter, enter space-hyphen-space as a custom delimiter and split at each occurrence. This gives ID, Name and City columns. Then use Transform > Format > Trim and Clean to remove extra spaces and hidden characters, Capitalize Each Word for names, and set correct data types. Split by Number of Characters handles fixed-width codes like state and branch prefixes.
Tips to crack the Deloitte Advanced Excel interview
Practise guesstimates and simple business cases, since consulting interviews often test structured thinking alongside technical skills.
Be ready to explain dashboards or analyses you built in Excel, SQL, Power BI or Tableau and the business decisions they supported.
Read about Deloitte's service lines such as Risk Advisory and Consulting so you can say which practice suits you.
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; Deloitte interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the Deloitte Advanced Excel interview?
Candidates usually go through 4 stages: Online assessment (aptitude + technical); Group discussion or case discussion; Technical interview; 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 Deloitte Advanced Excel interview?
This practice set leans on Power Query and Data Cleaning (7), Macros and VBA Automation (5), Pivot Tables and Data Summaries (4), Logical, Text and Date Functions (4). Revise those first, then practise explaining one or two of your own projects end to end, because most Deloitte interviewers ask you to walk through real work.
Are these the exact questions asked at Deloitte?
No. These are curated practice questions that match the skills and difficulty usually tested in consulting interviews like Deloitte's. They are not a leaked or verified list of past questions.
Where does Deloitte hire Advanced Excel professionals in India?
Deloitte has large teams in Hyderabad, Bengaluru, Mumbai, Gurugram, Pune. Openings and locations change often, so check the Deloitte careers page and filter by city and experience.