40 questions for MIS Executive interviews — 24 easy, 15 medium, 1 hard.
About the MIS Executive role
An MIS Executive prepares daily, weekly and monthly reports for sales, operations or HR teams. The work involves pulling data from ERP or CRM exports, cleaning it, building pivot summaries and dashboards, and sending accurate reports on fixed deadlines. It is one of the most common fresher entry points into data work in India, especially in BPOs, KPOs, banks, retail and manufacturing companies.
Pivot tables XLOOKUP and SUMIFS Data cleaning Report formatting Dashboard basics
Solved for this roleStart 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
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.
02
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.
03
You change input values but the totals do not update until you press something. What is happening and how do you fix it?
The workbook is probably in Manual calculation mode, often inherited from another file opened first in the same session. Press F9 to recalculate now, then set Formulas > Calculation Options > Automatic. If only some cells stay stale, check whether they are formatted as Text or whether Show Formulas is on. For very heavy files, manual mode can be kept deliberately, but tell users to press F9 before reading results.
04
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.
05
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.
06
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.
07
What is a combo chart with a secondary axis and when is it useful?
A combo chart shows two series with different chart types, for example monthly sales as columns and margin percentage as a line. The percentage would be invisible on the same scale as crores of sales, so it is plotted on a secondary vertical axis on the right. Label both axes clearly, because readers can misread which series belongs to which scale.
08
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.
09
Tell me about a time you had to clean up a messy spreadsheet someone else built.
Situation: I inherited a sales tracker with merged cells, hard-coded totals and three different date formats. Task: my manager needed reliable weekly numbers. Action: I kept a backup copy, converted the data into an Excel Table, fixed dates with Text to Columns, replaced typed totals with SUMIFS and added data validation dropdowns. Result: weekly reporting time fell from two hours to twenty minutes and the totals stopped being questioned.
10
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.
11
Walk through creating a pivot showing monthly sales by region.
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.
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.
13
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.
14
Dates in an exported column are left-aligned and formulas like DATEDIF return #VALUE!. How do you fix them?
Left alignment usually means the dates are stored as text. Select the column, use Data > Text to Columns, choose Date with the correct order such as DMY and finish; Excel converts them to real dates. For mixed or messy text, DATEVALUE or DATE with LEFT, MID and RIGHT works. If the file comes regularly, set the column type in Power Query using Using Locale so day and month are never swapped.
15
How do you choose the right chart type for business data?
Match the chart to the question. Use column or bar charts to compare categories, line charts for trends over time, pie or doughnut only for a share of a whole with few slices, scatter charts for the relationship between two numbers, histograms for distribution, and combo charts when two measures with different scales belong together, such as sales and margin percentage. The simplest chart that answers the question is usually the best.
16
Describe a report you built with pivot tables that helped a decision.
Situation: our store team felt discounts were not driving sales. Task: check it with data. Action: I built a pivot from six months of bills with discount band in rows, month in columns, and sales and margin as values, plus a slicer for store. Result: the pivot showed discounts above 20 percent added little volume but cut margin sharply, and the manager capped discounts at 15 percent the following month.
17
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.
Rows lists categories down the left side, such as Region. Columns spreads categories across the top, such as Month. Values holds the numbers being summarised, such as Sum of Sales or Count of Orders. Filters applies a filter to the whole pivot, such as Financial Year. Moving a field from Rows to Columns changes the layout instantly, which is why pivots are quick for exploring data.
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 Data Validation and how do you create a dropdown list?
Data Validation restricts what users can type in a cell, which prevents bad input in forms and trackers. For a dropdown, select the cells, go to Data > Data Validation, choose Allow: List and set Source to a range such as =$K$2:$K$10 or a typed list like Open,Closed,On Hold. Pointing the source at a Table column keeps the list updated automatically when new options are added.
21
What are slicers and timelines, and how do you connect one slicer to several pivots?
Slicers are clickable filter buttons for a field, and timelines are slider filters for dates by day, month, quarter or year. They make filtering obvious for dashboard users. To control several pivots, right-click the slicer and choose Report Connections, then tick each pivot. This works only for pivots built on the same source or data model, because they share the same pivot cache.
22
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.
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.
24
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.
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.
26
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.
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.
28
You need serial numbers in a list that stay continuous after rows are filtered or deleted. How do you do it?
Plain typed numbers or =ROW()-1 break when rows are hidden by a filter. Use SUBTOTAL, which ignores filtered-out rows:
=SUBTOTAL(103, $B$2:B2)
Function 103 is COUNTA that skips hidden rows, and the expanding range counts non-blank cells up to the current row. Now the numbering restarts from 1 for whatever is visible, which is useful in printed or shared filtered views.
29
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.
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.
31
SUM returns 0 on a column of numbers exported from software. How do you fix it?
The numbers are most likely stored as text, often with leading apostrophes, trailing spaces or non-breaking spaces (CHAR(160)). Check with =ISTEXT(A2) or LEN. Quick fixes are Data > Text to Columns > Finish, the green error triangle > Convert to Number, or Paste Special > Multiply by 1. For stubborn cases use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) or set the column type to Decimal in Power Query.
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.
33
You are given raw sales data and asked to build an interactive dashboard. Walk through your approach.
Clarify the audience, KPIs and refresh frequency first. Load and clean the data through Power Query into a Table or the Data Model. Build pivots for each view on a calculation sheet, create pivot charts, and connect slicers for region, product and month to all pivots. Add KPI cards using GETPIVOTDATA, format consistently, protect the layout and test that Refresh All updates everything when new data is added.
34
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.
35
You added new rows of data but the pivot does not show them. Why, and how do you prevent it?
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.
36
How do you stop a pivot from losing formatting and column widths on refresh?
Open PivotTable Options > Layout & Format, untick Autofit column widths on update and tick Preserve cell formatting on update. Set number formats through Value Field Settings > Number Format rather than formatting cells directly, because field-level formats survive refresh and layout changes. For a consistent look across reports, create a custom PivotTable Style and apply it to every pivot.
37
The pivot shows Count of Sales instead of Sum of Sales when you drag the field. Why?
Excel defaults to Count when a value column contains blanks or text, such as NA or a number stored as text. Change it through Value Field Settings > Sum, but also fix the source: replace text placeholders with zero or blank, convert text numbers to real numbers, and refresh. Otherwise the text entries are silently left out of the sum and totals will be understated.
38
What is the difference between relative, absolute and mixed cell references?
A relative reference like A1 shifts when a formula is copied, an absolute reference like $A$1 never shifts, and a mixed reference like $A1 or A$1 locks only the column or only the row. In a multiplication grid, =$A2*B$1 copied across and down keeps the header column and header row fixed. Press F4 while editing a reference to cycle through the four forms.
XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode) is available in Microsoft 365 and Excel 2021 or later:
=XLOOKUP(A2,Emp[ID],Emp[Name],"Not found")
It defaults to exact match, looks left or right, has a built-in not-found value, can return several columns at once, and can search from the bottom up. It replaces VLOOKUP, HLOOKUP and most INDEX/MATCH uses.
40
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.
Frequently asked questions
What does a MIS Executive do?
An MIS Executive prepares daily, weekly and monthly reports for sales, operations or HR teams. The work involves pulling data from ERP or CRM exports, cleaning it, building pivot summaries and dashboards, and sending accurate reports on fixed deadlines. It is one of the most common fresher entry points into data work in India, especially in BPOs, KPOs, banks, retail and manufacturing companies.
Which skills are tested in a MIS Executive interview?
Expect questions on Pivot tables, XLOOKUP and SUMIFS, Data cleaning, Report formatting, Dashboard basics. Most rounds also ask you to explain a project and solve one practical problem live.
How do I prepare for a MIS Executive interview?
Revise Excel Fundamentals and Formatting, Lookup and Dynamic Array Functions, Pivot Tables and Data Summaries, Charts and Dashboards first using the topic sheets, practise the questions on this page aloud, then do one full mock interview to practise timing.