17.8 Scenario-Based Questions

Q63. You receive a daily file with inconsistent city names and duplicates. How would you automate cleaning?

I would build a Power Query that trims and capitalises text, maps variants through a mapping table, fixes data types with the right locale and removes duplicates on the order key. Next day, I drop the new file in the folder and click Refresh. I would add a reconciliation check (row count and total before/after).

Q64. A manager says the dashboard total doesn't match the finance report. What do you do?

Clarify definitions first (gross vs net, delivered only, date range and time zone), then reconcile step by step: compare row counts and totals at the source, check filters and slicers, duplicates, cancelled/returned orders and cut-off time. I document the difference and agree one definition in the KPI notes.

Q65. How would you find customers who ordered in September but not in October?

Create unique customer lists per month (UNIQUE/FILTER or pivots), then =COUNTIF(OctList,A2)=0 against the September list – or a Power Query Left Anti merge.

Q66. How do you highlight products whose sales dropped more than 10% vs last month?

Conditional Formatting › New Rule › Use a formula: =$D2<$C2*0.9 applied to the table rows, where C is last month and D is this month.

Q67. How would you prevent wrong data entry in a shared target sheet?

Data validation (lists, whole-number limits, dates), input messages and error alerts, unlocked input cells with sheet protection, and conditional formatting that flags suspicious values.

Q68. You need to send each city manager only their own data every week. How would you do it?

Filter by city and copy to separate sheets or workbooks – manually with a pivot's Show Report Filter Pages, or automatically with a VBA macro that loops through unique cities, copies filtered rows to a new workbook, saves it and optionally exports a PDF.

Q69. How would you calculate the attendance hours and overtime of delivery partners?

Hours: =MOD(Out-In,1)*24 – MOD also handles shifts that cross midnight. Overtime: =MAX(0,Hours-9). Summarise with SUMIFS by partner and week.

Q70. Explain one Excel project you have done.

Use STAR (Module 18.1): the business problem, the data, what you cleaned and built (Tables, lookups, pivots, dashboard, macro), and the result – stated honestly with verifiable numbers.