SMALL BUSINESS · EXCEL & GOOGLE SHEETS
Order Tracking Spreadsheet: How to Track Customer Orders in Excel or Google Sheets
November and December bring more orders than any other months, and that is when a missed order costs the most: a late gift is a refund, a bad review or both. An order tracking spreadsheet keeps every order in one list with its status, its ship-by date and what you actually earned on it. Here is how to set one up: the columns, the status dropdowns, the formulas that flag late orders, and a checker you can try below.
THE SHORT ANSWER
What an order tracking spreadsheet needs to do
An order tracking spreadsheet is a table with one row per order: who ordered, what, when, for how much, which stage it is in and when it must ship. Three formulas turn the list into a working tool: a ship-by date from your processing time, a late flag that lights up when that date passes without a shipment, and profit per order after fees, materials and postage. A second tab counts orders by status and adds up sales by month.
= 1 order
Multi-item orders get one line per item with the same order ID.
6 + 3
Six steps from New to Delivered, plus On hold, Cancelled and Refunded.
Ship-by
Order date plus processing time in business days: WORKDAY.
Dashboard
COUNTIFS by status, SUMIFS by month and channel.
Why bother if Etsy or Shopify already lists your orders? Because the marketplace view stops at its own orders. The spreadsheet is where orders from Etsy, your website, Instagram messages and a craft fair end up in one queue, with your own stages (like "in production") and your real costs, which no order page shows.
STEP 1
The columns to include
Start with the columns below on a tab called Orders. Columns marked FORMULA fill themselves; everything else is typed or picked from a dropdown. The letters are the ones the formulas in this guide use, so if you build the sheet in this order you can copy them as they are.
| Col | Column | What goes in it |
|---|---|---|
| A | Order ID | The marketplace order number, or your own (2026-0412). Never reuse one. |
| B | Order date | The date the order was paid. A real date, not text, or the formulas break. |
| C | Channel | Dropdown: Etsy, Website, Instagram, Market, Wholesale. |
| D | Customer | Name or username. Keep addresses in the marketplace if you can (see the FAQ on privacy). |
| E | Item / SKU | One item per row; personalization notes go in the Notes column. |
| F | Qty | Number of units. |
| G | Unit price | Price per unit before discount. |
| H | Shipping charged | What the customer paid for shipping (0 for free shipping). |
| I | Discount | Coupon or sale amount in dollars. |
| J | Order total FORMULA | Units × price + shipping − discount. Leave sales tax out of it. |
| K | Fees | Marketplace and payment fees for this order (from the order page or payout report). |
| L | Product cost | Materials or wholesale cost of what you sent. |
| M | Label cost | What you paid for postage and packaging. |
| N | Profit FORMULA | Order total minus fees, product cost and label. |
| O | Processing days | Business days you promised to ship within (your listing's processing time). |
| P | Ship-by FORMULA | The deadline that drives the late flag. |
| Q | Status | Dropdown, see step 2. |
| R | Shipped date | Date the carrier got the package (first scan). |
| S | Carrier + tracking | USPS, UPS, FedEx and the tracking number. |
| T | Late flag FORMULA | LATE, Shipped late or On time. |
| U | Notes | Personalization, gift message, special requests. |
STEP 2
Order statuses and how to make the dropdown
Typed statuses drift ("shipped", "Shipped ", "sent") and then COUNTIFS stops counting them. A dropdown keeps one spelling. For most product sellers these stages cover the whole life of an order:
- NewOrder received, not yet checked.
- PaidPayment cleared, details confirmed.
- In productionBeing made or personalized. Skip it if you only resell stock.
- PackedLabel bought, waiting for pickup or drop-off.
- ShippedFirst carrier scan; shipped date filled in.
- DeliveredTracking shows delivered.
Add three side statuses that are not steps: On hold (waiting for the customer's answer), Cancelled and Refunded. Keep them in the same list so they can be counted, and exclude them from the late flag.
Excel
- Make a Lists tab and type the nine statuses in A2:A10 (channels in B2:B6).
- Select the Status column on the Orders tab, then Data › Data Validation.
- Allow: List; Source:
=Lists!$A$2:$A$10. Keep "In-cell dropdown" ticked. - On the Error Alert tab choose Stop, so a typo can't get in.
Google Sheets
- Select the Status column, then Insert › Dropdown (or Data › Data validation › Add rule).
- Criteria: Dropdown (from a range) and pick
Lists!A2:A10, or type the options directly. - Give each option a color: the chips then show the stage at a glance.
- Under Advanced options choose Reject the input for invalid data.
STEP 3
Formulas: order total, ship-by date, late flag and profit
Put these in row 2 and fill down. They work the same in Excel and Google Sheets. WORKDAY and NETWORKDAYS skip Saturdays and Sundays; add a Holidays tab with dates like Thanksgiving and Christmas and pass it as the last argument, or those days count as working days.
| Column | Formula (row 2) | What it does |
|---|---|---|
| J · Order total | =IF(B2="","",F2*G2+H2-I2) | Units × price + shipping charged − discount; stays blank on empty rows so averages aren't dragged down by zeros. |
| N · Profit | =IF(B2="","",J2-K2-L2-M2) | What the order really earned. Profit margin: =IF(J2=0,"",N2/J2). |
| P · Ship-by | =IF(B2="","",WORKDAY(B2,O2,Holidays!$A:$A)) | Order date plus your processing time in business days. |
| T · Late flag | =IF(OR(B2="",Q2="Cancelled",Q2="Refunded"),"",IF(R2="",IF(TODAY()>P2,"LATE",""),IF(R2>P2,"Shipped late","On time"))) | LATE while an overdue order is still unshipped; afterwards it records whether it went out on time. |
| Days to ship | =IF(R2="","",NETWORKDAYS(B2,R2,Holidays!$A:$A)-1) | Business days from order to first scan; average it to see your real processing time. |
| Days left | =IF(OR(R2<>"",P2=""),"",NETWORKDAYS(TODAY(),P2,Holidays!$A:$A)-1) | Business days until the ship-by date (negative = overdue). |
Example. An order placed on Monday, November 30, 2026 (Cyber Monday) with a 3-business-day processing time must ship by Thursday, December 3. The customer paid $48 including shipping; fees were $4.62, materials $14 and the label $6.40, so the order earned $22.98, a 47.9% margin. The checker below starts with these numbers.
Conditional formatting: make late orders impossible to miss
Select the data rows (for example A2:U1000) and add a formula rule. Excel: Home › Conditional Formatting › New Rule › Use a formula. Google Sheets: Format › Conditional formatting › Custom formula is. The dollar sign before the column letter makes the whole row change color:
| Color | Rule | Means |
|---|---|---|
| RED | =$T2="LATE" | Past the ship-by date and not shipped. Ship it or message the customer today. |
| AMBER | =AND($R2="",$P2<>"",$P2-TODAY()<=1,$P2>=TODAY()) | Due today or tomorrow. |
| GREY | =OR($Q2="Delivered",$Q2="Cancelled",$Q2="Refunded") | Closed orders fade out so open ones stand out. |
Put the red rule first. In Google Sheets the first matching rule wins; in Excel, tick "Stop If True" on it. Then sort or filter by Ship-by, and the top of the sheet becomes today's packing list.
STEP 4
Monthly totals and a simple dashboard
On a second tab, a handful of formulas answer the questions you ask every week. They read the Orders tab, so nothing is typed twice:
| Question | Formula |
|---|---|
| How many orders are in each status? | =COUNTIFS(Orders!$Q:$Q,A2) next to each status name in A2:A10 |
| How many are late right now? | =COUNTIFS(Orders!$T:$T,"LATE") |
| Sales in November 2026 | =SUMIFS(Orders!$J:$J,Orders!$B:$B,">="&DATE(2026,11,1),Orders!$B:$B,"<"&DATE(2026,12,1)) |
| Profit in November 2026 | The same formula with Orders!$N:$N as the first range |
| Orders in a month | =COUNTIFS(Orders!$B:$B,">="&DATE(2026,11,1),Orders!$B:$B,"<"&DATE(2026,12,1)) |
| Sales by channel | =SUMIFS(Orders!$J:$J,Orders!$C:$C,"Etsy") |
| On-time shipping rate | =COUNTIFS(Orders!$T:$T,"On time")/(COUNTIFS(Orders!$T:$T,"On time")+COUNTIFS(Orders!$T:$T,"Shipped late")) |
| Average order value | =AVERAGEIFS(Orders!$J:$J,Orders!$Q:$Q,"<>Cancelled") |
Build a month column once (12 rows with the first day of each month in A, the SUMIFS pointing at A2 and EDATE(A2,1)) and you have the whole year for a column chart. The on-time rate matters on Etsy: its Star Seller program counts orders shipped on time with tracking, and the bar third-party guides cite is 95%; check the current standard in your Shop Manager.
Two habits keep the totals honest. Count refunds as their own rows or zero out the order (otherwise November sales look better than they were), and keep sales tax out of the order total. When Etsy or Shopify collects tax as the marketplace facilitator, it was never your income; it belongs in its own column if you need it at all. At year end the same tab feeds your year-end bookkeeping checklist.
Q4 ORDER RUSH
Getting through the holiday order rush
From Black Friday (November 27, 2026) to mid-December, a small shop can see several weeks' worth of orders in a few days. The spreadsheet helps in three ways.
- Publish a last-order date, and calculate it. Take the carrier's last send-by date for Christmas delivery and subtract your processing time:
=WORKDAY(send_by_date,-processing_days,Holidays!$A:$A). With a 3-day processing time and a December 17 send-by date, the last safe order day is Monday, December 14. This year's USPS and FedEx dates are in our Christmas planning checklist; check the carriers' own pages again before you announce yours, because they can change. - Be honest about processing time. If made-to-order items take longer in December, raise the processing days in your listings and in column O for new orders. The ship-by date follows, and you never promise what you can't ship.
- Work from the ship-by date, not the order date. Sort by column P each morning. Orders from wholesale or local pickup with later deadlines drop down; rush items rise to the top.
FREE CHECKER
Ship-by date, late status and profit for one order
The same logic as the spreadsheet formulas: business days only (no holidays), profit after fees, materials and postage. Leave "Shipped on" empty for an order that hasn't gone out yet.
Fees vary by platform and order; for Etsy's listing, transaction, payment processing and offsite ads fees, see how much Etsy takes per sale.
THE OTHER SIDE
Spreadsheet or order management software?
A spreadsheet is not the right tool forever, and it's fair to say when. Your marketplace already has an order page (Etsy's Orders & Shipping, Shopify's Orders), and dedicated order management or shipping apps connect several channels, buy labels and sync stock. The question is what you need on top of that.
A spreadsheet is enough when…
- you sell on one or two channels and enter orders in a few minutes a day;
- you need your own stages (proofs, production, personalization) the marketplace doesn't have;
- you want profit per order, not just sales, and a history you own;
- custom, local or wholesale orders never pass through a checkout.
Look at software when…
- you copy the same order by hand into more than one place every day;
- stock has to stay in sync across channels so you don't oversell;
- several people pack orders at the same time;
- you print dozens of labels a day and want rates compared automatically.
There is a middle way: export orders as CSV (Shopify: Orders › Export; Etsy: Shop Manager › Settings › Options › Download Data), paste them into a Raw tab, and let the Orders tab look them up. You keep the marketplace as the source of truth and the spreadsheet for costs, stages and reporting. For stock levels and reorder points, pair it with an inventory count routine; for tax set-asides on the profit column, see how much to set aside for taxes.
READY-MADE TEMPLATE
An order tracker for Excel and Google Sheets
Everything above can be built from a blank sheet. If you'd rather start with the columns, status and priority dropdowns, color coding and a dashboard already set up, there is a ready-made Order Tracker; check the product page for current features.
Order Tracker Spreadsheet Template
The current listing describes an order log with product, quantity, price, discount, revenue, customer, delivery method, status, priority and shipping dates; formulas for revenue and order value; color-coded status and priority fields; and dashboards for orders by status and priority, monthly and annual revenue and top-selling products. Currency, delivery methods and statuses are editable.
- Status and priority dropdowns with colors
- Revenue, average order value and order counts
- Monthly and annual breakdowns, top products
Tracking stock or repeat customers too? See the Inventory Tracker Spreadsheet Template for Google Sheets & Excel and the Client Tracker + CRM, or browse all small business templates.
Product features and compatibility are listed on the product page and may change.
FAQ
Order tracking spreadsheet questions
How do I create an order tracking spreadsheet?
Make one row per order with columns for order ID, order date, channel, customer, item, quantity, price, shipping, discount, order total, fees, costs, processing days, ship-by date, status, shipped date, carrier and tracking. Add a status dropdown, a WORKDAY formula for the ship-by date, a late flag and a profit column, then a second tab with COUNTIFS by status and SUMIFS by month.
What columns should an order tracker have?
At minimum: order ID, order date, customer, item, quantity, order total, status, ship-by date, shipped date and tracking number. Add fees, product cost and label cost if you want profit per order, and a channel column if you sell in more than one place.
How do I add a status dropdown in Google Sheets?
Select the status column, choose Insert › Dropdown, enter the options (New, Paid, In production, Packed, Shipped, Delivered, On hold, Cancelled, Refunded) or point to a range, give each a color and set invalid input to Reject. In Excel, use Data › Data Validation › List.
How do I flag late orders in Excel?
Calculate a ship-by date with =WORKDAY(order_date, processing_days, holidays), then use =IF(shipped_date="", IF(TODAY()>ship_by, "LATE", ""), IF(shipped_date>ship_by, "Shipped late", "On time")) and a conditional formatting rule that colors the row red when the flag says LATE.
Is a spreadsheet good enough to track orders for a small business?
Yes, for most shops with one or two sales channels that enter orders by hand in a few minutes a day. Move to order management software when you re-type orders into several places daily, need stock synced across channels, or have several people packing at once.
Is it safe to keep customer details in a spreadsheet?
Keep as little as you need: a name or username and the order number is usually enough, because the address stays in the marketplace. Never store full card numbers. Share the file only with people who pack orders, use your account's two-step verification, and protect formula columns so they aren't overwritten.
Sources
- Federal Trade Commission, Business Guide to the FTC's Mail, Internet, or Telephone Order Merchandise Rule, and the rule text, 16 CFR Part 435 (checked October 13, 2026).
- Microsoft Support: WORKDAY, NETWORKDAYS, COUNTIFS, Apply data validation to cells.
- Google Docs Editors Help: Create an in-cell dropdown list, Use conditional formatting rules in Google Sheets.
- Shopify Help Center, Exporting orders.
- Etsy Star Seller criteria (95% on-time shipping with tracking, 95% message response, 4.8 rating) as summarized by third-party seller guides in 2026; Etsy publishes the current standard in Shop Manager.
Calendar dates and the worked example were calculated for this guide. General information, not legal or tax advice.
