Advanced Excel Interview Questions in Visakhapatnam
36 practice questions with answers for Advanced Excel interviews in Visakhapatnam.
Advanced Excel jobs in Visakhapatnam
Visakhapatnam (Vizag) has a developing IT market with delivery centres, start-ups and IT parks around Rushikonda. Many local candidates also apply to Hyderabad and remote roles, so a mix of service and product styles is common.
Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Visakhapatnam.
Solved for VisakhapatnamStart 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
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).
02
You need to find and remove duplicate customer records but keep the original data safe. How do you do it?
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.
03
What are UserForms in VBA and when would you use one?
A UserForm is a custom dialog box with text boxes, dropdowns, option buttons and command buttons, created in the VBA editor through Insert > UserForm. It is useful for controlled data entry, such as an employee or expense entry form that validates inputs before writing a row to a Table, or for letting users choose options before a report macro runs. It reduces errors from typing directly into sheets.
04
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.
05
Dates from a vendor file in dd/mm/yyyy format show as errors or swap day and month. How do you fix it?
The system locale is probably English (United States), which reads dates as mm/dd/yyyy, so 13/04/2026 becomes an error and 05/04/2026 becomes 4 May. In Power Query, right-click the column > Change Type > Using Locale, choose Date and English (India). This tells Power Query how to read the text regardless of the computer's settings, so the file refreshes correctly on any machine.
06
Write VBA to save each worksheet as a separate PDF.
Use ExportAsFixedFormat inside a loop:
Sub SheetsToPDF()
Dim ws As Worksheet, p As String
p = ThisWorkbook.Path & "\"
For Each ws In ThisWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=p & ws.Name & ".pdf", _
Quality:=xlQualityStandard, IgnorePrintAreas:=False
Next ws
End Sub
Set print areas and page setup first so each PDF fits the page properly.
07
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.
08
How do you highlight an entire row when the Status column says Overdue?
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.
09
Tell me about a time you presented data to senior leadership.
Situation: I had to present quarterly branch performance to regional directors. Task: make twenty pages of tables understandable in ten minutes. Action: I built one dashboard sheet with four KPIs against target, a trend chart and a ranked bar chart of branches, and kept detail tables as backup. I opened with the key message and the two branches needing action. Result: the meeting ended with clear decisions, and the format became the standard quarterly review pack.
10
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.
11
What are the main elements of an Excel chart and how do you format them?
A chart has a chart title, plot area, horizontal and vertical axes, axis titles, data series, data labels, legend and gridlines. Click the plus button next to a chart to switch elements on or off, and select any element and press Ctrl+1 to open its format pane. Removing unnecessary gridlines and legends, and labelling series directly, usually makes charts cleaner.
12
Write a month-over-month growth percentage measure.
Use DATEADD on the Date table to shift the period one month back:
Sales PM := CALCULATE([Total Sales],DATEADD('Date'[Date],-1,MONTH))
MoM % := DIVIDE([Total Sales]-[Sales PM],[Sales PM])
Place Month from the Date table in the pivot rows. For the first month in the data, Sales PM is blank, so DIVIDE returns blank rather than an error.
13
Your manager wants each product's share of sales within its region, not of the grand total. How do you show it?
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.
14
How do you use approximate match to apply discount or tax slabs?
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.
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.
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.
17
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.
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.
19
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.
20
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.
21
How do you group dates and numbers in a pivot, and why does grouping sometimes fail?
Right-click a date in Rows and choose Group to group by days, months, quarters or years; for numbers, group by a range such as 0 to 100000 in steps of 10000 to create sales bands. Grouping fails with the message Cannot group that selection when the column contains blanks, text values or dates stored as text. Clean the source column, refresh, and grouping works.
22
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.
23
Why are data types important in Power Query, and what problems does the automatic Changed Type step cause?
Data types decide how values are sorted, summed and loaded, so a number stored as text will not total and a date stored as text will not group by month. Power Query adds an automatic Changed Type step that hard-codes every column name, so a renamed or removed column later causes refresh errors. Many analysts delete it and set types once, deliberately, after the structure is final.
24
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.
25
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.
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.
27
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.
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.
29
What is the difference between a Sub and a Function in VBA?
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.
30
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.
31
HR wants a column that marks employees Eligible for bonus if their rating is 4 or more and tenure is over 1 year, or if they appear in a Top Performers list. How do you write it?
Combine AND inside OR, and use COUNTIF to check the list:
=IF(OR(AND(C2>=4, D2>1), COUNTIF(TopPerformers, A2)>0), "Eligible", "Not Eligible")
Here C is rating, D is tenure in years, A is employee ID and TopPerformers is a named range. Matching on ID instead of name avoids errors from spelling differences.
32
What is a star schema and why is it recommended for the Data Model?
A star schema has one central fact table holding events and numbers, such as Sales with date, product ID, customer ID and amount, surrounded by dimension tables that describe them, such as Date, Product and Customer. Each dimension links to the fact table with a one-to-many relationship. It keeps the model small, makes filters flow predictably and keeps DAX measures simple.
33
Explain the match_mode and search_mode arguments of XLOOKUP.
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.
34
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.
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.
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.
Few big employers listed in Visakhapatnam?
Many Visakhapatnam candidates also apply to teams in Hyderabad and to remote or hybrid roles. Practise the Hyderabad set as well.
Which companies hire Advanced Excel professionals in Visakhapatnam?
Companies with large offices in Visakhapatnam include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are Advanced Excel interviews in Visakhapatnam different from other cities?
The questions depend more on the company than the city. Visakhapatnam's mix of employers decides the style: Many local candidates also apply to Hyderabad and remote roles, so a mix of service and product styles is common
How do I find Advanced Excel jobs in Visakhapatnam?
Shortlist companies with Visakhapatnam offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Visakhapatnam filter, and practise the questions on this page before each interview.