01
What is Power Query and why is it useful?
Show answer
An ETL tool inside Excel for importing, cleaning and shaping data with recorded steps that refresh automatically when the source changes.
Reporting Analyst / MIS Analyst Interview Questions & Answers
40 questions for Reporting Analyst / MIS Analyst interviews — 28 easy, 12 medium.
About the Reporting Analyst / MIS Analyst role
A reporting or MIS analyst produces accurate recurring reports, daily, weekly and monthly, for operations, sales and finance teams, and automates them wherever possible. The work is Excel-heavy with Power Query, PivotTables and increasingly Power BI or Tableau. Interviews test advanced Excel formulas, data cleaning, reconciliation of numbers, KPI definitions and how you meet tight daily and month-end deadlines without errors.
Advanced Excel formulas Power Query automation Report reconciliation KPI tracking Deadline management
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
02
What is data cleaning and why does it take so much of an analyst's time?
Show answer
Data cleaning means fixing or removing incorrect, incomplete, duplicated or inconsistent data so analysis is trustworthy. Real data comes from many systems and manual entry, so you meet typos, mixed formats, missing values and duplicates. Analysts often spend half or more of a project on it because every later chart and model depends on it. Wrong inputs give confident but wrong answers, which is worse than no answer.
03
A KPI on the leadership dashboard jumps 35% overnight. Before anyone celebrates, what do you check?
Show answer
First check the data: did the refresh load duplicate rows, did a filter or definition change, did late data from the previous day arrive, or did a new source or currency get added? Compare the raw count with the source system. Then check business reasons, such as a big promotion, a bulk order or a holiday. Only after the number is confirmed should it be communicated, with the reason attached.
04
How do you show actual versus target on a dashboard?
Show answer
Use a KPI card with actual, target and variance as both value and percentage, coloured by status. For trends, a bar or line for actual with a target line or band works well. Bullet charts compare actual against target and ranges compactly in tables. Make the period clear, such as month to date versus prorated monthly target, because comparing half a month of sales to a full month target looks falsely bad.
05
What is a pivot table and when do you use it?
Show answer
A pivot table summarises large data by dragging fields into rows, columns, values and filters — for example total sales by region and month — without writing formulas.
06
What are bookmarks and drill-through in Power BI?
Show answer
Bookmarks save a report view state for navigation; drill-through jumps to a detail page filtered to the item you picked.
07
How do you split a full name column into first and last name in Excel?
Show answer
-- A2 = 'Priya Ramesh Iyer'
-- first name
=TEXTBEFORE(TRIM(A2), " ")
-- last name (text after the last space)
=TEXTAFTER(TRIM(A2), " ", -1)
-- older Excel: first name
=LEFT(TRIM(A2), FIND(" ", TRIM(A2)&" ")-1)
-- or use Data > Text to Columns or Flash Fill (Ctrl+E) for a one-off task
08
SUMIFS vs COUNTIFS?
Show answer
SUMIFS adds values that meet several conditions; COUNTIFS counts rows that meet several conditions.
Example: =SUMIFS(Sales, Region, "North", Month, "Jan")
09
Tell me about a time you were at risk of missing a reporting deadline.
Show answer
Situation: a month-end report was due at 10 am, and at 8 am the source data had not loaded. Task: deliver something reliable. Action: I informed my manager immediately, checked with the data team for an estimate, and prepared the report on the previous day's data, clearly labelled. Result: leadership got the report on time with a caveat, and I sent the final version by noon. We later added a data arrival alert.
10
What do the match_mode and search_mode arguments of XLOOKUP control?
Show answer
match_mode decides how a value is matched: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, and 2 wildcard. search_mode decides direction: 1 searches first to last, -1 searches last to first, and 2 or -2 use binary search on sorted data. Searching last to first is a quick way to fetch the latest entry for a customer without sorting, and next-smaller matching handles tax slabs or commission bands.
11
What are named ranges in Excel and why are they useful?
Show answer
A named range gives a cell or range a meaningful name, such as GST_Rate or SalesData, managed from the Name Manager. Formulas become readable, for example =Amount*GST_Rate instead of =B2*$H$1, and they do not break when you move the input cell. Names also work as data validation sources and in charts. For growing data, prefer Excel Tables, whose structured references expand automatically.
12
How do you unpivot a wide monthly sales sheet into a long table in Power Query?
Show answer
Load the table into Power Query. Select the identifier columns such as Region and Product, then choose Transform, Unpivot Other Columns. The month columns become two columns, Attribute and Value; rename them Month and Sales. Change Month to a date type and Sales to a decimal number. Using Unpivot Other Columns rather than Unpivot Columns means new months added later are picked up automatically on refresh.
13
What KPIs would you track for a customer support call centre?
Show answer
Volume and staffing: calls offered, answered, abandoned rate and average speed of answer. Efficiency: average handle time and occupancy. Quality and outcome: first contact resolution, customer satisfaction or CSAT score, and repeat contacts within 7 days. Service level, for example 80 percent of calls answered in 20 seconds, ties them together. Balance speed metrics with quality, otherwise agents rush calls to cut handle time.
14
How would you find customers who never placed an order?
Show answer
LEFT JOIN customers to orders and keep rows where order_id IS NULL, or use NOT EXISTS, which also handles NULLs safely.
15
What is the difference between COUNT, COUNTA and COUNTIF?
Show answer
COUNT counts numbers, COUNTA counts non-empty cells and COUNTIF counts cells meeting one condition; COUNTIFS handles several conditions.
16
What are slicers and filters?
Show answer
Slicers are on-page visual filters users click; filters can be set at visual, page or report level and may be hidden from users.
17
Explain the types of SQL joins.
Show answer
INNER returns matching rows, LEFT keeps all left rows, RIGHT keeps all right rows, FULL keeps all rows from both, and CROSS returns every combination.
18
When would you use a one-tailed test instead of a two-tailed test?
Show answer
A two-tailed test checks for a difference in either direction and is the safer default for A/B tests. A one-tailed test checks only one direction, such as treatment being better, and needs fewer users. Use it only if you decided beforehand that you would act the same way whether the change has no effect or a negative effect. Otherwise you may miss harm, which is why most teams stay two-tailed.
19
Numbers in an Excel column are stored as text and SUM returns zero. How do you fix it?
Show answer
Signs are left-aligned numbers, green triangles, and COUNT returning less than COUNTA. Fix options: select the column and use the warning menu Convert to Number; use Data, Text to Columns and Finish; or a helper column with =VALUE(TRIM(A2)). If there are hidden characters, use =VALUE(SUBSTITUTE(TRIM(A2), CHAR(160), "")) to remove non-breaking spaces from web exports. In Power Query, simply change the column type to number.
20
Your FILTER formula shows #SPILL! instead of results. Why, and how do you fix it?
Show answer
Dynamic array formulas such as FILTER, UNIQUE and SORT return several values that spill into neighbouring cells. #SPILL! appears when that spill range is blocked by existing data, merged cells, or when the formula sits inside an Excel Table, which does not allow spilling. Fix it by clearing the blocking cells, unmerging, or moving the formula outside the Table. Select the error cell to see the dashed border of the range it needs.
21
Write Excel formulas to get a sorted list of unique customer names and their count.
Show answer
-- unique customers from column B, sorted A to Z
=SORT(UNIQUE(B2:B1000))
-- number of unique customers
=COUNTA(UNIQUE(B2:B1000))
-- unique customers sorted by total sales, highest first
=SORTBY(UNIQUE(B2:B1000), SUMIFS(D2:D1000, B2:B1000, UNIQUE(B2:B1000)), -1)
22
Which KPIs would you include in an HR attrition dashboard?
Show answer
Core KPIs: headcount, monthly and annualised attrition rate, voluntary versus involuntary split, and early attrition within the first 90 days or first year. Breakdowns by department, location, manager, tenure band, performance rating and salary band show where it concentrates. Add hiring metrics such as time to fill and offer acceptance rate, and exit reasons from exit interviews. Protect privacy by hiding very small groups.
23
What is the difference between a metric and a KPI?
Show answer
A metric is any quantity you measure, such as page views, tickets closed or average order value. A KPI, key performance indicator, is a metric chosen because it directly reflects progress towards an important business goal and usually has a target and owner. Every KPI is a metric, but most metrics are not KPIs. A good dashboard leads with a few KPIs and keeps supporting metrics for diagnosis.
24
How do you remove duplicates in Excel?
Show answer
Data tab, Remove Duplicates, pick the columns to compare. For a non-destructive way use UNIQUE() or conditional formatting to highlight repeats first.
25
What are field parameters in Power BI?
Show answer
Field parameters let report users switch which measure or dimension a visual uses through a slicer. For example, one chart can switch between Sales, Profit and Quantity, or between grouping by Region, Category and Channel. This replaces many duplicate visuals or bookmark tricks, keeping the report lighter and easier to maintain. They are created from Modeling, New parameter, Fields, which generates a small table behind the slicer.
26
What is data validation in Excel?
Show answer
Data → Data Validation restricts what can be entered in a cell (lists, number ranges, dates), reducing manual entry errors in shared sheets.
27
What are vanity metrics?
Show answer
Vanity metrics look impressive but do not help decisions or reflect real value, such as total app downloads, total registered users or social media likes. They usually only go up. Better alternatives are actionable metrics such as active users, retention by cohort, conversion rate and revenue per user. A quick test: if the number changed, would anyone do anything differently? If not, it probably does not belong on the main dashboard.
28
Write SQL to calculate sessions, orders and conversion rate by traffic source.
Show answer
SELECT
s.traffic_source,
COUNT(*) AS sessions,
COUNT(o.order_id) AS orders,
ROUND(100.0 * COUNT(o.order_id) / COUNT(*), 2) AS conversion_pct
FROM sessions s
LEFT JOIN orders o
ON o.session_id = s.session_id
GROUP BY s.traffic_source
ORDER BY sessions DESC;
-- assumes at most one order per session; otherwise use COUNT(DISTINCT o.session_id)
29
What is tidy data?
Show answer
Tidy data follows three rules: each variable is a column, each observation is a row, and each type of observational unit is a table. A sheet with months as columns is not tidy; unpivoting it to Region, Month and Sales columns makes it tidy. Tidy data is easy to filter, group, join and chart in pandas, SQL, Power BI and Tableau, so reshaping into this form is a core cleaning step.
30
What is conditional formatting?
Show answer
It automatically formats cells based on rules such as colour scales, data bars or highlighting values above a target, making patterns visible at a glance.
31
How does the FILTER function work in Excel? Write a formula to list all North region orders.
Show answer
-- A2:D500 holds Order, Customer, Region, Amount
=FILTER(A2:D500, C2:C500="North", "No orders")
-- two conditions (AND) multiply the tests
=FILTER(A2:D500, (C2:C500="North")*(D2:D500>10000), "No orders")
-- OR conditions add the tests
=FILTER(A2:D500, (C2:C500="North")+(C2:C500="East"))
32
What is Power BI and its main components?
Show answer
A Microsoft BI tool made of Power BI Desktop for building reports, the Service for sharing, and Mobile; it connects, models and visualises data.
33
What is the difference between a calculated table in DAX and a table created in Power Query?
Show answer
A Power Query table is built during data refresh using M, before data enters the model, and is compressed like any imported table. A DAX calculated table is computed after load from other model tables, using functions such as CALENDAR, SUMMARIZE or DISTINCT. Prefer Power Query for shaping and cleaning source data. Use calculated tables for model-dependent needs, such as a date table from existing data or a small what-if table.
34
Pin codes in your CSV lost their leading zeros after opening in Excel. Why and how do you prevent it?
Show answer
Excel guessed the column was a number and dropped leading zeros; the same happens with long IDs turning into scientific notation. Identifiers that are not used in arithmetic should be stored as text. Import through Data, From Text/CSV or Power Query and set the column type to Text, or read it in pandas with dtype={'pincode': str}. To repair, use =TEXT(A2, "000000") when the original length is known.
35
What is data profiling?
Show answer
Data profiling is a quick systematic look at a dataset before analysis: row counts, data types, null percentages, distinct counts, minimum and maximum, common values and value patterns per column. In pandas, df.info(), df.describe(include='all') and value_counts() cover most of it; Power Query has column quality and distribution views. Profiling reveals problems like a date column stored as text or a status field with unexpected values early.
36
Users say a Power BI report still shows yesterday morning's data, although a refresh is scheduled every few hours. What do you check?
Show answer
Open the semantic model's refresh history in the Service to see whether refreshes failed, for example due to expired credentials, an offline gateway or a timeout. Check whether the report is connected to a different copy of the model, whether the source itself was updated, and whether incremental refresh only covers some partitions. Also confirm the schedule time zone. Finally, add a 'data as of' timestamp on the report.
37
What are strategic, operational and analytical dashboards?
Show answer
Strategic dashboards serve leadership with a few high-level KPIs and trends, updated daily to monthly. Operational dashboards monitor live or near-live activity for teams on the ground, such as orders pending dispatch or call queue length, often with alerts. Analytical dashboards let analysts and managers explore data with filters and drill-downs to find causes. Knowing which type you are building decides the level of detail, refresh rate and design.
38
A file has dates in mixed formats, some dd/mm/yyyy and some mm/dd/yyyy. How do you handle it?
Show answer
Find out which system or region produced each format, since a source column often explains it. Values with a day above 12 reveal the format unambiguously. Parse with an explicit format per source, for example pd.to_datetime(col, format='%d/%m/%Y', errors='coerce'), rather than letting the tool guess. Check failed or suspicious parses, such as future dates, and ask the source team to send ISO format yyyy-mm-dd going forward.
39
An inner join between orders and customers returns fewer orders than the orders table has. What could be wrong?
Show answer
Orphan records are child rows whose foreign key has no matching parent, such as orders with a customer_id that does not exist in the customer table. They break joins silently: an inner join drops them and totals fall short. Find them with a LEFT JOIN from the child to the parent and filter where the parent key IS NULL. Then fix the source, add an Unknown member, or report them separately.
40
How should you lay out a dashboard page?
Show answer
Readers scan from the top-left, so place the most important KPIs as cards across the top, then trend charts, then breakdowns and detail tables at the bottom. Group related visuals, align them on a grid and keep consistent spacing, fonts and colours. Put filters in one consistent place. Limit a page to roughly five to eight visuals that answer related questions, and give the page a clear title stating its purpose.
Frequently asked questions
What does a Reporting Analyst / MIS Analyst do?
A reporting or MIS analyst produces accurate recurring reports, daily, weekly and monthly, for operations, sales and finance teams, and automates them wherever possible. The work is Excel-heavy with Power Query, PivotTables and increasingly Power BI or Tableau. Interviews test advanced Excel formulas, data cleaning, reconciliation of numbers, KPI definitions and how you meet tight daily and month-end deadlines without errors.
Which skills are tested in a Reporting Analyst / MIS Analyst interview?
Expect questions on Advanced Excel formulas, Power Query automation, Report reconciliation, KPI tracking, Deadline management. Most rounds also ask you to explain a project and solve one practical problem live.
How do I prepare for a Reporting Analyst / MIS Analyst interview?
Revise Excel, Dashboards and KPIs, Data Cleaning, Power BI first using the topic sheets, practise the questions on this page aloud, then do one full mock interview to practise timing.
Topic sheets for this role
Other roles
More ways to practise
Company-wise Data Analytics questionsPackage-wise Data Analytics questionsExperience-wise Data Analytics questionsRole-wise Data Analytics questionsCity-wise Data Analytics questionsMost asked Data Analytics questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsData Analytics mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsData Analytics topic sheets