Give the workbook visible data stages
Store the source as an untouched, read-only file and import it into a working copy rather than correcting the only original. Separate input, cleaning, analysis and reported output. Plain worksheet names and a short README sheet can identify the sequence, source revision, responsible researcher and intended starting point.
Avoid hybrid sheets where raw observations, comments, manual totals and charts sit in the same unmarked space. Use structured tables or defined ranges and state which row contains headings. Put units, permitted categories and missing-value codes in a data dictionary. A blank, zero and “not collected” express different states and should not become identical merely because the cells look empty.
Keep formulae inspectable
Prefer visible cell references to unexplained constants embedded inside formulae. If a threshold is chosen for a scholarly reason, place it in a labelled parameter cell and cite or explain the choice. Named ranges can improve readability, but their scope and definition must be listed. Check whether relative and absolute references move as intended when a formula is filled.
Flag volatile functions involving the current date, time or random values. A recalculation may change them. External links can point to a local path, an unavailable network share or an obsolete revision. Record the calculation mode and force a complete recalculation before fixing the submission version. If formulae have been replaced by values, retain a route back to the generating sheet or archived workbook.
Treat filters, sorts and hidden records as method
An active filter can remove cases from a total or chart without appearing in a printed result. Record the variable, condition, purpose, row count before and after filtering, and scholarly reason. Where exclusion is part of the method, create a durable inclusion flag and reason code. Every case then remains auditable without depending on a transient menu state.
Sorting is dangerous when it affects one column but not the whole record. Check stable row identifiers before and after the operation. Hidden rows and columns should not serve as long-term documentation. Remove them intentionally from a release copy or explain their role. Conditional formatting is also presentation rather than data: if a colour encodes a category, store the category in a field.
A PivotTable needs a declared source and refresh state
Record the source table, row fields, column fields, value aggregation, filters, groups and calculated fields. Confirm that the source expands when new records are added. A PivotTable may display cached values until it is refreshed; state the date and result of the final refresh. Preserve any errors or warnings instead of silently dismissing them.
Grouped dates and numerical bins are analytical decisions. Document boundaries and treatment of blank values. Compare grand totals with an independent calculation. Several PivotTables may share a cache or slicer, so test interactions. Give each pivot a meaningful name and record which published figure or table was exported from which output.
Trace selected results backwards
Choose three published quantities: a simple value, a grouped summary and one involving exclusions. Begin with the PDF table or figure and trace each number through its output sheet and formulae to the source row. Open the workbook in a fresh session, refresh queries and PivotTables, and record warnings. Compare the recalculated output with the fixed submission state.
Preserve the workbook in a format that retains formulae. For longer-term inspection, include open exports of source and result tables where rights and data protection permit. A PDF records appearance but not cell logic. Identify the Excel release and important add-ins in your software environment record.
Make manual edits controlled exceptions
Manual correction is not automatically poor practice; an invisible correction is. A changes sheet should state row ID, field, old value, new value, rationale, evidence, date and editor. If the same rule affects many records, a visible Power Query or scripted transformation is usually easier to review. Retain the original values so the decision can be reversed and challenged.
Protect formula cells against accidental typing and distinguish input cells without relying on colour alone. Use data validation for permitted categories, while remembering that copied data can bypass some interface controls. Before deposit, inspect external links, hidden names, comments, personal metadata and macros. Release only the components that are necessary and allowed.