36 practice questions with answers for Advanced Excel interviews in Indore.
Advanced Excel jobs in Indore
Indore is one of central India's fastest-growing IT hubs, with IT-services campuses and product start-ups in its Super Corridor and IT parks. Companies here hire many freshers and value hands-on project work.
Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Indore.
Solved for IndoreStart 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
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.
02
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.
03
Count orders from the North region with amount above 50,000 placed in January 2026.
Use COUNTIFS with one pair of range and criteria per condition, with dates built using DATE so the formula does not depend on regional settings:
=COUNTIFS(Orders[Region],"North",
Orders[Amount],">50000",
Orders[Date],">="&DATE(2026,1,1),
Orders[Date],"<"&DATE(2026,2,1))
Using less than 1 February is safer than less than or equal to 31 January when dates contain time values.
04
Why do you want a role that uses Excel heavily, such as MIS or reporting?
I enjoy turning raw numbers into reports that people actually use to decide things. During my studies and projects I built pivot dashboards and lookup-based trackers, and I liked seeing a three-hour manual task become a ten-minute refresh. An MIS role lets me build those skills daily, understand how the business runs, and grow towards analytics tools like Power BI and SQL, which build directly on Excel.
05
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.
06
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.
07
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.
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.
Pie charts become unreadable with more than five or six slices, because people are poor at comparing angles. They cannot show change over time, and 3D or exploded pies distort sizes. Slices should add up to a meaningful whole; percentages from different bases should never be in one pie. A sorted bar chart almost always communicates the same information more clearly.
10
What are dynamic arrays and what causes a #SPILL! error?
In Microsoft 365, a formula can return many values that spill into nearby cells, as with UNIQUE, FILTER, SORT and SEQUENCE. Other formulas can refer to the whole result with the spill operator, for example =COUNTA(E2#). #SPILL! appears when the spill area is blocked by data, the formula sits inside a Table, the area overlaps merged cells, or the result would run past the sheet edge. Clear the blocking cells to fix it.
11
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.
12
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.
13
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.
14
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.
15
What causes the Formula.Firewall error in Power Query and how do you resolve it?
The error appears when a query combines data from sources with different privacy levels, for example using a value from an Excel sheet to filter a web or database query, because Power Query prevents data from one source leaking to another. Fix it by setting correct privacy levels in Data Source Settings, restructuring so the combination happens in separate staging queries, or, for trusted personal files, choosing to ignore privacy levels.
16
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?
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.
17
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.
18
VLOOKUP returns #N/A even though you can see the value in the table. What do you check?
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.
19
How do you return all matching values for a lookup, not only the first?
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],""))
20
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.
21
What checks do you run when cleaning a new dataset?
Check row counts against the source, then look for duplicate keys, blanks in mandatory fields, inconsistent text such as Delhi and New Delhi, extra spaces and mixed case, dates stored as text or in the wrong locale, negative or extreme numbers that look like typos, and codes losing leading zeros. Power Query's Column Quality and Column Profile views show errors, empties and distinct counts for each column quickly.
22
Explain the join kinds available in Power Query Merge.
Left Outer keeps all rows from the first table and matches from the second, the most common choice. Right Outer does the reverse. Full Outer keeps all rows from both. Inner keeps only matching rows. Left Anti keeps rows in the first table with no match, and Right Anti keeps rows in the second with no match. Anti joins are excellent for finding missing invoices or customers during reconciliation.
23
What is conditional formatting? Give two business uses.
Conditional formatting changes a cell's colour, font, icon or data bar based on a rule, so patterns stand out without reading every number. Common uses are highlighting invoices overdue by more than 30 days in red and adding data bars to a sales column so top performers are visible at a glance. Keep rules few and consistent, because many overlapping rules slow down large files.
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.
25
Write VBA to consolidate the first sheet of every Excel file in a folder into one sheet.
Loop with Dir, open each file, copy its data below the last row and close it:
Sub Consolidate()
Dim f As String, wb As Workbook, dest As Worksheet, r As Long
Const P As String = "C:\Reports\"
Set dest = ThisWorkbook.Worksheets("All")
f = Dir(P & "*.xlsx")
Do While f <> ""
Set wb = Workbooks.Open(P & f, ReadOnly:=True)
r = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row + 1
wb.Worksheets(1).UsedRange.Offset(1).Copy dest.Cells(r, 1)
wb.Close False
f = Dir
Loop
End Sub
26
A shared Excel file has grown to 40 MB and takes ages to recalculate. What do you check?
Start with Ctrl+End to see if the used range is far beyond the real data and delete the extra rows and formats. Then look for volatile functions like OFFSET, INDIRECT and TODAY, whole-column references inside SUMPRODUCT or array formulas, and duplicated conditional formatting rules. Replace repeated lookups with one helper column, move heavy transforms to Power Query, and save as .xlsb if size still matters.
27
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.
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.
29
A manager wants a 50-row branch report where every row also shows a small trend of the last 12 months. What do you use?
Use sparklines: select the 12 monthly values for all rows, choose Insert > Sparklines > Line and set the location to a column next to the data. Turn on high and low point markers, and under Axis choose the same minimum and maximum for all sparklines if branches should be compared fairly. They fit inside a cell, print well and update when the data changes.
The Data Model is an in-memory database inside the workbook, powered by the VertiPaq engine, which compresses data by column and can handle millions of rows, far beyond the sheet limit. Power Pivot is the window used to manage it: import tables, create relationships, and write DAX measures and calculated columns. Pivot tables built on the Data Model can combine several related tables without VLOOKUP.
31
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.
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.
33
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.
34
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.
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.
36
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.
Few big employers listed in Indore?
Many Indore candidates also apply to teams in Pune and to remote or hybrid roles. Practise the Pune set as well.
Which companies hire Advanced Excel professionals in Indore?
Companies with large offices in Indore include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are Advanced Excel interviews in Indore different from other cities?
The questions depend more on the company than the city. Indore's mix of employers decides the style: Companies here hire many freshers and value hands-on project work
How do I find Advanced Excel jobs in Indore?
Shortlist companies with Indore offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Indore filter, and practise the questions on this page before each interview.