MIS Executive Interview Questions

The core set includes VLOOKUP/XLOOKUP and INDEX/MATCH for merging data, SUMIFS/COUNTIFS/AVERAGEIFS for conditional summaries, IF/IFS and IFERROR for logic, TEXT and date functions for formatting, and PivotTables for fast summaries.

A dashboard is a single screen that shows the most important metrics (KPIs) using charts, cards and tables. It lets management see the health of the business at a glance instead of reading long tables, which speeds up decisions.

A KPI (Key Performance Indicator) is a measurable value that shows how well a goal is being met. Examples: monthly sales, order fulfilment rate, average handling time, attrition %, on-time delivery %, and collection efficiency.

A report is a detailed, often tabular document for a specific period or purpose. A dashboard is a visual, high-level summary of key metrics on one screen, usually interactive and refreshed regularly. Reports go deep; dashboards give the big picture fast.

VLOOKUP finds a value in the first column of a table and returns a value from a column to the right. It falls short because it can only look to the right, breaks when columns are inserted, and defaults to approximate match. XLOOKUP or INDEX/MATCH solve these issues.

Use Power Query to import and transform the source files so the steps replay on refresh; build summaries with PivotTables/formulas linked to that query; and refresh with one click (or schedule it). For repetitive formatting or exports, a short VBA macro can finish the job. The aim is a report you refresh, not rebuild.

Standardise in Power Query: set consistent column names and data types, trim/clean text, split or merge columns, and then append (stack similar tables) or merge (join on a key). This produces one clean table that every report can rely on.

Use Data Validation for inputs, wrap lookups in IFERROR, add check cells (e.g. totals that must reconcile), highlight exceptions with conditional formatting, and lock formula cells via Protect Sheet so users only edit inputs.

A static report is a snapshot that must be rebuilt for new data. A dynamic report updates automatically when the source changes — achieved with Tables, Power Query, PivotTables and dynamic ranges — so it scales and saves time.

Base the chart on an Excel Table (Ctrl+T) so new rows are included automatically, or use a PivotChart linked to a PivotTable. In Excel 365 you can also chart a spilled dynamic array and reference it with the # operator so the chart grows with the data.

Keep a target column alongside actuals, compute variance (=Actual-Target) and variance % (=(Actual-Target)/Target), and highlight shortfalls with conditional formatting. A combo chart (bars for actuals, a line for target) communicates it clearly to management.

Separate the workbook into layers: a raw data sheet/query (never edited by hand), a processing layer (Power Query + helper tables), and a presentation layer (dashboard with PivotTables/charts). Use a Data Model with relationships for large data, parameterise date ranges, document assumptions, and keep one refresh button. This makes the report maintainable and auditable.

Bring both into Power Query, align keys and types, then do a full outer merge and expand to compare. Rows that appear on only one side, or where amounts differ, are your exceptions. Alternatively, use SUMIFS on both sources against a common key and flag non-zero differences. Always show the reconciliation total so reviewers trust it.

Avoid volatile functions (OFFSET, INDIRECT, NOW), replace array-heavy formulas with helper columns or the Data Model, limit ranges instead of whole columns, remove unused formatting/names, and push heavy transformation into Power Query. Set calculation to Manual while editing.

Lead with the KPI and its trend vs target, use variance (actual vs plan vs last period), highlight the top drivers, and add a one-line commentary explaining the "why" and the recommended action. Keep charts simple and consistent. Management wants decisions, not raw tables.

Use Power Query, which is resilient to many changes, and reference columns by name. Add a validation step that checks expected columns exist and errors loudly if not. Avoid hard-coded cell positions; drive everything from named tables so a shifted column does not silently corrupt the output.

Power Query pulls and cleans the source each morning; PivotTables/charts build the summary; a VBA macro (or Office Script + Power Automate) refreshes the file, exports a PDF, and emails it on a schedule. The key is zero manual steps so it runs reliably every day.