36 practice questions with answers for Advanced Excel interviews in Bhubaneswar.
Advanced Excel jobs in Bhubaneswar
Bhubaneswar is eastern India's emerging IT hub, with campuses of large IT-services companies in Infocity and Info Valley. Freshers make up a big share of hiring, so fundamentals and aptitude rounds matter most.
Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Bhubaneswar.
Solved for BhubaneswarStart 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.
When a pivot is created, Excel stores a snapshot of the source data called the pivot cache, and the pivot reads from it, not directly from the sheet. That is why changes need a refresh. Pivots built from the same Table share one cache, which keeps file size down and lets slicers connect across them. Creating pivots from different copies of the data creates separate caches and makes the file heavier.
02
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.
Structured references use Table and column names instead of cell addresses, which makes formulas readable and self-adjusting. For a Table named Sales:
=SUM(Sales[Amount]) totals the whole column
=[@Qty]*[@Price] multiplies values in the same row
=COUNTIFS(Sales[Region],"North")
If rows are added or a column moves, the formula still points to the right data.
04
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.
05
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.
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.
Ctrl+Arrow jumps to the edge of data and Ctrl+Shift+Arrow selects to it. Ctrl+T creates a Table, Ctrl+Shift+L toggles filters, Ctrl+1 opens Format Cells, Alt+= inserts AutoSum, F4 toggles absolute references or repeats the last action, F2 edits a cell, Ctrl+; inserts today's date, Ctrl+Alt+V opens Paste Special and Ctrl+E runs Flash Fill. Mentioning a few you genuinely use is better than reciting a long list.
08
Tell me about a time data from two teams did not match. How did you resolve it?
Situation: finance and sales reported different revenue for the same quarter. Task: find the gap before the review meeting. Action: I pulled both datasets, built a key on invoice number, and used lookups to list invoices present in only one file. Most of the gap came from credit notes that sales did not deduct and invoices booked on the last day. Result: both teams agreed a single definition and I added a reconciliation tab to the monthly pack.
09
Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?
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.
10
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.
11
Write a custom number format that shows positives in green, negatives in red brackets and zero as a dash.
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.
12
What is the difference between nested IF and IFS? Show an example.
Nested IF places one IF inside another, which becomes hard to read after three levels. IFS (Excel 2019 and later) tests conditions in order and returns the value for the first true one:
=IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Fail")
The final TRUE acts as the else part. For many bands, a lookup table with XLOOKUP approximate match is easier to maintain than either.
13
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.
EDATE moves a date by whole months, keeping the day where possible; EOMONTH returns the last day of a month that many months away.
=EDATE(A2,3) date three months later, useful for EMI or renewal dates
=EOMONTH(A2,0) last day of the same month
=EOMONTH(A2,-1)+1 first day of the same month
These are standard in billing cycles, month-end closing and ageing reports.
15
List the steps to build a sales and margin percentage combo chart.
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.
16
Ten regional heads each need the same dashboard but must see only their own region. How do you deliver it?
Since Excel files cannot restrict data inside a workbook securely, create separate files. Add a Region parameter cell that drives Power Query filters or pivot filters, then loop through regions with a short VBA macro that sets the region, refreshes, removes other data and saves a copy or PDF per region. For regular use at scale, Power BI with row-level security is the better solution.
17
What is the difference between Summarize Values By and Show Values As?
Summarize Values By chooses the calculation, such as Sum, Count, Average, Max or Min. Show Values As changes how that result is presented, for example % of Grand Total, % of Column Total, % of Parent Row Total, Running Total In, Difference From or Rank Largest to Smallest. Adding the same field twice, once as Sum and once as % of Grand Total, shows the amount and the share side by side.
18
Describe a time you found an error in a report just before it was sent.
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.
19
How do you remove duplicate customers but keep the most recent record in Power Query?
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
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.
21
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.
22
How do you build a dependent dropdown, where the City list changes based on the selected State?
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#.
23
An imported report has merged cells, blank spacer rows and subtotal lines, so sorting and filtering keep failing. How do you make it usable?
Work on a copy. Unmerge all cells, then fill the blanks with the value above using Go To Special > Blanks and =cell above, followed by paste as values. Filter out blank rows and subtotal lines and delete them, keep one header row and convert the range to a Table. If the same report arrives every month, repeat these steps once in Power Query so the cleanup becomes a single refresh.
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.
25
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.
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.
26
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.
27
What principles do you follow when designing an Excel dashboard?
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.
28
How do you do a two-way lookup that picks both a row and a column?
Use two MATCH functions inside INDEX, one for the row and one for the column:
=INDEX($B$2:$M$50,MATCH($P$2,$A$2:$A$50,0),MATCH($Q$2,$B$1:$M$1,0))
Here P2 holds the product and Q2 the month. With XLOOKUP you can nest one inside another:
=XLOOKUP(P2,A2:A50,XLOOKUP(Q2,B1:M1,B2:M50))
The inner XLOOKUP returns the whole month column, and the outer one picks the product row.
29
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.
IFERROR replaces any error, such as #N/A, #DIV/0!, #VALUE! or #REF!, with a value you choose. IFNA replaces only #N/A, which is the normal result when a lookup finds no match. IFNA is safer around lookups because a genuine problem like a deleted column (#REF!) still shows up instead of being hidden as a blank or zero. Use IFERROR only where every error truly means the same thing.
31
What is the difference between a calculated column and a measure in DAX?
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.
32
A column shows amounts rounded to whole rupees, but its total differs from a manual total of the displayed values by a few rupees. Why?
Number formatting only changes how a value looks; Excel still adds the full decimals stored in each cell. So the sum of hidden paise differs from the sum of the rounded figures shown. If the report must add up exactly as displayed, round the values with =ROUND(value,0) in the source formulas rather than using the Precision as displayed option, which permanently changes stored data for the whole workbook.
33
Build a live leaderboard of the top 5 sales reps by total sales using dynamic array functions.
Get unique reps, total their sales, sort descending and take the top five:
=LET(r,UNIQUE(Sales[Rep]),
t,SUMIFS(Sales[Amount],Sales[Rep],r),
TAKE(SORTBY(HSTACK(r,t),t,-1),5))
HSTACK puts names and totals side by side, SORTBY orders by totals, and TAKE keeps five rows. Without TAKE, use INDEX(...,SEQUENCE(5),{1,2}).
Switch off screen updates, automatic calculation and events while the macro runs, and always restore them:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' ... main code ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Also avoid Select, process data in arrays instead of cell by cell, and use an error handler so settings are restored on failure.
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.
36
Tell me about a time stakeholder feedback changed your dashboard.
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.
Few big employers listed in Bhubaneswar?
Many Bhubaneswar candidates also apply to teams in Kolkata and to remote or hybrid roles. Practise the Kolkata set as well.
Which companies hire Advanced Excel professionals in Bhubaneswar?
Companies with large offices in Bhubaneswar include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are Advanced Excel interviews in Bhubaneswar different from other cities?
The questions depend more on the company than the city. Bhubaneswar's mix of employers decides the style: Freshers make up a big share of hiring, so fundamentals and aptitude rounds matter most
How do I find Advanced Excel jobs in Bhubaneswar?
Shortlist companies with Bhubaneswar offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Bhubaneswar filter, and practise the questions on this page before each interview.