An udhaar khata in Excel is a single ledger tab with date, customer, credit sale and payment received, plus a summary tab that uses SUMIFS for each customer's balance and an ageing split into 0-30, 31-60 and 60+ days. It replaces the paper register and shows at a glance who owes how much and for how long.
How is the ledger tab laid out?
| Column | Heading | Notes |
|---|---|---|
| A | Date | Date of the sale or payment |
| B | Customer | Exactly one spelling per customer |
| C | Sale on credit (₹) | Amount added to what they owe |
| D | Payment received (₹) | Amount they paid |
| E | Note | Bill number, items, UPI reference |
One row per event, never more. A sale of ₹800 on credit is a row with 800 in column C, and a part-payment later is a row with the amount in column D. Use a drop-down for customer names; a spelling such as Sharma and Sharma ji becomes two different people to the formulas.
How do I get each customer's balance?
On a Summary tab, list customers in column A. Balance in B2:
- =SUMIFS(Ledger!$C$2:$C$1000, Ledger!$B$2:$B$1000, A2) - SUMIFS(Ledger!$D$2:$D$1000, Ledger!$B$2:$B$1000, A2)
That is total credit given minus total payments received. For the shop's total outstanding, add up column B. To see a running balance within the ledger itself, add a column F with the customer's cumulative balance: =SUMIFS($C$2:C2, $B$2:B2, B2) - SUMIFS($D$2:D2, $B$2:B2, B2), using the expanding range so each row shows the balance after that entry.
How do I add ageing?
Ageing needs one assumption: payments settle the oldest credit first. With that, the balance is made up of the most recent sales. Columns C, D and E on the Summary tab hold the three buckets.
- 0-30 days in C2: =MIN(B2, SUMIFS(Ledger!$C$2:$C$1000, Ledger!$B$2:$B$1000, A2, Ledger!$A$2:$A$1000, ">"&TODAY()-30))
- 31-60 days in D2: =MIN(B2-C2, SUMIFS(Ledger!$C$2:$C$1000, Ledger!$B$2:$B$1000, A2, Ledger!$A$2:$A$1000, ">"&TODAY()-60, Ledger!$A$2:$A$1000, "<="&TODAY()-30))
- 60+ days in E2: =B2-C2-D2
The first bucket is the smaller of the balance and the credit given in the last 30 days; the second takes what remains, up to the credit of the next 30 days; and whatever is left is older than 60 days.
What does the summary look like with five customers?
These customers and amounts are made up, as on one particular day.
| Customer | Balance | 0-30 days | 31-60 days | 60+ days |
|---|---|---|---|---|
| Sharma ji | ₹1,200 | ₹1,200 | ₹0 | ₹0 |
| Ramesh | ₹3,450 | ₹900 | ₹1,550 | ₹1,000 |
| Meena | ₹600 | ₹600 | ₹0 | ₹0 |
| Kiran Stores | ₹8,200 | ₹2,200 | ₹3,000 | ₹3,000 |
| Anil | ₹450 | ₹0 | ₹0 | ₹450 |
The balances add up to 1,200 + 3,450 + 600 + 8,200 + 450 = ₹13,900. The buckets add up to 4,900 + 4,550 + 4,450 = ₹13,900 too, a useful check. Notice that Kiran Stores has the largest amount and ₹3,000 beyond 60 days, so that is the first conversation. Anil owes only ₹450, but all of it is old, which a plain balance would hide.
How do I collect without losing goodwill?
- Sort the Summary by the 60+ column and start from the top.
Visit or call with the exact amount and the dates it relates to. Offer to settle part now and the rest by a date you note. Record each promise in the Note column. Where the customer pays by UPI, put the reference in the note, so a dispute is settled by the record. Ask regular customers to initial or acknowledge their monthly balance.
How do I keep the ledger safe?
- Save the file with a date in its name once a week and keep a copy on a second device
- Protect the sheet with a pass-code on the phone or laptop it lives on, so customer balances do not leak
- Never delete a row; add a correcting row with a note
- Compare the total outstanding with the paper register for the first month
Weekly routine
- Enter all credit sales and payments from the day book
- Read the 60+ column
- Make the calls
- Note outcomes
Useful ideas start with a clearer picture of your own spending.
Explore your reports

