36 practice questions with answers for Advanced Excel interviews in Kochi.
Advanced Excel jobs in Kochi
Kochi's Infopark and SmartCity host IT-services firms, product companies and start-ups, making it Kerala's largest tech job market along with Thiruvananthapuram. Testing and data roles are common in its service and product teams.
Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Kochi.
Solved for KochiStart 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
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.
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.
03
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.
04
How does INDEX/MATCH work and why is it often preferred over VLOOKUP?
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.
05
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.
06
How do you write a user-defined function (UDF) in VBA? Give an example.
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.
07
Write a Power Query custom column that classifies orders into High, Medium and Low by amount.
Add Column > Custom Column and enter an M if expression; M is case-sensitive and uses lowercase if, then, else:
if [Amount] >= 100000 then "High"
else if [Amount] >= 25000 then "Medium"
else "Low"
Then set the new column type to Text. The Conditional Column dialog can build the same logic without typing, but writing M is faster for nested or combined conditions using and/or.
08
What does converting a range into an Excel Table (Ctrl+T) give you?
A Table expands automatically when new rows or columns are added, so formulas, charts and pivot tables based on it pick up new data without editing ranges. It also gives banded rows, header filters, a Total Row, calculated columns that fill down by themselves, and structured references like Sales[Amount]. For any data that grows every month, a Table is the safest starting point.
09
What does SUMPRODUCT do and when would you use it?
SUMPRODUCT multiplies matching items in arrays and adds the results. It is ideal for weighted averages and for conditional sums in older Excel versions:
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10) weighted average price
=SUMPRODUCT((A2:A100="North")*(MONTH(D2:D100)=4)*C2:C100)
The second example handles a condition on MONTH, which SUMIFS cannot do directly. Avoid full-column references because SUMPRODUCT evaluates every row.
10
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.
11
Your manager says your chart is misleading. What could be wrong?
The most common cause is a column or bar chart whose vertical axis does not start at zero, which exaggerates small differences. Other issues are inconsistent scales between side-by-side charts, a secondary axis that is not labelled, 3D effects distorting sizes, missing months making a trend look smooth, or totals mixed with categories. Fix the axis, label units and state the time period in the title.
VLOOKUP can only look to the right of the key column, so it cannot return a value from a column on the left. The column index is a hard-coded number that breaks when columns are inserted. It returns only the first match, defaults to approximate match if the last argument is forgotten, and can be slow on big ranges. XLOOKUP or INDEX/MATCH solve all of these problems.
13
What are VBA events? Give an example of Worksheet_Change.
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.
14
A sales file has employee names but the master list has employee IDs, and some names repeat. How do you join them safely?
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.
15
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.
16
Explain VLOOKUP syntax and the difference between exact and approximate match.
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.
17
What is the difference between a calculated field and a calculated item?
A calculated field creates a new value field from other fields, such as Margin = Profit / Sales, and it works on the summed totals. A calculated item creates a new member inside a field, such as North + East as a combined region. Calculated items cannot be used with the Data Model, can double-count in totals and slow pivots, so many analysts prefer grouping or a source column instead.
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.
19
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.
20
How do you show actual versus target in a compact bullet-style chart?
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.
21
Tell me about a time you automated a repetitive task.
Situation: every Monday I merged five regional order files and cleaned them by hand, which took about three hours. Task: reduce the effort and errors. Action: I built a Power Query folder import, standardised columns, fixed date locale issues and loaded the result to a pivot dashboard, then wrote a one-page guide for the team. Result: the job took ten minutes, copy-paste errors disappeared and two other teams adopted the same setup.
22
Fifteen people edit the same tracker and you keep getting conflicting versions over email. How would you fix the process?
Move the file to OneDrive or SharePoint so everyone co-authors one live copy, and stop emailing attachments. Restructure it into a single input Table with Data Validation, lock formula columns with sheet protection, and give each person a filtered view or their own sheet view. Use version history for recovery. If edits keep growing, move the data to a SharePoint list or database and keep Excel for reporting.
23
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.
24
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.
25
Tell me about a time a lookup gave wrong results that nobody noticed at first.
Situation: a monthly incentive sheet used VLOOKUP with the last argument left blank, so it ran an approximate match. Task: I was asked to verify payouts before release. Action: I spotted employees getting a neighbour's target, changed every lookup to exact match with XLOOKUP, and added a count of missing IDs. Result: twelve wrong payouts were corrected before payment, and exact match became our team's standard rule.
26
How do you combine all files in a folder using Power Query?
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.
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.
28
You receive 12 monthly sales files and need one pivot for the year. How do you build it?
Put all files in one folder and use Data > Get Data > From File > From Folder > Combine & Transform. Power Query appends them using the first file as a sample, adds the source file name as a column and applies the same cleaning steps. Load the result as a Table or to the Data Model and build the pivot on it. Next year, adding a file to the folder and clicking Refresh All updates everything.
29
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.
Loop from the bottom up so deleting a row does not skip the next one:
Sub DeleteBlankRows()
Dim r As Long, ws As Worksheet
Set ws = ActiveSheet
For r = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Trim(ws.Cells(r, "A").Value) = "" Then ws.Rows(r).Delete
Next r
End Sub
For big data, filtering blanks and deleting visible rows in one action is faster.
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.
32
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.
SUM adds a single column. SUMX iterates a table, evaluates an expression for each row and adds the results:
Revenue := SUMX(Sales,Sales[Qty]*Sales[Price])
This avoids storing a Qty times Price calculated column. Other X functions such as AVERAGEX, MAXX and COUNTX work the same way. Iterating a very large table with complex expressions can be slower, so keep the expression simple.
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.
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.
36
A macro works on your computer but fails on a colleague's. What could be the reasons?
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.
Which companies hire Advanced Excel professionals in Kochi?
Companies with large offices in Kochi include EY, Grant Thornton Bharat. Use their company pages below to practise employer-specific sets.
Are Advanced Excel interviews in Kochi different from other cities?
The questions depend more on the company than the city. Kochi's mix of employers decides the style: Testing and data roles are common in its service and product teams
How do I find Advanced Excel jobs in Kochi?
Shortlist companies with Kochi offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Kochi filter, and practise the questions on this page before each interview.