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

Advanced Excel Interview Questions in Gurgaon

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

Advanced Excel jobs in Gurgaon

Gurgaon (Gurugram) is home to many multinational, consulting, fintech and consumer-internet offices. Interviews often expect strong business understanding along with technical skills, especially for analytics and product-facing roles.

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

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

Why do you need a separate Date table in the Data Model?

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

Write a custom number format that shows positives in green, negatives in red brackets and zero as a dash.

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Custom formats have up to four sections separated by semicolons: positive; negative; zero; text. Press Ctrl+1 > Number > Custom and enter: [Green]#,##0;[Red](#,##0);"-";@ For Indian lakh-crore grouping you can use: [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0 The value stays unchanged; only the display changes.
03

A sales file has employee names but the master list has employee IDs, and some names repeat. How do you join them safely?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Do not look up by name alone, because a duplicate name returns the first match and silently assigns sales to the wrong person. First flag duplicates in the master with COUNTIF(names,name)>1. Then build a composite key, such as name plus branch or name plus email, in both tables and look up on that. Rows still ambiguous should go back to the data owner for an ID rather than be guessed.
04

Tell me about a time you had to explain a DAX number that looked wrong to a manager.

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

What are VBA events? Give an example of Worksheet_Change.

Macros and VBA AutomationMedium
Save to ▾
Show answer
Events run code automatically when something happens, such as opening a workbook (Workbook_Open) or editing a cell (Worksheet_Change). In the sheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F:F")) Is Nothing Then Application.EnableEvents = False Target.Offset(0, 1).Value = Now Application.EnableEvents = True End If End Sub This timestamps column G when status in F changes.
06

What is a waterfall chart and where is it used?

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

A few very large values make every other bar in your chart look tiny. How do you present the data clearly?

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

How do you build a dependent dropdown, where the City list changes based on the selected State?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Create a list of cities under each state name and give each list a named range matching the state, for example Maharashtra or Karnataka (no spaces). In B2 make a State list. In C2 use Data Validation > List with source: =INDIRECT(B2) In Microsoft 365 you can avoid named ranges with a spill formula in a helper cell: =FILTER(Cities[City],Cities[State]=B2) and point the validation at that spill range using =$H$2#.
09

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.
10

What does CALCULATE do in DAX?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
CALCULATE evaluates an expression under modified filters. It is the most important DAX function: North Sales := CALCULATE([Total Sales],Region[Region]="North") Online Sales 2026 := CALCULATE([Total Sales],Sales[Channel]="Online",'Date'[Year]=2026) Each filter argument replaces any existing filter on that column, while filters on other columns from the pivot still apply. Wrapping a filter in KEEPFILTERS adds to the existing filter instead of replacing it.
11

A workbook with the Data Model has grown to 300 MB. How do you reduce its size?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
VertiPaq size depends mostly on columns and how many unique values each has. Remove columns not used in reports, especially IDs and free text. Split date-time columns into date and time or drop the time, round decimals to required precision, and replace calculated columns with measures. Use integer keys instead of long text keys, and load only the years required. Filtering at Power Query level avoids loading unneeded rows.
12

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.
13

How do you stop a pivot from losing formatting and column widths on refresh?

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

What is GETPIVOTDATA and how do you control it?

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

Get the latest price of each product from a transaction log sorted by date.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
If the log is sorted oldest to newest, search from the bottom: =XLOOKUP(A2,Log[Product],Log[Price],,0,-1) If it is not sorted, find the latest date first and then match on both: =LET(d,MAXIFS(Log[Date],Log[Product],A2), XLOOKUP(1,(Log[Product]=A2)*(Log[Date]=d),Log[Price])) The second version is order-independent and safer for appended data.
16

Tell me about a time your automation failed after you handed it over. What did you do?

Macros and VBA AutomationMedium
Save to ▾
Show answer
Situation: a report macro I wrote stopped working for the accounts team. Task: fix it quickly and stop it happening again. Action: I found that a sheet had been renamed and the macro used a fixed sheet name and file path. I fixed it with ThisWorkbook.Path and code names, added an error handler with a clear message, and wrote a short usage note. Result: it ran reliably for the rest of the year and I now test macros on another user's machine before handover.
17

Your manager asks you to replace all VLOOKUPs in a legacy workbook with XLOOKUP. How do you approach it?

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

What is the difference between Reference and Duplicate in Power Query?

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

How do you find the last used row in a column with VBA?

Macros and VBA AutomationEasy
Save to ▾
Show answer
The most reliable common method starts from the bottom of the sheet and moves up: Dim lastRow As Long With ThisWorkbook.Worksheets("Data") lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With Using Long, not Integer, avoids overflow beyond 32,767 rows. If the data is an Excel Table, ListObjects("Sales").ListRows.Count is even cleaner.
20

What are named ranges and why are they useful?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A named range gives a meaningful name, such as GST_Rate or Target_FY26, to a cell, range or constant. Formulas like =Amount*GST_Rate are easier to read and audit than =B2*$Z$1. Names are managed in Formulas > Name Manager and can be scoped to the workbook or a single sheet. They also work as navigation targets in the Name Box and as Data Validation sources.
21

How do you combine all files in a folder using Power Query?

Power Query and Data CleaningMedium
Save to ▾
Show answer
Place files with the same structure in one folder. Go to Data > Get Data > From File > From Folder, select the folder and click Combine & Transform. Choose the sheet or table from the sample file; Power Query creates a helper function and appends all files, adding a Source.Name column with the file name. Apply cleaning steps once, Close & Load, and later just drop new files in and Refresh.
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

What is the difference between Protect Sheet, Protect Workbook and Encrypt with Password?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Protect Sheet stops edits to locked cells; you first unlock input cells through Format Cells > Protection so users can type only there. Protect Workbook locks the structure, so sheets cannot be added, deleted or renamed. Encrypt with Password (File > Info) is the only option that blocks opening the file. Sheet and workbook protection are about preventing accidents, not real security, because they are easy to bypass.
24

What is the difference between a Sub and a Function in VBA?

Macros and VBA AutomationEasy
Save to ▾
Show answer
A Sub performs actions, such as formatting cells or saving files, and is run as a macro. A Function returns a value and can be used in worksheet formulas as a user-defined function or called from other code. Both are written in a standard module, opened through Alt+F11 > Insert > Module. Event code goes in the sheet or ThisWorkbook module instead.
25

VLOOKUP returns #N/A even though you can see the value in the table. What do you check?

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

What are parameters in Power Query and when do you use them?

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

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.
28

How do you get a distinct count, such as unique customers, in a pivot table?

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

Calculate working days between two dates excluding Sundays and a holiday list.

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

What principles do you follow when designing an Excel dashboard?

Charts and DashboardsMedium
Save to ▾
Show answer
Start from the questions the audience asks. Put three to five headline KPIs at the top, trends and breakdowns below, and filters such as slicers in one consistent place. Use one colour palette with a highlight colour for what matters, avoid 3D and decorative effects, label units clearly like Rs lakh, and fit the view on one screen. Keep raw data and calculations on separate hidden sheets.
31

Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?

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

How do you handle two senior managers asking for conflicting versions of the same dashboard?

Charts and DashboardsHard
Save to ▾
Show answer
Situation: sales wanted revenue by booking date while finance wanted it by invoice date. Task: avoid two dashboards with different numbers. Action: I met both, wrote down each definition and its use, and proposed one dashboard with a clearly labelled toggle and a note explaining the difference. Result: both agreed, the numbers stopped being argued over in meetings, and the definition was added to our data glossary.
33

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.
34

Refreshing a Power Query that combines 200 CSV files from a folder takes ten minutes. How do you speed it up?

Power Query and Data CleaningHard
Save to ▾
Show answer
Reduce work as early as possible: remove unneeded columns and filter rows in the sample file transform so every file is smaller. Delete redundant Changed Type steps and set types once. Avoid sorting and merges against large tables before the combine. Set helper queries to connection only. Copy files to a local drive if SharePoint is slow, and archive old years into a single file so only recent files are combined each time.
35

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.
36

What are slicers and timelines, and how do you connect one slicer to several pivots?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Slicers are clickable filter buttons for a field, and timelines are slider filters for dates by day, month, quarter or year. They make filtering obvious for dashboard users. To control several pivots, right-click the slicer and choose Report Connections, then tick each pivot. This works only for pivots built on the same source or data model, because they share the same pivot cache.

Companies with offices in Gurgaon (11)

Consulting

Accenture

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Bengaluru, Hyderabad, Pune +2

View questions
Consulting

Deloitte

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Hyderabad, Bengaluru, Mumbai +2

View questions
Consulting

EY

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Bengaluru, Kolkata, Gurugram +2

View questions
Consulting

KPMG

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Gurugram, Bengaluru, Mumbai +2

View questions
Consulting

PwC

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Kolkata, Bengaluru, Mumbai +2

View questions
IT services

Genpact

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Gurugram, Hyderabad, Bengaluru +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
Banking & finance

American Express

Advanced Excel interview questions

  • 30 questions
  • 5 rounds

Gurugram, Bengaluru

View questions
Product

Amazon

Advanced Excel interview questions

  • 30 questions
  • 5 rounds

Bengaluru, Hyderabad, Chennai +2

View questions
Consulting

Grant Thornton Bharat

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Gurugram, Mumbai, Bengaluru +3

View questions

Frequently asked questions

Which companies hire Advanced Excel professionals in Gurgaon?

Companies with large offices in Gurgaon include Accenture, Deloitte, EY, KPMG, PwC, Genpact, EXL, ZS Associates, American Express, Amazon. Use their company pages below to practise employer-specific sets.

Are Advanced Excel interviews in Gurgaon different from other cities?

The questions depend more on the company than the city. Gurgaon's mix of employers decides the style: Interviews often expect strong business understanding along with technical skills, especially for analytics and product-facing roles

How do I find Advanced Excel jobs in Gurgaon?

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

Other cities

DelhiNoidaBangaloreHyderabadPuneChennaiMumbaiKolkataAhmedabadJaipurLucknowChandigarhIndoreKochiCoimbatoreBhubaneswarNagpurVisakhapatnamThiruvananthapuram

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