Day 30: Excel Mock Interview — Full Simulation

Minute 1–3 (Data Quality): Convert to Table (Ctrl+T). Check shape with Ctrl+End. Run COUNTBLANK on each column. Check data types — are dates actually dates? Are prices numbers? Minute 3–8 (Orientation): df.describe() equivalent — MIN/MAX on Quantity and UnitPrice to spot outliers. Check date range. Understand granularity (one row = one order line). Minute 8–15 (Core Analysis): Add Revenue column (=Quantity*UnitPrice). Pivot Table: Revenue by Region and Category. Pivot Table: Revenue by Month (group OrderDate). Top 10 products by Revenue. Minute 15–20 (Delivery Analysis): Add Delivery Days column (=NETWORKDAYS(OrderDate,DeliveryDate)). Average delivery days by Region. Products with delivery > 7 days. Minute 20–28 (Insights): What drives revenue? Which category/region is growing? Any delivery issues? Minute 28–30 (Summary): Write 5 bullet-point findings.

Step 1: Pick one failing row and check the lookup value: =LEN(A2) in one cell, =LEN(lookup_key_in_table) for the matching row. If lengths differ, there are extra characters. Step 2: Check for spaces: =EXACT(A2, matching_table_value). EXACT is case-sensitive and space-sensitive — if EXACT returns FALSE on visually identical values, there are hidden spaces or different case. Step 3: Apply TRIM to both sides: =VLOOKUP(TRIM(A2), table, 2, FALSE). Step 4: Check data types: =ISNUMBER(A2) vs =ISTEXT(corresponding_table_value). If one is text and the other is number, they will never match. Step 5: If still failing, use CLEAN+TRIM: =VLOOKUP(TRIM(CLEAN(A2)), table, 2, FALSE).

Discovery phase (30 min): Document every manual step — which files, what copying, what formatting, what calculations. Identify the stable parts (formula logic, layout) vs changing parts (new month data). Automation design: Source files in a consistent folder → Power Query connects and combines them automatically. Cleaning steps (remove duplicates, fix dates, add Revenue column) → Power Query Applied Steps. Summary tables → Pivot Tables connected to the Power Query output. Charts → linked to Pivot Tables. Formatting → template locked. Execution: next month, drop new file in folder → click Data → Refresh All → report updates in 2 minutes. Validation → sanity check sheet confirms totals match expected range. Result: 4 hours → 15 minutes. Document the refresh procedure for backup coverage.

Do not just describe the numbers — provide an actionable insight. North generates the highest absolute revenue (₹45L, 36% of total) but has the lowest margin (32%) — suggesting high volume at thin margins, possibly due to price competition or product mix tilted toward lower-margin items. East has the highest margin (44%) on the lowest revenue (₹21L) — this is an underpenetrated region where margin quality is proven. The business case: investigate whether East's high margin reflects a genuinely better customer mix or just smaller scale that has not yet faced pricing pressure. If it is structural, East deserves investment to grow revenue while protecting margin. Recommendation: do not chase North-style volume in East — protect the margin advantage while growing scale.

This question tests self-awareness and growth mindset. A strong answer: "My next priority is Power BI. I can build everything a business needs in Excel, but as datasets grow to millions of rows and stakeholders want self-service filtering, Excel becomes the wrong tool. Power BI connects directly to databases, handles much larger data, publishes dashboards online so stakeholders access them without opening a file, and has more powerful DAX calculations than Excel Pivot Tables. Learning Power BI is a natural extension of my Excel skills — the concepts are the same but the scale and distribution model are different. I am also looking to deepen my Python skills for automating data gathering from APIs, which Excel cannot do."