DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on Originally published at ashishexcelnotes.hashnode.dev

How to track customer orders and payments in Excel (free template approach)

#excel #smallbusiness #productivity #beginners

If you sell on WhatsApp, Instagram or at a stall, orders tend to live in chats and payments in your bank app. The question "who still owes me, and for what?" ends up taking half an hour. One Excel table with a few formulas answers it in seconds. Here's how to set it up.

Make it a real Excel Table

Type these headers in row 1, select them plus a few blank rows, and press Ctrl+T (tick "My table has headers"). Name the table Orders in Table Design > Table Name.

Field What goes in
Order date Date
Order no. e.g. 1001, 1002
Customer Name
Phone Optional
Item What they bought
Qty Number
Rate Price per unit
Amount Formula
Paid Amount received so far
Balance Formula
Pay mode Dropdown: Cash, UPI, Bank, Card
Delivery Dropdown: Pending, Shipped, Delivered
Status Formula

Tables are worth it because formulas fill down automatically when you add a row, and references read like words.

The formulas

Amount:

=[@Qty]*[@Rate]
Enter fullscreen mode Exit fullscreen mode

Balance:

=[@Amount]-N([@Paid])
Enter fullscreen mode Exit fullscreen mode

Status:

=IF([@Amount]=0,"",IF([@Balance]<=0,"Paid",IF(N([@Paid])>0,"Part-paid","Unpaid")))
Enter fullscreen mode Exit fullscreen mode

Days since order (useful for chasing):

=IF([@Balance]>0,TODAY()-[@[Order date]],"")
Enter fullscreen mode Exit fullscreen mode

Dropdowns

Select the Pay mode column, Data > Data Validation > List, and type Cash,UPI,Bank,Card. Do the same for Delivery. Consistent values are what make the summaries below work.

Colour what needs attention

Select the Status column, Home > Conditional Formatting > Highlight Cells Rules > Equal To: "Unpaid" red, "Part-paid" amber. Add a rule on Delivery = "Pending" if shipping is your bottleneck.

Customer balances in one view

On a new sheet, list customers in column A (in Excel 365/2021, =UNIQUE(Orders[Customer]) does it for you). Then:

Owed:   =SUMIFS(Orders[Balance],Orders[Customer],A2)
Orders: =COUNTIFS(Orders[Customer],A2)
Oldest unpaid (days): =MAXIFS(Orders[Days since order],Orders[Customer],A2)
Enter fullscreen mode Exit fullscreen mode

MAXIFS needs Excel 2019 or later. Sort by "Owed", largest first, and you have your follow-up list.

Monthly sales and collections

Add a helper column Month in the table:

=TEXT([@[Order date]],"yyyy-mm")
Enter fullscreen mode Exit fullscreen mode

Then a quick summary:

Sales:     =SUMIFS(Orders[Amount],Orders[Month],"2026-10")
Collected: =SUMIFS(Orders[Paid],Orders[Month],"2026-10")
Enter fullscreen mode Exit fullscreen mode

Or insert a pivot table: Rows = Month, Values = Sum of Amount, Sum of Paid, Sum of Balance.

Part payments

If a customer pays in instalments, there are two ways:

  1. Simple: just update the Paid cell (e.g. 500 becomes 1200). Fine for a few orders.
  2. Proper: a second table, Payments (Date, Order no., Amount, Mode). Then the Paid column becomes: 
=SUMIFS(Payments[Amount],Payments[Order no.],[@[Order no.]])
Enter fullscreen mode Exit fullscreen mode

This keeps a dated record of every payment, which helps when a customer says "I already paid".

Find an order fast

Above the table, add a search cell (say B1) and a result line. For an order number:

=IFERROR(INDEX(Orders[Balance],MATCH($B$1,Orders[Order no.],0)),"Not found")
Enter fullscreen mode Exit fullscreen mode

When a customer messages "what do I owe?", type their order number in B1 and answer in seconds. For a name search, use the table's own filter: click the Customer header arrow and type part of the name in the search box.

Keep a backup

A single file is a single point of failure. Once a week, File > Save As a dated copy (for example "Orders 2026-10-25.xlsx") to a second location such as a cloud drive or a pen drive. It takes ten seconds and saves you from a corrupted file or a lost phone.

Common mistakes

  1. Typing customer names differently. "Priya", "Priya S" and "priya" become three customers. Keep a customer list and use a dropdown.
  2. Merging cells for neat headings above the table. It breaks sorting and filters; keep headings outside the table.
  3. Deleting paid orders. Filter them out instead; history is useful.
  4. One sheet per month. One table with a Month column is far easier to summarise.

A two-minute daily habit

At the end of the day: add today's orders, update Paid from your UPI/bank app, then filter Status to "Unpaid". That's your follow-up list for tomorrow.
This article was drafted with AI tools. It's general spreadsheet guidance, not accounting, tax or legal advice.

Top comments (0)