16. Practice Exercises with Answer Hints

16.14 Mixed Scenario Exercises

By Ravindra Bagale · Last updated: 25 September 2026

1. A manager sends two lists – Blinkit orders and payment gateway records. Find orders that were delivered but not paid. Hint: XLOOKUP/COUNTIFS from orders into payments; filter "Not found".
2. Monthly sales per city for a year are in 12 separate sheets. Build one summary. Hint: Power Query From Folder/append, or 3D reference =SUM(Jan:Dec!B2) if layouts are identical.
3. The workbook is slow (40 MB). List five fixes. Hint: remove volatile functions (OFFSET, INDIRECT, TODAY in thousands of rows), full-column references, unused formatting; use Tables/Power Query; set Manual calculation while editing; save as .xlsb.
4. Calculate MoM growth % per city from a pivot. Hint: Show Values As › % Difference From › Base field Month › (previous).
5. Highlight rows where sales dropped more than 10% vs last month. Hint: Conditional Formatting › New Rule › Use a formula: =$D2<$C2*0.9.
6. Frequent buyers: customers with 5 or more orders in October. Hint: pivot Count of Order ID by Customer with a Value Filter ≥ 5, or COUNTIFS.
7. Extract the domain from e-mails like zoya@example.com. Hint: =MID(A2,FIND("@",A2)+1,100) or =TEXTAFTER(A2,"@").