Ravindra BagaleCourses & study guides

16. Practice Exercises with Answer Hints

16.14 Mixed Scenario Exercises

By Last updated:

  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,"@").

Ravindra Bagale's Tip

Many students read a scenario question and immediately start writing a formula. First write the approach in 1–2 sentences (which data, which key, how you will check), then the formula. Do the same in an interview – explain the approach first, then the solution.