Sales Order Tracking Excel Template: Setup and Tips

How-to basics · Updated November 20, 2026

A sales order tracking Excel template is a worksheet for recording each order, following its status, and checking key dates and amounts. Start with columns for Order ID, Order Date, Customer, Product, Quantity, Unit Price, Order Total, Status, Due Date, Ship Date, Days Open, and Notes; turn the range into an Excel Table so you can sort and filter orders as work progresses.

What a sales order tracker is for

A sales order tracker gives a small business or team one organized view of orders from receipt through fulfillment. It can help answer practical questions: Which orders are waiting to be processed? What is due to ship soon? Which customers have outstanding orders? How much order value has been recorded for a chosen period?

It is important to distinguish order tracking from inventory management, invoicing, and payment reconciliation. A tracker records the information you enter; it does not automatically reserve stock, create a legally compliant invoice, or confirm that a customer has paid. It is most useful as a lightweight coordination tool, especially when order volume and the number of people updating records are manageable.

For a straightforward version, use one row per order and one order ID per row. This works when each order can be summarized with a single product description or a combined order value. If customers can order several products with different quantities, consider keeping an order-summary sheet and a separate order-lines sheet. Repeating an order total on every product line can inflate sales summaries if you later add the repeated values.

Recommended worksheet columns

Keep the column names clear and consistent. A useful starting layout is:

  • Order ID: A unique identifier, such as SO-1042. Do not rely on the row number, because sorting can change row positions.
  • Order Date: The date the business accepted or entered the order. Decide which event this means and use the same rule consistently.
  • Customer: The customer name or an internal customer code. A code can reduce ambiguity when names are similar.
  • Product / SKU: A short description or product identifier. For multi-item orders, use a separate order-lines sheet rather than squeezing several products into one cell.
  • Quantity and Unit Price: The quantity ordered and the agreed price per unit. Keep prices numeric and use number formatting for currency.
  • Order Total: The line or order value calculated from quantity and unit price, using a clear rule for discounts, shipping, and tax if those are included.
  • Status: A controlled stage such as New, Processing, Ready to Ship, Shipped, Delivered, or Cancelled.
  • Due Date and Ship Date: The expected fulfillment date and the actual shipment date. Leave the ship date blank until an order ships.
  • Days Open and Notes: A calculated age for unresolved orders, plus brief details such as a delay or a customer request.

Do not store sensitive payment-card data in a tracking workbook. If you need payment progress, record a limited status such as Unpaid, Paid, or Refunded and keep payment processing in an appropriate system.

Build the template in Excel

In Excel for Microsoft 365 on Windows, put the headings in row 1 and enter orders starting in row 2. Select the range and choose Insert > Table, or press Ctrl+T; confirm that My table has headers is selected. On a Mac, Command+T opens the Create Table dialog in Excel, and the same header option applies. Tables provide filter buttons, consistent formatting, and automatically extend when you add rows directly below them.

Use a date format for the date columns, a currency format for Unit Price and Order Total, and a whole-number format for Quantity. Select the relevant cells and use the Number Format controls on the Home tab. Avoid typing currency symbols into the values themselves: entering 25 as a number and formatting it as currency is easier to calculate than storing text such as “$25 each.”

To make Status entries consistent, select the status cells and choose Data > Data Validation. In the dialog, set Allow to List and enter the permitted stages as the source, separated by commas, for example New,Processing,Ready to Ship,Shipped,Delivered,Cancelled. If the list will change, put the status options in a separate worksheet and use that range as the validation source. Data Validation helps prevent typos, but it does not stop someone from pasting invalid values into cells, so review imported or pasted records.

Choose a naming convention for order IDs and customer names before the sheet grows. A unique ID makes it easier to discuss an order without confusing two customers with similar names. If more than one person edits the workbook, agree who can change status, how cancellations are recorded, and whether old orders should ever be deleted. Usually, retaining cancelled records with a Cancelled status preserves a more useful history.

Useful formulas for totals and aging

With the suggested layout, put Quantity in column E, Unit Price in F, Order Total in G, Status in H, Order Date in B, and Days Open in K. Enter the first formula below in the indicated row. If the range is an Excel Table, Excel will commonly copy a calculated-column formula down the table as new rows are added.

With quantity in E2 and unit price in F2, enter this formula in G2. For an illustrative order of 3 units at 24 each, the result is 72 in the workbook’s currency.

=E2*F2

This basic calculation does not include discounts, tax, or shipping. If those affect the amount you need to track, add separate columns and define whether Order Total means merchandise value or the final customer charge. A consistently defined total is more useful than a more complicated formula whose meaning is unclear.

With Order ID in A2, Order Date in B2, Status in H2, and Days Open in K2, use this formula in K2 to show elapsed calendar days for orders that are not delivered or cancelled. If an open order was entered 10 days ago, the result is 10; delivered and cancelled orders show a blank. The value updates when Excel recalculates because it uses TODAY().

=IF(OR(A2="",H2="Delivered",H2="Cancelled"),"",TODAY()-B2)

Format K as a number, not a date. For a small summary area, you can also count open orders and flag late ones. These examples use whole-column references, so they include records added later. In a very large workbook, using a defined table range can be more efficient.

With Order ID in column A and Status in H, enter this formula in a summary cell to count rows with an ID that are neither delivered nor cancelled. If there are 12 such order rows, it returns 12.

=COUNTIFS(H:H,"<>Delivered",H:H,"<>Cancelled",A:A,"<>")

With Status in H, Due Date in I, and Order ID in A, use this formula to count identified orders that are not delivered or cancelled and have a nonblank due date earlier than today. For 2 qualifying late orders, it returns 2. A due date equal to today is not counted as late.

=COUNTIFS(H:H,"<>Delivered",H:H,"<>Cancelled",I:I,"<"&TODAY(),I:I,">0",A:A,"<>")

With Order Total in G and Status in H, use this formula to sum values for delivered rows. If those rows have totals of 72 and 40, it returns 112. This is recorded delivered order value, not proof of cash collected; payment status and accounting records are separate.

=SUMIFS(G:G,H:H,"Delivered")

Make the tracker easier to use

Filters are useful for reviewing a subset without rearranging the underlying data. Use the Table filter buttons to show only one status, a particular customer, or a date range. Sort by Due Date to see approaching commitments, but remember that a sort changes the visible order of records; it does not change the actual order dates or status.

Conditional formatting can draw attention to exceptions, such as a due date that has passed. On Windows, select the due-date cells and choose Home > Conditional Formatting > Highlight Cells Rules. A simple date rule can make overdue dates easier to spot, but it should match your business definition of late. For example, some teams consider an order late only if it has not shipped, while others use delivery date instead of ship date.

Freeze the heading row if the tracker is long: choose View > Freeze Panes > Freeze Top Row. Use concise notes and avoid turning the Notes column into a second, unstructured order database. If a detail needs to be reported or filtered regularly, give it its own column, such as Shipping Method or Payment Status.

Common problems and how to prevent them

  • Duplicate order IDs: Check for duplicates when combining files or entering orders. A duplicate can make a record hard to identify and can distort counts.
  • Inconsistent statuses: Variations such as “Shipped,” “ship,” and “sent” split filters and summaries. Use a dropdown and agree on what each stage means.
  • Dates stored as text: Text that looks like a date may not sort or calculate as expected. Enter a genuine Excel date and apply a date format; check imported data carefully.
  • Missing due dates: A tracker cannot identify a late order if no due date is entered. Make the date required when it is known, and distinguish blank from “not applicable.”
  • Overwritten formulas: Pasting data into a calculated column can replace formulas. Protect formula columns if appropriate, or periodically check that the Table formula still fills new rows.
  • Confusing order value with revenue received: A total in the order sheet may be a quoted or fulfilled amount, not a payment. Use separate fields and accounting records for payment and financial reporting.

Back up the workbook regularly and limit editing access to people who need it. If it is stored in OneDrive or SharePoint, version history and coauthoring may help teams review changes, but a shared file still needs clear ownership and data-entry rules.

When Excel is enough—and when to switch

Excel is a reasonable choice when the workflow is simple, the number of orders is modest, and a small number of people can maintain the same file. It is flexible: columns, filters, and summaries can be adapted without setting up a dedicated system. A spreadsheet can also be a useful prototype for deciding what information an order process actually needs.

Consider an order-management or inventory system when several users need simultaneous, controlled updates; stock must be reserved across channels; orders need automated shipping or customer notifications; or audit trails and role-based permissions matter. A spreadsheet can be shared, but it does not automatically provide the same safeguards and workflow integration as a system designed for order processing. Compare software based on the functions and service terms you need, not just the monthly price.

Moving from Excel does not mean the workbook was a mistake. The columns and status definitions can help specify requirements for a future system. Before migrating, remove obsolete fields, check for duplicate IDs, standardize dates and statuses, and decide which historical records must be retained.

Frequently Asked Questions

Can I use Excel to track sales orders for free?
Excel itself is a licensed Microsoft product, though it may be included in a Microsoft 365 plan or available through an organization. The template layout and formulas described here do not require a paid add-in. Check the terms of the Excel version and account you use.

Should each product in an order have its own row?
For a simple tracker, one row per order is easiest if a single product description and total are sufficient. For orders containing multiple products, use one row per product on an order-lines sheet and a separate order summary, or use an order-management system. Do not sum a repeated order-level total across multiple item rows.

How do I track whether an order has shipped?
Use a Status field and record a Ship Date when the shipment leaves. If you need to distinguish fulfillment from delivery, use separate Shipped and Delivered stages. A tracking number can be another field, but customer or carrier tracking updates will not appear automatically unless connected to a suitable service.

Can I track payments in the same workbook?
You can add a Payment Status field, such as Unpaid, Part Paid, Paid, or Refunded, and possibly a payment date. Keep the meaning of each value clear, and do not treat an order total or a Delivered status as evidence that payment was received. Reconcile financial records with the appropriate accounting or payment system.

Why does Days Open change from day to day?
The example uses TODAY(), which returns the current date when Excel recalculates. That makes the age update over time. If you need a permanent record of the age at a particular point, save a dated snapshot or record the relevant event date rather than relying only on a live formula.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.