Spreadsheet checks help identify incorrect inputs, formula ranges and misleading totals. A total that looks reasonable can still contain missing invoices, duplicates or reversed signs. Start by defining what the sheet is supposed to prove.
Spreadsheet checks: define the register before checking it
Suppose you are reviewing supplier invoices for one month. The register contains supplier name, invoice reference, date, amount and payment status. State whether the amounts include tax and whether credit notes are entered as negative figures or in a separate column.
Without those definitions, two people can produce different totals while both believe they followed the instructions. Consistency comes before a complex formula.
Use a control total from independent evidence
If the approved source documents total PKR 240,000, compare that amount with the register total. An unexplained difference of PKR 12,000 may point to a missing invoice, a duplicate or several smaller errors. The register’s own total is not independent evidence of its completeness.
Also compare the number of documents where useful. Equal totals can hide one missing invoice and another duplicate of the same amount. Amount and count checks answer different questions.
Check duplicates using more than one field
Two invoices for PKR 8,000 may both be valid. Two rows with the same supplier and invoice reference deserve investigation. Compare dates, amounts and original documents before removing anything.
Keep an audit trail of corrections: original row, reason, evidence and corrected value. Deleting data without an explanation can make the sheet look tidy while making review harder.
Inspect formulas and ranges
- Check whether the total includes the first and last data rows.
- Look for a typed number inside a column that should use formulas.
- Check whether a formula has shifted its references when copied.
- Review filtered or hidden rows before interpreting a subtotal.
- Confirm that numbers are stored and treated as numbers.
Test one calculation manually. If the sheet multiplies quantity by unit price, use a simple row whose answer you can verify quickly. A spot check does not prove every row, but it can expose a repeated formula error.
Check signs and reconcile the final result
A supplier credit note should reduce the amount owed under the chosen convention. If it increases the balance instead, the arithmetic may be working perfectly on the wrong sign.
After corrections, reconcile the register to the relevant ledger or other record. Record outstanding differences rather than forcing agreement. Retain a read-only copy of the reviewed version if that suits the organisation’s process.
A ten-minute practice exercise
Create a small register with five invoices, one deliberate duplicate and one credit note entered with the wrong sign. Ask another person to review it using the checks above. The goal is to explain each finding, not just produce the expected total.
These habits support sound management information. Explore the MA1 course or browse ASAACCA’s professional courses to build your accounting and data skills.
A worked review with errors that partly cancel
Assume the correct invoice register contains five invoices: PKR 10,000, PKR 12,000, PKR 8,000, PKR 15,000 and PKR 5,000. There is also a supplier credit note of PKR 2,000, entered as a negative amount. The correct net total is PKR 48,000. These amounts are illustrative and exclude tax. The source-document count is six, with five invoices and one credit note.
Now imagine the PKR 12,000 invoice is missing, the PKR 8,000 invoice appears twice, and the credit note is entered as positive PKR 2,000. The spreadsheet total is still PKR 48,000. The missing invoice reduces the total by PKR 12,000, the duplicate increases it by PKR 8,000, and the reversed credit note increases it by PKR 4,000. The mistakes cancel exactly.
Even the row count can agree in this example: the missing document and duplicate offset each other. This is why a total and count are useful checks but do not replace document-level matching. Compare each supplier and reference with its evidence, and verify the sign of each credit note. Agreement can coexist with incorrect records.
Keep input cells and calculation cells distinct
Identify which cells hold source data and which contain formulas. A clear visual convention helps a reviewer avoid overwriting a calculation. Store assumptions such as rates in a labelled input area rather than scattering unexplained constants through formulas. If a rate changes, the reviewer should be able to locate the authorised assumption and understand its effect.
Protect formula cells where appropriate, while recognising that protection is not a guarantee of accuracy. A locked formula can still be wrong. Check one simple case manually and one boundary case, such as a zero quantity or a credit note. Test that copied formulas keep the intended references and include all required rows.
Dates and references need consistent formats
A date entered as text may not sort or filter as intended. An ambiguous date such as 03/04 can be interpreted differently under different regional settings. Use an agreed format and check the underlying date value, especially when importing data. Apply the reporting period consistently to invoices and credits rather than assuming every visible row belongs to the same month.
Invoice references can contain leading zeros, letters or punctuation. Treat them as identifiers rather than quantities. Converting 000123 to 123 may make matching less reliable if the supplier’s reference includes those zeros. Remove accidental spaces carefully and keep the original reference available when normalising imported data for comparison.
Review filtered totals before reporting them
A filtered view can show only unpaid invoices while a total formula continues to include all rows. Another formula may total only visible rows. Neither is automatically wrong; the correct choice depends on whether the report is meant to show all purchases, all outstanding amounts or a filtered subset. Label the figure with its scope.
Before sharing a report, inspect active filters, hidden rows and the dates used. Check that a reviewer can reproduce the displayed total. A screenshot of a filtered sheet without its assumptions can be misleading even when the workbook is mathematically correct. Record the cut-off and selection criteria alongside the result.
Reconcile an opening balance to a closing balance
For a supplier balance, a useful structure is opening payable plus new invoices minus credit notes minus payments equals closing payable, subject to the stated sign convention. If the opening balance is PKR 20,000, invoices total PKR 48,000, credits total PKR 3,000 and payments total PKR 40,000, the expected closing amount owed is PKR 25,000.
Compare that result with the ledger and supplier statement. A difference may be a timing item, an omitted transaction or a classification error. Keep the reconciliation separate from the register’s own sum so that the checks address different risks. Explain unresolved differences with references and dates instead of inserting an unnamed adjustment.
Make the reviewed version reproducible
Record who prepared the sheet, who reviewed it and which source documents were used. Keep the reviewed version identifiable and preserve corrections with a reason. When a new month’s data is added, check formulas and reporting boundaries again. Reusing a workbook saves time, but an old range or assumption can silently exclude new rows and undermine an otherwise careful review.
Frequently asked questions
Does a correct SUM formula prove a spreadsheet is accurate?
No. It can total incomplete, duplicated or wrongly classified data correctly. Check the inputs and the purpose as well as the formula.
Should you delete every repeated amount?
No. Different valid transactions can have the same amount. Investigate combinations such as supplier, invoice number, date and amount before treating a row as a duplicate.