Logistics & shipping

Customs Clearance Tracker

Follow every clearing request from the moment documents leave your hands, with turnaround per agent and the queries that are holding things up

Download the template — free

✓ No account, no email · ✓ Excel 2016+ and Google Sheets · ✓ Free for commercial use

Clearance is the leg where responsibility is hardest to pin down, because the file leaves your desk and the next thing you hear is either a release or a query. Tracking it means recording two things nobody usually writes down: when the documents actually went to the agent, and what came back. Once those exist, the turnaround per agent is arithmetic, and the conversation stops being about whose fault it is.

What the worksheet looks like

The actual columns, with sample rows

Clearances sheet: one row per declaration, turnaround and query ageing derived.
A	B	C	D	E	F	G	H	I	J
1	Ref	Agent	Entry type	Docs sent	Query raised	Query closed	Released	Days	Status
2	CL-2201	Adjei & Co	Import	2026-02-03			2026-02-07	4	Released
3	CL-2202	Adjei & Co	Import	2026-02-14	2026-02-17	2026-02-25	2026-02-27	13	Released
4	CL-2203	Nartey Clearing	Transit	2026-03-01	2026-03-05			11	In query

The formulas that do the work

Every one of these is in the file you download

=IF($D5="","",IF($G5="",$L$2-$D5,$G5-$D5)) Turnaround: documents sent until released, and for anything still open, until today. Measuring only closed files flatters every agent by hiding the ones still stuck.
=IF($D5="","",IF($G5<>"","Released",IF(AND($E5<>"",$F5=""),"In query","With agent"))) Status derived from what has happened, not typed. An open query outranks everything else because it is the one state where the agent is waiting on you.
=IF(AND($E5<>"",$F5=""),$L$2-$E5,IF(AND($E5<>"",$F5<>""),$F5-$E5,"")) Days a query has been open, or how long it took to close. Query time is usually the whole difference between a four-day clearance and a thirteen-day one.
=IFERROR(AVERAGEIFS(Clearances!$H$5:$H$204,Clearances!$B$5:$B$204,$A5,Clearances!$I$5:$I$204,"Released"),"") Average turnaround per agent, released files only, so an open file does not quietly inflate someone’s record before it is resolved.
=IF($A5="","",COUNTIFS(Clearances!$B$5:$B$204,$A5,Clearances!$I$5:$I$204,"In query")) Open queries per agent — the number worth acting on today.

Four days or thirteen, with the same agent

A worked example with real numbers

Adjei & Co clear CL-2201 in four days. Two weeks later CL-2202 takes thirteen. The instinct is to blame the agent and shop around. The query columns say otherwise: a query was raised on 17 February and not closed until 25 February — eight days of the thirteen, waiting on a document from your side. The agent's own working time was five days, not thirteen. Without those two columns, the same table would have justified changing agent and changed nothing.

What is in the workbook

Every tab, and what it holds

Every tab in the workbook and what it is for.
Tab	Contents
README	What to fill in and what is calculated, with the colour key.
Clearances	One row per declaration, 200 rows, with turnaround, query ageing and status derived.
By agent	Average turnaround on released files, open queries and volume per agent.
Open	Everything not yet released, oldest first.

Features and related templates

What is included, and what to look at next

What it does

Turnaround measured from documents sent, not from arrival
Open files counted at today's date, not excluded
Query time separated from agent working time
Per-agent averages and open-query counts
Excel 2016+ and Google Sheets

Related templates

Need it built around your own data?

Your own lanes, carriers and milestone names
Connected to your forwarder's exports
Delivered within 24 hours

Get your Excel job done

Questions about this template

The ones that come up most

Why measure from when documents were sent?

Because that is the moment the clock starts for the agent. Measuring from vessel arrival mixes your own document preparation into their score, which makes the number useless for choosing between agents.

We use several entry types. Does that matter?

It matters a lot — a transit entry and a full import entry are not comparable. The Entry type column is there so you can average within a type rather than across all of them.

Can I track duty and taxes here too?

Add columns to the right of Released; nothing in the formulas depends on the table ending where it does. If you want duty reconciled against payments, that is closer to the accounts receivable aging template.