36 practice questions with answers for Advanced Excel interviews in Delhi.
Advanced Excel jobs in Delhi
Delhi NCR has one of India's biggest pools of IT, consulting, banking and start-up jobs. Delhi itself hosts many corporate, government-tech and consulting offices, and most candidates also apply across Noida and Gurgaon, so interviews here mix service-company and product-company styles.
Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Delhi.
Solved for DelhiStart 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
Extract the first and last name from a full name like Rahul Kumar Sharma.
Classic formulas:
First: =LEFT(A2,FIND(" ",A2)-1)
Last: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
In Microsoft 365 it is simpler:
First: =TEXTBEFORE(A2," ")
Last: =TEXTAFTER(A2," ",-1)
The -1 tells TEXTAFTER to use the last space, which handles middle names. Run TRIM on the source first to remove double spaces.
02
What is a pivot table and when should you use one?
A pivot table summarises large tabular data by dragging fields into rows, columns, values and filters, without writing formulas. Use it when you need totals, counts or averages broken down by categories such as region, product and month, and when the breakdown changes often. It works best on clean data with one header row, no blank rows and one record per row, ideally stored in an Excel Table.
03
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.
04
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.
05
Write a running total measure for cumulative sales across dates.
Keep all dates up to the last date in the current filter:
Running Sales :=
CALCULATE([Total Sales],
FILTER(ALL('Date'[Date]),
'Date'[Date]<=MAX('Date'[Date])))
Place Month or Date from the Date table in the pivot rows. To restart every financial year, use TOTALYTD with the "3/31" year-end instead.
06
Explain row context, filter context and context transition.
Filter context is the set of filters applied to a calculation, coming from pivot rows, columns, slicers and CALCULATE. Row context exists when DAX iterates rows, as in a calculated column or SUMX, and lets you refer to the current row's values; it does not filter other tables by itself. Context transition happens when CALCULATE, or a measure reference, is used inside a row context and turns the current row into an equivalent filter context.
07
How do you look up a value using multiple criteria, such as Employee ID and Month?
In Microsoft 365, multiply logical tests so only the row matching all conditions becomes 1:
=XLOOKUP(1,(Pay[EmpID]=A2)*(Pay[Month]=B2),Pay[Salary],"Not found")
In older Excel use INDEX/MATCH entered as an array formula (Ctrl+Shift+Enter):
=INDEX(Pay[Salary],MATCH(1,(Pay[EmpID]=A2)*(Pay[Month]=B2),0))
For very large data a helper column joining ID and Month is faster.
08
Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?
The source file's header changed, for example Amount became Amount (Rs), and a step that refers to the old name, often Changed Type or Renamed Columns, breaks. Open the query, click through the steps to find the first error, and update the name or rename the column back early in the query. To prevent repeats, agree a fixed template with the data owner and avoid steps that list every column unnecessarily.
09
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.
10
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.
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.
12
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.
13
What is LAMBDA and how do you create a custom function without VBA?
LAMBDA lets you define a reusable function from a formula. Create it in Name Manager, for example name GSTInclusive with:
=LAMBDA(amount,rate,ROUND(amount*(1+rate),2))
Then use =GSTInclusive(B2,18%) anywhere in the workbook. Helper functions like MAP, BYROW, REDUCE and SCAN apply a LAMBDA across arrays. Unlike VBA functions, LAMBDAs need no macro-enabled file, but they require Microsoft 365.
14
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.
15
Tell me about a time stakeholder feedback changed your dashboard.
Situation: I built a sales dashboard with eight charts for the regional team. Task: get it adopted. Action: in the review, managers said they only checked target achievement and the bottom five branches each morning, so I moved those to the top, removed four rarely used charts and added a branch slicer. Result: usage went up, the meeting started using it daily, and I learned to ask what decisions a dashboard supports before building it.
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 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.
18
How do you send personalised emails from Excel through Outlook using VBA?
Use late binding so no library reference is needed, and loop through the rows:
Sub SendMails()
Dim ol As Object, m As Object, r As Long
Set ol = CreateObject("Outlook.Application")
For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set m = ol.CreateItem(0)
m.To = Cells(r, 2).Value
m.Subject = "Payment reminder"
m.Body = "Dear " & Cells(r, 1).Value & ", your due amount is Rs " & Cells(r, 3).Value
m.Display
Next r
End Sub
Use .Display to review before switching to .Send.
19
How do you create a chart that automatically shows the last 12 months as new data is added?
Create named ranges with INDEX so they move with the data, assuming dates in column A from row 2:
LastRow: =COUNTA(Data!$A:$A)
ChartDates: =INDEX(Data!$A:$A,LastRow-11):INDEX(Data!$A:$A,LastRow)
ChartSales: =INDEX(Data!$B:$B,LastRow-11):INDEX(Data!$B:$B,LastRow)
Then edit the chart series to =Book1.xlsx!ChartSales and the axis to ChartDates. A Table-based chart grows automatically but shows all months, not a rolling window.
20
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.
IFERROR replaces any error, such as #N/A, #DIV/0!, #VALUE! or #REF!, with a value you choose. IFNA replaces only #N/A, which is the normal result when a lookup finds no match. IFNA is safer around lookups because a genuine problem like a deleted column (#REF!) still shows up instead of being hidden as a blank or zero. Use IFERROR only where every error truly means the same thing.
22
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.
A waterfall chart shows how a starting value moves to an ending value through a series of positive and negative steps. It is common in finance for profit bridges, such as last year's profit, plus volume growth, minus price discount, minus higher costs, giving this year's profit. Excel 2016 and later include it under Insert > Waterfall; set the first and last bars as totals so they start from zero.
24
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.
25
Calculate working days between two dates excluding Sundays and a holiday list.
NETWORKDAYS excludes Saturday and Sunday by default. For a six-day week common in India, use NETWORKDAYS.INTL with weekend code 11 (Sunday only):
=NETWORKDAYS.INTL(A2,B2,11,Holidays[Date])
To find a due date 10 working days after A2:
=WORKDAY.INTL(A2,10,11,Holidays[Date])
Keep the holiday list in a Table so it can be updated each year.
LET assigns names to intermediate results inside one formula, so a value is calculated once and the formula reads like steps:
=LET(sales,SUMIFS(Data[Amt],Data[Rep],A2),
target,XLOOKUP(A2,Targets[Rep],Targets[Target]),
IF(target=0,"",sales/target))
Without LET, the SUMIFS would be repeated in each branch. It improves speed on large sheets and makes complex formulas easier to review.
27
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.
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.
When a pivot is created, Excel stores a snapshot of the source data called the pivot cache, and the pivot reads from it, not directly from the sheet. That is why changes need a refresh. Pivots built from the same Table share one cache, which keeps file size down and lets slicers connect across them. Creating pivots from different copies of the data creates separate caches and makes the file heavier.
Rows lists categories down the left side, such as Region. Columns spreads categories across the top, such as Month. Values holds the numbers being summarised, such as Sum of Sales or Count of Orders. Filters applies a filter to the whole pivot, such as Financial Year. Moving a field from Rows to Columns changes the layout instantly, which is why pivots are quick for exploring data.
31
What is the difference between a workbook, a worksheet and a range?
A workbook is the whole file (.xlsx, .xlsm or .xlsb). A worksheet is one tab inside it with 1,048,576 rows and 16,384 columns. A range is any group of cells such as A2:D100 that a formula, chart or format refers to. Good practice is to keep raw data, calculations and output on separate worksheets of the same workbook so each layer is easy to audit.
32
What is the difference between Append and Merge in Power Query?
Append stacks tables one below another, like combining January, February and March sales that have the same columns; it is similar to SQL UNION. Merge joins two tables side by side on a matching key, like adding product category from a Products table to Sales; it is similar to a SQL JOIN or VLOOKUP. Choosing the right one is the first step in any multi-table task.
33
Tell me about a time data from two teams did not match. How did you resolve it?
Situation: finance and sales reported different revenue for the same quarter. Task: find the gap before the review meeting. Action: I pulled both datasets, built a key on invoice number, and used lookups to list invoices present in only one file. Most of the gap came from credit notes that sales did not deduct and invoices booked on the last day. Result: both teams agreed a single definition and I added a reconciliation tab to the monthly pack.
34
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.
35
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.
36
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.
Frequently asked questions
Which companies hire Advanced Excel professionals in Delhi?
Companies with large offices in Delhi include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are Advanced Excel interviews in Delhi different from other cities?
The questions depend more on the company than the city. Delhi's mix of employers decides the style: Delhi itself hosts many corporate, government-tech and consulting offices, and most candidates also apply across Noida and Gurgaon, so interviews here mix service-company and product-company styles
How do I find Advanced Excel jobs in Delhi?
Shortlist companies with Delhi offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Delhi filter, and practise the questions on this page before each interview.