DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on Originally published at ashishexcelnotes.hashnode.dev

How to track export shipment documents and payments in Excel (for small exporters)

#excel #smallbusiness #productivity #tutorial

A small export order involves a lot of paperwork that has to match: proforma invoice, commercial invoice, packing list, shipping bill, bill of lading or airway bill, certificate of origin, and the payment terms you agreed. When you're handling your first few shipments, the risk isn't usually a single big mistake. It's a document nobody chased, or a payment date nobody noticed. A simple Excel tracker helps you see what's still open.

Note: which documents you need depends on the product, the destination, the Incoterm and your buyer's bank. This article covers spreadsheet mechanics only. It is not export, customs, tax or legal advice; confirm requirements with your CHA, freight forwarder, bank or the official sources.

Sheet 1: Shipments

One row per shipment:

Col Field
A Shipment ID (e.g. EXP-001)
B Buyer
C Country
D Invoice no.
E Invoice date
F Currency
G Invoice value
H Incoterm (dropdown: EXW, FOB, CIF, DAP…)
I Advance received
J Balance due
K Balance due date
L Docs complete?
M Payment status

Balance (J2):

=IF($A2="","",G2-N(I2))
Enter fullscreen mode Exit fullscreen mode

Payment status (M2):

=IF($A2="","",IF(J2<=0,"Received",IF(AND(K2<>"",TODAY()>K2),"Overdue","Open")))
Enter fullscreen mode Exit fullscreen mode

Keep currency as its own column and never add USD and EUR invoices together. Totals per currency use SUMIFS with a currency condition.

Sheet 2: Document checklist

This is the useful part. Make a long table with one row per shipment × document:

Col Field
A Shipment ID
B Document
C Status (dropdown: Pending, Done, NA)
D Date done
E Note (who has it, file name)

For each new shipment, paste your standard document list (the 10–15 you normally need) and set the ones that don't apply to NA. "NA" matters: it's the difference between "we decided this isn't needed" and "we forgot".

Add Data Validation on column C: Data > Data Validation > List, Pending,Done,NA.

Linking the two sheets

Back on Shipments, column L counts what's still pending:

=IF($A2="","",COUNTIFS(Docs!$A:$A,$A2,Docs!$C:$C,"Pending"))
Enter fullscreen mode Exit fullscreen mode

Then a readable label in a new column N:

=IF($A2="","",IF(COUNTIFS(Docs!$A:$A,$A2)=0,"No checklist",IF(L2=0,"Ready","HOLD: "&L2&" pending")))
Enter fullscreen mode Exit fullscreen mode

The "No checklist" case catches a shipment where you forgot to add document rows at all, which would otherwise show 0 pending and look ready.

Colour column N with conditional formatting: text containing "HOLD" in red, "Ready" in green.

Costs and contribution

Add cost columns to Shipments: freight, insurance, CHA/forwarder charges, bank charges. Then:

Total cost:   =SUM(P2:S2)
Contribution: =IF($A2="","",G2-T2)
Enter fullscreen mode Exit fullscreen mode

Keep costs in the same currency as the invoice, or add an explicit rate column you type yourself. Don't let a formula guess an exchange rate; the rate you actually got from the bank is the one that matters.

A weekly view

On a Start sheet, a few counts tell you where to look:

Shipments on HOLD:  =COUNTIF(Shipments!N:N,"HOLD*")
Overdue payments:   =COUNTIF(Shipments!M:M,"Overdue")
Due in next 7 days: =COUNTIFS(Shipments!K:K,">="&TODAY(),Shipments!K:K,"<="&TODAY()+7,Shipments!J:J,">0")
Enter fullscreen mode Exit fullscreen mode

Reusing your document list

Typing 14 document names for every shipment gets old fast. Keep a DocList sheet with your standard list in column A and, in column B, a short note on when each one applies (for example "only if buyer asks", "only for LC payments"). For a new shipment, copy A2:A15, paste it into the Docs sheet, and fill the Shipment ID beside each row by typing it once and double-clicking the fill handle.

If you ship to two very different markets, keep two lists side by side (say "Standard" and "LC shipments") and copy the one that fits. Over time the notes column becomes your own record of what each buyer and bank actually asked for, which is worth more than any generic list.

Protect the formulas

Select the formula columns (balance, status, pending count, contribution), shade them light grey, then Format Cells > Protection and keep them locked while unlocking the input columns. Review > Protect Sheet with no password stops accidental overwrites without getting in your way.

Common mistakes

  1. Invoice and packing list disagree on quantity or weight. Add a check column comparing them if you type both.
  2. Ticking "Done" with no file. Use the Note column to record where the document is saved.
  3. One sheet per shipment. It looks tidy but you can't count across shipments. Use one long checklist table.
  4. Deleting old shipments. Filter them; next year's buyer often wants the same documents as last year's. This article was drafted with AI tools. It's general spreadsheet guidance, not export, customs, tax or legal advice.

Top comments (0)