Excel for Accounting: Templates and Formulas

General Ledger Template

Structure

• Date
• Account
• Description
• Debit
• Credit
• Balance

Running Balance Formula

=IF(ROW()=2, D2-E2, F1+D2-E2)

Bank Reconciliation

• Bank balance + Outstanding deposits - Outstanding checks = Adjusted bank
• Book balance + Credits - Debits = Adjusted book
• Difference should equal zero

Trial Balance

• Account list from Chart of Accounts
• SUMIF for debit totals
• SUMIF for credit totals
• Verify debits = credits

Formula

=SUMIF(Ledger[Account], A2, Ledger[Debit])

Financial Statements

Income Statement

Revenue - COGS = Gross Profit
Gross Profit - Operating Expenses = Operating Income

Balance Sheet

Assets = Liabilities + Equity

Useful Functions for Accounting

• SUMIF: Account totals
• EOMONTH: Period end dates
• DATEDIF: Aging calculations
• ROUND: Currency precision

Conclusion

Excel remains an essential tool for accounting professionals at all levels.