Build a Mini-ERP / Accounting System in Excel

Last updated: 27 June 2026

A complete accounting system in Excel is built on a single workbook with seven linked sheets — Chart of Accounts, Journal, Cash Book, Ledger, Trial Balance, P&L, and Balance Sheet. Each sheet feeds the next using SUMIF formulas and Excel Tables, so a single journal entry flows automatically all the way to your financial statements. For most Indian SMBs with fewer than 500 transactions a month, this setup covers every statutory requirement without spending a rupee on software.

Key takeaways

• Seven sheets, one workbook: COA → Journal → Ledger → Trial Balance → P&L → Balance Sheet, with Cash Book as a filtered view of the Journal.
• Excel Tables (Ctrl+T) are the foundation — they auto-expand and make SUMIF formulas update automatically as you add new rows.
• One workbook per Indian financial year (1 April to 31 March) prevents reconciliation headaches and keeps you compliant with Section 44AA.
• Lock formula cells with Review → Protect Sheet to prevent accidental overwrites.
• Companies must use Schedule III format for the Balance Sheet; proprietorships can use a simpler two-column layout.
• If you cross 500 transactions per month or need multi-user editing, plan a migration to dedicated accounting software.
• All Ankeshan templates described here are free to download, no sign-up required.

Why build the whole system in one workbook?

Many small businesses keep a cash book in one file, a sales register in another, and a rough P&L in a third spreadsheet. The result is manual copy-pasting, version confusion, and a closing process that takes days instead of hours. A single linked workbook eliminates that friction: every transaction you enter in the Journal propagates automatically to every downstream sheet.

The architecture mirrors the double-entry bookkeeping workflow that has been standard since the 15th century — and that Indian law still requires. Section 44AA of the Income Tax Act mandates that certain businesses and professionals maintain books of account, explicitly naming a cash book, journal, and ledger. This seven-sheet workbook covers all three.

Fact box. Section 44AA and Books of Account: Under Section 44AA of the Income Tax Act, 1961, a non-specified business must maintain books once its income exceeds ₹1.2 lakh or turnover/gross receipts exceed ₹10 lakh in any of the three preceding years, while specified professionals (doctors, lawyers, architects, etc.) must keep the prescribed books under Rule 6F once gross receipts exceed ₹1.5 lakh. The core books are a cash book, journal, and ledger. A tax audit under Section 44AB is triggered when turnover exceeds ₹1 crore (or ₹10 crore if 95% or more of receipts and payments are digital). Your Excel workbook can serve as the underlying book of account, provided it is printed or backed up and can be produced for assessment.

What is the 7-sheet architecture?

The flow is strictly one-directional: Journal is the single source of truth. No numbers are typed into Ledger, TrialBalance, P&L, or BalanceSheet — they all pull from Journal via formulas. This means there is only one place to correct a mistake.

How do I set up the Chart of Accounts sheet?

The Chart of Accounts (COA) is your master list. Every account you will ever debit or credit must appear here first.

Column structure:

Setup tips:

1. Use a consistent numbering convention: 1xxx for Assets, 2xxx for Liabilities, 3xxx for Equity, 4xxx for Income, 5xxx for Expenses.
2. Apply data validation on the Type column (Data → Data Validation → List: Asset, Liability, Equity, Income, Expense) to prevent typos.
3. Name this range "COA" (Formulas → Name Manager) so other sheets can reference it cleanly.

For a ready-made COA tailored to Indian businesses — including GST output/input tax accounts and TDS payable — see the Chart of Accounts template for India.

How do I build the Journal sheet?

The Journal is the engine of the whole system. Every financial event — a sale, a payment, a depreciation entry — goes here as at least one debit and one credit of equal value.

Column structure:

Step-by-step build:

1. Enter your column headers in row 1.
2. Select the entire data range and press Ctrl+T to convert it to an Excel Table. Name the table Journal in the Table Design tab.
3. Use VLOOKUP or XLOOKUP to auto-fill Account Name from the COA when you type a Code: =IFERROR(VLOOKUP(C2,COA,2,0),"")
4. Add a balance-check helper column to confirm each journal voucher balances:

=SUMPRODUCT((Journal[Ref No]=[@[Ref No]])*Journal[Debit (₹)])-SUMPRODUCT((Journal[Ref No]=[@[Ref No]])*Journal[Credit (₹)])

If this returns anything other than 0 for a given Ref No, the entry does not balance. Apply conditional formatting to highlight non-zero values in red.

Fact box. Excel Tables are the foundation: When you format a range as an Excel Table (Ctrl+T), it gains structured references — formulas like Journal[Debit (₹)] always refer to the entire column, including rows you add later. Without an Excel Table, a SUMIF formula you write today will silently miss new rows added below its original range. This single habit — always convert your data ranges to Tables — is the difference between a workbook that works once and one that works all year.

How do I set up the Cash Book sheet?

The Cash Book is a filtered view of the Journal, showing only transactions that touch Cash in Hand (Code 1001) and Bank (Code 1002), with a running balance.

Build method:

1. In a helper column on the Journal sheet, flag cash/bank rows: =IF(OR([@[Account Code]]=1001,[@[Account Code]]=1002),"Y","N")
2. On the CashBook sheet, use Advanced Filter or FILTER (Excel 365) to pull only "Y" rows:
   =FILTER(Journal,Journal[CashFlag]="Y")
3. Add a Running Balance column: for the first row enter the opening balance; for each subsequent row:
   =E2+F3-G3 (Previous Balance + Debit − Credit)

For a standalone download with the running balance already built in, see the Cash Book format in Excel.

How do I build the Ledger sheet?

The Ledger shows the total debits, total credits, and net closing balance for every account. You do not type balances here — they are calculated from the Journal using SUMIF.

Column structure and formulas:

• A positive Net Balance means a debit balance (normal for assets and expenses).
• A negative Net Balance means a credit balance (normal for liabilities, equity, and income).
• To populate the Account Name column automatically, link it from the COA using VLOOKUP on the Code.

For a more detailed walkthrough including sub-ledger reconciliation, see the General Ledger template in Excel.

How do I build the Trial Balance sheet?

The Trial Balance lists every account's net balance and splits it into a Debit column or Credit column. The two columns must sum to zero — if they do not, you have an error somewhere in the Journal.

Build steps:

1. Copy the Account Code and Account Name columns from the Ledger (or link them).
2. Add a Debit Balance column: =IF(Ledger_NetBalance>0, Ledger_NetBalance, 0)
3. Add a Credit Balance column: =IF(Ledger_NetBalance<0, ABS(Ledger_NetBalance), 0)
4. At the bottom, add totals: =SUM(DebitColumn) and =SUM(CreditColumn)
5. Add a check cell: =SUM(DebitColumn)-SUM(CreditColumn) — this must equal 0.

Apply conditional formatting to the check cell: green fill if 0, red fill otherwise. A red cell means you have an unbalanced entry somewhere in the Journal.

See the full guide and free template at Trial Balance in Excel.

How do I build the P&L sheet?

The Profit and Loss account pulls balances from the Ledger for all Income (4xxx) and Expense (5xxx) accounts.

Structure:

Link each line to the relevant Ledger balance using SUMIF on the account type or code range:

=SUMIF(Ledger[Code],"4???",Ledger[Net Balance])*-1

(Multiply by −1 because income accounts carry a credit/negative net balance in the Ledger.)

Download the standalone template at Profit and Loss template in Excel.

How do I build the Balance Sheet?

The Balance Sheet links from the Ledger for all Asset (1xxx), Liability (2xxx), and Equity (3xxx) accounts.

For companies: Use the Schedule III format prescribed under the Companies Act, 2013 — two-sided presentation is no longer required; vertical format is standard.

For proprietorships and partnerships: A simpler two-column layout (Assets on one side, Capital + Liabilities on the other) is acceptable.

Fundamental check:

Total Assets = Total Liabilities + Total Equity

Net Profit is not added separately — it is already a component of Equity. After you close the books, the year's net profit is transferred from the P&L into Retained Earnings (or Owner's Capital), so it sits inside the Equity total. If you are checking the sheet before that closing entry is posted, add the period's net profit to Equity once (Total Assets = Total Liabilities + Total Equity + Net Profit); after closing, drop the Net Profit term to avoid double-counting it.

If the sheet does not balance, check that net profit has been carried from the P&L into Equity exactly once and that every account is classified into the right type on the COA.

Full Schedule III template and guidance: Balance Sheet format in Excel.

What are the best practices for data integrity?

Once the workbook is working, protect it against accidental damage:

1. Freeze panes on every sheet (View → Freeze Panes → Freeze Top Row) so headers stay visible as you scroll.
2. Lock formula cells: Select all formula cells → Format Cells → Protection → Locked. Then go to Review → Protect Sheet. Only input cells (Journal rows) should be unlocked.
3. Name your ranges: Use Formulas → Name Manager to name key ranges (COA, Journal table). Named ranges make formulas easier to audit.
4. Validate inputs: Use Data Validation on the Account Code column in the Journal to restrict entries to codes that exist in the COA.
5. One workbook per financial year: The Indian financial year runs 1 April to 31 March. Start a fresh workbook on 1 April each year. Bring in closing balances from the previous year as opening entries (debit assets, credit liabilities and equity with Ref "OB-2526").
6. Save format: For large workbooks (5,000+ rows), save as Excel Binary Workbook (.xlsb). It loads faster and consumes less disk space than .xlsx. Keep a monthly .xlsx backup for sharing.
7. Cash payment discipline: Under Section 40A(3) of the Income Tax Act, cash payments above ₹10,000 to a single person in a single day are disallowed as a business expense. Flag these in the Journal narration column so they are easy to identify at tax time.

When should I migrate away from Excel?

Excel is the right tool until it is not. Plan a migration to dedicated accounting software when any of the following apply:

How Ankeshan helps

Ankeshan is an Excel-native accounting add-in built specifically for Indian SMBs. It installs directly into Excel and gives you the 7-sheet workbook pre-built — COA pre-loaded with common Indian business accounts, Journal with built-in balance validation, and financial statements that update automatically. No new software to learn, no browser required, and your data stays on your own machine.

If you want to try it before it goes live, join the waitlist at ankeshan.com/waitlist.

Frequently asked questions

How many sheets should my accounting workbook have?
Seven is the recommended minimum: COA, Journal, CashBook, Ledger, TrialBalance, P&L, and BalanceSheet. You can add supporting sheets for subsidiary ledgers (debtors, creditors, fixed assets) or GST working, but do not combine core accounting sheets — keeping them separate makes it easier to audit and protect individual sheets.

Can multiple people edit the same Excel file at the same time?
Not reliably. Excel's co-authoring feature works in OneDrive or SharePoint but creates conflicts when two people edit the same Journal row simultaneously. The practical solution is to designate one person as the accountant who owns the file, and have others submit transaction data via a separate input sheet or a shared form that the accountant reviews before posting.

Should I use macros (VBA) in my accounting workbook?
Keep VBA to a minimum in a workbook that must be opened on different machines or shared with auditors. Formula-based workbooks are more portable and transparent. If you do use macros — for example, a button that auto-numbers journal vouchers — document every macro and store the workbook in a macro-enabled format (.xlsm). Never use macros to post transactions silently; every entry should be visible in the Journal.

How do I handle opening balances from a previous year?
On the first day of the new financial year (1 April), enter a single compound journal entry with Ref "OB-YYYY" that debits every asset account for its closing balance and credits every liability and equity account for its closing balance. The Net Profit from the previous year should be transferred to Retained Earnings (or Owner's Capital for proprietorships) as a separate entry before you close the old workbook.

Does Excel meet Section 44AA requirements?
Section 44AA requires that specified books — including cash book, journal, and ledger — be maintained. A properly structured Excel workbook that contains all three, is backed up regularly, and can be printed on demand satisfies the spirit of the requirement. However, if your business is subject to a tax audit under Section 44AB, your chartered accountant may require that figures be exported or reconciled with a formal accounting system. Consult your CA for your specific situation.

What GST accounts should I include in the COA?
At a minimum, add: GST Output (CGST Payable, SGST Payable, IGST Payable) under Liabilities, and GST Input (CGST Receivable, SGST Receivable, IGST Receivable) under Assets. GST slabs in India are Nil, 5%, 18%, and 40% (the 12% and 28% slabs were abolished on 22 September 2025) — you may want separate income accounts for each slab if you sell goods at different rates, to make GSTR-1 filing easier.

Sources and disclaimer

• Income Tax Act, 1961 — Section 44AA (books of account), Section 44AB (tax audit), Section 40A(3) (cash payment disallowance)
• Companies Act, 2013 — Schedule III (balance sheet format)
• GST Council notifications on applicable tax slabs
• ICAI Guidance Notes on Accounting Standards applicable to SMEs

This article is for general educational purposes only and does not constitute legal, tax, or accounting advice. Indian tax law changes frequently. Consult a qualified chartered accountant for advice specific to your business.

Related links

• Accounting in Excel — hub
• Chart of Accounts for India — free Excel template
• Trial Balance in Excel — step-by-step guide
• Financial Year-End Closing Checklist for Indian SMBs