Summarise a Defect Register in Excel
A spreadsheet summary can show how recorded items are distributed across contractors and statuses. Start with a checked export, then reconcile the totals so the summary describes the selected register rather than an assumed view of the whole project.
By Blu Mint Digital. 3 minute read. Last reviewed .
Export the intended set of items
In Site Audit Pro, confirm whether the contractor filter is set to all items or a named company before exporting CSV. Save the file with an issue date and keep an unchanged copy as the source for the summary.
The export contains register fields and a photo count, not embedded photo files. Its No. column is numbered for that export. Do not use that number as a permanent cross-report identifier when later selections may change.
Check the import before counting
Use Excel's text/CSV import workflow when you need control over how values are interpreted. Inspect the delimiter, headers and preview. Treat identifiers as text where leading zeros matter, and check dates before using them in overdue calculations.
Microsoft explains these controls in its text and CSV import guidance. The exact interface depends on your Excel version. After loading, check the first and last records against the exported file rather than assuming that a successful opening means every field was interpreted as intended.
Build a contractor-by-status summary
Use the imported table as the source for a PivotTable. Place Contractor in rows, Status in columns and a consistently populated field such as Title in Values, summarised as Count. Do not sum the per-export No. values; adding row labels does not produce an item count. Microsoft's PivotTable instructions explain how to change the value calculation.
- Keep blank contractor assignments visible for follow-up.
- Check that similarly spelled company names have not split one contractor.
- Compare the grand total with the number of exported item rows.
- Explain the status definitions alongside the summary.
- Refresh the summary only from a clearly identified replacement export.
For example, a twelve-item file should yield twelve counted items when every title is populated. If the total differs, inspect blank values, selection range and summarisation method before publishing a conclusion.
Keep the spreadsheet's role clear
Editing the spreadsheet does not update Site Audit. Decide whether it is an issued summary or a separately maintained working register, and avoid quietly treating both as current. Photo review still belongs with the PDF or original project records.
Use the status guide before presenting a verified-completion figure. The weekly report workflow shows how to explain new items, reopened items and duplicate corrections rather than relying on a single percentage.