Capstone: clean a 2,000-row messy sales export + build a summary block

Download the workbook below. This guided challenge combines references, text cleanup, dates, Tables and conditional aggregation. All data is synthetic. The formulas work without XLOOKUP or dynamic arrays. Allow 30 minutes; save a working copy before starting.

1. Inspect and agree the rules · 3 minutes

Your manager needs net sales by region and month for January–March 2026. The export has one line per order, so Order ID is the unique key for this exercise. Real multi-line orders would need an order-plus-line key.

Open Read Me, then Raw Export. There are 2,000 data rows, excluding the header. Clean Work contains a copy plus empty helper columns H:Q. Keep Raw Export unchanged as the audit trail.

Filter a few examples before editing. If duplicate IDs had conflicting values, stop and investigate instead of arbitrarily keeping the first row. In this supplied file, unit prices are present and dates are valid; the Status formula below is tailored to its missing-quantity problem.

2. Normalize with helper columns · 9 minutes

In Clean Work, enter these formulas in row 2. Fill H2:Q2 down through row 2001 using the fill handle, or select H2:Q2001 and press Ctrl+D after entering the first row.

TRIM handles ordinary spaces; replacing CHAR(160) first also handles the non-breaking spaces planted in this file. CLEAN removes common nonprinting characters. The date formula avoids interpreting ISO text according to a regional date setting. The number conversion assumes this export's comma thousands separator and whole-number prices.

Format I as yyyy-mm-dd, L as a number, and M/Q as currency or numbers with two decimals. Formatting alone does not convert text into numbers. Check ISNUMBER(I2) and a nonblank L/M cell; they should return TRUE. The negative quantities must remain negative.

3. Freeze values, deduplicate and create a Table · 5 minutes

1. Before removing anything, filter P for Duplicate: it should identify 100 repeated rows. Clear that filter.
2. Copy H2:Q2001, then Paste Special → Values onto the same range. This freezes the results before rows move.
3. Select A1:Q2001. Data → Remove Duplicates → confirm headers → select only Clean ID. Remove 100 duplicate rows, leaving 1,900 orders. Select the whole record range, not just the ID cells.
4. Select A1:Q1901, press Ctrl+T, confirm headers, and name the Table Sales in Table Design. This precise range excludes the now-empty trailing rows.
5. Filter Status = Review. There should be 20 retained orders. Keep them visible in your audit; do not fill missing quantities with zero or an average. Clear the filter when finished.

There are 1,880 Ready orders. Blank quantities can occur in more than one raw row because a missing-value row can also be duplicated: count review items after deduplication. Do not add issue counts together to infer rows removed.

4. Build the summary block · 8 minutes

The Summary sheet provides labels. Enter these formulas:

Regions are in E2:E5. In F2 enter =COUNTIFS(Sales[Clean Region],E2,Sales[Status],"Ready"). In G2 enter =SUMIFS(Sales[Net Sales],Sales[Clean Region],E2,Sales[Status],"Ready"). Fill both down to row 5.

Month-start dates are already in E9:E11. In F9:

=COUNTIFS(Sales[Clean Date],">="&E9,Sales[Clean Date],"<"&DATE(YEAR(E9),MONTH(E9)+1,1),Sales[Status],"Ready")

In G9:

=SUMIFS(Sales[Net Sales],Sales[Clean Date],">="&E9,Sales[Clean Date],"<"&DATE(YEAR(E9),MONTH(E9)+1,1),Sales[Status],"Ready")

Fill both down to row 11. Using an inclusive start and exclusive next-month start also works when valid source dates contain times. All summaries explicitly require Ready status; worksheet filters alone do not change SUMIFS results.

5. Reconcile and hand over · 5 minutes

Check these independently before looking at Clean Answer and Answer Key:

• Raw rows = unique orders + duplicates removed: 2,000 = 1,900 + 100.
• Unique orders = Ready + Review: 1,900 = 1,880 + 20.
• Net units 8,904; net revenue ₹8,783,340; average Ready order ₹4,671.99 (display rounded, retain full precision).
• There are 46 return orders and 19 zero-price orders; both remain in the Ready group.
• The four regional revenues and three monthly revenues must each sum to B8. Their order counts must each sum to B6.

Write two short observations in Summary A15:A16 and your unresolved-data note in A18. For example, East has the highest regional net revenue in this sample; 20 orders remain excluded pending quantity confirmation. Describe observations, not unsupported causes. Save your workbook with Raw Export, your cleaned Table and the formula-based summary intact.

Completion criteria: correct row reconciliation, all review rows retained, returns/zero prices preserved, numeric dates/amounts, matching regional/monthly totals, and a clear handover note. If a check fails, revisit the relevant rows; do not hide every error with IFERROR or hard-code the expected total.