If you sell physical products on Etsy, Shopify, eBay, Amazon, Instagram or your own site, orders can slip: one ships late, another has no tracking number, and you're not sure which ones made money. A simple Excel tracker shows what's still to ship, what's on its way and what each order earned. This guide builds one with plain formulas.
Note: this is spreadsheet guidance only. Carrier and marketplace rules differ, so check theirs for delivery times and shipping requirements. It isn't tax, legal or accounting advice.
Sheet 1: Settings
| Cell | Value |
|---|---|
| B1 | Currency label, e.g. USD
|
| B2 | Handling days: how many working days you give yourself to ship (e.g. 2) |
Add two lists in the same sheet: your sales channels (A5 down) and your carriers (C5 down). Edit them to match what you actually use.
Sheet 2: Orders
One row per order:
| Col | Field |
|---|---|
| A | Order ID |
| B | Order date |
| C | Channel (dropdown) |
| D | Customer |
| E | Ship-to country |
| F | Item |
| G | Qty |
| H | Item price (each) |
| I | Shipping charged |
| J | Order revenue |
| K | Product cost (total) |
| L | Shipping paid |
| M | Fees (platform and payment) |
| N | Profit |
| O | Margin |
| P | Carrier (dropdown) |
| Q | Tracking number |
| R | Ship by |
| S | Shipped on |
| T | Delivered on |
| U | Status |
| V | Days late shipping |
Add the dropdowns with Data > Data Validation > List, pointing at your channel and carrier lists.
Calculate revenue, profit and margin
J2: =IF($B2="","",N(G2)*N(H2)+N(I2))
N2: =IF($B2="","",J2-N(K2)-N(L2)-N(M2))
O2: =IF(OR($B2="",N(J2)=0),"",N2/J2)
Quick check: 2 mugs at 18 each plus 6 shipping charged is revenue 42. With 14 product cost, 5 shipping paid and 3 fees, profit is 20 and margin is about 48%.
Put your fees in as a number you read from the marketplace or payment statement; the sheet doesn't guess them.
Work out when each order should ship
R2: =IF($B2="","",WORKDAY(B2,N(Settings!$B$2)))
WORKDAY skips Saturdays and Sundays. It doesn't know public holidays; add a holiday list as a third argument if you want it to.
A status that updates itself
U2: =IF($B2="","",IF(T2<>"","Delivered",IF(S2<>"","In transit",IF(TODAY()>R2,"LATE - ship now","To ship"))))
V2: =IF($B2="","",MAX(0,IF(S2<>"",S2,TODAY())-R2))
You only type two dates: when you shipped and when it arrived. The status follows. Use Conditional Formatting to colour "LATE - ship now" red and "Delivered" green.
A summary sheet
On a Summary sheet, count orders by status and add up money:
To ship: =COUNTIF(Orders!U2:U1001,"To ship")
Late: =COUNTIF(Orders!U2:U1001,"LATE - ship now")
In transit: =COUNTIF(Orders!U2:U1001,"In transit")
Delivered: =COUNTIF(Orders!U2:U1001,"Delivered")
Revenue: =SUM(Orders!J2:J1001)
Profit: =SUM(Orders!N2:N1001)
Margin: =IF(Revenue=0,0,Profit/Revenue)
Last 30 days: =SUMIFS(Orders!J2:J1001,Orders!B2:B1001,">="&(TODAY()-30))
Replace Revenue and Profit with the cells that hold those totals.
Then a small table by channel, listing each channel once, shows where the money comes from:
Orders: =COUNTIF(Orders!$C$2:$C$1001,D3)
Revenue: =SUMIFS(Orders!$J$2:$J$1001,Orders!$C$2:$C$1001,D3)
Profit: =SUMIFS(Orders!$N$2:$N$1001,Orders!$C$2:$C$1001,D3)
Make it a daily habit
At the start of the day, filter column U to "LATE - ship now" and "To ship". That's your packing list. When you ship, type the date and tracking number. When the carrier shows delivered, type that date.
Common mistakes
- Revenue and profit in the same column. Keep them apart.
- Forgetting fees. A sale can look good until the platform and payment fees come off.
- Typing channel names differently. Use the dropdown, or the channel table will miss rows.
- Deleting old orders. Filter them out of view instead; you'll want the history for returns and monthly totals.
Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.
Practical spreadsheet templates for small businesses, made with AI assistance.
Top comments (0)