Customer Credit (Khata) Book in Excel: Udhaar Balance per Customer with SUMIFS

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: customer-credit-khata-book-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Raja's khata from 1 September to 14 October 2026 (made-up values):

₹7,620 is out with customers. Salman's ₹3,900 is above the ₹3,000 reminder limit, and Ravina has not paid anything yet. October alone added ₹3,480 of new credit.

2. Build it step by step

Step 1. The ledger

1. In A6:F6 type Date, Customer, Type, Amount, + / −, Customer balance. Each credit or payment is one new row.
2. Select B7:B200 > Data Validation > List with the customer names, and C7:C200 with Credit,Payment (drop-down lists).

Step 2. Plus or minus

1. In E7 type =IF(C7="Credit",D7,-D7). Credit adds to what the customer owes; a payment subtracts.

Step 3. Running balance per customer

1. In F7 type =SUMIF($B$7:B7,B7,$E$7:E7) and copy down.
2. The range starts fixed at row 7 and grows as you copy, so each row adds only that customer's entries so far. After Salman's 12-10 credit his balance is ₹3,900 (SUMIF, mixed references).

Step 4. Customer summary

1. In H7:H10 type the customer names.
2. In I7 type =SUMIFS($D$7:$D$19,$B$7:$B$19,H7,$C$7:$C$19,"Credit") and in J7 the same with "Payment".
3. In K7 type =I7-J7 for the balance.

Step 5. Days since last payment

1. MAXIFS(max_range, criteria_range1, criteria1, ...) returns the biggest value that matches; for dates that is the latest one (MAXIFS).
2. In L7 type =IF(J7=0,"No payment yet",$B$3-MAXIFS($A$7:$A$19,$B$7:$B$19,H7,$C$7:$C$19,"Payment")): 37 days for Shahrukh.

Step 6. Status and colours

1. In B4 type the reminder limit (₹3,000). In M7 type =IF(K7>=$B$4,"Remind now",IF(K7>0,"Pending","Clear")).
2. Select M7:M10 > Highlight Cells Rules > Equal To "Remind now" red, "Clear" green. Select A7:F200 > Use a formula =$C7="Payment" green (formula rules).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Typing a payment as a negative amount. Type the amount as a positive number and choose Payment. Column E makes it negative.
2. Different spellings of a name. "Salman" and "salman " are different to SUMIFS. Use the drop-down so names always match.
3. Fixing both ends of the running range. $B$7:$B$7 would never grow. Only the start is fixed: $B$7:B7.
4. Editing old rows to show a payment. Never change an old credit. Add a new Payment row so the history stays honest.
5. Forgetting new customers in the summary. Add the name to the drop-down list and to the summary table, then copy the formulas down.

5. More real examples

Example 1: Lending between friends. Use the same ledger for money lent to friends: Lent and Returned instead of Credit and Payment.

Example 2: A tiffin or milk service. Each day's supply is a Credit, each month's payment a Payment. The balance shows who is behind.

Example 3: A ready-to-send reminder. Add ="Namaste "&H7&", your balance is ₹"&TEXT(K7,"#,##0") to build a reminder message to copy.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I make a khata book in Excel?

Keep one row per credit or payment with date, customer, type and amount, then total each customer with SUMIFS.

How do I show a running balance for each customer?

Use =SUMIF($B$7:B7,B7,$E$7:E7), where column E is + for credit and − for payment.

How do I find the last payment date of a customer?

Use =MAXIFS(date_column, customer_column, name, type_column, "Payment"). MAXIFS is in Excel 2019 and Microsoft 365.

How do I flag customers who owe too much?

Type a limit in a cell and use =IF(balance>=limit,"Remind now",...) with a red highlight.

Can I keep a separate sheet per customer?

One ledger is easier: filters and SUMIFS give any customer's view without copying data.

7. Quick quiz

Ten questions on this page. Sign in to save your score.