26. Excel Tricks Every Analyst Should Know
Practice and Answer Key: Clean and Summarise a Messy Export
Goal of this practice: Use six tricks from this module to clean a messy export and summarise it by city.
या practice चं ध्येय: या module मधल्या सहा tricks वापरून messy export साफ करा आणि city प्रमाणे summary बनवा.
इस practice का लक्ष्य: इस module की छह tricks से messy export साफ़ करें और city के हिसाब से summary बनाएँ.
Suppose Shahrukh receives a fictional export of 8 order rows (A1:C9). Some cities have extra spaces, some City cells are blank (the city is the same as the row above), the amounts are stored as text, and one order appears twice. All data is fictional practice data.
| A: Order ID | B: City | C: Amount (text) |
|---|---|---|
| BLK-2001 | ·Pune | 450 |
| BLK-2002 | (blank) | 120 |
| AMN-2003 | Nashik· | 95 |
| BLK-2004 | (blank) | 300 |
| BLK-2002 | (blank) | 120 |
| AMN-2005 | Kolhapur | 210 |
| BLK-2006 | ·Kolhapur· | 180 |
| AMN-2007 | Nashik | 75 |
Exercise (one under the other):
- Clean the City column with TRIM (trick 5).
- Fill the blank cities with the value above (trick 2).
- Convert Amount to numbers with VALUE (trick 6).
- Flag duplicate Order IDs with COUNTIF (trick 18), then remove them (trick 7).
- Make a PivotTable of Sum of Amount by City with % of Grand Total (trick 27).
- Paste Values before sending (trick 29).
Expected result (check your numbers):
| Check | Expected |
|---|---|
| Rows after removing duplicates | 7 |
| Duplicate removed | BLK-2002 (second copy) |
| Total amount | ₹1,430 |
| Pune | ₹570 (39.9%) |
| Nashik | ₹470 (32.9%) |
| Kolhapur | ₹390 (27.3%) |
Answer key (open after you try)
D2: =TRIM(B2) (then fill blanks: Go To Special › Blanks, =Up arrow, Ctrl+Enter)
E2: =VALUE(C2)
F2: =COUNTIF($A$2:$A$9,A2)>1 → TRUE for both BLK-2002 rows
Data › Remove Duplicates on Order ID
PivotTable: Rows City, Values Sum of Amount, Show Values As › % of Grand Total
Common mistakes:
| What you see | Why | Fix |
|---|---|---|
| Kolhapur appears twice in the PivotTable | One value still has a space | TRIM the column and paste values before pivoting |
| Total is 0 | Amounts are still text | Use VALUE or Convert to Number |
| A blank city row is filled with the wrong city | You sorted before pasting values | Undo, paste values first, then sort |
| Total is ₹1,550 | Duplicate BLK-2002 not removed | Remove Duplicates on Order ID |
Ravindra Bagale's rule: clean first, check counts second, summarise last.
Quick recap
- Ctrl+Enter fills a formula into a whole selection; Go To Special › Blanks fills gaps.
- TRIM, VALUE, Remove Duplicates and Text to Columns fix most imports.
- Visible cells only, the Name Box and Find All select exactly what you need.
- F4, Evaluate Formula and Show Formulas keep formulas honest; IFERROR and XLOOKUP messages keep them readable.
- Row highlights and Center Across Selection format for reading, not decoration.
- Total Row, % of Grand Total and date grouping summarise; Paste Values makes a safe copy to send.
Loading practice tasks...
- Booking value (Easy): D2 × E2.
- Is TK-02 repeated? (Easy): Does TK-02 appear more than once?
- Total seats (Easy): Total of D2:D7.
- Shows list (spill) (Easy): UNIQUE of B2:B7.
- Paid revenue (Medium): Total seats × price where Status = "Paid".
- Different bookings (Medium): How many different Ticket IDs?
- Hamilton seats (Medium): Seats for "Hamilton".
- Duplicate or first (row 5) (Medium): Is A5 a "Duplicate" or "First"?
- Paid revenue without the repeat (Hard): Paid revenue minus the repeat row (D5*E5).
- Clean rows (spill) (Hard): UNIQUE of A2:F7.
- Seats on 6 Dec (Hard): Seats on the date in C3.