Snag list format in Excel: columns, set-up and limits

29 August 2026 · 7 min read

Excel is the most common home for a snag list, and it does a lot right: sorting, filtering, counting, and a format everyone can open. This is how to set one up so it holds together on a live job, and where to expect it to give way.

The column order

Put them in this order. It reads left to right the way you actually work an item.

Ref | Block | Level | Room | Description | Trade | Severity | Raised | Target | Status | Closed | Photo ref | Notes

• Ref — a number that never changes and is never reused. If item 14 is closed, nothing else ever becomes item 14.
• Block / Level / Room — three columns, not one. Splitting them is what lets you filter to a single floor later; a single "Location" column cannot be filtered usefully.
• Description — the defect as observed, not the remedy you have in mind. "Hairline crack to plaster, 300mm, above architrave" beats "needs filling".
• Trade — the column you will issue by, so keep the values consistent.
• Severity — at minimum, whether it blocks handover.
• Raised / Target / Closed — three dates. The audit trail that protects you later.
• Photo ref — see the section on photographs below. This is the weak point.

Four set-up steps that pay for themselves

1. Freeze the header row. View → Freeze Panes → Freeze Top Row. Obvious, skipped by almost everyone, and you will scroll this sheet a thousand times.

2. Dropdowns on Trade, Severity and Status. Data → Data Validation → List. Free text in those three columns is what makes a sheet unfilterable: once you have "Plumber", "plumbing" and "Plumbing (M&E)" in the same column, no filter reaches all three.

3. Conditional formatting on Status. Closed rows in grey, anything past its target date in red. You want the state of the job visible without reading it.

4. A count block above the table. Three cells: =COUNTIF(J:J,"Open"), =COUNTIF(J:J,"Closed") and the percentage complete. This is the number the client asks for every week, and having it live saves you recounting.

Issuing to one trade

Filter the Trade column to the one you want, select the visible rows, then File → Print → Print Selection → Save as PDF. Name the file with the trade, the date and the revision: Snags_Electrical_R1_2026-08-29.pdf.

Keep the master unfiltered and issue copies. Sending the master itself invites edits you cannot see, and the moment two people hold two versions the numbering diverges — after which item 42 means two different defects and nobody can tell which.

Where Excel gives way

Photographs. This is the real limitation, and there is no clean fix:

• Images pasted into cells are floating objects, anchored loosely. The first time anyone sorts the sheet, they detach from their rows.
• Hyperlinks to a shared folder break as soon as the folder is renamed, reorganised, or opened by someone whose path differs.
• Sixty embedded photographs make the file slow to open and too big to email.

If you are staying in Excel, the least-bad approach is a strict naming convention — 014_Bed2_crack.jpg — with the ref number first, one folder per inspection, and the filename recorded in the Photo ref column. It works exactly as long as everyone follows it.

Revisions. A spreadsheet has one state: its current one. It cannot show what it said when you issued R1, so when a contractor says an item was never on the list, you have edits but no evidence. The workaround is discipline — save a dated, read-only copy every time you issue, and never edit an issued copy.

Two people at once. Shared workbooks help, but a filtered view is personal and a sort is not: one person sorting by trade while another types into row 40 is how items end up with the wrong description.

When to stop

Excel is the right tool for a short list, few photographs, one author. The signals that you have outgrown it are specific: you are spending evenings matching photographs to rows, you cannot answer "what did we issue in week two", or you are describing locations in words because there is nowhere to put a mark on the drawing.

At that point the fix is not a better spreadsheet. It is a format where the photograph and the position on the plan are part of the defect record rather than attachments to it. The format comparison is in snag list templates compared.