A ₹650 grocery payment can appear in your UPI app and your bank statement. It is still one purchase. A ₹2,000 ATM withdrawal moves money into your wallet; it does not mean you have spent ₹2,000 on household goods. Keeping those two distinctions clear makes an Excel expense tracker much more useful.
This tutorial shows how to record UPI and cash expenses together, reconcile them each week and avoid double-counting. We use a small fictional example and screenshots already published from the actual Next Gen Bazaar Monthly Budget Planner Excel Template. The screenshots contain sample data, separate from the tutorial example.
1. Prepare your Excel expense tracker
If you use our budget planner, save an untouched backup and open a working copy in desktop Microsoft Excel. Read the Getting Started Guide and clear the labelled sample entries as instructed. Use the existing Transaction Log and marked input cells; preserve formula cells and follow the workbook’s category and payment-method choices.
Decide whether you are tracking your own spending or the whole household. Use that scope consistently. Keep category and payment method separate: Groceries explains what you bought, while UPI explains how you paid.
The workbook uses manual entry. It does not connect to your bank or import UPI transactions automatically. Desktop Microsoft Excel is required and is not included; Google Sheets compatibility and mobile functionality have not been verified.
2. Record a UPI purchase once
After a completed purchase, enter the date, a recognisable description, the expense category, the payment method and the amount in the appropriate fields of your tracker. Use the workbook’s instructions for its transaction type and amount conventions.
For example, record a completed ₹650 grocery purchase as a groceries expense paid by UPI. When you later find the same debit on your linked bank statement, match it to that entry. Do not add another ₹650 expense simply because it appears in a second source.
- Match the details: compare the date, amount and merchant or recipient description.
- Keep context: use a brief note such as “weekly groceries” rather than an unexplained “payment.”
- Check uncertain payments: if the app status and bank record disagree, resolve what happened before treating an attempted payment as a completed purchase.
A transfer between your own accounts is different from buying groceries. Keep transfer reconciliation notes separately if your workbook does not provide a supported transfer type. Avoid placing transfers into a spending category just to make a bank balance match.
3. Track cash purchases, not just ATM withdrawals
For a purchase-based expense log, record what you buy with cash. Keep a separate wallet note so you can check whether your physical cash agrees with the purchases you recorded.
Suppose your wallet starts at ₹0 and you withdraw ₹2,000. You spend ₹40 on tea and ₹260 on vegetables. Your cash expenses are ₹300, and you should have ₹1,700 left, assuming there were no other cash movements. The withdrawal itself is a transfer from your bank to your wallet.
Recording both the ₹2,000 withdrawal as an expense and the ₹300 of purchases would incorrectly report ₹2,300 of spending. Record the purchases in the expense tracker and keep the withdrawal in your separate cash-reconciliation note.
A simple wallet check is: opening cash + cash added − cash purchases − other cash removed = closing cash. Note cash gifts, deposits or other movements separately so they are not mistaken for missing purchases.
4. Work through a combined UPI and cash example
In a separate practice worksheet, put the following six headings in cells A1:F1 and these three fictional purchases in rows 2–4. Enter dates as Excel dates and amounts as numbers, then apply rupee formatting. This practice layout is separate from the purchased workbook’s cell layout.
| Date (A) | Description (B) | Category (C) | Payment method (D) | Type (E) | Amount (F) |
|---|---|---|---|---|---|
| 20 Oct 2026 | Weekly groceries | Groceries | UPI | Expense | ₹650 |
| 20 Oct 2026 | Tea | Dining out | Cash | Expense | ₹40 |
| 21 Oct 2026 | Vegetables | Groceries | Cash | Expense | ₹260 |
The total is ₹950: ₹650 paid by UPI plus ₹300 paid in cash. Groceries total ₹910 across both methods, while dining out totals ₹40. This is why a payment-method total and a spending-category total answer different questions.
5. Optional: total UPI and cash spending with SUMIFS
Try these formulas outside the data range in your separate practice worksheet. They are examples for columns D, E and F above, not instructions to replace formulas in the budget planner.
-
UPI expenses:
=SUMIFS(F2:F4,D2:D4,"UPI",E2:E4,"Expense")returns 650. -
Cash expenses:
=SUMIFS(F2:F4,D2:D4,"Cash",E2:E4,"Expense")returns 300.
SUMIFS adds amounts that match both the payment-method and transaction-type conditions. Keep the ranges the same size and extend all of them together when adding rows. Use straight quotation marks around the labels. See Microsoft’s SUMIFS documentation for syntax and troubleshooting.
6. Reconcile your records, then review the dashboard
Once a week, set aside a few minutes to:
- Match completed UPI purchases to your existing entries and check for duplicates.
- Enter cash purchases while receipts or notes are still available.
- Count your wallet cash and compare it with your separate wallet calculation.
- Investigate differences instead of adding an unexplained expense to force a match.
- Review category totals in the Monthly Dashboard against your budgets.
The dashboard screenshot shows category budgets alongside recorded spending. Its “Cash remaining” figure is the calculated remainder after expenses and savings set aside; it is not a count of notes and coins in your wallet. Keep the physical-cash check separate.
At month-end, confirm that entries use the correct dates before reviewing the Year Overview. A partial month should not be read as a complete month.
Save a dated backup after reconciling. If a total looks wrong, check duplicate purchases, dates, numbers stored as text, inconsistent labels and accidentally changed formulas before adding more entries.
Frequently asked questions
Should an ATM withdrawal count as an expense?
In the purchase-based method used here, the withdrawal is a bank-to-wallet transfer. Record the actual cash purchases as expenses and maintain the withdrawal in separate reconciliation notes.
What if I paid by UPI but the transaction also appears on my bank statement?
Match the bank record to the existing purchase. The two records describe one payment; they should not become two expenses.
How should I record a refund?
Follow your workbook’s refund instructions and reference the original purchase. Do not assume every formula supports negative values or create a new income label without checking how the workbook treats it.
Can the budget planner read my UPI app automatically?
No. The workbook uses manual transaction entry. UPI and cash identify payment methods, not live integrations.
Use one workbook for your monthly review
The Monthly Budget Planner Excel Template India includes a Transaction Log, Monthly Dashboard, Monthly Budgets, Year Overview, Savings Goals and Getting Started Guide. It supports up to 1,000 transactions and budget comparisons across 10 categories, with INR formatting and sample previews on the product page.
View the budget planner, actual workbook previews and compatibility details. For a broader setup walkthrough, read our monthly expense tracker guide for Indian households.
