Excel for Accounting Students: Skills to Learn Before Work

Excel for accounting students comes down to a short list of skills you will use in your first week at work: totals by account with SUMIF, lookups with VLOOKUP or XLOOKUP, ROUND for exact cents, and checks that show when two lists don't agree. Your courses teach the accounting; this guide shows the Excel side, with one small ledger you can rebuild yourself. It's for students heading into internships, graduate roles or an employer's Excel test.

Which Excel skills accounting students need#

Accounting work in Excel is mostly totalling, matching and checking. These skills cover most of it:

You don't need macros or advanced modelling to start. Employers mostly want these basics done quickly and without errors. For the wider list beyond accounting, see the Excel skills for finance jobs.

SUMIF and SUMIFS: totals by account#

SUMIF adds the numbers that meet one condition, which is exactly how you get an account balance from journal lines. Here is a small journal:

The Cash balance is Cash debits minus Cash credits:

=SUMIF(B2:B9,"Cash",C2:C9)-SUMIF(B2:B9,"Cash",D2:D9)

That's 5,850 − 1,500 = 4,350. The same pattern, pointed at a list of account names with $ signs on the ranges, gives every balance in a trial balance. Then check that it balances: =SUM(C2:C9)-SUM(D2:D9) should be 0 (7,350 − 7,350). For two conditions, such as account and month, use SUMIFS. The SUMIF function guide covers every argument.

VLOOKUP and XLOOKUP: match accounts and invoices#

Lookups pull a value from another list by a shared key, such as an account code or invoice number. With a chart of accounts in G2:H20 (codes in G, names in H), this returns the name for the code in A2:

=VLOOKUP(A2,$G$2:$H$20,2,FALSE)

FALSE means exact match, which you almost always want with codes. Without it, VLOOKUP can return the wrong account quietly. XLOOKUP is the modern version, =XLOOKUP(A2,$G$2:$G$20,$H$2:$H$20), and needs Excel 2021 or Microsoft 365. Learn both: many workbooks and tests still use VLOOKUP. The VLOOKUP function guide explains #N/A errors, which usually mean the code isn't in the list or has an extra space.

ROUND: make the cents add up#

Formatting a cell to two decimals only changes what you see. Excel still keeps every decimal behind it, and those hidden fractions can make totals differ by a cent. ROUND changes the value itself:

=ROUND(1000/3,2)

This returns 333.33. Here is the classic allocation problem: split 1,000 of rent equally across three departments. Three rounded shares of 333.33 total 999.99, one cent short. The usual fix is to round the first two shares (in B2:B3) and let the last one take the difference: =1000-SUM(B2:B3) in B4 gives 333.34. The total is then exactly 1,000.00. The ROUND function guide also covers ROUNDUP and ROUNDDOWN.

Reconciliations: find what doesn't match#

A reconciliation compares two lists that should agree, such as your cash book and the bank statement, and explains the differences. In Excel, that's a matching problem. With your book amounts in B2:B20 and the bank's amounts in F2:F20:

=IF(COUNTIF($F$2:$F$20,B2)=0,"Not on bank","OK")

Fill it down and every item missing from the bank list is flagged. Common causes are timing differences, such as deposits in transit, and typing errors. For matching on a reference number instead of an amount, use XLOOKUP or VLOOKUP and look for #N/A. The bank reconciliation guide walks through a full example, and Excel skills for accounts payable shows the same matching on supplier invoices.

Pivot tables, formatting and shortcuts#

Three more skills make you faster than most new starters:

• Pivot tables summarize thousands of lines by account, month or supplier in seconds: Insert > PivotTable. Start with the pivot table beginner guide.
• Accounting number format lines up currency symbols and shows negatives in parentheses if you choose. Find it under Home > Number Format > Accounting.
• Absolute references. $G$2:$H$20 stays fixed when you fill a formula down. Press F4 on Windows (Mac: ⌘T) to add the $ signs while editing a reference.

Learn a handful of shortcuts too: Ctrl+Arrow to jump to the end of data (Mac: ⌘+Arrow), Ctrl+Shift+Arrow to select it (Mac: ⌘⇧+Arrow), and Alt+= for AutoSum (Mac: ⌘⇧T).

How to practise before your first job#

Practise the way the work arrives: a small, messy list and a question to answer.

1. Rebuild coursework in Excel. Journals, a trial balance, a depreciation schedule and a simple bank reconciliation are all good projects.
2. Time yourself. Many employers use timed Excel tests. Try the finance Excel mock test and read what to expect in an Excel test for accountants.
3. Find your level first. The Excel level test shows which of these skills you already have and where to start.
4. Learn the bookkeeping side too. Excel skills for bookkeepers and Excel skills for fresh graduates cover the everyday tasks new starters get first.

Practise it#

The three skills accounting teams test most are conditional totals, lookups and rounding. Practise SUMIF in a live spreadsheet: you type the formula and it checks it instantly. Then practise VLOOKUP with an exact match and practise ROUND, ROUNDUP and ROUNDDOWN. Or work out the Cash balance in the exercise on this page.