Excel for Bookkeeping: The Skills You Use Every Day

Excel for bookkeeping comes down to a handful of skills you use every day: totalling transactions by category, rounding to the cent, keeping a running balance and proving that your numbers agree. You don't need advanced formulas. You need simple ones used carefully. This guide is for bookkeepers, accounting assistants and small business owners, and every example uses one small expense list you can type in yourself.

What bookkeepers use Excel for#

Bookkeepers often keep or export transaction data into Excel, even when the business uses accounting software. Typical files include:

• Expense and income lists: date, payee, category and amount.
• Bank reconciliations: the bank statement next to the ledger, with differences explained.
• Supplier and invoice lists: what's owed, what's paid and what's overdue. The guide to Excel skills for accounts payable goes deeper on this.
• Monthly summaries: spending by category, compared with budget.

The examples use this March expense list in A1:D7:

Totals by category with SUMIF#

SUMIF adds the amounts in rows that match one condition. It turns a long transaction list into a category summary.

=SUMIF(C2:C7,"Supplies",D2:D7)

returns 261.84 (84.60 + 47.25 + 129.99). The three parts are the range to check, the condition and the range to add. Put the category names in a column, say F2:F4, and point at them instead of typing each name:

=SUMIF($C$2:$C$7,F2,$D$2:$D$7)

Fill it down (Windows: Ctrl+D; Mac: ⌘D) and you get Supplies 261.84, Utilities 275.45 and Postage 18.90. The $ signs keep the data ranges fixed while F2 moves to F3 and F4. See the SUMIF function guide for more examples.

For two conditions, such as category and month, use SUMIFS. The range to add comes first: =SUMIFS(D2:D7,C2:C7,"Supplies",A2:A7,">="&DATE(2024,3,10)) returns 129.99, the Supplies spending from 10 March onward.

Rounding to the cent with ROUND#

ROUND changes the value Excel stores. Formatting a cell to two decimals only changes what you see. In bookkeeping, that difference is where one-cent errors come from.

Say you split a 100.00 bill three ways with =100/3. Each cell shows 33.33, but stores 33.3333… The total of the three cells shows 100.00, while the visible numbers add up to 99.99. Anyone checking by hand finds a cent missing.

Round each share instead:

=ROUND(100/3,2)

Now each cell really holds 33.33, and the total is honestly 99.99. Put the extra cent on the last share, for example =100-SUM(B2:B3), so the split adds back to 100.00.

The same applies to tax. Tax at 8% on 84.60 is 6.768. =ROUND(D2*8%,2) stores 6.77. Use ROUNDUP or ROUNDDOWN only when a rule tells you to always round one way. The ROUND function guide explains the third argument, and Excel skills for finance jobs covers rounding in reports.

Keeping a running balance#

A running balance shows the account balance after every transaction. If the opening balance is in E1 and amounts are in column D, with money out shown as negative numbers, type this in E2:

=E1+D2

Then fill down. Each row adds its own amount to the balance above it. If your sheet keeps money in and money out in separate columns, use =E1+C2-D2 instead. Check the last balance against the bank statement. If they don't match, the difference tells you how much to look for.

Reconciling two lists#

A reconciliation compares two records of the same money, such as your ledger and the bank statement, and explains every difference. Excel helps in three ways:

1. Compare totals first. If the ledger total is in G1 and the bank total in G2, =ROUND(G1-G2,2) shows the difference. Rounding stops tiny floating-point leftovers from showing as 0.00000001.
2. Match items with a lookup. =XLOOKUP(B2,BankRefs,BankAmounts,"Missing") finds each ledger reference on the bank list (Excel 2021 or Microsoft 365). In older versions, use VLOOKUP with FALSE inside IFERROR.
3. Flag mismatches. =IF(ROUND(D2-E2,2)=0,"OK","Check") marks rows where the two amounts differ.

Check cells that prove your figures agree#

A check cell is a formula that should always return zero (or TRUE) when the books are right. Good examples:

• The category totals minus the grand total: =ROUND(SUM(G2:G4)-SUM(D2:D7),2) should be 0.
• Debits minus credits in a trial balance should be 0.
• The closing balance minus the bank statement balance should be 0 once reconciled.

Put check cells near the top of the sheet and colour them with conditional formatting when they're not zero. They catch a missing row or a typo before anyone else does.

It also helps to count what you expect. =COUNT(D2:D7) should equal the number of transactions on the statement, here 6. If it shows 5, one amount was probably typed or pasted as text, and SUM and SUMIF are quietly skipping it. Text numbers often sit on the left of the cell; convert them with VALUE or Data > Text to Columns.

Clean data entry habits#

Formulas only work on consistent data. "Supplies" and "Supplies " with a trailing space are different categories to SUMIF. Use a dropdown list for categories (Data > Data Validation > List), enter dates as real dates rather than text, and keep one transaction per row. Many of the same habits apply to stock and sales records, as the guides to Excel for retail jobs and Excel skills for supply chain roles show.

Practise it#

Bookkeeping skills stick when you use them on real numbers. Practise SUMIF in a live spreadsheet: you type the formula, and it checks it instantly. Then practise ROUND, ROUNDUP and ROUNDDOWN, and put it all together in the department budget practice, which combines SUM, AVERAGE, ROUND and absolute references. For a timed check, try the finance Excel mock test.