Make messy data useful

Before you begin

• Basic rows/columns and simple arithmetic.
• A spreadsheet is helpful; manual completion is supported.

Free original, non-accredited practice. Self-review is not independent assessment. No particular provider, payment platform or country is mandatory; use a legitimate available route or explicitly labelled manual practice.

Start with clean sample data

A spreadsheet is easier to check when each row represents one event and each column has one meaning. Start with fictional expenses, not bank statements. Keep dates consistent, amounts numeric, and categories drawn from an explicit list. Stable receipt identifiers make duplicate handling possible. The five-row warm-up totals 60: Travel 10, Food 5, Travel 15, Tools 20, and Food 10. These expected values are deliberately small enough to calculate by hand. If an AI suggests a summary, compare it with this independent baseline rather than trusting the narrative.

Worked example

WARM-UP TOTAL: 10 + 5 + 15 + 20 + 10 = 60.
TRAVEL: 10 + 15 = 25.
FOOD: 5 + 10 = 15.
TOOLS: 20.
COLUMNS: Date, Category, Description, Amount, ReceiptID.

Your practical task

1. Import the warm-up CSV into a spreadsheet or inspect it as text.
2. Check that every row has the same five fields.
3. Calculate the total and category subtotals independently.
4. Write the allowed category list: Travel, Food, Tools.
5. Explain why a receipt ID is useful before attempting deduplication.

Evidence to keep

• A correctly structured five-row table.
• An independently checked total of 60 and three subtotals.
• A short data dictionary for each column.

Mistakes to watch for

• Storing currency symbols inside numeric amounts.
• Putting two transactions into one cell or row.

Check your understanding

If the spreadsheet returns 6010 instead of 70 after an addition, what data-type problem might exist?

No-service / offline route

Use a printed table or text editor and calculate totals manually.

Ask for a formula, then test it

AI can help draft a formula, but the spreadsheet calculates the result. Specify the exact column layout, row range, and desired treatment of blanks. A formula that references the wrong column can be syntactically valid and still give a plausible wrong answer. With the warm-up headers in row 1, Amount is column D and Category is column B. SUM(D2:D6) should produce 60. SUMIF(B2:B6,"Travel",D2:D6) should produce 25. Some spreadsheet locales use semicolons instead of commas; adjust syntax without changing the logic. Always state the layout when asking a model for help.

Worked example

TOTAL: =SUM(D2:D6)
TRAVEL: =SUMIF(B2:B6,"Travel",D2:D6)
EXPECTED: 60 and 25.
COUNTEREXAMPLE: Summing E2:E6 addresses receipt IDs, not amounts.

Your practical task

1. Write the column map beside your data.
2. Ask an AI for a total and a Travel subtotal formula, or write them yourself.
3. Run the formulas and compare with the hand-checked values.
4. Deliberately change one range to the wrong column and observe the result.
5. Record the correct formula, expected result, and observed failure.

Evidence to keep

• Two formulas with correct ranges.
• A pass/fail record against expected totals.
• One documented wrong-range example.

Mistakes to watch for

• Accepting a formula because the model explains it confidently.
• Ignoring that a locale may require different separators.

Check your understanding

What evidence distinguishes a correct formula from a formula that merely happens to give the right total once?

No-service / offline route

Annotate the ranges in text and perform the same calculation by hand.

Add a failure test

Tests should cover more than the original happy path. Try a new row beyond the initial range, a blank amount, a category misspelling, a zero amount, and a negative value. Your data policy decides what is valid; do not let the model silently choose the policy for you. For this workshop, amounts must be finite numbers greater than or equal to zero, categories must match the allowed list, and receipt IDs must be unique. This simplified rule deliberately excludes refunds; a real expense system would need an explicit policy for them. The purpose is to make rejection visible instead of quietly altering the data.

Worked example

CASE: Add Travel 12.50 beyond the original range.
EXPECTED TOTAL IF INCLUDED: 72.50.
FAILURE: A fixed formula still returns 60.
FIX: Expand the range or use a structured table; then rerun all earlier checks.

Your practical task

1. Add a sixth valid expense of 12.50.
2. Check whether the original range includes the new row.
3. Create test rows for blank, negative, misspelled-category, and zero values.
4. Write the expected handling for each case before calculating.
5. Update the formula/range and confirm the earlier totals still behave as expected.

Evidence to keep

• A five-case edge-test table.
• A corrected expanding-range approach or documented manual process.
• A stated policy for zero and negative values.

Mistakes to watch for

• Treating every negative amount as a harmless typo.
• Fixing the range without rerunning earlier checks.

Check your understanding

Why is silently changing a negative value to positive worse than recording a rejected row?

No-service / offline route

Use the supplied cases and mark which the stated validation policy accepts.

Clean and validate extracted data

AI-assisted extraction should produce candidate rows, not trusted accounting records. Preserve the original input and record changes. A missing amount should remain missing or enter a reject log; it must not become a guessed number just to make a table complete. The dirty-expenses fixture contains twelve rows. Under the workshop policy, seven are accepted and five rejected: one duplicate receipt ID, one non-numeric amount, one unknown category, one negative amount, and one missing date. The accepted total is 72.50. Zero is valid. Dates must be supplied, but this exercise does not implement a full calendar validator.

Worked example

EXPECTED ACCEPTED ROWS: 7.
EXPECTED REJECTED ROWS: 5.
REJECT LOG: ReceiptID, original row, reason, action.
REASONS: duplicate_id; non_numeric_amount; unknown_category; negative_amount; missing_date.

Your practical task

1. Read dirty-expenses.csv without overwriting it.
2. Apply the written rules one by one and record every rejection.
3. Produce a clean table in the original column order.
4. Compare it against expected-clean.csv and the five reject reasons.
5. Explain why the zero-amount row is accepted but the missing amount is not.

Evidence to keep

• Seven accepted rows and a five-row reject log.
• An unchanged original input file.
• A clean total of 72.50 with no guessed amounts.

Mistakes to watch for

• Deleting problematic rows without a trace.
• Treating missing, zero, and invalid text as the same value.

Check your understanding

Can another person reconstruct why each original row was accepted or rejected?

No-service / offline route

Perform the validation manually using the fixture and expected-output file.

Produce an auditable summary

A summary needs reconciliation before presentation. The clean fixture’s categories total Travel 37.50, Food 15.00, and Tools 20.00, which sum to 72.50. A chart should communicate those values, not hide the excluded rows. Separate what the data contains from what you infer. These twelve fictional records cannot establish a real spending trend or recommend layoffs. State the date scope, accepted-row count, rejected-row count, and validation policy beside the summary. If you ask AI for a narrative, give it only the checked aggregate and require it not to invent causes.

Worked example

CHECKED SUMMARY: Travel 37.50; Food 15.00; Tools 20.00; total 72.50.
SAFE NARRATIVE: Travel is the largest category in this fictional accepted subset.
UNSUPPORTED NARRATIVE: Fuel inflation caused overspending this month.

Your practical task

1. Calculate category subtotals from the accepted rows.
2. Reconcile the subtotals to the grand total.
3. Create a small bar chart or labelled text chart.
4. Write a two-sentence narrative restricted to the checked facts.
5. Attach a footnote stating that five original rows were rejected and why.

Evidence to keep

• A reconciled category summary and simple chart.
• A factual narrative with no invented causal explanation.
• A visible validation and exclusion note.

Mistakes to watch for

• Using a pretty chart to conceal a failed total.
• Turning a tiny fictional sample into a real-world business conclusion.

Check your understanding

Which sentence in your report would require evidence beyond this dataset?

No-service / offline route

Draw a text bar chart and write the checked totals directly from the expected file.

Capstone: ship a checked expense pipeline

Build a repeatable process from original input through validation, clean data, rejection records, totals, and a short report. It can be manual, spreadsheet-based, or a small script. The acceptance criteria are the same: predictable treatment of bad rows and reproducible checked results. Do not describe the result as accounting software. It is an educational validation workflow using fictional data. Include instructions for adding a new receipt and explain what must happen if a duplicate ID has conflicting values. A person should review such conflicts rather than the system silently choosing one.

Worked example

PACK: original.csv, rules.md, clean.csv, rejects.csv, summary.md, tests.md.
ACCEPTANCE: 7 accepted; 5 rejected; total 72.50; category totals reconcile; rerunning unchanged input produces the same result.

Your practical task

1. Assemble your input, written rules, clean output, reject log, and summary.
2. Run the same process twice and compare outputs.
3. Add one valid row and one duplicate-ID conflict as change tests.
4. Score the capstone rubric and document unresolved limitations.
5. Export the files with a README that another learner can follow.

Evidence to keep

• A reproducible six-file data workflow.
• Baseline tests and two change tests.
• A limitation note covering refunds, dates, and real financial use.

Mistakes to watch for

• Claiming production readiness from a small fixture.
• Overwriting the original data during cleaning.

Check your understanding

What would you need to add before this process could safely touch real financial records?

No-service / offline route

Deliver the same artefacts using manually checked tables; automation is optional.

Capstone self-review

Score each criterion 0 (missing), 1 (partial), or 2 (demonstrated). A self-review pass needs at least 8/10 and 2/2 on the final safety/limits criterion. This is practice feedback, not verified assessment.

• Input preservation: Original data and validation policy are retained.
• Correct validation: The baseline yields 7 accepted and 5 rejected rows with reasons.
• Reconciled totals: Total 72.50; Travel 37.50, Food 15.00, Tools 20.00.
• Repeatability: Reruns and added-row/duplicate-conflict tests are documented.
• Scope and privacy — critical: Only fictional data; financial limitations and exclusions are clear.

Review questions

Practice files

• LICENCE.md
• dirty-expenses.csv
• expected-clean.csv
• expected-rejects.csv
• expected-summary.json
• validation-rules.md
• warmup-expenses.csv

References and editorial limits

Original authored practice material; not independently expert-reviewed.

• Google Sheets function reference
• LibreOffice Calc guidance

Browse all build paths → · Curriculum overview