Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Advanced Excel Interview Questions in Pune

36 practice questions with answers for Advanced Excel interviews in Pune.

Advanced Excel jobs in Pune

Pune combines IT-services campuses in Hinjewadi and Kharadi with product, automotive and BFSI technology centres. It has a large mid-level job market for data and QA roles, and interviews are often practical and project-based.

Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Pune.

Solved for PuneStart 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

Dates in an exported column are left-aligned and formulas like DATEDIF return #VALUE!. How do you fix them?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
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.
02

How do you automatically highlight the highest and lowest points in a line chart?

Charts and DashboardsMedium
Save to ▾
Show answer
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

What is the difference between RELATED and RELATEDTABLE?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
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.
04

What are dynamic arrays and what causes a #SPILL! error?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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.
05

You change input values but the totals do not update until you press something. What is happening and how do you fix it?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
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.
06

How do you use approximate match to apply discount or tax slabs?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Create a slab table with the lower limit of each band in ascending order and the rate next to it. Approximate match returns the row for the largest limit not greater than the value: =VLOOKUP(B2,Slabs!$A$2:$B$6,2,TRUE) =XLOOKUP(B2,Slabs[From],Slabs[Rate],,-1) If B2 is 7.5 lakh and the bands start at 0, 3, 7 and 10 lakh, the 7 lakh row is returned. Unsorted data gives wrong results silently.
07

How do you handle errors in VBA?

Macros and VBA AutomationMedium
Save to ▾
Show answer
Use On Error GoTo to jump to a handler, report the problem and clean up: Sub ImportData() On Error GoTo Fail Application.ScreenUpdating = False ' ... main code ... Done: Application.ScreenUpdating = True Exit Sub Fail: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Done End Sub Avoid blanket On Error Resume Next, which hides real failures.
08

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 Tables and Data SummariesMedium
Save to ▾
Show answer
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.
09

How do you highlight an entire row when the Status column says Overdue?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Select the full data range, for example A2:H500, then Home > Conditional Formatting > New Rule > Use a formula. Enter: =$F2="Overdue" Choose a fill colour and click OK. The dollar before F locks the column, while the relative row 2 lets the rule move down row by row, so every column in that row gets coloured when column F matches.
10

You need to reconcile a bank statement with the ledger and list entries missing from either side. How?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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.
11

Tell me about a time you got a last-minute analysis request. How did you handle it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Situation: at 5 PM, my manager needed customer-wise outstanding for a client call at 9 AM. Task: deliver an accurate summary quickly. Action: I confirmed the exact question and cut-off date, pulled the ledger, built a pivot by customer and ageing bucket, cross-checked the total with the trial balance and flagged three unusual balances. Result: the report was ready by 7 PM, and my notes helped the manager answer follow-up questions in the call.
12

What are Power Pivot and the Excel Data Model?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
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.
13

What is the difference between a calculated column and a measure in DAX?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
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.
14

Explain VLOOKUP syntax and the difference between exact and approximate match.

Lookup and Dynamic Array FunctionsEasy
Save to ▾
Show answer
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) searches the first column of the table and returns a value from the column number given: =VLOOKUP(A2,Products!$A:$D,3,FALSE) FALSE (or 0) means exact match and is what you need for IDs and codes. TRUE, the default, means approximate match and needs the first column sorted ascending; it is used for slabs like tax or discount bands.
15

What is the difference between Flash Fill and Text to Columns?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Text to Columns splits a column by a fixed delimiter or fixed width in one go, which suits clean data like comma-separated codes. Flash Fill (Ctrl+E) learns a pattern from one or two examples you type, such as extracting first names or formatting phone numbers. Both produce static values, so if the source changes you must redo them, unlike formulas or Power Query steps.
16

Describe a time you taught colleagues to use pivot tables.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Situation: three teammates built monthly summaries by filtering and copying data by hand. Task: my lead asked me to help them speed up. Action: I ran two short sessions using our own sales data, showing how to create a pivot, group dates, add slicers and refresh after new rows, and shared a one-page guide. Result: within a month each of them produced their summary in minutes, and fewer copy-paste errors reached the manager.
17

Return the Indian financial year (April to March) for a date, like FY 2026-27.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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).
18

Your manager wants each product's share of sales within its region, not of the grand total. How do you show it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Put Region and then Product in Rows, and add Sales to Values twice. For the second copy, open Value Field Settings > Show Values As > % of Parent Row Total. Each product then shows its share of its own region, and each region totals 100 percent. Using % of Grand Total instead would show share of the whole company, which answers a different question.
19

How do you remove duplicate customers but keep the most recent record in Power Query?

Power Query and Data CleaningMedium
Save to ▾
Show answer
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.
20

Why does a date show as a number like 46023 when joined with text, and how do you fix it?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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.
21

Users of your Data Model pivot keep dragging Year, Quarter and Month separately and make mistakes. How do you make it easier?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
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.
22

Why use DIVIDE instead of the / operator in DAX?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
DIVIDE handles division by zero safely and returns blank, or a value you choose, instead of an error: Margin % := DIVIDE([Total Profit],[Total Sales]) Margin % := DIVIDE([Total Profit],[Total Sales],0) Blank results are hidden in pivots, which keeps reports clean when a product has no sales in a month. Use the / operator only when the denominator can never be zero, because it is slightly faster.
23

Your Power Query output has 20 lakh rows and Excel says it cannot load it to a sheet. What do you do?

Power Query and Data CleaningMedium
Save to ▾
Show answer
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.
24

Write a running total measure for cumulative sales across dates.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
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.
25

Explain relationships, cardinality and filter direction in the Data Model.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
A relationship links a key column in one table to a matching column in another. Cardinality describes how many rows match: one-to-many is the standard case, where each product appears once in Products but many times in Sales. In Excel's Data Model, filters flow from the one side to the many side, so selecting a category in Products filters Sales, but not the reverse.
26

List the steps to build a sales and margin percentage combo chart.

Charts and DashboardsMedium
Save to ▾
Show answer
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.
27

How does INDEX/MATCH work and why is it often preferred over VLOOKUP?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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.
28

How do you count unique values in a column?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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.
29

A report exported from PDF repeats the header row on every page and has page-footer lines. How do you clean it?

Power Query and Data CleaningMedium
Save to ▾
Show answer
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.
30

How do you write a user-defined function (UDF) in VBA? Give an example.

Macros and VBA AutomationMedium
Save to ▾
Show answer
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.
31

Explain TRIM, CLEAN, PROPER, UPPER, LOWER and SUBSTITUTE.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
TRIM removes leading, trailing and repeated spaces between words. CLEAN removes non-printable characters such as line breaks from system exports. PROPER, UPPER and LOWER change case, for example PROPER turns RAHUL SHARMA into Rahul Sharma. SUBSTITUTE replaces specific text, like =SUBSTITUTE(A2,"-","") to strip hyphens from a phone number. They are often nested together when cleaning customer master data.
32

Explain the FILTER function with an example.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
FILTER returns all rows that meet a condition and spills them into neighbouring cells: =FILTER(Sales,Sales[Region]="South","No data") =FILTER(Sales,(Sales[Region]=G1)*(Sales[Amount]>100000)) Multiplying conditions means AND; adding them with + means OR. Because the output updates live, FILTER is useful for searchable lists and drill-down sheets without needing pivot tables or macros. It requires Microsoft 365 or Excel 2021.
33

Describe a time you found an error in a report just before it was sent.

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Situation: a monthly MIS was due to leadership in an hour. Task: I was doing a final review. Action: I noticed the region totals did not match the grand total, traced it to a SUMIFS range that stopped at row 500 while data had grown to 620 rows, converted the source to a Table and re-checked totals against the raw export. Result: the report went out on time with correct numbers, and I added a reconciliation check cell to the template.
34

How is the Excel Data Model related to Power BI?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
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.
35

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.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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.
36

How do you return all matching values for a lookup, not only the first?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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],""))

Companies with offices in Pune (11)

IT services

TCS

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Mumbai, Chennai, Pune +2

View questions
IT services

Infosys

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Bengaluru, Mysuru, Pune +2

View questions
IT services

Wipro

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Bengaluru, Hyderabad, Pune +2

View questions
Consulting

Accenture

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Bengaluru, Hyderabad, Pune +2

View questions
IT services

Cognizant

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Chennai, Bengaluru, Pune +2

View questions
IT services

Capgemini

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Mumbai, Bengaluru, Pune +2

View questions
Consulting

Deloitte

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Hyderabad, Bengaluru, Mumbai +2

View questions
Consulting

KPMG

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Gurugram, Bengaluru, Mumbai +2

View questions
Analytics

EXL

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Noida, Gurugram, Bengaluru +1

View questions
Consulting

ZS Associates

Advanced Excel interview questions

  • 30 questions
  • 5 rounds

Pune, Gurugram, Bengaluru

View questions
Product

Amazon

Advanced Excel interview questions

  • 30 questions
  • 5 rounds

Bengaluru, Hyderabad, Chennai +2

View questions

Frequently asked questions

Which companies hire Advanced Excel professionals in Pune?

Companies with large offices in Pune include TCS, Infosys, Wipro, Accenture, Cognizant, Capgemini, Deloitte, KPMG, EXL, ZS Associates. Use their company pages below to practise employer-specific sets.

Are Advanced Excel interviews in Pune different from other cities?

The questions depend more on the company than the city. Pune's mix of employers decides the style: It has a large mid-level job market for data and QA roles, and interviews are often practical and project-based

How do I find Advanced Excel jobs in Pune?

Shortlist companies with Pune offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Pune filter, and practise the questions on this page before each interview.

Other cities

DelhiNoidaGurgaonBangaloreHyderabadChennaiMumbaiKolkataAhmedabadJaipurLucknowChandigarhIndoreKochiCoimbatoreBhubaneswarNagpurVisakhapatnamThiruvananthapuram

More ways to practise

Company-wise Advanced Excel questionsExperience-wise Advanced Excel questionsRole-wise Advanced Excel questionsCity-wise Advanced Excel questionsMost asked Advanced Excel questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsAdvanced Excel mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsAdvanced Excel topic sheets