Analyze spreadsheet data

Define the scope, check data quality and verify calculations with a small sample.

1. Add the spreadsheet and define the scope

Choose a workspace, add the spreadsheet with + → File and confirm its filename. Preserve the original and save results separately. State what each row represents, the date range, units and questions. Counting distinct order IDs differs from counting detail rows. First confirm sheet names, columns and sample rows.

Read the attached spreadsheet and list sheet names, columns and the first five rows. Do not modify the original. Analyze completed September orders only, in CNY. Count distinct order IDs and list refunds separately. Report sales, valid orders and average order value by channel. Ask if a field is ambiguous.

2. Check quality before calculating

Check missing fields, duplicate IDs, invalid dates and mixed currency units. Blank does not always mean zero; repeated detail rows do not always mean duplicate orders. List affected rows and proposed rules before cleaning. Report input, included and excluded row counts with reasons.

List missing fields, duplicate IDs, unusual dates and amount units. Do not delete records. Show row numbers and recommended treatment for approval. Average order value = sales / valid orders; if the denominator is zero, mark it unavailable.

3. Check totals, formulas and conclusions

Request a summary, definitions and exceptions. Confirm channel totals match the overall total and that refunds follow the agreed rule. Numerators and denominators must use the same scope. Check date order and avoid comparing a full month with an incomplete one. Recalculate a few source orders yourself.

Provide channel summary, exceptions and metric definitions. Show formulas and source locations, and reconcile channel totals. Separate observed facts, possible explanations and unresolved questions.

4. Save a result that can be reviewed

Specify filenames, destination and required sheets or notes. Open the output and check sheets, key numbers, text and dates. Resolve file-reading errors before accepting an analysis. Reuse definitions for the next dataset, but recheck its dates and exceptions.

Practice: three fictional orders

A001: website / CNY 100 / completed. A002: website / CNY 200 / completed. A003: community / CNY 50 / refunded. Counting completed orders only gives website sales of CNY 300, two orders and average order value of CNY 150; list the CNY 50 refund separately. Ask for included and excluded order IDs and compare with these hand-checkable results.