HR & Behavioural Advanced Excel Interview Questions

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.

Situation: in my internship, the team spent two hours every morning copying sales figures from three exports into one summary. Task: I was asked to make it faster. Action: I converted the exports to Tables, used XLOOKUP and SUMIFS to build a summary sheet, and added data validation for the date. Result: the report took ten minutes, errors from manual copying stopped, and the format was reused by another team.

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.

Situation: a regional sales summary I sent showed one zone 18% higher than actual. Task: correct it before decisions were taken. Action: I traced it to a SUMIFS range that stopped one row short, sent a corrected file within the hour with a short note explaining the cause, and added a check cell comparing the summary total to the raw data total. Result: the manager appreciated the quick ownership and the check now catches such gaps automatically.

Situation: my manager was unsure why the new incentive sheet showed lower payouts. Task: explain the logic without formulas. Action: I added a one-page summary with the slab table, walked through two real employees step by step, and used colour to show which inputs drove the result. Result: the manager understood it in one meeting, approved the sheet and asked me to add the same summary to future models.

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.

Situation: finance and sales reported different revenue for the same quarter. Task: find the gap before the review meeting. Action: I pulled both datasets, built a key on invoice number, and used lookups to list invoices present in only one file. Most of the gap came from credit notes that sales did not deduct and invoices booked on the last day. Result: both teams agreed a single definition and I added a reconciliation tab to the monthly pack.

Situation: three teammates built monthly summaries by filtering and copying data by hand. Task: my lead asked me to help them speed up. Action: I ran two short sessions using our own sales data, showing how to create a pivot, group dates, add slicers and refresh after new rows, and shared a one-page guide. Result: within a month each of them produced their summary in minutes, and fewer copy-paste errors reached the manager.

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.

Situation: at 5 PM, my manager needed customer-wise outstanding for a client call at 9 AM. Task: deliver an accurate summary quickly. Action: I confirmed the exact question and cut-off date, pulled the ledger, built a pivot by customer and ageing bucket, cross-checked the total with the trial balance and flagged three unusual balances. Result: the report was ready by 7 PM, and my notes helped the manager answer follow-up questions in the call.

Situation: I had to present quarterly branch performance to regional directors. Task: make twenty pages of tables understandable in ten minutes. Action: I built one dashboard sheet with four KPIs against target, a trend chart and a ranked bar chart of branches, and kept detail tables as backup. I opened with the key message and the two branches needing action. Result: the meeting ended with clear decisions, and the format became the standard quarterly review pack.

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.

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.

Situation: our team spent a day each month cleaning vendor files by hand. Task: I believed Power Query could fix it but the team feared a new tool. Action: I rebuilt one month's cleanup as a query, then ran it side by side with the manual process and showed identical results in five minutes. I documented each step and trained two colleagues. Result: the team adopted it, saving about eight hours a month.

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.

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.

Situation: our sales data crossed two million rows and normal pivots crashed. Task: deliver a monthly dashboard within two weeks. Action: I learned Power Pivot and DAX through documentation and practice files, built a star schema with a Date table, wrote measures for YTD and growth, and checked every number against old reports. Result: the dashboard refreshed in under a minute, and I ran a short session to train two colleagues.

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.

Situation: a report macro I wrote stopped working for the accounts team. Task: fix it quickly and stop it happening again. Action: I found that a sheet had been renamed and the macro used a fixed sheet name and file path. I fixed it with ThisWorkbook.Path and code names, added an error handler with a clear message, and wrote a short usage note. Result: it ran reliably for the rest of the year and I now test macros on another user's machine before handover.

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.