Excel for Accounting: Essential Skills and Templates

Essential Accounting Formulas

SUMIF/SUMIFS: Sum based on criteria — essential for account categorization

=SUMIFS(Amounts, Accounts, “4000”, Dates, “>=”&StartDate)

VLOOKUP/XLOOKUP: Pull data from chart of accounts, vendor lists, etc.

IF with AND/OR: Classify transactions based on multiple criteria

Bank Reconciliation Template

A proper reconciliation template needs:

• Bank statement balance (starting point)
• Outstanding checks section (with check numbers and dates)
• Deposits in transit section
• Adjusted bank balance (calculated)
• Book balance comparison

Use conditional formatting to highlight discrepancies — any difference should turn red immediately.

Financial Statement Formatting

Professional financial statements follow specific conventions:

• Numbers in thousands or millions with consistent notation
• Negative amounts in parentheses, not minus signs
• Subtotals with single underlines
• Grand totals with double underlines
• Right-aligned amounts with consistent decimal places

Aging Reports

AR and AP aging reports categorize invoices by days outstanding:

=IF(TODAY()-InvoiceDate<=30,“Current”,IF(TODAY()-InvoiceDate<=60,“30-60 Days”,...))

Or use SUMPRODUCT for totals by bucket:

=SUMPRODUCT((TODAY()-Dates>30)*(TODAY()-Dates<=60)*(Amounts))

Working with Dates in Accounting

Date handling is critical for:

• Period cutoffs (month-end, quarter-end, year-end)
• Depreciation schedules
• Amortization tables
• Invoice aging calculations

EOMONTH returns the last day of a month — essential for period calculations.

Official Resources

• EOMONTH function — end of month calculations
• NETWORKDAYS function — working days calculations