Double-Entry Bookkeeping in Excel (for Beginners)
Last updated: 27 June 2026
Double-entry bookkeeping means every transaction is recorded twice — one debit and one equal credit — so your books always balance. You can implement a complete double-entry system in a plain Excel workbook using a journal sheet and a handful of SUMIF formulas. No specialist software is required, especially in the early years of an Indian SMB.
Key takeaways
- Every transaction affects at least two accounts: debits must always equal credits.
- The accounting equation — Assets = Liabilities + Equity — must hold after every entry; Excel formulas can enforce this automatically.
- The five account types are Assets, Liabilities, Equity, Income, and Expenses; each has a "normal balance" side (Dr or Cr).
- A journal in Excel needs six columns: Date, Ref, Account, Dr, Cr, Narration.
- Use
SUMIFto validate that debits equal credits for every reference number, and to post balances to a simple ledger. - Section 44AA of the Income Tax Act lists the books every business must keep: cash book, journal, and ledger.
- Common beginner mistakes — recording credits as negative numbers or swapping the Dr/Cr columns — will corrupt your trial balance silently.
What exactly is double-entry bookkeeping?
In single-entry bookkeeping you write one line per transaction, like a personal bank register. Double-entry adds a second line that explains where the money came from or where it went. The two lines together form a balanced entry.
A simple rule: for every transaction, the total amount debited equals the total amount credited.
| Side | Increases | Decreases |
|---|---|---|
| Debit (Dr) | Assets, Expenses | Liabilities, Equity, Income |
| Credit (Cr) | Liabilities, Equity, Income | Assets, Expenses |
This table is the heart of bookkeeping. Tape it to your monitor until it is automatic.
What are the five account types and their normal balances?
Every account falls into exactly one of five categories. The "normal balance" is the side that increases the account.
| # | Account type | Normal balance | Examples |
|---|---|---|---|
| 1 | Assets | Debit | Cash, Bank, Debtors, Inventory, Machinery |
| 2 | Liabilities | Credit | Creditors, Loans, GST Payable |
| 3 | Equity | Credit | Owner's Capital, Retained Earnings |
| 4 | Income | Credit | Sales, Interest Received |
| 5 | Expenses | Debit | Salaries, Rent, Purchase of Goods |
The accounting equation ties these together: Assets = Liabilities + Equity. Income and Expenses flow through Equity via Profit & Loss at year-end.
Fact box. The accounting equation — Assets = Liabilities + Equity — must hold true at all times. If your trial balance does not balance (total debits ≠ total credits), you have an error somewhere. Because Excel does not enforce this automatically, a SUMIF check on each reference number is your first line of defence.
How do you set up a double-entry journal in Excel?
Step 1 — Create the Journal sheet
Open a new workbook. Name the first sheet Journal. Set up these six columns in row 1:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Date | Ref | Account | Dr | Cr | Narration |
- Date — transaction date (format as DD-MM-YYYY for Indian convention).
- Ref — a unique reference number per transaction (e.g., JV-001, JV-002). Both lines of a transaction share the same Ref.
- Account — account name from your Chart of Accounts.
- Dr — debit amount (leave blank, not zero, if this line is a credit).
- Cr — credit amount (leave blank, not zero, if this line is a debit).
- Narration — brief description of what happened.
Step 2 — Enter your first journal entries
Four entries every Indian SMB will recognise:
| Date | Ref | Account | Dr | Cr | Narration |
|---|---|---|---|---|---|
| 01-07-2026 | JV-001 | Cash | 11,800 | Cash sale of goods | |
| 01-07-2026 | JV-001 | Sales | 10,000 | Cash sale of goods | |
| 01-07-2026 | JV-001 | GST Payable (18%) | 1,800 | GST collected on sale | |
| 05-07-2026 | JV-002 | Purchase of Goods | 50,000 | Goods bought on credit from M/s Sharma | |
| 05-07-2026 | JV-002 | Creditors — M/s Sharma | 50,000 | Goods bought on credit | |
| 31-07-2026 | JV-003 | Salaries Expense | 25,000 | July salary paid via bank | |
| 31-07-2026 | JV-003 | Bank Account | 25,000 | July salary paid via bank |
Notice that JV-001 has three lines (one debit, two credits) but still balances: ₹11,800 Dr = ₹10,000 + ₹1,800 Cr.
Step 3 — Add a validation formula
In a spare column (say column G), add this header: Balanced?
In G2 enter:
=IF(SUMIF($B$2:$B$1000,B2,$D$2:$D$1000)=SUMIF($B$2:$B$1000,B2,$E$2:$E$1000),"OK","ERROR")
Copy this formula down the entire journal. It checks that all debit lines for a given Ref number sum to the same total as all credit lines. Any row showing ERROR flags a data-entry mistake before it reaches your trial balance.
Step 4 — Build a simple Ledger sheet
Create a second sheet named Ledger. List each account name in column A (e.g., A2 = "Cash", A3 = "Sales", and so on).
In B2 (Debit total for Cash):
=SUMIF(Journal!C:C,A2,Journal!D:D)
In C2 (Credit total for Cash):
=SUMIF(Journal!C:C,A2,Journal!E:E)
In D2 (Balance):
=B2-C2
For asset and expense accounts, a positive balance in D2 is normal (they carry a debit balance). For liability, equity, and income accounts, a negative D2 means a credit balance — which is also normal. Rename column D to "Balance (Dr+/Cr−)" so readers are not confused.
Fact box. Section 44AA of the Income Tax Act, 1961 requires specified businesses to maintain books of account including a cash book, journal, and ledger. A business whose turnover exceeds ₹1 crore (or ₹10 crore if at least 95% of receipts and payments are digital) is liable for a tax audit under Section 44AB. Keeping a well-maintained Excel journal and ledger satisfies the books-of-account requirement and makes audit preparation significantly faster.
What GST rates apply when recording sales entries?
From 22 September 2025, India operates four GST slabs: Nil, 5%, 18%, and 40%. The earlier 12% and 28% slabs were abolished on that date. When you post a sales entry, split the GST component into a separate credit line to GST Payable, using the correct rate for the goods or service.
For a ₹10,000 sale attracting 18% GST: debit Cash/Debtors ₹11,800, credit Sales ₹10,000, credit GST Payable ₹1,800.
What are the most common beginner mistakes?
Using negative numbers instead of a Cr column. If you record a credit as −₹10,000 in the Dr column, SUMIF totals will be wrong and your validation formula will not catch the imbalance correctly. Always use two separate columns.
Swapping Dr and Cr sides. Recording a cash receipt as a credit to Cash (instead of a debit) is the single most common error. Refer to the normal-balance table above every time you are unsure.
Sharing a Ref number across unrelated transactions. If JV-001 is used for two different transactions, your SUMIF validation will combine both and may hide errors that cancel each other out. Use one Ref per transaction.
Mixing GST collected (liability) with sales revenue. The full invoice amount debited to the customer includes GST, but only the base price is income. The GST portion must go to a GST Payable (liability) account.
Ignoring the Section 40A(3) cash limit. Cash payments exceeding ₹10,000 to a single person in a single day are disallowed as a deduction under Section 40A(3). If you record such a payment in your journal, flag it with a note in the Narration column so your tax consultant can adjust it at year-end.
How Ankeshan helps
Ankeshan's Excel add-in includes a pre-built double-entry journal template with the SUMIF validation already wired in, an auto-posting ledger, and a one-click trial balance — all inside Excel, no new software to learn. The free template described in this article is available to download with no sign-up. If you want the automated version with GST account codes, chart-of-accounts preloaded for Indian SMBs, and error alerts built in, join the waitlist at ankeshan.com/waitlist.
Frequently asked questions
What is the difference between a debit and a credit?
A debit is an entry on the left side of an account; a credit is an entry on the right side. Whether a debit increases or decreases an account depends on the account type — debits increase assets and expenses, while credits increase liabilities, equity, and income. The two sides must always balance.
Is double-entry bookkeeping required by law in India?
Section 44AA of the Income Tax Act requires specified professionals and businesses to maintain books of account — specifically a cash book, journal, and ledger — but does not mandate a particular format. Double-entry is the internationally accepted method that produces these three books as natural outputs, and tax authorities expect them to reconcile.
What accounts does an Indian SMB typically need?
At a minimum: Bank, Cash, Debtors (Accounts Receivable), Creditors (Accounts Payable), GST Payable, GST Input Credit, Sales, Purchases, Salaries Expense, Rent Expense, and Owner's Capital. A full starter chart of accounts for Indian businesses is available at /accounting/chart-of-accounts-india-excel/.
Do I need accounting software, or is Excel enough?
Excel is sufficient for most businesses with fewer than a few hundred transactions per month. The SUMIF-based approach described here scales to thousands of rows without performance issues on a modern PC. When you find yourself spending more than a couple of hours per week on manual reconciliation, that is the signal to consider dedicated software.
Can I track GST input credit in the same Excel journal?
Yes. Create an account called "GST Input Credit" (an asset). When you pay GST on a purchase, debit GST Input Credit instead of including the GST in the purchase cost. At the end of each month, offset GST Input Credit against GST Payable to arrive at your net GST liability.
What happens if my journal does not balance?
Your trial balance will show unequal debit and credit totals. Use the SUMIF Balanced? column to find the Ref number where debits and credits do not match, then correct that entry. Never force-balance by adding an unexplained adjustment entry — that obscures a real error.
Sources and disclaimer
- Income Tax Act, 1961 — Section 44AA (books of account), Section 44AB (tax audit), Section 40A(3) (cash payment limit)
- GST Council notification abolishing 12% and 28% slabs, effective 22 September 2025
- ICAI Guidance Note on books of account and accounting standards for SMEs
This article is for general educational purposes only and does not constitute legal or tax advice. Consult a qualified Chartered Accountant for advice specific to your business situation.