A sales invoice register is a sheet with one row per invoice you issue: date, invoice number, customer, amount, tax shown, total, how much has been received and the payment mode. With a Received column and a Balance formula, you can see who owes you at a glance. The layout below works in Excel and Google Sheets.
What columns should the register have?
- A: Date of invoice
- B: Invoice number (running, no gaps)
- C: Customer
- D: Amount before tax
- E: Tax shown on the invoice (copy what it says)
- F: Total, with the formula =D2+E2
- G: Received so far
- H: Balance, with the formula =F2-G2
- I: Status (Paid, Part paid, Unpaid, Overdue), as a dropdown
- J: Payment mode (UPI, NEFT, cheque, cash)
- K: Notes
For the dropdown, select I2:I500, then use Data validation (in Excel) or Data, Data validation (in Google Sheets) and enter a list of the four statuses.
What does a filled sample look like?
These ten rows are invented for a freelance designer. The tax amounts are only sample figures; on a real invoice, copy what the invoice shows.
| Date | Invoice no | Customer | Amount | Tax shown | Total | Received | Status |
|---|---|---|---|---|---|---|---|
| 03-Apr-26 | D/26-27/001 | Kaveri Sweets | ₹12,000 | ₹2,160 | ₹14,160 | ₹14,160 | Paid |
| 09-Apr-26 | D/26-27/002 | Nilgiri Tea Co | ₹8,500 | ₹1,530 | ₹10,030 | ₹10,030 | Paid |
| 14-Apr-26 | D/26-27/003 | Arora Dental | ₹25,000 | ₹4,500 | ₹29,500 | ₹15,000 | Part paid |
| 19-Apr-26 | D/26-27/004 | Meera Books | ₹6,000 | ₹1,080 | ₹7,080 | ₹7,080 | Paid |
| 24-Apr-26 | D/26-27/005 | Studio Nine | ₹18,000 | ₹3,240 | ₹21,240 | ₹0 | Unpaid |
| 02-May-26 | D/26-27/006 | Kaveri Sweets | ₹4,500 | ₹810 | ₹5,310 | ₹5,310 | Paid |
| 08-May-26 | D/26-27/007 | Orchid Hotels | ₹30,000 | ₹5,400 | ₹35,400 | ₹35,400 | Paid |
| 12-May-26 | D/26-27/008 | Studio Nine | ₹9,000 | ₹1,620 | ₹10,620 | ₹0 | Overdue |
| 20-May-26 | D/26-27/009 | Nilgiri Tea Co | ₹15,000 | ₹2,700 | ₹17,700 | ₹17,700 | Paid |
| 27-May-26 | D/26-27/010 | Meera Books | ₹7,500 | ₹1,350 | ₹8,850 | ₹8,850 | Paid |
Check the totals: invoiced ₹1,59,890; received 14,160 + 10,030 + 15,000 + 7,080 + 5,310 + 35,400 + 17,700 + 8,850 = ₹1,13,530; balance ₹46,360, which is 14,500 on Arora Dental, 21,240 and 10,620 on Studio Nine.
Which formulas are useful?
- Total outstanding: =SUM($H$2:$H$500)
- Overdue only: =SUMIF($I$2:$I$500,"Overdue",$H$2:$H$500)
- One customer's balance: =SUMIF($C$2:$C$500,"Studio Nine",$H$2:$H$500)
- Invoiced in a month, with the month start date in M1: =SUMIFS($F$2:$F$500,$A$2:$A$500,">="&$M$1,$A$2:$A$500,"<"&EDATE($M$1,1))
How do you record part payments and corrections?
Do not edit the invoice amount when a customer pays in parts. Keep Total as billed and add each payment to Received. In the sample, Arora Dental owes ₹29,500 and has paid ₹15,000, so the Balance shows ₹14,500 until the next payment. If an invoice has to be corrected, do not overwrite the old row. Keep it, mark it cancelled in Notes, and issue a new numbered invoice. Your CA can tell you the right way to handle credit notes in your case.
How do you keep it accurate?
Fill the row when you issue the invoice, not at month end. When money arrives, update Received and Status the same day. Keep a PDF of each invoice in a folder named by financial year and use the invoice number as the file name, so a row and its PDF always match.
At month end, filter the Status column for Unpaid and Overdue, and send each customer a short reminder with the invoice number and the balance. Because the balance is a formula, you never have to calculate it by hand, and the sum at the foot of column H always matches the list you are chasing.
What should you do each week?
- Update Received for every payment that arrived.
- Turn Unpaid to Overdue when the due date passes.
- Send a polite reminder for each overdue line.
- Check that invoice numbers have no gaps.
This register covers money in only. SpendixAI is a spending tracker, not an invoicing tool, so keep it for the money-out side and leave client invoices in this sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


