Spreadsheet quality

Five Quick Spreadsheet Checks Before You Send That Report

The spreadsheet looks fine. The totals are neat. But one wrong formula can change the report without showing an obvious error.

You do not need to be a spreadsheet expert to run these five checks. Work on a copy of the file. If something looks wrong and you do not know the business rule behind it, stop and ask before changing it.

1. Look for cells that break the pattern

A formula is the instruction a spreadsheet uses to calculate a result. In a column of formulas, most rows should follow the same pattern.

Row 19 is different. Someone may have typed a number over the formula. It could be a valid one-time change, or it could be a mistake. Mark it and ask.

Quick check: click several cells in the same calculated column. Do the formulas change by row in the same way?

2. Check the first and last row of every total

Totals often miss a new first or last row.

If the data runs from row 5 through row 42, a total that says =SUM(F5:F41) leaves out row 42. The number may still look believable.

Quick check: click the total. Confirm that the highlighted range starts and ends on the right rows. Repeat this for each section and grand total.

3. Find numbers typed over formulas

After Check 1 flags a cell as different, this is how you confirm whether it holds a formula or a typed number.

A typed number inside a formula column will not update when the source data changes.

Look for one cell that shows a plain number while the cells above and below show formulas. Formatting and color may look identical, so click the cell and look at the formula bar.

Do not replace the number right away. First find out whether it was an approved override.

4. Test blanks, zeros, and error messages

Try a safe test in a copy of the workbook:

• What happens when a needed cell is blank?
• Is zero a real result, or does it mean data is missing?
• Does the sheet show an error such as #N/A (a spreadsheet message meaning a needed value is not available or was not found) or hide it as zero?
• What happens when a value is entered as text instead of a number?

A clear message such as “Missing rate” is often safer than a zero that looks real.

5. Compare the headline total with a second simple check

Do not rely on one formula for an important total. Use a second, simple check.

• add the section totals and compare them with the grand total;
• compare the number of report rows with the number of source records;
• calculate one sample row by hand;
• compare an opening amount plus changes with the closing amount.

If the two numbers do not match, stop before sending the report.

Quick checklist

• ☐ Similar rows use similar formulas.
• ☐ Each total includes the correct first and last row.
• ☐ Typed numbers inside formula columns are explained.
• ☐ Blanks, zeros, and error messages are clear.
• ☐ Important totals match a second simple check.
• ☐ I saved a clean copy before making changes.
• ☐ I asked about anything that may be an approved exception.
• ☐ I recalculated and reviewed the report after changes.

When to stop and ask for help

Stop before changing the workbook when:

• you do not know what the formula is supposed to do;
• a typed number may be an approved override;
• the file uses outside data you cannot check;
• the result affects payroll, taxes, accounting, legal duties, or a filed report;
• fixing one cell changes an important result elsewhere.

These checks can catch common problems. They cannot prove that every formula is correct, and they are not an audit or certification.

Next step

Use the checklist on a copy of the workbook before you send the report. If you want bounded specialist help, view Steven Nash’s spreadsheet formula-audit service on Fiverr.

Communication, contracting, and payment remain on Fiverr. Steven Nash is the individual marketplace seller. Nash Forward is the educational publisher, not the seller or contracting party.

A question for discussion

Which of these five checks has caught a real spreadsheet problem for you, or which one would you add for another small-business owner? Please leave out customer, employee, financial, or other sensitive data.