Build a General Ledger in Excel

Last updated: 27 June 2026

A general ledger is the classified record of every transaction your business has made, organised by account. It is the secondary book of accounts that sits between your journal (day-wise entries) and your trial balance (account-wise totals). Indian SMBs subject to Section 44AA are legally required to maintain one — and a single-sheet Excel ledger built with SUMIF gets the job done without expensive software.

Key takeaways

• A general ledger groups journal entries by account (cash, debtors, sales, GST payable, etc.) so you can see the full history and balance of each account at a glance.
• The single-sheet approach — one row per transaction, filtered by account name — is easier to maintain than separate sheets per account and works well for businesses with up to a few hundred accounts.
• Convert your data range to an Excel Table (Ctrl+T) so that SUMIF formulas expand automatically as you add rows.
• Use the SUMIF running-balance formula to see each account's balance after every posting without manual arithmetic.
• Post GST separately: maintain an Input GST Credit account and an Output GST Payable account — do not net them.
• At year-end, zero out income and expense accounts to retained earnings; assets, liabilities, and equity accounts carry forward.
• The free Ankeshan template (no sign-up required) includes the column layout, SUMIF formulas, and a sample chart of accounts for Indian SMBs.

What exactly is a general ledger, and why do Indian SMBs need one?

A journal records transactions in date order. A general ledger re-sorts those same transactions by account — so you can answer questions like "What is my total sales for April?" or "How much GST credit do I have this quarter?" without scrolling through every journal entry.

Under Section 44AA of the Income Tax Act, businesses and professionals whose gross receipts exceed the prescribed threshold must maintain a cash book, a journal, and a ledger. The tax audit threshold currently stands at ₹1 crore turnover (₹10 crore if 95% or more of receipts and payments are digital). Even if your turnover is below the threshold, a ledger is essential for accurate GST return filing and for producing a trial balance at year-end.

Fact box. Section 44AA read with Rule 6F lists the books of accounts a business must maintain. The three core books are: (1) cash book, (2) journal, and (3) ledger. Failure to maintain these can attract a penalty of up to ₹25,000 under Section 271A.

What are the two ways to build a ledger in Excel?

Option A — Multi-sheet ledger (one sheet per account)

Each account (Cash, Bank, Debtors, Sales, etc.) gets its own worksheet. You post entries to the relevant sheet, and the closing balance appears at the bottom.

Pros: Familiar to accountants who use physical ledger books. Easy to print individual account statements.

Cons: Copying entries across sheets is error-prone. No single place to filter or search. Difficult to build a consolidated trial balance without complex cross-sheet formulas.

Option B — Single-sheet ledger (recommended)

All transactions sit in one table. You filter by account name or use SUMIF to pull account-wise totals. This is the approach described in the rest of this article.

Pros: One place to enter data. Excel Table auto-expands. SUMIF formulas work across the whole range in one step. Easy to pivot, filter, or hand off to a CA.

Cons: Requires understanding of at least one formula (SUMIF). Can become slow if the file grows beyond ~50,000 rows (unlikely for most SMBs).

How do I set up the column layout?

Create a new worksheet named Ledger and format it as an Excel Table (select any cell, press Ctrl+T, confirm the header row).

Keep column C consistent. Use a drop-down list (Data → Data Validation → List) linked to your Chart of Accounts sheet so account names never diverge.

How do I compute the running balance with SUMIF?

The running balance shows the net balance of each account up to and including each row. Because one account appears multiple times in the table, a simple subtraction formula will not work — you need SUMIF with an expanding range.

Paste this formula in G2 and fill it down:

=SUMIF($C$2:C2,C2,$E$2:E2)-SUMIF($C$2:C2,C2,$F$2:F2)

How it works:

1. $C$2:C2 — the Account column, from row 2 to the current row. The first reference is absolute; the second is relative, so the range expands as you fill down.
2. C2 — the criterion: match only rows where Account equals the account in this row.
3. $E$2:E2 and $F$2:F2 — the Debit and Credit columns, same expanding range.
4. The formula sums all debits for this account up to the current row, then subtracts all credits. A positive result is a debit balance; negative is a credit balance.

Fact box. A running balance approach means each posting is self-checking — if you enter a wrong amount, you can spot it immediately by comparing the balance to your bank statement or cash-on-hand, rather than discovering the error only at month-end.

How do I pull closing balances for a trial balance?

Create a separate sheet named Trial Balance. In column B, list every account name. In column C, use:

=SUMIF(Ledger[Account],B2,Ledger[Debit])-SUMIF(Ledger[Account],B2,Ledger[Credit])

This pulls the net balance (total debits minus total credits) for each account. A positive result means a debit balance (normal for assets and expenses); a negative result means a credit balance (normal for liabilities, equity, and income).

Because the formula references the named Table columns (Ledger[Account], Ledger[Debit], Ledger[Credit]), it automatically covers all rows added to the table — no need to update the formula range manually.

Which accounts should an Indian SMB include?

A practical starting chart of accounts for a trading or service business:

Separate accounts for each GST slab make reconciliation with GSTR-2B and GSTR-3B straightforward. Never net input GST against output GST in the ledger — the accounts are offset only when you file your return and make the payment.

How do I post GST entries correctly?

GST involves two sides:

On a sale (output tax):

On a purchase (input credit):

At the time of GST payment, debit Output GST Payable and credit Input GST Credit for the set-off amount, then debit the remaining balance and credit Bank.

How do I close accounts at year-end?

Income and expense accounts (temporary accounts) must be closed to the Profit & Loss / Retained Earnings account at the end of each financial year (31 March).

Steps:

1. Total all income accounts (credit balances). Pass a journal entry: Debit each income account, Credit Profit & Loss.
2. Total all expense accounts (debit balances). Pass a journal entry: Debit Profit & Loss, Credit each expense account.
3. The net credit (or debit) remaining in Profit & Loss is your net profit (or loss). Transfer it to Retained Earnings / Owner's Capital.
4. Asset, liability, and equity accounts are permanent — their closing balances become the opening balances for the new year.

In Excel, add a column FY to filter entries by financial year if you are maintaining multiple years in one file.

Frequently asked questions

What is the difference between a journal and a ledger?

A journal records every transaction in date order — it is the book of original entry. A ledger reclassifies the same transactions by account — it is the book of secondary entry. You post from the journal to the ledger; the ledger then feeds the trial balance.

How many sheets should my Excel ledger have?

For most Indian SMBs, two sheets are enough: one named Ledger (all transactions) and one named Trial Balance (account-wise totals via SUMIF). You can add a third Chart of Accounts sheet to store the drop-down list source. Multi-sheet ledgers (one per account) are harder to maintain and offer no formula advantage.

Can I use Power Query instead of SUMIF?

Yes — Power Query can group rows by account and sum debits and credits to produce a closing balance table. It is more powerful for large datasets and cleaner for refreshing after bulk imports. However, it does not give you a row-by-row running balance within the transaction table. For most SMBs, SUMIF is simpler and requires no Power Query knowledge.

How do I handle opening balances when setting up mid-year?

Add a row for each account dated the day before your start date (e.g., 31-03-2026), with "Opening Balance" in the Particulars column. Enter the opening balance as a Debit or Credit depending on the normal balance of the account. The SUMIF running-balance formula will then carry forward the correct opening figure into all subsequent entries.

How Ankeshan helps

Ankeshan is building a ledger and bookkeeping module designed for Indian SMBs — with GST account mapping, automatic journal-to-ledger posting, and one-click trial balance export, all inside your existing workflow. If you'd like early access, join the waitlist at ankeshan.com/waitlist.

The free Excel templates referenced in this article are available to download from that page — no sign-up required.

Sources and disclaimer

• Income Tax Act, 1961, Section 44AA and Rule 6F (books of accounts)
• GST Act, 2017 — input tax credit rules (Section 16)
• Microsoft Excel documentation — Excel Tables and SUMIF function

This article is for general informational purposes only and does not constitute legal, tax, or financial advice. Consult a qualified chartered accountant for advice specific to your business.

Related articles

• Accounting in Excel — hub
• Double-entry bookkeeping in Excel
• Build a trial balance in Excel
• Chart of accounts for India — Excel template