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

Advanced Excel Interview Questions in Kolkata

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

Advanced Excel jobs in Kolkata

Kolkata has a steady IT-services and BFSI technology job market around Salt Lake Sector V and New Town. Many openings are with service companies and their delivery centres, with a focus on strong fundamentals.

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

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

Compare CONCAT, TEXTJOIN and the & operator.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
The & operator joins items one by one, CONCAT joins a list or range, and TEXTJOIN adds a delimiter and can skip blanks: =A2&" "&B2 =CONCAT(A2:C2) =TEXTJOIN(", ",TRUE,A2:E2) TEXTJOIN is the best choice for building address lines or a comma-separated list of selected items, because the TRUE argument avoids double commas where cells are empty.
02

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

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

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

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

You added new rows of data but the pivot does not show them. Why, and how do you prevent it?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
The pivot's source is probably a fixed range like A1:H500 and the new rows are below it, or the pivot has not been refreshed. Fix it with PivotTable Analyze > Change Data Source, then Refresh. To prevent it, convert the source to an Excel Table so the range expands automatically, use Data > Refresh All, and enable Refresh data when opening the file in PivotTable Options.
07

You need product category in the Sales table to create a calculated column. How do you do it in the Data Model?

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

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

Explain the match_mode and search_mode arguments of XLOOKUP.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. search_mode: 1 first to last (default), -1 last to first, 2 binary search ascending, -2 binary search descending. =XLOOKUP("*Infra*",Cust[Name],Cust[ID],,2) wildcard match =XLOOKUP(A2,Log[Item],Log[Price],,0,-1) last occurrence Binary modes are fast but give wrong answers if the data is not sorted.
10

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

You need to find and remove duplicate customer records but keep the original data safe. How do you do it?

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

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

Your team wants lookups to show Not Found instead of #N/A for missing codes, without hiding genuine formula errors. How do you do it?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Use a function that only handles missing values, not every error: =XLOOKUP(A2, Codes[Code], Codes[Price], "Not Found") or =IFNA(VLOOKUP(A2, Codes, 2, FALSE), "Not Found"). Avoid IFERROR here, because it also hides #REF! or #VALUE! caused by broken ranges. Add =COUNTIF(D:D, "Not Found") at the top so missing codes are counted and followed up.
14

How do you show actual versus target in a compact bullet-style chart?

Charts and DashboardsMedium
Save to ▾
Show answer
Create a clustered column chart with Target and Actual series. Format the Target series with no fill and a thick outline or a light colour, set Series Overlap to 100 percent so both bars sit on top of each other, and make the Actual bar narrower by putting it on the secondary axis with a larger gap width. Match both axis scales. Readers immediately see whether Actual falls short of or crosses the target.
15

Tell me about a time stakeholder feedback changed your dashboard.

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

How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?

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

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

Walk through creating a pivot showing monthly sales by region.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Click inside the data, press Ctrl+T to make it a Table, then Insert > PivotTable > New Worksheet. Drag Region to Rows, Order Date to Columns and Amount to Values. Right-click a date and choose Group > Months (and Years if data covers more than one year). Set Value Field Settings > Number Format to #,##0 and add a slicer for Product if needed. Refresh after new data arrives.
19

What does INDIRECT do and what are its risks?

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

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

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

How do you see the rows behind a pivot value?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Double-click any value cell and Excel creates a new sheet listing every source row that makes up that number. This drill-down is very useful for checking totals or answering a manager's why question. If the file is shared and you do not want people extracting raw data, untick Enable show details in PivotTable Options > Data, though this is not a strong security control.
23

A macro works on your computer but fails on a colleague's. What could be the reasons?

Macros and VBA AutomationMedium
Save to ▾
Show answer
Common causes are hard-coded file paths or user names, missing library references shown as MISSING in Tools > References, macros blocked by Trust Center settings or because the file came from the internet, and 32-bit versus 64-bit Office, which needs PtrSafe in API declarations. Different regional settings for dates and decimals, or a renamed sheet, also break code. Use ThisWorkbook.Path and late binding to make macros portable.
24

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

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#.
26

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

Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?

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

What are histogram and Pareto charts used for?

Charts and DashboardsMedium
Save to ▾
Show answer
A histogram groups numeric values into bins and shows how often each bin occurs, for example how many orders fall between 0 to 1,000 rupees or 1,000 to 5,000 rupees. A Pareto chart sorts categories by frequency and adds a cumulative percentage line, showing the vital few, such as the 20 percent of complaint types causing 80 percent of complaints. Both are available under Insert > Statistic Charts.
29

Budgets are set by category and month, but sales are recorded by product and day. How do you compare them in one pivot?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Do not relate the two fact tables directly. Create shared dimension tables at a grain both can link to: a Category table and a Date table with a Month column. Relate Budget[Category] and Sales via Products to Category, and both date columns to the Date table, using the first day of the month for budgets. Then measures [Total Sales] and [Total Budget] work together when the pivot uses Category and Month.
30

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

What is the difference between a workbook, a worksheet and a range?

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

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

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

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],""))
35

How does macro security work in Excel?

Macros and VBA AutomationEasy
Save to ▾
Show answer
Trust Center settings control whether macros run: disabled with notification is typical. Files in a trusted location, or signed with a trusted digital certificate, run without prompts. Since 2022, Office blocks macros by default in files downloaded from the internet or email attachments that carry the Mark of the Web; users must unblock the file in its Properties. Organisations usually manage these settings through policy.
36

How do you create and use a custom function in Power Query?

Power Query and Data CleaningHard
Save to ▾
Show answer
Write a query that takes a parameter, then call it for each row or file: (filePath as text) as table => let Src = Excel.Workbook(File.Contents(filePath)), Data = Src{[Item="Sales",Kind="Table"]}[Data] in Data Save it as fnGetSales, then Add Column > Invoke Custom Function on a table of file paths and expand the result. This gives more control than the automatic Combine Files helper.

Companies with offices in Kolkata (6)

IT services

Wipro

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
Consulting

EY

Advanced Excel interview questions

  • 30 questions
  • 4 rounds

Bengaluru, Kolkata, Gurugram +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
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 Kolkata?

Companies with large offices in Kolkata include Wipro, Cognizant, EY, PwC, Genpact, Grant Thornton Bharat. Use their company pages below to practise employer-specific sets.

Are Advanced Excel interviews in Kolkata different from other cities?

The questions depend more on the company than the city. Kolkata's mix of employers decides the style: Many openings are with service companies and their delivery centres, with a focus on strong fundamentals

How do I find Advanced Excel jobs in Kolkata?

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

Other cities

DelhiNoidaGurgaonBangaloreHyderabadPuneChennaiMumbaiAhmedabadJaipurLucknowChandigarhIndoreKochiCoimbatoreBhubaneswarNagpurVisakhapatnamThiruvananthapuram

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