BuildQuantify logoBuildQuantify
Excel Tutorials / Punch List Tracker in Excel
COST TRACKING

Punch List Tracker in Excel

7 min read · Template referenced below

Punch lists tend to live in someone's notebook or a dozen text messages. Centralizing them in a tracked spreadsheet makes closeout status visible to the whole team instead of just whoever wrote it down.

1
Set up the core columns.
Item, Location, Trade, Assigned To, Date Identified, Status, Date Closed.
2
Add a Status dropdown.
Select the Status column → Data → Data Validation → List → type Open,In Progress,Closed. This keeps status entries consistent so formulas can count them reliably.
3
Count open items live.
=COUNTIF(StatusColumn,"Open") gives a running count of unresolved items — put this at the top of the sheet so it's the first thing anyone sees.
4
Calculate percent complete.
=COUNTIF(StatusColumn,"Closed")/COUNTA(StatusColumn) shows what fraction of the punch list is actually resolved, formatted as a percentage.
5
Color-code rows by status.
Conditional Formatting → New Rule → formula =$F2="Open" (adjust the column letter) with a red fill; a second rule for "Closed" with green. Entire rows update automatically as status changes.
Add a "Days Open" column with =IF(Status="Closed",DateClosed-DateIdentified,TODAY()-DateIdentified) — this surfaces items that have been sitting open too long, not just how many exist.

A live punch list like this replaces the walk-through-and-guess approach to closeout with an actual, shareable record of what's left.

Worked example: 25 items at walk-through

Say the final walk-through produced 25 items, and after a week the Status column contains 14 Closed, 6 In Progress and 5 Open. The formulas then show:

MetricFormulaResult
Open items=COUNTIF(F:F,"Open")5
Percent complete=COUNTIF(F:F,"Closed")/COUNTA(F2:F26)56%
Unresolved electrical items=COUNTIFS(C:C,"Electrical",F:F,"<>Closed")depends on your data

The COUNTIFS version answers the question every superintendent gets asked: which trade is holding up closeout? Add one row per trade at the top of the sheet and the list becomes a live scoreboard.

Common mistakes

  • Free-text status entries. "Done", "done " and "Complete" are three different values to a formula. Use the dropdown so only the agreed words can be entered. Trailing spaces are the usual culprit when a count looks wrong.
  • Counting blank rows. COUNTA over an entire column includes the header. Use an exact range, or an Excel Table, so the denominator is correct.
  • No photo reference. Add a Photo column with a file name or link so the person fixing the item sees exactly what was flagged.

Frequently asked questions

Can several people update the same punch list?

Yes. Store the workbook in OneDrive or SharePoint and use co-authoring, or share a Google Sheets copy. Avoid emailing copies back and forth, because versions diverge quickly.

How do I print only open items?

Filter the Status column to Open and In Progress, then print the active sheet. The filtered rows are the only ones that print.

Back to all tutorials →
← Back to all tutorials