MIS and Reporting Manager Interview Questions &amp; Answers

Show answer

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.

How do you remove duplicate customers but keep the most recent record in Power Query?

Show answer

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.

Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?

Show answer

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.

How do you handle two senior managers asking for conflicting versions of the same dashboard?

Charts and DashboardsHard

Show answer

Situation: sales wanted revenue by booking date while finance wanted it by invoice date. Task: avoid two dashboards with different numbers. Action: I met both, wrote down each definition and its use, and proposed one dashboard with a clearly labelled toggle and a note explaining the difference. Result: both agreed, the numbers stopped being argued over in meetings, and the definition was added to our data glossary.

Explain row context, filter context and context transition.

Show answer

Filter context is the set of filters applied to a calculation, coming from pivot rows, columns, slicers and CALCULATE. Row context exists when DAX iterates rows, as in a calculated column or SUMX, and lets you refer to the current row's values; it does not filter other tables by itself. Context transition happens when CALCULATE, or a measure reference, is used inside a row context and turns the current row into an equivalent filter context.

How do you send personalised emails from Excel through Outlook using VBA?

Macros and VBA AutomationHard

Show answer

Use late binding so no library reference is needed, and loop through the rows: Sub SendMails() Dim ol As Object, m As Object, r As Long Set ol = CreateObject("Outlook.Application") For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row Set m = ol.CreateItem(0) m.To = Cells(r, 2).Value m.Subject = "Payment reminder" m.Body = "Dear " & Cells(r, 1).Value & ", your due amount is Rs " & Cells(r, 3).Value m.Display Next r End Sub Use .Display to review before switching to .Send.

What principles do you follow when designing an Excel dashboard?

Show answer

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.

Why do you need a separate Date table in the Data Model?

Power Pivot, Data Model and DAXMedium

Show answer

Time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a continuous date column with no gaps, covering full years. A Date table also adds columns like Month Name, Fiscal Year and Quarter for slicing. Create it with Power Query or DAX, for example: Date = CALENDAR(DATE(2024,4,1),DATE(2027,3,31)) Then mark it as the date table in Power Pivot and relate it to every fact table's date column.

What is the difference between RELATED and RELATEDTABLE?

Power Pivot, Data Model and DAXMedium

Show answer

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.

Write a running total measure for cumulative sales across dates.

Show answer

Keep all dates up to the last date in the current filter: Running Sales := CALCULATE([Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date]<=MAX('Date'[Date]))) Place Month or Date from the Date table in the pivot rows. To restart every financial year, use TOTALYTD with the "3/31" year-end instead.

Tell me about a time you automated a repetitive task.

Show answer

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.

Budgets are set by category and month, but sales are recorded by product and day. How do you compare them in one pivot?

Show answer

Do not relate the two fact tables directly. Create shared dimension tables at a grain both can link to: a Category table and a Date table with a Month column. Relate Budget[Category] and Sales via Products to Category, and both date columns to the Date table, using the first day of the month for budgets. Then measures [Total Sales] and [Total Budget] work together when the pivot uses Category and Month.

A measure shows the same value on every row of the pivot. What is wrong?

Show answer

The field in the rows is not filtering the table the measure uses. Usually there is no relationship between the two tables, the relationship is inactive, or it points the wrong way, for example the row field comes from the many side of a different fact table. Excel often shows a warning that relationships may be needed. Check Manage Relationships and use fields from dimension tables linked to the fact table.

Explain relationships, cardinality and filter direction in the Data Model.

Show answer

A relationship links a key column in one table to a matching column in another. Cardinality describes how many rows match: one-to-many is the standard case, where each product appears once in Products but many times in Sales. In Excel's Data Model, filters flow from the one side to the many side, so selecting a category in Products filters Sales, but not the reverse.

Fifteen people edit the same tracker and you keep getting conflicting versions over email. How would you fix the process?

Excel Fundamentals and FormattingHard

Show answer

Move the file to OneDrive or SharePoint so everyone co-authors one live copy, and stop emailing attachments. Restructure it into a single input Table with Data Validation, lock formula columns with sheet protection, and give each person a filtered view or their own sheet view. Use version history for recovery. If edits keep growing, move the data to a SharePoint list or database and keep Excel for reporting.

What are histogram and Pareto charts used for?

Show answer

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.

You need product category in the Sales table to create a calculated column. How do you do it in the Data Model?

Power Pivot, Data Model and DAXMedium

Show answer

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.

What are parameters in Power Query and when do you use them?

Show answer

A parameter is a named value, created in Manage Parameters, that queries can reference instead of a hard-coded value. A common use is a FolderPath parameter so the same workbook works when moved to another computer or shared drive. Parameters can also hold a cut-off date or a region filter. Changing one value updates every query that uses it, which makes files easier to hand over.

How do you build a KPI card showing achievement against target with an up or down indicator?

Show answer

Calculate achievement in a cell, for example =Actual/Target-1, and apply a custom number format that adds arrows and colours: [Color10]▲ 0.0%;[Red]▼ 0.0%;0.0% Place the actual value in a large font above it, link a text box to the cells for flexible placement, and keep labels short like Revenue vs Target. Conditional formatting icon sets are an alternative but are less precise.

What is a star schema and why is it recommended for the Data Model?

Show answer

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.

Your manager wants a fixed-format P&L with rows in a custom order, but the data is in the Data Model and the pivot keeps reshaping itself. What do you do?

Show answer

Use CUBE functions instead of a pivot. Select the pivot and use PivotTable Analyze > OLAP Tools > Convert to Formulas, or write them directly, for example CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]", $A5, B$3). Each cell becomes an independent formula, so you can reorder rows, insert subtotal lines and apply fixed formatting, while numbers still refresh from the model.

A monthly process takes four hours of copy-paste from 30 branch files. How would you automate it?

Show answer

First agree a fixed template with the branches, ideally a named Table. Save all files to one SharePoint or shared folder, then build a From Folder query that combines them, adds the branch name from the file name, standardises types and filters bad rows. Load to the Data Model, build the pivots and dashboard on it, and add a check sheet comparing row counts per branch. Monthly work becomes refresh and review.

How do you automatically highlight the highest and lowest points in a line chart?

Show answer

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.

Tell me about a time you built a macro that others in your team relied on. How did you keep it maintainable?

Macros and VBA AutomationMedium

Show answer

Situation: I wrote a macro that split a master file into branch-wise reports for 30 branches. Task: others had to run it while I was on leave. Action: I added Option Explicit, comments, a settings sheet for paths and branch lists, error handling with clear messages and a short user guide. Result: colleagues ran it for months without my help, and adding new branches needed only a row in the settings sheet.

What causes the Formula.Firewall error in Power Query and how do you resolve it?

Show answer

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.

Write DAX measures for year-to-date sales and sales for the same period last year.

Show answer

With a proper Date table marked as a date table and related to Sales: Sales YTD := TOTALYTD([Total Sales],'Date'[Date],"3/31") Sales LY := CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date])) YoY % := DIVIDE([Total Sales]-[Sales LY],[Sales LY]) The "3/31" year-end argument gives an Indian April to March financial year; without it, YTD resets on 1 January.

When should a team move from an Excel dashboard to Power BI?

Charts and DashboardsHard

Show answer

Excel dashboards work well for small teams, one-off analysis and data up to a few lakh rows. Move to Power BI when data comes from several live sources, needs scheduled refresh, row-level security for different users, mobile access, or a large audience who should not receive files. The Data Model and DAX skills learned in Power Pivot transfer directly, so the move is usually smooth.

Write a month-over-month growth percentage measure.

Show answer

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.

Write VBA to delete rows where column A is blank.

Macros and VBA AutomationMedium

Show answer

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.

A workbook with the Data Model has grown to 300 MB. How do you reduce its size?

Show answer

VertiPaq size depends mostly on columns and how many unique values each has. Remove columns not used in reports, especially IDs and free text. Split date-time columns into date and time or drop the time, round decimals to required precision, and replace calculated columns with measures. Use integer keys instead of long text keys, and load only the years required. Filtering at Power Query level avoids loading unneeded rows.

Tell me about a time you had to explain a DAX number that looked wrong to a manager.

Show answer

Situation: a manager said our margin percentage in the Data Model pivot did not match his calculator. Task: show whether the measure was right. Action: I took one region, showed that he was averaging row percentages while the measure divided total margin by total sales, and walked through both in a small table. Result: he agreed the measure was correct, and I added a note to the report explaining how the KPI is calculated.

Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?

Show answer

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.

Write a DAX measure for each product's percentage of total sales.

Show answer

Remove the product filter in the denominator so it always returns the overall total: % of All Products := DIVIDE([Total Sales], CALCULATE([Total Sales],ALL(Products))) Use ALLSELECTED(Products) instead of ALL if the percentage should respect slicer selections, so the visible products add up to 100 percent. Other filters such as region or month still apply in both versions.

What is the difference between SUM and SUMX?

Show answer

SUM adds a single column. SUMX iterates a table, evaluates an expression for each row and adds the results: Revenue := SUMX(Sales,Sales[Qty]*Sales[Price]) This avoids storing a Qty times Price calculated column. Other X functions such as AVERAGEX, MAXX and COUNTX work the same way. Iterating a very large table with complex expressions can be slower, so keep the expression simple.

How do you create and use a custom function in Power Query?

Show answer

Write a query that takes a parameter, then call it for each row or file: (filePath as text) as table => let Src = Excel.Workbook(File.Contents(filePath)), Data = Src{[Item="Sales",Kind="Table"]}[Data] in Data Save it as fnGetSales, then Add Column > Invoke Custom Function on a table of file paths and expand the result. This gives more control than the automatic Combine Files helper.

What is query folding in Power Query?

Show answer

Query folding means Power Query translates your steps into the source's own query language, such as SQL, so filtering and grouping happen inside the database and only the result is sent to Excel. This is much faster for large sources. Steps like adding index columns or some custom M functions can break folding. Right-click a step and check View Native Query to see whether it still folds.

Your weekly dashboard must go to 40 people who cannot access the source files. How do you distribute it safely?

Show answer

Decide what they need: a view or a working file. For a view, export the dashboard sheet to PDF or share a view-only link from SharePoint or OneDrive. For a file, save a copy, break external links, paste pivots or formulas as values if needed, remove hidden sheets and personal data with Inspect Document, and protect the sheet. Automate the steps with a macro or Office Script so every week is identical.

How is the Scripting.Dictionary object used in VBA?

Macros and VBA AutomationHard

Show answer

A Dictionary stores key-value pairs with fast lookups, ideal for unique lists and grouping: Dim d As Object, i As Long, v As Variant Set d = CreateObject("Scripting.Dictionary") v = Range("A2:B10001").Value For i = 1 To UBound(v) d(v(i, 1)) = d(v(i, 1)) + v(i, 2) Next i Range("D2").Resize(d.Count, 1).Value = Application.Transpose(d.Keys) This totals amounts per customer far faster than repeated SUMIF calls.

Ten regional heads each need the same dashboard but must see only their own region. How do you deliver it?