Spreadsheets: From Cells to Evidence

Start with a bounded question

Our fictional retailer wants to know which region needs a closer review of net sales in January 2026. For this exercise, net sales means recorded sales minus recorded refunds, expressed in unspecified currency units. The export covers only the illustrative orders shown below, not a complete trading month. That distinction belongs in the final report, even when every calculation is correct.

One row is intended to represent one order. In a real export, first confirm whether rows instead represent order lines, shipments or payment events. The same order identifier can legitimately appear several times in a line-item dataset. Removing repeats without checking the grain can destroy valid business activity.

Preserve the input

Create three sheets named Raw, Checks and Analysis. Paste the untouched export into Raw and keep a separate working copy. Record the source filename, extraction date and reporting scope in a small notes area. Formatting alone is not a cleaning log: another reader needs to know what changed and why.

Use the ISO 8601-style date notation shown here to make the intended date order clear. Still verify that your application has imported a real date value rather than a string. Check decimal separators and locale settings before trusting a numeric-looking column.

Flag before you remove

With headers in row 1 and order identifiers in column A, enter the following check in a helper column. Fill it down to row 7. A result above one identifies an identifier that needs investigation; it does not automatically authorize deletion.

=COUNTIF($A$2:$A$7,A2)

Both A104 records match across every field. In this fictional example, the source owner confirms that one is a repeated export record. Retain one and log the removal of the other. A105 is different: the order exists, but its sales amount is unavailable. Retain the order, flag it as incomplete and exclude it from the net-sales calculation until the amount is confirmed.

Calculate without inventing values

On the cleaned working sheet, suppose sales is column E and refund is column F. Place this formula in G2 and fill it down through the five unique orders. Leave the missing source cell genuinely blank; do not type the word “Unknown” into a numeric field.

=IF(AND(ISNUMBER(E2),ISNUMBER(F2)),E2-F2,"")

The numeric checks prevent a blank sales amount from being silently treated as zero. They also flag numbers accidentally imported as text. Resolve those types deliberately rather than suppressing every warning. In this exercise, the four complete net values are 100, 70, 120 and 40.

Make the quality limit visible

The known sales total is 400 and the known refunds for the complete orders total 70, giving net sales of 330. Monetary coverage is four of five unique orders, or 80% by order count. That is not 80% of revenue: the missing sales amount prevents you from knowing the revenue share represented by the incomplete order.

Do not use spreadsheet color as the only record of a decision. Add explicit fields such as quality_status and exclusion_reason. Record that the missing order belongs to North, because regional completeness differs: North has two complete orders out of three, while South has two out of two. The displayed comparison is therefore conditional on known amounts.

Self-check: why is a total of 370 wrong here?

It includes the repeated A104 net amount of 40. The arithmetic can be correct over the wrong input. Reconcile row counts and identifiers before investigating the formula.

Your completed workbook should make the raw input, the cleaning decision and the result distinguishable. That separation is the foundation for the next lesson: writing the same calculation as a reproducible SQL query.

Next studySQL: Questions in Queries →