General Ledger Excel: The Complete Guide

A general ledger in Excel is a structured workbook that records journal-entry lines by account and produces account activity, balances, a trial balance, and financial-report inputs. It can work for a small, low-volume operation when one controlled source table is used, every journal entry balances, formulas are protected, supporting documents are traceable, statements are reconciled, and someone reviews the close.

Excel is flexible, but it is not automatically an accounting system. A workbook can silently accept duplicate transactions, invalid accounts, broken formulas, overwritten history, unbalanced entries, and uncontrolled access. Design controls before relying on its reports.

When Excel may be appropriate

A spreadsheet ledger may fit a simple entity with modest transaction volume, few users, limited integrations, straightforward tax and reporting needs, and a disciplined monthly reviewer. It can also serve as a controlled import, cleanup, mapping, reconciliation, or analysis workbook before data moves into accounting software.

It is a weaker fit when the business needs simultaneous users, automated feeds, inventory, payroll, sales tax, foreign currency, consolidated entities, complex receivables or payables, audit permissions, approval workflows, high transaction volume, or real-time reporting. The cost of manual controls can exceed software cost.

Recommended workbook structure

Sheet	Purpose	Core control
Instructions	Scope, basis, period, owners, and procedures	Version and reviewer identified
Chart_of_Accounts	Valid account codes, names, types, and status	Unique codes and controlled changes
Journal	One row per debit or credit line	Entry IDs balance and required fields validate
Trial_Balance	Opening, debit, credit, and ending balance by account	Total debits equal total credits
Reconciliations	Statement and schedule tie-outs	Difference, owner, evidence, and approval
Reports	Balance sheet and profit and loss	Reports tie to the trial balance
Change_Log	Approved structural and closed-period changes	Date, user, reason, and reviewer

Keep raw imported data on a separate immutable sheet when used. Do not paste over the journal or reports to “refresh” a period. Preserve source files outside the workbook as well.

Chart of accounts columns

• Account code and account name.
• Account type: asset, liability, equity, income, or expense.
• Normal balance and report section.
• Parent or reporting group.
• Active or inactive status.
• External statement or schedule required.
• Tax mapping kept separate from book reporting when needed.
• Definition, allowed uses, and examples for ambiguous accounts.

Codes should be unique and stable. Do not reuse an inactive code for a different purpose. Use a separate dimension column for customer, vendor, department, job, property, class, or location instead of creating thousands of accounts.

Journal table design

Use one Excel table with consistent columns rather than separate sheets for each month or account. Microsoft explains that structured references use table and column names and adjust as rows are added or removed. This makes formulas easier to inspect than long, shifting cell ranges.

Useful journal columns include:

• Entry ID and line number.
• Transaction date and posting period.
• Source type and source document ID.
• Description and business purpose.
• Account code.
• Debit and credit as separate numeric columns.
• Customer, vendor, class, job, or other required dimension.
• Preparer, approval reference, and posting status.
• Reversal link or correcting-entry reference when applicable.

Every line should have either a debit or credit, not both. Every entry ID should total to equal debits and credits. Dates should fall in open periods, account codes should exist in the active chart, and required dimensions should depend on the account or transaction type.

Build the ledger step by step

1. Define the legal entity, accounting basis, fiscal year, reporting currency, materiality, and users.
2. Create the controlled chart of accounts and reporting groups.
3. Create the journal as an Excel table with required fields and unique entry IDs.
4. Add data validation for account codes, dates, source types, and status values.
5. Add entry-level debit-equals-credit tests and duplicate-source tests.
6. Build the trial balance from the journal using controlled formulas or pivots.
7. Map the trial balance to the balance sheet and profit and loss.
8. Create reconciliation schedules for cash, cards, receivables, payables, loans, payroll, taxes, assets, and equity.
9. Protect formula and structural cells while leaving controlled input cells available.
10. Test the workbook with known entries, reversals, period boundaries, invalid codes, duplicates, and corrections.
11. Document the monthly close, review, backup, retention, and migration process.

Balanced-entry tests

At minimum, calculate debit total, credit total, and difference for each entry ID. Prevent posting when the difference is not zero. Also test that the overall trial balance has equal total debits and credits. A balanced entry can still be wrong, so balancing is necessary but not sufficient.

For example, an $800 equipment purchase paid by bank should debit equipment and credit cash for $800. If both lines were posted to expense and cash, the entry would balance but classification would still be wrong. Source review and account definitions catch errors that arithmetic cannot.

Data validation

Use drop-down lists or validation rules for account code, entry type, period, currency, and status. Microsoft notes that data validation can be affected by protected or shared sheets, copied data, invalid formulas, and calculation settings. Test the behavior users will actually use, including pasted imports.

Validation should reject or flag blank entry IDs, invalid accounts, dates outside open periods, text in amount fields, both debit and credit on one line, zero-value lines, missing source IDs, and required dimensions. Do not rely on cell color alone because formatting does not prevent invalid data.

Duplicate detection

Create a duplicate key using entity, source type, source ID, date, amount, and counterparty when appropriate. Flag exact and near duplicates. Bank and card imports often duplicate transactions already entered from invoices, bills, checks, or deposits.

Review duplicates rather than deleting automatically. Two invoices can legitimately share an amount and date, while one transaction can appear with slightly different descriptions across systems.

Trial balance design

The trial balance should list every active account and any inactive account with activity. Show opening balance, period debits, period credits, ending balance, account type, and report group. Its total debit and credit columns must agree. Opening balances should tie to the prior approved closing trial balance.

Do not hard-code ending balances. They should derive from the journal and controlled opening data. Investigate sign inconsistencies, missing accounts, formula omissions, filtered rows, and manual overrides.

Financial reports

Map asset, liability, and equity accounts to the balance sheet. Map income and expense accounts to the profit and loss. Reports should identify the entity, period, accounting basis, currency, and comparison. The balance sheet must balance, and report totals must tie to the trial balance.

Keep cash flow and tax reports separate unless their logic is explicitly designed and tested. Book profit, taxable income, and cash movement are different measures.

Bank and credit-card reconciliation

For each statement, compare the ledger with the statement beginning balance, ending balance, and transactions. Record supported bank items missing from the journal, leave valid uncleared checks and deposits as timing items, investigate differences, and reach zero adjusted difference. Save the statement and reconciliation.

A running bank balance in the journal is not a reconciliation. It may omit pending, duplicated, late, or unauthorized activity. Use the independent statement and a defined cutoff.

Other balance-sheet reconciliations

• Accounts receivable to customer detail and subsequent receipts.
• Accounts payable to vendor detail, statements, and subsequent payments.
• Payroll liabilities to payroll reports, filings, and payments.
• Loans to lender statements and amortization schedules.
• Fixed assets to purchase, disposal, and depreciation schedules.
• Sales and payroll taxes to returns and agency accounts.
• Equity to formation documents, contributions, distributions, and prior statements.
• Clearing accounts to source-system settlements and bank deposits.

Each reconciliation should state the ledger balance, source balance, reconciling items, adjusted balance, difference, owner, date, and reviewer.

Period close and locking

Define a close calendar and stop uncontrolled edits after approval. Worksheet protection can reduce accidental changes, but Microsoft states that worksheet protection is not a security feature. It should not be treated as encryption or access control.

After close, preserve a dated read-only export or controlled version, reconciliation package, trial balance, and reports. Record later corrections as new journal entries with references and approval. Do not overwrite the original source or silently change formulas.

Workbook security

Store the file in a controlled location with individual access, version history, backup, and tested recovery. Limit who can change formulas, account mappings, opening balances, and closed periods. Avoid emailing multiple editable copies because users may create competing ledgers.

Do not store passwords, full payment credentials, or unnecessary sensitive personal data in the ledger. Use secure source-document storage and link by controlled IDs.

Formula protection and error checks

Protect calculated columns, trial-balance formulas, report mappings, and control totals. Add visible tests for unbalanced entries, invalid accounts, duplicate IDs, missing sources, dates outside periods, unmapped accounts, formula errors, and balance-sheet imbalance. A dashboard should show zero unresolved control errors before close.

Microsoft provides formula-error checks, but the reviewer should also compare formulas across rows and test whether inserted data is included. A formula can return a plausible number while excluding a new row or account.

Example journal

Entry	Date	Account	Debit	Credit	Source
J-1042	2026-07-05	Operating cash	$2,500		DEP-0705
J-1042	2026-07-05	Service revenue		$2,500	DEP-0705
J-1043	2026-07-06	Software expense	$120		INV-8841
J-1043	2026-07-06	Credit card payable		$120	INV-8841

The two entries balance individually. The deposit still needs customer or sales support, and the software purchase still needs an invoice, business purpose, approval, correct period, and later card reconciliation.

Monthly review checklist

• All expected source files were received through the cutoff.
• Journal-entry IDs are unique and every entry balances.
• No invalid or unmapped account codes remain.
• Bank, card, receivable, payable, payroll, loan, tax, asset, equity, and clearing balances are supported.
• Trial-balance debits equal credits and opening balances tie to the prior close.
• Financial reports tie to the trial balance and the balance sheet balances.
• Exceptions have owners, amounts, deadlines, and resolution evidence.
• Formula, mapping, and structural changes were reviewed.
• The approved version and supporting package were backed up.

Common spreadsheet failures

Separate monthly sheets break continuity. One sheet per account duplicates data. Hard-coded totals hide omitted rows. Copying formulas inconsistently creates silent errors. Free-text account names split one account into several spellings. Pasting bank data into the ledger duplicates invoices or checks. Deleting mistakes destroys the audit trail. Shared files without ownership create conflicting versions.

Prevent these problems with one journal table, controlled identifiers, stable account codes, structured formulas, validation, protected calculations, correction entries, close versions, and review.

When to migrate to accounting software

Consider migration when volume or users increase, reconciliations take too long, invoices and bills need subledgers, payroll or inventory is added, permissions matter, integrations multiply, reporting requires dimensions, tax compliance becomes complex, or spreadsheet errors recur. Do not wait until the workbook is unusable.

Before migration, reconcile every material balance, clean the chart, document open items, lock the conversion date, preserve historical exports, map accounts and dimensions, test opening balances, and run parallel reports. The new system should not import unexplained differences as permanent opening balances.

Testing before first use

Create a small test pack with an owner contribution, customer sale and receipt, vendor bill and payment, credit-card purchase and payment, loan principal and interest, asset purchase, payroll summary, refund, correcting entry, and reversal. Confirm each entry balances, reaches the correct account and report, carries the required dimensions, and appears in the expected period.

Then test failures: duplicate a source ID, enter an inactive account, omit a line, paste text into an amount, change a closed date, insert a new account, delete a journal row, and break a report mapping. The workbook should reject the input or display a visible exception. Document which controls are preventive and which require reviewer detection.

Ownership and operating procedure

Name a workbook owner, backup owner, preparer, and reviewer. The instructions should explain file location, version name, source intake, posting, corrections, reconciliation, close, approval, report export, backup, restoration, and incident response. Record the Excel version and any features required for formulas or tables.

A spreadsheet becomes fragile when only one person understands it. Periodically have another qualified user reproduce the trial balance and close from the written procedure. If the process cannot survive an absence or handoff, simplify it or migrate.

Record retention

IRS Publication 583 discusses journals, ledgers, invoices, receipts, statements, canceled checks, and other records supporting business activity. Retain the workbook together with source documents, reconciliations, reports, tax workpapers, and change history under the business’s policy and current requirements. A ledger without source evidence is incomplete.

Continue with the general ledger guide, accounting in Excel, and financial reporting services.